幾個小時以來，我一直在嘗試和谷歌搜索，但沒有找到有效的解決方案。這是我的問題：
我有一個具有以下結構的表：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
可悲的是，它給出了錯誤的連續天數，并且計算的天數甚至與開始/結束日期不匹配。
非常感謝您的想法
馬丁
uj5u.com熱心網友回復：
這是一個差距和島嶼問題。您可以使用傳統的解決方案：
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
uj5u.com熱心網友回復：
這被稱為間隙和孤島問題。解決此問題的一種典型方法是對行進行編號（此處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&fiddle=f89e579db31ffd956fdea5d437625b68
如果您發現它更具可讀性，您可以添加一個步驟：首先使行不同，然后遍歷行并獲取位置和行號的差異（然后您可以使用ROW_NUMBER而不是 獲得DENSE_RANK），然后聚合。
uj5u.com熱心網友回復：
這些是來自@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 的解決方案
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482486.html
標籤：sql PostgreSQL