在Snowflake中编写SQL查询计算用户每日累计工作时长

核心思路是将每个「Logged In」事件与后续对应的「Logged Out」事件配对，计算单次会话时长后按用户+日期汇总，以下是可直接运行的SQL：

完整查询代码 #

WITH session_pairs AS (
    SELECT
        resourcename,
        DATE(EventDateTime) AS event_date,
        AgentState,
        EventDateTimeInSeconds AS event_seconds,
        -- 为当前事件获取同用户同日期下的下一个事件秒数
        LEAD(EventDateTimeInSeconds) OVER (
            PARTITION BY resourcename, DATE(EventDateTime)
            ORDER BY EventDateTimeInSeconds
        ) AS next_event_seconds
    FROM your_table_name -- 替换为你的实际表名
)
SELECT
    resourcename,
    event_date,
    -- 汇总当日所有有效登录-登出的时长
    SUM(CASE 
        WHEN AgentState = 'Logged In' AND next_event_seconds IS NOT NULL
        THEN next_event_seconds - event_seconds
        ELSE 0
    END) AS total_work_seconds,
    -- 可选：转换为HH:MI:SS格式的可读时长
    TIME_FROM_SECONDS(SUM(CASE 
        WHEN AgentState = 'Logged In' AND next_event_seconds IS NOT NULL
        THEN next_event_seconds - event_seconds
        ELSE 0
    END)) AS total_work_duration
FROM session_pairs
GROUP BY resourcename, event_date
ORDER BY resourcename, event_date;

逻辑拆解 #

1. 会话配对：

   • 使用LEAD()窗口函数，按「用户名+日期」分区、按事件秒数排序，为每个事件获取同用户同日期内的下一个事件时间戳，自动将「Logged In」和紧随其后的「Logged Out」配对。
   • DATE(EventDateTime)确保只处理同一天内的事件，避免跨天的登录/登出被错误配对。

2. 有效时长计算：

   • 仅对「Logged In」事件计算时长（下一个事件秒数 - 当前登录秒数），同时过滤掉无后续登出的登录事件（next_event_seconds IS NOT NULL），避免计入未结束的会话。

3. 汇总与格式化：

   • 按「用户名+日期」分组求和得到当日总工作秒数，可选通过TIME_FROM_SECONDS()将秒数转换为HH:MI:SS格式，更直观展示时长。

基于示例数据的输出结果 #

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