如何在MySQL中按周统计给定起止日期的活跃事件数量？

没问题，完全可以实现按周统计活跃事件数量，包括那些跨多个周的事件——核心是要先生成目标年份的所有周的日期范围，再判断每个事件是否和这些周有时间重叠，最后分组计数。

问题分析 #

你遇到的核心问题是直接用WEEK(Start_Date)只能统计事件开始所在的周，但跨周的事件在后续周的活跃状态没被捕获。我们需要的逻辑是：只要事件的时间范围和某一周有任何重叠（哪怕只重叠一天），这个事件就要被计入该周的活跃数。

具体实现代码 #

假设你的表名为events，下面是针对2019年数据的完整SQL：

-- 生成2019年所有周的日期范围（按MySQL WEEK模式1：周一为一周起始，周编号1-53）
WITH weekly_ranges AS (
    SELECT 
        WEEK(week_start, 1) AS week_num, -- 指定WEEK模式，确保和你的定义一致
        week_start,
        DATE_ADD(week_start, INTERVAL 6 DAY) AS week_end
    FROM (
        -- 生成2019年的所有日期，筛选出每周的周一作为周起始
        SELECT 
            DATE_ADD('2019-01-01', INTERVAL seq DAY) AS week_start
        FROM (
            SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
            UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
            UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
            UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
            UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
            UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
            UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34
            UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39
            UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44
            UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49
            UNION ALL SELECT 50 UNION ALL SELECT 51 UNION ALL SELECT 52
        ) AS sequence
        WHERE 
            DATE_ADD('2019-01-01', INTERVAL seq DAY) <= '2019-12-31'
            AND DAYOFWEEK(DATE_ADD('2019-01-01', INTERVAL seq DAY)) = 2 -- 筛选周一（DAYOFWEEK返回2代表周一）
    ) AS weeks
)
-- 关联事件表统计每周活跃事件数
SELECT 
    CONCAT('2019第', wr.week_num, '周') AS week_label,
    COUNT(DISTINCT e.ID) AS active_events_count
FROM 
    weekly_ranges wr
LEFT JOIN 
    events e ON e.Start_Date <= wr.week_end AND e.End_Date >= wr.week_start
GROUP BY 
    wr.week_num, week_label
ORDER BY 
    wr.week_num;

代码解释 #

1. 生成周范围CTE：

   • 先通过序列生成2019年的所有周一（周起始），再计算出对应的周结束日期（周一+6天=周日）。
   • WEEK(week_start, 1)指定周模式为1，确保和你使用的WEEK()函数行为一致（如果你的周是周日起始，把模式改成0，同时DAYOFWEEK判断改成1）。

2. 关联统计：

   • LEFT JOIN的条件e.Start_Date <= wr.week_end AND e.End_Date >= wr.week_start是判断事件和周是否有时间重叠的关键——只要事件的开始不晚于周结束，且结束不早于周开始，就说明这个事件在该周内是活跃的。
   • COUNT(DISTINCT e.ID)确保同一个事件在同一个周内不会被重复计数（比如事件覆盖整个周的情况）。

调整说明 #

• 如果你的表名不是events，记得替换成实际表名。
• 如果周的起始定义不同（比如周日为第一天），修改DAYOFWEEK的判断值（1代表周日）和WEEK()函数的模式参数即可。

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