主页 / dba / 问题 / 316064
Accepted
9001_db
Asked: 2022-08-26 08:20:58 +0800 CST

聚合重叠的日期间隔

  • 772

我有一个包含一些帐户的表格，以及他们订阅的开始和结束日期。但是，这些订阅有时会重叠，我需要每个连接订阅期的开始和结束日期。就像在示例图像中一样。

我尝试将订阅期与日期参考表合并，并在有订阅时标记日期。然而，代码变得相当复杂。我想一定有一个更简单的解决方案。

在此处输入图像描述

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')
sql-server aggregate

1 个回答

  1. Best Answer

    直接地：

    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

    • 5

相关问题