基于日期重叠的SQL表转换实现方案问询

我是SQL新手，研究中需要处理一张记录人员id、物品item获取日期date及使用时长use的表，希望基于每个人员的日期重叠情况转换表结构，得到按区间划分的并发物品使用记录。

现有表结构及数据 #

期望输出表结构及数据 #

已完成的初步工作 #

我已经完成了将日期转换为相对起始日的初步查询，但不知道如何继续实现区间合并与物品合并，已尝试的SQL代码如下：

-- Create a dummy table
CREATE TABLE tbl (
  id int,
  start_date date,
  day_of_use int,
  item varchar(8)
);

-- Populating table with sample data
INSERT INTO tbl VALUES (1, "2023-02-01", 90, "A");
INSERT INTO tbl VALUES (1, "2023-03-02", 10, "B");
INSERT INTO tbl VALUES (1, "2023-03-15", 15, "C");
INSERT INTO tbl VALUES (2, "2023-02-05", 10, "B");
INSERT INTO tbl VALUES (2, "2023-02-13", 30, "A");

-- Querying the overlap
SELECT
  a.id,
  DATEDIFF(a.start_date, b.init) + 1 AS start,
  day_of_use,
  DATEDIFF(a.start_date, b.init) + day_of_use AS end,
  item
FROM
  tbl AS a
  LEFT JOIN
  (
    SELECT id, MIN(start_date) AS init
    FROM tbl
    GROUP BY id
    ORDER BY id
  ) AS b ON a.id = b.id;

后续实现方案 #

要实现区间合并和物品合并，我们可以通过提取关键分界点、生成连续区间、统计区间内物品、合并相同物品组合的连续区间这几个步骤完成，以下是适配MySQL的完整SQL代码：

WITH 
-- 步骤1：计算每个物品的相对起始/结束日，同时获取每个用户的最早日期
item_intervals AS (
    SELECT 
        t.id,
        DATEDIFF(t.start_date, user_init.init) + 1 AS rel_start,
        DATEDIFF(t.start_date, user_init.init) + t.day_of_use AS rel_end,
        t.item
    FROM tbl t
    JOIN (
        SELECT id, MIN(start_date) AS init 
        FROM tbl 
        GROUP BY id
    ) user_init ON t.id = user_init.id
),
-- 步骤2：提取所有关键日期点（每个物品的开始和结束+1，用于生成区间分界）
all_points AS (
    SELECT id, rel_start AS point FROM item_intervals
    UNION
    SELECT id, rel_end + 1 AS point FROM item_intervals
),
-- 步骤3：按用户分组，将相邻的点组成连续区间
continuous_intervals AS (
    SELECT 
        id,
        point AS interval_start,
        LEAD(point) OVER (PARTITION BY id ORDER BY point) - 1 AS interval_end
    FROM all_points
    ORDER BY id, point
),
-- 步骤4：过滤掉无效区间（end为null的是最后一个点之后的区间，无需保留）
valid_intervals AS (
    SELECT * 
    FROM continuous_intervals 
    WHERE interval_end IS NOT NULL
),
-- 步骤5：统计每个区间内的所有物品，用逗号拼接
interval_items AS (
    SELECT 
        vi.id,
        vi.interval_start AS start,
        vi.interval_end AS end,
        GROUP_CONCAT(DISTINCT ii.item ORDER BY ii.item) AS items
    FROM valid_intervals vi
    JOIN item_intervals ii 
        ON vi.id = ii.id 
        AND vi.interval_start <= ii.rel_end 
        AND vi.interval_end >= ii.rel_start
    GROUP BY vi.id, vi.interval_start, vi.interval_end
),
-- 步骤6：合并连续的、物品组合相同的区间
final_result AS (
    SELECT 
        id,
        MIN(start) AS start,
        MAX(end) AS end,
        items AS item
    FROM (
        SELECT 
            *,
            -- 用累加分组：当物品组合变化时，分组ID+1
            SUM(CASE WHEN prev_items = items THEN 0 ELSE 1 END) OVER (PARTITION BY id ORDER BY start) AS group_id
        FROM (
            SELECT 
                *,
                LAG(items) OVER (PARTITION BY id ORDER BY start) AS prev_items
            FROM interval_items
        ) lagged
    ) grouped
    GROUP BY id, group_id, items
    ORDER BY id, start
)
SELECT id, start, end, item FROM final_result;

代码说明 #

1. item_intervals：统一计算每个物品相对于用户最早日期的起始和结束日，和之前的查询逻辑一致。
2. all_points：提取所有区间的起始点和结束点的下一天，这些点是区间的分界点。
3. continuous_intervals：用窗口函数LEAD将相邻的分界点组成连续的区间。
4. valid_intervals：过滤掉最后一个无效的区间（end为null）。
5. interval_items：关联每个区间和物品的使用区间，找出所有在该区间内生效的物品，用GROUP_CONCAT拼接成字符串。
6. final_result：用窗口函数判断连续区间的物品组合是否相同，合并相同组合的连续区间，得到最终结果。

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