聚合重疊的日期間隔

我有一個包含一些帳戶的表格，以及他們訂閱的開始和結束日期。但是，這些訂閱有時會重疊，我需要每個連接訂閱期的開始和結束日期。就像在範例圖像中一樣。

我嘗試將訂閱期與日期參考表合併，並在有訂閱時標記日期。然而，程式碼變得相當複雜。我想一定有一個更簡單的解決方案。

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

引用自：https://dba.stackexchange.com/questions/316064