sql - 确定postgres中一系列日期的连续长度

Question

是否有一种直接的方法来确定 Postgres 中日期序列的连续长度？

为简单起见，假设我有一个日期表，如下所示:

2017-01-01
2017-01-02
2017-01-03
2017-01-04
2017-01-06
2017-01-08
2017-01-09

对于每个日期，我想知道它在连续日期序列中的计数。所以我想要:

2017-01-01 1
2017-01-02 2
2017-01-03 3
2017-01-04 4
2017-01-06 1
2017-01-08 1
2017-01-09 2

我想我需要使用一个过程并循环游标来保持计数，但我想知道是否有更聪明的方法？我花了一些时间查看窗口函数，但没有看到任何符合要求的东西。

Answer 1

您可以通过减去序号来识别序列。常量值代表一个序列。在此之后，您的枚举是 row_number() 的应用:

select t.date,
       row_number() over (partition by date - seqnum * interval '1 day' order by date) as col2
from (select t.*,
             row_number() over (order by date) as seqnum
      from t
     ) t;