Redshift SQL按用户活动日期间隔分桶存入三类临时表实现方法

实现思路 #

• 首先对每个用户的活动记录按活动日期排序，计算两个核心间隔值：
  • 相邻两次活动的间隔天数：用于判断是否为连续次日活跃
  • 单次活动与该用户首次活动的间隔天数：用于判断是否为周度活跃
• 按优先级分桶：连续次日活跃（表1）> 周度活跃（表2）> 其余（表3）

具体SQL实现 #

首先通过CTE加工出所需的间隔字段，再按标签生成三张临时表：

WITH user_activity_prep AS (
    SELECT
        User_id,
        "User Name",
        Join_date,
        Activity_date,
        -- 字符串格式活动日期转日期类型，若源字段已是日期类型可删除该转换
        TO_DATE(Activity_date, 'DD-MM-YYYY') AS activity_dt,
        -- 取当前用户上一次活动日期
        LAG(TO_DATE(Activity_date, 'DD-MM-YYYY')) OVER (PARTITION BY User_id ORDER BY TO_DATE(Activity_date, 'DD-MM-YYYY')) AS prev_activity_dt,
        -- 取当前用户首次活动日期
        FIRST_VALUE(TO_DATE(Activity_date, 'DD-MM-YYYY')) OVER (PARTITION BY User_id ORDER BY TO_DATE(Activity_date, 'DD-MM-YYYY')) AS first_activity_dt
    FROM your_original_table_name -- 替换为实际源表名称
),
user_bucket_tag AS (
    SELECT
        User_id,
        "User Name",
        Join_date,
        Activity_date,
        DATEDIFF(day, prev_activity_dt, activity_dt) AS day_gap_from_prev,
        DATEDIFF(day, first_activity_dt, activity_dt) AS day_gap_from_first,
        -- 分桶标签：1对应表1，2对应表2，3对应表3
        CASE
            -- 连续次日活跃判定：包含连续序列的首条活动记录
            WHEN (day_gap_from_prev = 1 OR (day_gap_from_prev IS NULL AND LEAD(day_gap_from_prev) OVER (PARTITION BY User_id ORDER BY activity_dt) = 1)) THEN 1
            -- 周度活跃判定：距离首次活动间隔为6/7天的倍数区间
            WHEN MOD(day_gap_from_first,7) IN (0,6) THEN 2
            ELSE 3
        END AS bucket_tag
    FROM user_activity_prep
)
-- 生成临时表1：连续次日活跃用户记录
CREATE TEMP TABLE temporary_table1 AS
SELECT User_id, "User Name", Join_date, Activity_date
FROM user_bucket_tag
WHERE bucket_tag = 1
ORDER BY User_id, activity_dt;

-- 生成临时表2：周度活跃用户记录
CREATE TEMP TABLE temporary_table2 AS
SELECT User_id, "User Name", Join_date, Activity_date
FROM user_bucket_tag
WHERE bucket_tag = 2
ORDER BY User_id, activity_dt;

-- 生成临时表3：其余用户记录
CREATE TEMP TABLE temporary_table3 AS
SELECT User_id, "User Name", Join_date, Activity_date
FROM user_bucket_tag
WHERE bucket_tag = 3
ORDER BY User_id, activity_dt;

补充说明 #

如果需要调整分桶规则，直接修改user_bucket_tag中CASE语句的判定条件即可。

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