如何通过SQL视图聚合多行数据，计算登录时长与休息时长？

假设你的数据表名为person_actions，以下是创建目标视图的SQL语句：

CREATE VIEW person_time_summary AS
WITH action_pairs AS (
    SELECT
        Person_ID,
        Timestamp AS start_time,
        LEAD(Timestamp) OVER (PARTITION BY Person_ID ORDER BY Timestamp) AS end_time,
        Action AS start_action
    FROM person_actions
)
SELECT
    Person_ID,
    ROUND(SUM(CASE 
                WHEN start_action = 'LOGGED ON' 
                THEN EXTRACT(EPOCH FROM (end_time - start_time)) / 3600 
                ELSE 0 
             END), 1) AS LoggedInTime_hours,
    ROUND(SUM(CASE 
                WHEN start_action = 'ON BREAK' 
                THEN EXTRACT(EPOCH FROM (end_time - start_time)) / 3600 
                ELSE 0 
             END), 1) AS OnBreakTime_hours
FROM action_pairs
WHERE start_action IN ('LOGGED ON', 'ON BREAK')
GROUP BY Person_ID;

代码说明 #

1. CTE action_pairs：用LEAD窗口函数按Person_ID分组、Timestamp排序，为每个动作获取紧随其后的下一个动作时间戳，实现开始动作（LOGGED ON、ON BREAK）与对应结束动作的配对。
2. 筛选有效配对：通过WHERE子句仅保留作为"起始触发"的动作，避免无效的结束动作参与计算。
3. 时长计算与求和：
   • 对LOGGED ON配对，将时间差转为小时（秒数除以3600）后求和，得到总登录时长。
   • 对ON BREAK配对执行相同逻辑，得到总休息时长。
   • 用ROUND函数统一保留一位小数，匹配目标输出格式。

验证结果 #

针对提供的样本数据，视图输出结果如下：

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