求兼容Athena的优化SQL：按员工设备统计使用时段及时长

实现思路 #

利用窗口函数标记连续使用的设备会话，将同一员工、同一设备且扫描间隔在设定阈值内的记录归为一个使用周期，再通过聚合计算每个周期的起止时间及运行时长。

WITH session_markers AS (
    SELECT
        employee_id,
        device_id,
        scan_time,
        -- 标记新会话：首次扫描或与上一次扫描间隔超过阈值（示例为3600秒，可按需修改）
        CASE
            WHEN LAG(scan_time) OVER (PARTITION BY employee_id, device_id ORDER BY scan_time) IS NULL
                 OR DATE_DIFF('second', LAG(scan_time) OVER (PARTITION BY employee_id, device_id ORDER BY scan_time), scan_time) > 3600
            THEN 1
            ELSE 0
        END AS new_session
    FROM device_scans
    -- 可选：添加目标时间段过滤，缩小计算范围
    WHERE scan_time BETWEEN TIMESTAMP '2024-01-01 00:00:00' AND TIMESTAMP '2024-01-31 23:59:59'
),
session_groups AS (
    SELECT
        employee_id,
        device_id,
        scan_time,
        -- 累加新会话标记，生成唯一会话分组ID
        SUM(new_session) OVER (PARTITION BY employee_id, device_id ORDER BY scan_time) AS session_id
    FROM session_markers
)
SELECT
    employee_id,
    device_id,
    MIN(scan_time) AS start_time,
    MAX(scan_time) AS end_time,
    DATE_DIFF('second', MIN(scan_time), MAX(scan_time)) AS total_run_seconds
FROM session_groups
GROUP BY employee_id, device_id, session_id
ORDER BY employee_id, device_id, start_time;

关键配置与优化说明 #

• 会话中断阈值：修改DATE_DIFF('second', ...) > 3600中的数值，匹配业务中判定设备使用结束的间隔秒数（如10分钟无扫描则设为600）。
• 时间范围过滤：保留session_markers中的WHERE子句，限定查询的时间段，减少数据扫描量提升性能。
• Athena性能优化：确保表按employee_id、device_id或scan_time做分区，或创建相关的列式存储优化，降低查询延迟。

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