提问人：lamurian 提问时间：9/13/2023 最后编辑：lemonlamurian 更新时间：9/13/2023 访问量：58
基于日期重叠转换表
Transform a table based on date overlap
问：
问题陈述
您好，我是初学者 SQL 用户。我可以访问一个数据库进行研究，其中有一张感兴趣的表格结构如下：
id |
date |
use |
item |
|---|---|---|---|
| 1 | 2023-02-01 | 90 | 一个 |
| 1 | 2023-03-01 | 10 | B |
| 1 | 2023-03-15 | 15 | C |
| 2 | 2023-02-05 | 10 | B |
| 2 | 2023-02-13 | 30 | 一个 |
这是人员完成的采集表。我想根据每个人员的重叠来转换此表，但不太确定如何实现这一点。itemdateiddateid
所需输出
我想生成的表格如下：
id |
start |
end |
item |
|---|---|---|---|
| 1 | 1 | 29 | 一个 |
| 1 | 30 | 39 | 甲、乙 |
| 1 | 40 | 43 | 一个 |
| 1 | 44 | 58 | A、C |
| 1 | 59 | 90 | 一个 |
| 2 | 1 | 8 | B |
| 2 | 9 | 10 | 甲、乙 |
| 2 | 11 | 38 | 一个 |
粗略的想法
我的想法是：
我想转换表格，用作每个人员的起始索引
dateid这样，我们可以将转换后的 转换为 ，以便获得使用的相对结束日期为 + - 1
dateusestartuseidstartuseenditem1 1 90 90 一个 1 30 10 39 B 1 44 15 58 C 2 1 10 10 B 2 9 30 38 一个 从视觉上看，每个人员的物品获取过程将是：
人员1：
+--------------C (15) +---------B (10) +------------------------------------------------------------------------------------------A (90) +------------------------------+--------------+--------------------------------------------> 1 30 44 90人员2：
+--------------------------------A (30) +---------B (10) +-------+--------------------------------> 1 9 40最后，我可以按如下方式评估并发项目获取：
idstartenditem1 1 29 一个 1 30 39 甲、乙 1 40 43 一个 1 44 58 A、C 1 59 90 一个 2 1 8 B 2 9 10 甲、乙 2 11 38 一个
我发现了什么
我发现了一些与这个问题相关的主题，包括：
我试过了什么
-- 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
;
虽然，我不确定如何从这里开始..任何想法将不胜感激，提前致谢。 SQL Fiddle：https://www.db-fiddle.com/f/eB1GnZTuuH5P71kr4pWqHu/2
答：
1赞
lemon
9/13/2023
#1
如果您使用的是 MySQL 8.0，可以尝试：
- 使用递归 CTE 展开您的值
- 将您的问题视为差距和孤岛问题
- 聚合以收集您的价值
为了解决间隙和孤岛问题，您可能希望使用连续记录中项目更改量的运行总和来重新创建分区。
WITH RECURSIVE cte AS (
SELECT id, start_date AS date_, day_of_use, item
FROM tbl
UNION ALL
SELECT id, DATE_ADD(date_, INTERVAL 1 DAY), day_of_use-1, item
FROM cte
WHERE day_of_use > 0
), cte2 AS (
SELECT id,
DENSE_RANK() OVER(PARTITION BY id ORDER BY date_) AS rn,
item
FROM cte
), cte3 AS (
SELECT id,
rn,
GROUP_CONCAT(item ORDER BY item) AS items,
LAG(GROUP_CONCAT(item ORDER BY item)) OVER(PARTITION BY id) AS prev_items
FROM cte2
GROUP BY id, rn
), cte4 AS (
SELECT id, rn, items,
COUNT(CASE WHEN prev_items != items THEN 1 END) OVER(PARTITION BY id ORDER BY rn) AS parts
FROM cte3
)
SELECT id, MIN(rn) AS start_, MAX(rn) AS end_, items
FROM cte4
GROUP BY id, items, parts
"输出“：
| 编号 | 项目 | 开始_ | 结束_ |
|---|---|---|---|
| 1 | 一个 | 1 | 29 |
| 1 | 答，乙 | 30 | 40 |
| 1 | 一个 | 41 | 42 |
| 1 | 答，C | 43 | 58 |
| 1 | 一个 | 59 | 91 |
| 2 | B | 1 | 8 |
| 2 | 答，乙 | 9 | 11 |
| 2 | 一个 | 12 | 39 |
在此处查看演示。
请注意，2023 年 2 月没有 29 天。
评论
0赞
lamurian
9/16/2023
谢谢@lemon，似乎它适合我的需求。但是，当我查询数据库时，它给了我一个错误，指出“窗口规范中没有订单列表”。我在 cte3 中添加了要查询的订单列表，如下所示： .小提琴工作正常：db-fiddle.com/f/sLSpeYXaLu64Nv86TvEQv7/4。不过，服务器仍然给我一个错误“组功能使用无效”。我不确定查询有什么问题，你能知道吗？lagLAG(GROUP_CONCAT(item)) OVER(PARTITION BY id ORDER BY rn)
1赞
lemon
9/16/2023
您可以尝试将聚合和窗口拆分为两个单独的 ctes （fiddle）。
1赞
lamurian
9/16/2023
谢谢！我将把它标记为已解决，目前它对我有用
评论