如何编写SQL合并连续的家庭电视设备会话记录？

你的思路方向是对的，但需要调整核心逻辑——我们要先给每个连续会话分配唯一的分组标识，再基于分组聚合得到合并后的完整会话。下面一步步拆解实现方法：

步骤1：标记新会话的起始行 #

对于同一个家庭（Household_ID）和同一台电视（TV_Set_ID），按时间排序后，如果当前行的Start_time不等于上一行的End_time，就说明这是一个新会话的开始。我们用窗口函数给这类行打标记，再通过累加生成分组ID：

WITH session_with_group AS (
    SELECT 
        Household_ID,
        TV_Set_ID,
        Start_time,
        End_time,
        -- 当当前行与上一行不连续时，生成新分组；连续则沿用之前的分组
        SUM(CASE WHEN LAG(End_time) OVER (PARTITION BY Household_ID, TV_Set_ID ORDER BY Start_time) = Start_time THEN 0 ELSE 1 END) 
        OVER (PARTITION BY Household_ID, TV_Set_ID ORDER BY Start_time) AS session_group
    FROM session
)

步骤2：按分组聚合合并会话 #

有了session_group这个唯一标识后，我们只需要按家庭、电视、分组ID聚合，取每组的最早开机时间和最晚关机时间即可：

SELECT 
    Household_ID,
    TV_Set_ID,
    MIN(Start_time) AS Start_time,
    MAX(End_time) AS End_time
FROM session_with_group
GROUP BY Household_ID, TV_Set_ID, session_group
ORDER BY Household_ID, TV_Set_ID, Start_time;

完整可运行代码 #

把两部分结合起来，完整SQL如下：

WITH session_with_group AS (
    SELECT 
        Household_ID,
        TV_Set_ID,
        Start_time,
        End_time,
        SUM(CASE WHEN LAG(End_time) OVER (PARTITION BY Household_ID, TV_Set_ID ORDER BY Start_time) = Start_time THEN 0 ELSE 1 END) 
        OVER (PARTITION BY Household_ID, TV_Set_ID ORDER BY Start_time) AS session_group
    FROM session
)
SELECT 
    Household_ID,
    TV_Set_ID,
    MIN(Start_time) AS Start_time,
    MAX(End_time) AS End_time
FROM session_with_group
GROUP BY Household_ID, TV_Set_ID, session_group
ORDER BY Household_ID, TV_Set_ID, Start_time;

逻辑说明 #

• LAG(End_time)：获取同一设备上一行的关机时间，用来判断当前行是否属于连续会话。
• 累加窗口函数SUM(...) OVER (...)：给每个新会话的起始行加1，连续的行会共享同一个session_group值，确保同一会话的所有行被归为一组。
• 最后用MIN和MAX提取会话的首尾时间，完美合并所有拆分的连续会话。

测试你的输入数据，这个查询会完全匹配你给出的预期输出。

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