最短的另一种方法是进行自我联接：
with grouped_result as( select sr.d, sum((fr.d is null)::int) over(order by sr.d) as group_number from tbl sr left join tbl fr on sr.d = fr.d + interval '1 day')select d, group_number, count(d) over m as consecutive_daysfrom grouped_resultwindow m as (partition by group_number)
输出：
d | group_number | consecutive_days ---------------------+--------------+------------------ 2012-04-28 08:00:00 | 1 | 3 2012-04-29 08:00:00 | 1 | 3 2012-04-30 08:00:00 | 1 | 3 2012-05-03 08:00:00 | 2 | 2 2012-05-04 08:00:00 | 2 | 2(5 rows)
实时测试：http：//www.sqlfiddle.com/#！1/93789/1
SR =第二行，FR
=第一行（或者前一行？銉）。基本上，我们正在执行向后跟踪，这是数据库不支持的模拟滞后
LAG（Postgres支持LAG，但解决方案很长，因为窗口不支持嵌套窗口）。因此，在此查询中，我们使用混合方法，通过联接模拟LAG，然后对其使用SUM窗口化，从而产生组号
更新
忘记了最终查询，上面的查询说明了组编号的基础，需要将其变形为：
with grouped_result as( select sr.d, sum((fr.d is null)::int) over(order by sr.d) as group_number from tbl sr left join tbl fr on sr.d = fr.d + interval '1 day')select min(d) as starting_date, max(d) as end_date, count(d) as consecutive_daysfrom grouped_resultgroup by group_number-- order by consecutive_days desc limit 1STARTING_DATE END_DATE CONSECUTIVE_DAYSApril, 28 2012 08:00:00-0700 April, 30 2012 08:00:00-0700 3May, 03 2012 08:00:00-0700 May, 04 2012 08:00:00-0700 2
更新
我知道为什么我的其他使用窗口函数的解决方案变长了，为什么我试图说明组编号和对组进行计数的逻辑又变长了。如果我像MySql方法那样追求目标，那么该窗口函数可能会更短。话虽如此，这是我以前的窗口函数方法，尽管现在更好：
with headers as( select d,lag(d) over m is null or d - lag(d) over m <> interval '1 day' as header from tbl window m as (order by d)) ,sequence_group as( select d, sum(header::int) over (order by d) as group_number from headers )select min(d) as starting_date,max(d) as ending_date,count(d) as consecutive_daysfrom sequence_groupgroup by group_number-- order by consecutive_days desc limit 1
实时测试：http：//www.sqlfiddle.com/#！1/93789/21
