Sql-Server
聚合重疊的日期間隔
我有一個包含一些帳戶的表格,以及他們訂閱的開始和結束日期。但是,這些訂閱有時會重疊,我需要每個連接訂閱期的開始和結束日期。就像在範例圖像中一樣。
我嘗試將訂閱期與日期參考表合併,並在有訂閱時標記日期。然而,程式碼變得相當複雜。我想一定有一個更簡單的解決方案。
IF OBJECT_ID('tempdb..#Subscriptions') IS NOT NULL DROP TABLE #Subscriptions CREATE TABLE #Subscriptions ( account_id varchar(1) ,start_date date ,end_date date ) INSERT INTO #Subscriptions (account_id, start_date, end_date) values ('A','2019-06-20','2019-06-29'), ('A','2019-06-25','2019-07-25'), ('A','2019-07-20','2019-08-26'), ('A','2019-12-25','2020-01-25'), ('A','2021-04-27','2021-07-27'), ('A','2021-06-25','2021-07-14'), ('A','2021-07-10','2021-08-14'), ('A','2021-09-10','2021-11-12'), ('B','2019-07-13','2020-07-14'), ('B','2019-06-25','2019-08-26')
直接地:
WITH cte1 AS ( SELECT account_id, start_date the_date, 1 weight FROM Subscriptions UNION ALL SELECT account_id, end_date, -1 FROM Subscriptions ), cte2 AS ( SELECT account_id, the_date, SUM(weight) OVER (PARTITION BY account_id ORDER BY the_date, weight DESC) weight FROM cte1 ), cte3 AS ( SELECT account_id, the_date, SUM(CASE WHEN weight = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY account_id ORDER BY the_date DESC) group_no FROM cte2 ) SELECT account_id, MIN(the_date) start_date, MAX(the_date) end_date FROM cte3 GROUP BY account_id, group_no ORDER BY 1,2
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=82f72d57e6c527c3ce59d166c675e48c