MySQL：用户自注册起每周消息量的动态统计方案问询

MySQL完全支持动态实现这个需求，不需要依赖Python等外部工具，核心是用**动态SQL（预处理语句）**自动生成适配周数的统计逻辑，同时需要先修正周数的计算逻辑（确保是用户注册后的相对周数，而非自然年周数）。

关键前提：修正周数计算逻辑 #

你原来的静态SQL中用WEEK(message_date)取自然年周数，这会导致不同用户的week_1对应不同的实际时间范围（比如A用户注册在1月，B用户注册在6月，两人的week_1不是各自注册后的第一周）。正确的做法是计算用户注册日到消息日期的相对周数：

TIMESTAMPDIFF(WEEK, user_join_date, message_date) + 1

• 若消息日期和注册日在同一周，结果为1（即week_1），以此类推。

动态实现方案：预处理语句生成动态统计SQL #

通过MySQL的递归CTE生成周数序列，再拼接成动态的统计SQL，自动适配所有需要统计的周数：

步骤1：设置GROUP_CONCAT长度（可选，周数较多时需要） #

SET SESSION group_concat_max_len = 1000000;

步骤2：计算全局最大需要统计的周数 #

SET @max_week = (
    SELECT MAX(TIMESTAMPDIFF(WEEK, user_join_date, CURDATE())) + 1
    FROM your_table
);

步骤3：生成动态CASE子句 #

WITH RECURSIVE week_nums AS (
    SELECT 1 AS week_num
    UNION ALL
    SELECT week_num + 1 FROM week_nums WHERE week_num < @max_week
)
SELECT GROUP_CONCAT(
    CONCAT(
        'SUM(CASE WHEN TIMESTAMPDIFF(WEEK, user_join_date, message_date) + 1 = ',
        week_num,
        ' THEN 1 ELSE 0 END) AS week_',
        week_num
    )
    SEPARATOR ', '
) INTO @case_clauses FROM week_nums;

步骤4：拼接并执行动态SQL #

SET @sql = CONCAT(
    'SELECT user_id, ', @case_clauses, '
    FROM your_table
    WHERE message_date >= user_join_date AND message_date <= CURDATE()
    GROUP BY user_id
    ORDER BY user_id;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明 #

• 该方案完全在MySQL内部执行，无需外部工具，会自动根据用户注册日到当前的最大周数生成对应的week_N列。
• 若部分用户没有某一周的消息，对应列会显示0。

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