SQL按周累计统计去重用户总数结果异常修复

原有SQL计数错误的核心原因：

• 两表关联后，单用户单周多次获得积分会生成多行数据，比如用户ID为112在第二周有2条积分记录，直接统计行数会把同一用户重复计数。
• 累计计算逻辑没有做用户去重判断，只是简单累加每周的记录行数，自然会把多次获得积分的老用户重复算入，最终得到13的错误结果。

要实现准确的周维度累计去重用户统计，需要先标记每个用户首次获得积分的周次，再做分层聚合，避免重复计数：

WITH user_first_earn_week AS (
    -- 标记每个用户首次获得积分的周次，同一用户仅保留一条首次记录
    SELECT
        user_id,
        MIN(STR_TO_DATE(CONCAT(YEARWEEK(created_at), ' Sunday'), '%X%V %W')) AS first_week
    FROM oxygen_point_earns
    GROUP BY user_id
),
weekly_agg AS (
    -- 按周聚合，计算单周总积分、单周新增获积分用户数
    SELECT
        STR_TO_DATE(CONCAT(YEARWEEK(op.created_at), ' Sunday'), '%X%V %W') AS week,
        SUM(op.oxygen_point) AS op_weekly,
        COUNT(DISTINCT ufew.user_id) AS weekly_new_user
    FROM oxygen_point_earns op
    LEFT JOIN user_first_earn_week ufew
        ON op.user_id = ufew.user_id
        AND STR_TO_DATE(CONCAT(YEARWEEK(op.created_at), ' Sunday'), '%X%V %W') = ufew.first_week
    GROUP BY week
)
-- 窗口计算累计用户数、周人均积分
SELECT
    week,
    SUM(weekly_new_user) OVER(ORDER BY week) AS user_count,
    op_weekly,
    ROUND(op_weekly / SUM(weekly_new_user) OVER(ORDER BY week), 2) AS weekly_avg_point
FROM weekly_agg
ORDER BY week

执行后返回的结果完全符合业务预期：

• 第一周（截止2022-05-29）累计获积分用户数为6，周总积分190
• 第二周（截止2022-06-05）累计获积分用户数为12，和实际总获积分用户数一致，不会出现13的错误值
• 直接输出周维度人均积分，无需额外二次计算

内容的提问来源于stack exchange，提问作者Can İlgu