PostgreSQL -连续日与min/max -表包含多个相等的天

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

Answer 2

这叫做gap &岛问题。解决这个问题的一个典型方法是对行进行编号(这里使用DENSE_RANK，因为日期还不是唯一的)，并将这些数字与相对位置进行比较。由于你方处理的是日期问题，我们的位置是从某一固定日期算起的天数。

select count(*), min(datum), max(datum)
from
(
select distinct 
datum,
datum - date '1900-01-01' - dense_rank() over (order by datum) as grp
from mytable
) grouped
group by grp
order by grp;

演示:https://dbfiddle.uk/?rdbms=postgres_14&小提琴= f89e579db31ffd956fdea5d437625b68

如果您发现它更具可读性，您可以添加一个步骤:首先使行不同，然后遍历行并获得位置和行号的差异(然后您可以使用ROW_NUMBER而不是DENSE_RANK获得)，然后聚合。

Answer 3

这些是来自@TheImpaler的和@Thorsten Kettner的答案的附加MySQL版本。小提琴

@TheImpaler的解决方案

select
datediff(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 datediff(datum, lag(datum) over(order by datum)) > 1 
then 1 else 0 end as i
from t
) x
) y
group by g;

@Thorsten Kettner的Solution

select 
count(*) as cnt,
min(datum) as date_min,
max(datum) as date_max
from
(
select distinct 
datum,
datediff(datum, date('1900-01-01')) - dense_rank() over (order by datum) as grp
from t order by datum
) grouped
group by grp
order by grp;