使用(雪花) sql基于重叠时间戳组合记录

Question

例如，我有一个数据集，该数据集由一个ID、一个开始时间和一个结束时间(ID、start_time、end_time)组成：

• (A，09，11)
• (B，10，12)

我需要使用sql代码将此数据集重新建模为以下格式(combined_ID、start_time、end_time)：

• (A，09，10)
• (A/B，10，11)
• (B，11，12)

有人能帮我吗？

Answer 1

我同意Mike Walton的观点，斯塔克溢出不是要求免费工作的地方，

但这太奇怪了我无法抗拒。

您的示例只有两个间隔，因此我不得不猜测如何处理任意数量的间隔。

这是我的行动：

WITH
  INPUT AS (SELECT * FROM VALUES ('A', 09, 11), ('B', 10, 12), ('C', 8, 11) T(ID, START_TIME, END_TIME)),
  SEGMENTS AS (
    SELECT
      SEGMENT_POINT SEGMENT_START,
      LEAD(SEGMENT_POINT, 1) OVER (ORDER BY SEGMENT_POINT) SEGMENT_END
    FROM (SELECT START_TIME SEGMENT_POINT FROM INPUT UNION SELECT END_TIME FROM INPUT)
  )
SELECT * FROM INPUT
UNION
SELECT
  LISTAGG(ID, '/') WITHIN GROUP (ORDER BY ID),
  SEGMENT_START,
  ANY_VALUE(SEGMENT_END)
FROM INPUT /* Intersect with SEGMENTS */
INNER JOIN SEGMENTS ON START_TIME < SEGMENT_END AND END_TIME > SEGMENT_START
GROUP BY SEGMENT_START HAVING COUNT(ID) > 1
ORDER BY 2, 1;

对于A和B，查询将输出上面列出的内容。添加(C，8，11)将输出：

ID     START_TIME   END_TIME
C               8         11
A               9         11
A/C             9         10
A/B/C          10         11
B              10         12