如何用SQL统计用户每周的登录天数频率（去重每日登录）

问题背景 #

现有数据表结构：

classroom_id(int): 教室ID
user_id (int): 用户ID
login_date(date): 登录日期
login_attempt_id(int): 唯一登录尝试ID

需求：统计用户每月内各周的登录天数频率——即一周内用户登录的不同天数（范围0-7），同一天多次登录仅计1次。输出需包含：

user_id(int): 用户ID
week: 周数（每月内的周序号）
frequency: 该周的登录天数频率

原SQL的问题 #

你写的SQL存在几个关键问题：

• date_format(login_date,'w') 取的是星期几（比如1代表周一，0代表周日），不是每月内的周数，会导致跨月的同星期被错误合并统计
• CTE里的distinct无效：因为带了唯一的login_attempt_id，每条登录尝试都会被保留，根本没实现"同一天多次登录仅计1次"的去重
• 统计用count(login_date)还是会把同一天的多条记录算进去，而且没关联月份，无法区分是哪个月的周

正确SQL实现 #

基础版（仅输出有登录记录的周） #

先对用户每日登录去重，再按用户、月份、当月周数统计登录天数：

WITH user_daily_login AS (
    -- 第一步：每个用户每天只保留一条记录，实现同一天多次登录仅计1次
    SELECT DISTINCT
        user_id,
        login_date,
        -- 提取年份+月份，确保按月份维度统计周数
        DATE_FORMAT(login_date, '%Y-%m') AS year_month,
        -- 提取当月的周数：%u表示周一为一周起始，周数从1开始；若需周日起始用%v
        DATE_FORMAT(login_date, '%u') AS week_of_month
    FROM your_table_name -- 替换为你的实际表名
)
SELECT
    user_id,
    -- 输出明确的周标识，比如"2024-05-W3"代表2024年5月第3周
    CONCAT(year_month, '-W', week_of_month) AS week,
    COUNT(DISTINCT login_date) AS frequency
FROM user_daily_login
GROUP BY user_id, year_month, week_of_month
ORDER BY user_id, year_month, week_of_month;

进阶版（输出每月所有周，无登录则frequency为0） #

如果需要强制输出用户每月的所有周（即使该周无登录也显示0），需要先生成用户-月份-周数的全量组合，再关联登录数据：

-- 生成所有可能的用户-月份-周数组合（每月最多5周）
WITH all_user_month_week AS (
    SELECT
        u.user_id,
        ym.year_month,
        w.week_of_month
    FROM
        (SELECT DISTINCT user_id FROM your_table_name) u
        CROSS JOIN (SELECT DISTINCT DATE_FORMAT(login_date, '%Y-%m') AS year_month FROM your_table_name) ym
        CROSS JOIN (SELECT 1 AS week_of_month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) w
),
-- 同基础版，先去重用户每日登录记录
user_daily_login AS (
    SELECT DISTINCT
        user_id,
        DATE_FORMAT(login_date, '%Y-%m') AS year_month,
        DATE_FORMAT(login_date, '%u') AS week_of_month,
        login_date
    FROM your_table_name
),
-- 统计各用户每月每周的实际登录天数
user_week_login AS (
    SELECT
        user_id,
        year_month,
        week_of_month,
        COUNT(DISTINCT login_date) AS frequency
    FROM user_daily_login
    GROUP BY user_id, year_month, week_of_month
)
-- 关联全量组合表，无登录记录的周用0填充
SELECT
    a.user_id,
    CONCAT(a.year_month, '-W', a.week_of_month) AS week,
    COALESCE(u.frequency, 0) AS frequency
FROM all_user_month_week a
LEFT JOIN user_week_login u
    ON a.user_id = u.user_id
    AND a.year_month = u.year_month
    AND a.week_of_month = u.week_of_month
ORDER BY a.user_id, a.year_month, a.week_of_month;

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