如何获取PostgreSQL中特定行的最接近日期

budadabubladend 发表于 Dev

布达巴布兰德

我有以下问题：

我有一个包含两种类型的数据的表。一个状态为1的状态，另一个状态为2的状态。对于状态为2的每一行，我都需要最后一个对应的状态为1的日期。这是一个示例：

date           state    id
'2016-04-03'   1        1
'2016-04-04'   1        1
'2016-04-05'   2        1
'2016-04-06'   2        1

预期结果：

date           lastdate       state    id
'2016-04-03'   '2016-04-03'   1        1
'2016-04-04'   '2016-04-04'   1        1
'2016-04-05'   '2016-04-04'   2        1
'2016-04-06'   '2016-04-04'   2        1
'2016-04-07'   '2016-04-07'   1        1

我不完全知道我被困在哪里。感觉到这种问题是您在寻找错误的方式，因此，我们将为您提供帮助。

提前谢谢！

编辑

看来我对问题的解释不够好，这是另一种尝试：

对于每一行，我需要具有状态= 1的具有相同ID的最后一行的日期。如果状态已经为1，则需要同一行的日期。

因此，为了澄清我的“预期结果”-表：

date           lastdate       state    id
'2016-04-03'   '2016-04-03'   1        1

状态是1，所以行的日期是状态1的最后一个日期

'2016-04-04'   '2016-04-04'   1        1

状态是1，所以行的日期是状态1的最后一个日期

'2016-04-05'   '2016-04-04'   2        1

状态为2，因此日期为1的最后一行的日期来自之前的行

'2016-04-06'   '2016-04-04'   2        1

状态为2，状态为1的最后日期为'2016-04-04'

'2016-04-07'   '2016-04-07'   1        1

状态再次为1，所以相同的日期

'2016-04-08'    '2016-04-07'  2        1

状态为2，因此设置了最后一行（之前的行）的日期

我希望我的意图现在可以理解。

克林

这可以通过将表与其自身连接来实现：

select distinct on (a.date) 
    a.date date, 
    coalesce(b.date, a.date) last_date, 
    a.state, 
    a.id
from test a
left join test b 
on a.state = 2 and b.state = 1 and b.date < a.date
order by a.date, b.date desc;

    date    | last_date  | state | id 
------------+------------+-------+----
 2016-04-03 | 2016-04-03 |     1 |  1
 2016-04-04 | 2016-04-04 |     1 |  1
 2016-04-05 | 2016-04-04 |     2 |  1
 2016-04-06 | 2016-04-04 |     2 |  1
 2016-04-07 | 2016-04-07 |     1 |  1
(5 rows)