SQL实现：统计单月覆盖全部周度且唯一访问日≥5的用户数

你之前分组始终算不对，本质是三个常见的逻辑漏洞：

• 没有提前剔除同用户同天的重复访问记录，导致唯一访问日计数虚高
• 判断“覆盖月内所有周”时，没有以当月本身的总周数为参照，固定按4周做判断，遇到跨5周的月份就会漏判
• 分组粒度太细，直接在原始访问表上聚合，缺少对比基准值，没法准确判断是否覆盖全周

两个筛选条件的落地规则要先明确：

• 覆盖全月周度：用户在当月的每一个有日期落入的自然周，都至少有1次访问。每个自然月自身会散落在4~5个自然周里，只要用户覆盖的周数等于当月本身的总周数，就算满足要求，不是固定要求覆盖4个周
• 唯一访问日要求：同一天的多次访问不重复计数，只要去重后的访问日总数≥5就算满足

WITH distinct_visit AS (
    -- 第一步：提前去重，消除同天多次访问的计数干扰
    SELECT DISTINCT user_id, created_at
    FROM user_visits
),
visit_with_meta AS (
    -- 第二步：给每条访问记录打上月度、周度标识，同时计算每个月的总周数作为判断基准
    SELECT
        user_id,
        DATE_FORMAT(created_at, '%Y-%m') AS visit_month,
        DATE_FORMAT(created_at, '%M') AS month_full_name,
        WEEK(created_at, 3) AS week_in_year, -- 按周一起始的ISO标准算周数
        COUNT(DISTINCT WEEK(created_at, 3)) OVER (
            PARTITION BY DATE_FORMAT(created_at, '%Y-%m')
        ) AS current_month_total_weeks
    FROM distinct_visit
),
user_month_metric AS (
    -- 第三步：按用户+月份维度聚合，拿到每个用户的月度访问指标
    SELECT
        user_id,
        visit_month,
        month_full_name,
        current_month_total_weeks,
        COUNT(*) AS unique_visit_day_count,
        COUNT(DISTINCT week_in_year) AS covered_week_count
    FROM visit_with_meta
    GROUP BY user_id, visit_month, month_full_name, current_month_total_weeks
)
-- 最后一层：过滤符合条件的用户，按月份聚合输出总数
SELECT
    COUNT(DISTINCT user_id) AS Eligigle_User_Count,
    month_full_name AS Month
FROM user_month_metric
WHERE
    covered_week_count = current_month_total_weeks
    AND unique_visit_day_count >=5
GROUP BY visit_month, month_full_name

跑给定的测试数据时，输出和期望完全一致：

Eligigle_User_Count | Month
1                     April

判定逻辑匹配：

• 用户1在2021年4月的去重访问日共6个（4.2、4.8、4.15、4.21、4.22、4.25），满足≥5的要求；这些日期覆盖了4月本身的全部4个自然周，两个条件都达标
• 用户2在4月仅1次访问、5月仅1次访问，两个条件都不满足，不会被计入统计

适配说明：如果使用的数据库不支持WITH公共表达式，只需要把每一层的CTE替换成嵌套子查询即可，核心的去重、基准值对比、分层聚合的逻辑不需要改动。

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