如何用Redshift SQL在Redash中统计间隔1小时内的用户会话？

要实现这个需求，核心是利用Redshift的窗口函数识别会话边界——当用户两次行为间隔超过1小时时，视为新会话的开始。下面是具体的实现步骤和完整SQL：

完整查询代码 #

WITH user_actions_ordered AS (
    -- 第一步：按用户和时间排序，确保行为顺序正确
    SELECT
        userID,
        datetime,
        -- 用LAG获取当前用户上一次行为的时间
        LAG(datetime) OVER (PARTITION BY userID ORDER BY datetime) AS prev_datetime
    FROM
        your_table_name  -- 替换成你的实际表名
),
session_boundaries AS (
    -- 第二步：标记新会话的起始点
    SELECT
        userID,
        datetime,
        -- 如果是第一次行为，或与上一次行为间隔超1小时，标记为新会话
        CASE
            WHEN prev_datetime IS NULL OR datetime - prev_datetime > INTERVAL '1 hour'
            THEN 1
            ELSE 0
        END AS is_new_session
    FROM
        user_actions_ordered
),
session_groups AS (
    -- 第三步：为每个行为分配会话ID（累计求和实现会话分组）
    SELECT
        userID,
        datetime,
        SUM(is_new_session) OVER (PARTITION BY userID ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
    FROM
        session_boundaries
)
-- 第四步：聚合得到每个会话的起止时间
SELECT
    userID,
    MIN(datetime) AS start_datetime,
    MAX(datetime) AS end_datetime
FROM
    session_groups
GROUP BY
    userID,
    session_id
ORDER BY
    userID,
    start_datetime;

代码拆解说明 #

让我一步步解释每个部分的作用：

1. user_actions_ordered：先对每个用户的行为按时间排序，用LAG()窗口函数抓取该用户上一次行为的时间戳，这是判断会话间隔的基础。
2. session_boundaries：通过比较当前行为与上一次行为的时间差，标记新会话起点——如果是用户的第一次行为，或者两次行为间隔超过1小时，就标记为1（新会话），否则为0。
3. session_groups：用SUM() OVER()的累计求和逻辑，为每个用户的行为分配唯一会话ID。每次遇到is_new_session=1时，求和结果递增，同一个会话内的所有行为会共享同一个session_id。
4. 最终聚合：按userID和session_id分组，取每组的最小时间作为会话开始时间，最大时间作为会话结束时间，得到完整的用户会话列表。

注意事项 #

• 确保你的datetime字段是Redshift支持的时间类型（如TIMESTAMP或TIMESTAMPTZ），否则时间差计算可能出错。
• 若表数据量较大，建议在userID和datetime字段上建立索引，提升查询性能。
• 对于只有单次行为的用户，start_datetime和end_datetime会是同一个值，这符合单个行为也属于独立会话的逻辑。

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