sql - PostgreSQL - 具有最小/最大值的连续天 - 表包含多个相等的天

Question

几个小时以来，我一直在尝试和谷歌搜索，但没有找到有效的解决方案。这是我的问题:

我有一个具有以下结构的表:id SERIAL, datum DATE, otherinfo VARCHAR(50)

数据可以有相同的日期条目:

id  datum      otherinfo
1   2019-12-28 testdata1
2   2019-12-28 testdata2
3   2019-12-29 testdata3
4   2019-12-29 testdata4
5   2019-12-31 testdata5-begin longest consecutive days
6   2019-12-31 testdata6
7   2020-01-01 testdata7
8   2020-01-01 testdata8
9   2020-01-02 testdata9
10  2020-01-03 testdata10
11  2020-01-04 testdata11
12  2020-01-04 testdata12
13  2020-01-05 testdata13-end longest consecutive days
14  2020-01-22 testdata14
15  2020-01-29 testdata15
16  2020-01-30 testdata16

我有兴趣获取开始日期和结束日期的连续天数。像这样的输出:

count | date MIN  | date MAX
6       2019-12-31  2020-01-05
2       2019-12-28  2019-12-29
2       2020-01-29  2020-01-30

我在 Stackoverflow 上找到了一些解决方法，但似乎总是与多个相同日期条目冲突。

我在以下 SQL 查询方面取得了最大的成功:

SELECT COUNT(*) -1 "count", MAX(datum), MIN(datum) FROM (SELECT *, date(datum) - row_number() OVER (PARTITION BY datum ORDER BY date(datum)) * INTERVAL '1 day' "filter" FROM table ) t1 GROUP BY filter HAVING COUNT(*) -1 > 0 ORDER BY count DESC

遗憾的是，它给出了错误的连续天数，并且计算的天数甚至与开始/结束日期不匹配。

非常感谢您的想法

马丁

Answer 1

这是一个差距和孤岛问题。您可以使用传统的解决方案:

select
  (max(datum) - min(datum)) + 1 as cnt,
  min(datum) as date_min,
  max(datum) as date_max
from (
  select x.*, sum(i) over(order by datum) as g
  from (
    select t.*,
      case when datum > lag(datum) over(order by datum) + 1 
           then 1 else 0 end as i
    from t
  ) x
) y
group by g