匹配起始日期与最近结束日期的SQL数据转换问题

原始表结构与数据 #

CREATE TABLE ActivityHX (
ITEM_ID         nvarchar(5),
LINE            nvarchar(2),
ACTIVITY_DATE   date,
ACTIVITY        nvarchar(8)
);

INSERT INTO ActivityHX (ITEM_ID, LINE, ACTIVITY_DATE, ACTIVITY)
VALUES
('38003', '1', '20230803', 'OPENED'),
('38003', '2', '20230803', 'CLOSED'),
('38003', '3', '20230812', 'REOPENED'),
('38003', '4', '20230814', 'CLOSED'),
('47155', '1', '20230805', 'OPENED'),
('47155', '2', '20230805', 'CLOSED'),
('47155', '3', '20230805', 'REOPENED'),
('47155', '4', '20230819', 'CLOSED');

原始数据查询结果：

目标格式 #

需要将数据转换为以下格式，每个条目对应一次开启到关闭的周期：

遇到的问题 #

数据逻辑为：多数条目仅有一组开/关日期，但存在多次重开的条目，且重开后紧跟关闭操作。尝试使用ROW_NUMBER()结合CASE语句转换数据时，仍会出现NULL值，无法压缩为目标的两行结构。

尝试的代码：

SELECT
 ITEM_ID
,CASE WHEN ACTIVITY IN ('OPENED', 'REOPENED') THEN ACTIVITY_DATE END 'START_DATE'
,CASE WHEN ACTIVITY = 'CLOSED' THEN ACTIVITY_DATE END 'END_DATE'
FROM (
SELECT
 ITEM_ID
,LINE
,ACTIVITY_DATE
,ACTIVITY
,Diff as 'Days Diff'
,ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY CASE WHEN Diff <= 0 then -1 else 1 end, abs(Diff)) as RN
from mydata) ActivityHX

补充场景：存在当日关闭后重开的数据，此类数据需匹配原始关闭日期而非对应关联的关闭日期。

解决方案 #

核心思路是为每个开启事件（OPENED/REOPENED）分配一个组号，然后将其与后续对应的关闭事件（CLOSED）配对。由于数据按LINE顺序排列，每个开启事件的对应关闭事件会被分到同一组中。

实现代码 #

WITH ActivityGroups AS (
    SELECT 
        ITEM_ID,
        ACTIVITY_DATE,
        ACTIVITY,
        -- 为每个开启事件生成组号：每遇到OPENED/REOPENED，组号递增
        SUM(CASE WHEN ACTIVITY IN ('OPENED', 'REOPENED') THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ITEM_ID ORDER BY LINE ASC) AS GroupId
    FROM ActivityHX
)
SELECT 
    ITEM_ID,
    MAX(CASE WHEN ACTIVITY IN ('OPENED', 'REOPENED') THEN ACTIVITY_DATE END) AS START_DATE,
    MAX(CASE WHEN ACTIVITY = 'CLOSED' THEN ACTIVITY_DATE END) AS END_DATE
FROM ActivityGroups
GROUP BY ITEM_ID, GroupId
ORDER BY ITEM_ID, START_DATE;

代码解释 #

1. CTE ActivityGroups：通过窗口函数SUM()，按ITEM_ID分组、LINE排序，每遇到OPENED或REOPENED就给组号加1，确保每个开启事件和对应的关闭事件在同一组。
2. 分组聚合：按ITEM_ID和GroupId分组，用MAX()提取组内的开启日期和关闭日期，每个组生成一行无NULL值的起止日期记录。
3. 处理补充场景：由于按LINE顺序生成组号，即使当日关闭后重开（如47155的2023-08-05），也会正确将第一次开启/关闭、第二次重开/关闭分为两组，完全匹配需求。

内容的提问来源于stack exchange，提问作者JPSeagull