SQL教程：如何在特定时间段内统计事件类别及其发生次数（包含零计数）

在实际业务场景中，我们经常需要统计某个时间段内不同类别的事件发生次数，比如统计某个月每天的订单数量、某段时间内各分类的访问次数等。很多时候，部分类别在对应时间段内没有发生事件，默认统计会遗漏这些类别，导致结果不完整。本文将介绍如何编写SQL语句，实现包含零计数的时间段内事件类别统计。

核心思路

要实现包含零计数的统计，核心逻辑分为三步：

• 生成完整的时间维度或类别维度范围，确保覆盖所有需要统计的时间段和类别
• 将原始事件表与维度表进行左连接，避免遗漏无事件的维度
• 对连接后的结果按维度和类别分组，使用条件统计函数计算符合时间范围的事件次数

示例场景说明

假设我们有如下两张表：

1. 事件表 event_log，存储所有事件发生记录，字段如下：

2. 事件类别字典表 event_type_dict，存储所有支持的类别，字段如下：

现在需要统计2024年5月1日到2024年5月3日，所有事件类别的发生次数，如果该类别在对应日期没有事件，次数显示为0。

实现步骤与代码

第一步：生成完整的时间范围

首先我们需要生成2024-05-01到2024-05-03的所有日期，这里使用递归CTE生成时间维度，不同数据库的时间生成方式略有差异，下面以MySQL 8.0+为例：

-- 生成2024-05-01到2024-05-03的日期序列
WITH RECURSIVE date_range AS (
    SELECT DATE('2024-05-01') AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE stat_date < '2024-05-03'
)
SELECT * FROM date_range;

执行上述语句会得到三行日期数据：2024-05-01、2024-05-02、2024-05-03，覆盖了我们需要统计的全部时间段。

第二步：关联时间维度和类别维度

接下来将时间维度与事件类别字典表进行交叉连接，得到所有「日期+类别」的组合，确保后续统计不会遗漏任何维度：

WITH RECURSIVE date_range AS (
    SELECT DATE('2024-05-01') AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE stat_date < '2024-05-03'
)
SELECT 
    d.stat_date,
    t.type_code,
    t.type_name
FROM date_range d
CROSS JOIN event_type_dict t;

交叉连接后会得到3天 × 3个类别 = 9条组合记录，每个日期下的每个类别都有一条独立记录。

第三步：左连接事件表并统计次数

将上面的组合结果与事件表进行左连接，连接条件要同时匹配日期、类别，并且事件时间在对应日期的范围内，最后按日期和类别分组，使用COUNT函数统计非空事件ID的数量，即可得到包含零计数的结果：

WITH RECURSIVE date_range AS (
    SELECT DATE('2024-05-01') AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE stat_date < '2024-05-03'
)
SELECT 
    d.stat_date AS 统计日期,
    t.type_code AS 类别编码,
    t.type_name AS 类别名称,
    COUNT(el.id) AS 事件发生次数
FROM date_range d
CROSS JOIN event_type_dict t
LEFT JOIN event_log el 
    ON el.event_type = t.type_code
    AND DATE(el.event_time) = d.stat_date
    -- 如果需要精确匹配时间段，也可以写成：
    -- AND el.event_time >= d.stat_date
    -- AND el.event_time < DATE_ADD(d.stat_date, INTERVAL 1 DAY)
GROUP BY d.stat_date, t.type_code, t.type_name
ORDER BY d.stat_date, t.type_code;

结果说明

上述查询返回的结果中，即使某个日期下某个类别没有对应的事件记录，由于使用了左连接，事件表字段会显示为NULL，COUNT(el.id)会统计为0，完美实现了包含零计数的统计需求。如果部分类别在字典表中不存在，只需要调整字典表的数据即可覆盖所有需要统计的类别。

不同数据库适配说明

如果是PostgreSQL数据库，生成时间范围的CTE可以调整为：

-- PostgreSQL生成日期序列
WITH date_range AS (
    SELECT generate_series(
        DATE('2024-05-01'),
        DATE('2024-05-03'),
        INTERVAL '1 day'
    )::DATE AS stat_date
)
SELECT * FROM date_range;

如果是SQL Server数据库，生成时间范围可以使用master..spt_values系统表或者递归CTE，递归CTE的写法与MySQL类似，只需要把DATE_ADD替换为DATEADD函数即可。

SQL统计时间范围统计左连接递归CTE零计数处理