MySQL技术问询：统计年度各月活跃事件数时出现重复结果

AI 小北

2026-7-10

问题描述

我有一张存储事件历史记录的MySQL表，包含超1000万条数据，表结构如下：

id   start_date   end_date
1    2023-01-01   2023-04-05
2    2023-01-06   2023-02-01
3    2023-04-01   2024-01-05

需求是统计每年每个月份的活跃事件数量（例如2023年1月应返回2个活跃事件，2023年4月也应返回2个），且已拥有包含每日、每月、每年数据的日历表。

尝试了以下查询，但结果出现大量重复行：

SELECT
    calendar.calendar_month,
    calendar.calendar_year,
    COUNT(*) AS active_events_count
from calendars calendar
LEFT JOIN
    events AS e
ON
    calendar.calendar_date BETWEEN DATE_FORMAT(e.start_date, '%Y-%m-01') AND DATE_FORMAT(e.end_date, '%Y-%m-01')
GROUP BY
     calendar.calendar_year,   calendar.calendar_month
ORDER BY
    calendar.calendar_year,   calendar.calendar_month

补充示例：
事件数据：

Id  start_date   end_date
10  2023-01-01   2023-05-31

执行查询：

SELECT
    calendar.calendar_month,
    calendar.calendar_year,
    e.start_date,
    e.end_date,
    e.id
from calendars calendar
LEFT JOIN
    events e
ON
    calendar.calendar_date BETWEEN DATE_FORMAT(e.start_date, '%Y-%m-01') AND DATE_FORMAT(e.end_date + INTERVAL 1 MONTH, '%Y-%m-01') - INTERVAL 1 DAY
where
e.start_date >= '2023-01-01'
and e.id = 10
ORDER BY
    calendar.calendar_year,   calendar.calendar_month, id

得到重复结果：

Id  calendar_year   calendar_month  start_date   end_date
10  2023            1               2023-01-13   2023-01-13
（重复31次）

解决方案

问题根源

你的查询用日历表的每日数据和事件表关联，导致一个覆盖整月的事件会和该月的31条（或对应天数）日历记录一一匹配，最终每条事件在对应月份会被重复统计多次，这就是重复行的原因。

优化方案

方案1：利用日历表的月度数据（推荐）

如果日历表包含月度维度的记录（比如每条记录对应一个年月，有calendar_year、calendar_month，以及该月的起始/结束日期），直接用月度数据关联，避免每日匹配：

SELECT
    cal.calendar_year,
    cal.calendar_month,
    COUNT(DISTINCT e.id) AS active_events_count
FROM calendars_monthly cal
LEFT JOIN events e
    ON e.start_date <= LAST_DAY(cal.calendar_date)
    AND e.end_date >= DATE_FORMAT(cal.calendar_date, '%Y-%m-01')
GROUP BY cal.calendar_year, cal.calendar_month
ORDER BY cal.calendar_year, cal.calendar_month;

如果只有每日日历表，也可以先提取每月唯一记录再关联：

SELECT
    cal.calendar_year,
    cal.calendar_month,
    COUNT(DISTINCT e.id) AS active_events_count
FROM (
    SELECT DISTINCT calendar_year, calendar_month, DATE_FORMAT(calendar_date, '%Y-%m-01') AS month_start, LAST_DAY(calendar_date) AS month_end
    FROM calendars
) cal
LEFT JOIN events e
    ON e.start_date <= cal.month_end
    AND e.end_date >= cal.month_start
GROUP BY cal.calendar_year, cal.calendar_month
ORDER BY cal.calendar_year, cal.calendar_month;

方案2：在原查询中去重统计

如果必须使用日历表的每日数据，统计时用COUNT(DISTINCT e.id)代替COUNT(*)，确保每个事件在对应月份只被统计一次：

SELECT
    calendar.calendar_year,
    calendar.calendar_month,
    COUNT(DISTINCT e.id) AS active_events_count
FROM calendars calendar
LEFT JOIN events e
    ON calendar.calendar_date BETWEEN e.start_date AND e.end_date
GROUP BY calendar.calendar_year, calendar.calendar_month
ORDER BY calendar.calendar_year, calendar.calendar_month;

注意：这个方法因为要处理每日数据关联，性能会比方案1差，对于1000万+数据的事件表，优先用方案1。

方案3：直接生成年月范围关联（无需日历表）

如果日历表只是用来生成年月维度，也可以直接用递归生成年月范围和事件表关联，避免依赖日历表：

WITH RECURSIVE year_months AS (
    SELECT '2023-01-01' AS month_start
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM year_months
    WHERE month_start <= '2024-12-01' -- 按需调整结束年月
)
SELECT
    YEAR(month_start) AS calendar_year,
    MONTH(month_start) AS calendar_month,
    COUNT(DISTINCT e.id) AS active_events_count
FROM year_months ym
LEFT JOIN events e
    ON e.start_date <= LAST_DAY(ym.month_start)
    AND e.end_date >= ym.month_start
GROUP BY YEAR(month_start), MONTH(month_start)
ORDER BY calendar_year, calendar_month;

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