查询2021年5月6-10日每日最活跃用户的SQL技术需求

核心问题是必须先生成目标日期范围内的所有日期，否则没有会话的日期不会出现在结果里。然后关联会话数据统计用户活跃度，最后筛选出每天的最活跃用户。

假设你的Sessions表结构如下（字段名可根据实际表结构调整）：

CREATE TABLE Sessions (
    session_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    session_start DATETIME
);

方法一：递归CTE生成日期序列 + 窗口函数排名 #

WITH date_range AS (
    -- 生成2021-05-06到2021-05-10的所有日期
    SELECT DATE('2021-05-06') AS target_date
    UNION ALL
    SELECT DATE_ADD(target_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE target_date < DATE('2021-05-10')
),
daily_user_sessions AS (
    -- 统计每天每个用户的会话数
    SELECT
        dr.target_date,
        s.user_name,
        COUNT(s.session_id) AS session_count
    FROM date_range dr
    LEFT JOIN Sessions s ON DATE(s.session_start) = dr.target_date
    GROUP BY dr.target_date, s.user_name
),
ranked_users AS (
    -- 对每天的用户按会话数降序排名，会话数相同的排名一致
    SELECT
        target_date,
        user_name,
        session_count,
        RANK() OVER (PARTITION BY target_date ORDER BY session_count DESC) AS rnk
    FROM daily_user_sessions
)
-- 筛选排名第一的用户，无会话时user_name显示为null
SELECT
    target_date,
    CASE WHEN session_count = 0 THEN NULL ELSE user_name END AS most_active_user
FROM ranked_users
WHERE rnk = 1;

关键说明 #

• 递归CTE date_range：生成指定范围内的所有日期，确保即使某天没有会话，也能出现在结果中。
• LEFT JOIN：关联日期序列和会话表，保证日期不丢失，无会话的日期对应的user_name和session_count会是null/0。
• 窗口函数RANK()：处理同一天多个用户会话数相同的情况（若有并列第一，会返回多个结果；若要仅返回一个，可改用ROW_NUMBER()，但会随机选取其一）。

方法二：使用现成日历表（若数据库中有） #

如果你的数据库有包含日期字段的日历表（比如calendar表），可简化日期生成步骤：

WITH daily_user_sessions AS (
    SELECT
        c.date AS target_date,
        s.user_name,
        COUNT(s.session_id) AS session_count
    FROM calendar c
    LEFT JOIN Sessions s ON DATE(s.session_start) = c.date
    WHERE c.date BETWEEN '2021-05-06' AND '2021-05-10'
    GROUP BY c.date, s.user_name
),
ranked_users AS (
    SELECT
        target_date,
        user_name,
        session_count,
        RANK() OVER (PARTITION BY target_date ORDER BY session_count DESC) AS rnk
    FROM daily_user_sessions
)
SELECT
    target_date,
    CASE WHEN session_count = 0 THEN NULL ELSE user_name END AS most_active_user
FROM ranked_users
WHERE rnk = 1;

处理并列第一的情况 #

如果需要把同一天并列第一的用户合并显示（用逗号分隔），可改用GROUP_CONCAT结合窗口函数：

WITH date_range AS (
    SELECT DATE('2021-05-06') AS target_date
    UNION ALL
    SELECT DATE_ADD(target_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE target_date < DATE('2021-05-10')
),
daily_user_sessions AS (
    SELECT
        dr.target_date,
        s.user_name,
        COUNT(s.session_id) AS session_count
    FROM date_range dr
    LEFT JOIN Sessions s ON DATE(s.session_start) = dr.target_date
    GROUP BY dr.target_date, s.user_name
),
max_session_counts AS (
    SELECT
        target_date,
        MAX(session_count) AS max_count
    FROM daily_user_sessions
    GROUP BY target_date
)
SELECT
    m.target_date,
    CASE WHEN m.max_count = 0 THEN NULL ELSE GROUP_CONCAT(DISTINCT d.user_name) END AS most_active_users
FROM max_session_counts m
LEFT JOIN daily_user_sessions d ON m.target_date = d.target_date AND d.session_count = m.max_count
GROUP BY m.target_date;

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