按月打用户访问标签的SQL优化及结果实现咨询

数据源 #

问题 #

需求是给用户访问记录打标：用户首次访问当月标记为FIRST，后续3个月内有访问标记为RETENTION，间隔3个月未访问后再次回访标记为REACTIVATE。
当前使用的查询是通过笛卡尔积生成所有用户和月份的组合，再通过lateral join关联获取每月最新访问记录：

select u.user_id, gs.yyyymm, s.last_visit_date
from (select distinct user_id from source s) u cross join
     generate_series('2021-01-01'::timestamp, '2021-12-01'::timestamp, interval '1 month'
                    ) gs(yyyymm) left join lateral
     (select max(s.visit_date) as last_visit_date
      from source s
      where s.user_id = u.user_id and
            s.visit_date >= gs.yyyymm and
            s.visit_date < gs.yyyymm + interval '1 month'
     ) s
     on 1=1;

该方案在用户量增长后性能下降明显，需要优化方案实现以下两类预期结果：

明细结果 #

聚合格式结果 #

优化方案 #

核心思路 #

原方案性能差的核心原因是对每个用户、每个月份都单独扫描一次源表做聚合，用户量和时间范围越大，扫描成本越高。优化方向是先一次性聚合所有用户的月度访问记录，再用窗口函数计算标签，最后补全缺失月份，全程仅需扫描一次源表。

实现代码 #

1. 明细结果查询

WITH user_monthly_visits AS (
    -- 第一步：按用户、访问月份聚合，去重得到每个用户每个有访问的月份记录
    SELECT 
        user_id,
        date_trunc('month', visit_date) AS visit_month
    FROM source
    -- 可按需添加时间过滤条件，减少扫描数据量
    -- WHERE visit_date >= '2021-01-01' AND visit_date < '2022-01-01'
    GROUP BY user_id, date_trunc('month', visit_date)
),
user_visit_tags AS (
    -- 第二步：用窗口函数给每个有访问的月份打标
    SELECT 
        user_id,
        visit_month,
        CASE 
            -- 首次访问标记为FIRST
            WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_month) = 1 THEN 'FIRST'
            -- 和上一次访问间隔小于等于3个月标记为RETENTION
            WHEN EXTRACT(YEAR FROM visit_month)*12 + EXTRACT(MONTH FROM visit_month) 
                 - (EXTRACT(YEAR FROM LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month))*12 
                    + EXTRACT(MONTH FROM LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month))) <= 3 THEN 'RETENTION'
            -- 间隔超过3个月回访标记为REACTIVATE
            ELSE 'REACTIVATE'
        END AS tag
    FROM user_monthly_visits
),
all_months AS (
    -- 生成目标时间范围内的所有月份
    SELECT generate_series('2021-01-01'::timestamp, '2021-12-01'::timestamp, interval '1 month') AS yyyymm
),
user_full_month AS (
    -- 补全所有用户和月份的组合，关联打标结果
    SELECT 
        u.user_id,
        am.yyyymm,
        uvt.tag
    FROM (SELECT DISTINCT user_id FROM source) u
    CROSS JOIN all_months am
    LEFT JOIN user_visit_tags uvt 
        ON u.user_id = uvt.user_id 
        AND am.yyyymm = uvt.visit_month
)
-- 输出明细结果
SELECT 
    EXTRACT(MONTH FROM yyyymm) AS 月份,
    user_id AS 用户ID,
    tag AS 类型
FROM user_full_month
ORDER BY user_id, yyyymm;

2. 聚合结果查询

在上述明细查询的基础上，修改最后一段的输出逻辑即可：

SELECT 
    EXTRACT(MONTH FROM yyyymm) AS 月份,
    COUNT(CASE WHEN tag = 'FIRST' THEN 1 END) AS First,
    COUNT(CASE WHEN tag = 'RETENTION' THEN 1 END) AS Retention,
    COUNT(CASE WHEN tag = 'REACTIVATE' THEN 1 END) AS Reactivate
FROM user_full_month
GROUP BY yyyymm
ORDER BY yyyymm;

额外性能优化建议 #

• 给源表source创建(user_id, visit_date)联合索引，可大幅加速第一步的分组聚合操作。
• 若仅需统计固定时间范围的结果，在第一步user_monthly_visits中添加visit_date的过滤条件，减少扫描的数据量。

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