SQL技巧：按用户和月份统计特定日期（如周六）的出现次数

2025-08-04

SQL技巧：按用户和月份统计特定日期（如周六）的出现次数

本文详细介绍了如何利用SQL查询，从包含账户和事件数据的表中，按每个用户和每个月份统计特定星期几（例如周六）的事件发生次数。教程将分步展示如何结合使用DAYOFWEEK函数进行日期筛选、GROUP BY进行分组聚合，并通过条件聚合（模拟PIVOT操作）将月份数据从行转换为列，最终生成清晰的统计报表，适用于数据分析和报表生成场景。

1. 理解数据结构与目标

在进行统计之前，我们首先需要了解数据结构和最终的统计目标。假设我们有两个mysql表：accounts 和 events。

accounts 表: 存储用户信息，包含 ID 和 name 字段。

+----+------+
| ID | name |
+----+------+
| 1  | Pete |
| 2  | Josh |
| 3  | Harry|
+----+------+

登录后复制

events 表: 存储事件信息，包含 ID, date (日期格式 YYYY-MM-DD), account_id 字段，其中 account_id 关联 accounts 表的 ID。

+----+------------+------------+
| ID | date       | account_id |
+----+------------+------------+
| 1  | 2021-10-09 | 1          |
| 2  | 2021-09-25 | 1          |
| ...| ...        | ...        |
+----+------------+------------+

登录后复制

我们的目标是生成一个报表，显示每个用户在每个月份中发生了多少个周六事件，报表结构如下：

+------+-----------+---------+----------+----------+
| Name | September | October | November | December |
+------+-----------+---------+----------+----------+
| Josh | 0         | 1       | 0        | 0        |
| Pete | 1         | 1       | 1        | 0        |
| Harry| 0         | 0       | 1        | 1        |
+------+-----------+---------+----------+----------+

登录后复制

2. 识别并初步统计周六事件

首先，我们需要从 events 表中筛选出所有周六发生的事件，并按用户和月份进行初步计数。MySQL提供了一个非常有用的函数 DAYOFWEEK()，它返回日期参数是星期几（1 = 星期日, 2 = 星期一, …, 7 = 星期六）。因此，周六对应的 DAYOFWEEK() 值为 7。

使用 WHERE DAYOFWEEK(date) = 7 来筛选周六事件，并使用 GROUP BY account_id, MONTH(date) 来按用户和月份进行分组计数。

SELECT 
    account_id,
    MONTH(date) AS month_number, 
    COUNT(*) AS saturday_count
FROM Events 
WHERE DAYOFWEEK(date) = 7
GROUP BY account_id, MONTH(date);

登录后复制

执行上述查询，您将得到一个中间结果集，例如：

+------------+--------------+----------------+
| account_id | month_number | saturday_count |
+------------+--------------+----------------+
| 1          | 9            | 1              |
| 1          | 10           | 1              |
| 1          | 11           | 1              |
| 2          | 10           | 1              |
| 3          | 11           | 1              |
| 3          | 12           | 1              |
+------------+--------------+----------------+

登录后复制

这个结果集已经包含了每个用户在每个月份的周六事件数量，但月份是以数字形式存在的，并且是多行数据，不符合最终报表所需的列式展示。

3. 将行数据转换为列数据（模拟PIVOT）

为了将上述中间结果转换为所需的列式报表（即月份作为列标题），我们需要进行“透视”（PIVOT）操作。在MySQL中，虽然没有像SQL Server或Oracle那样直接的 PIVOT 关键字，但我们可以通过条件聚合（Conditional Aggregation）来模拟这一功能。

我们将使用 SUM(CASE WHEN condition THEN value ELSE 0 END) 或更简洁的 SUM(condition)（在MySQL中布尔表达式为真时结果为1，为假时为0）来实现。

首先，我们可以使用公共表表达式（CTE，Common Table Expression，即 WITH 语句）来封装第一步的查询，提高代码的可读性。

WITH MonthlySaturdayCounts AS (
  SELECT 
      account_id,
      MONTH(date) AS month_number, 
      COUNT(*) AS saturday_count
  FROM Events 
  WHERE DAYOFWEEK(date) = 7
  GROUP BY account_id, MONTH(date)
)
SELECT 
    A.name,
    SUM(CASE WHEN MSC.month_number = 9 THEN MSC.saturday_count ELSE 0 END) AS September,
    SUM(CASE WHEN MSC.month_number = 10 THEN MSC.saturday_count ELSE 0 END) AS October,
    SUM(CASE WHEN MSC.month_number = 11 THEN MSC.saturday_count ELSE 0 END) AS November,
    SUM(CASE WHEN MSC.month_number = 12 THEN MSC.saturday_count ELSE 0 END) AS December
FROM Accounts AS A
LEFT JOIN MonthlySaturdayCounts AS MSC ON A.ID = MSC.account_id
GROUP BY A.ID, A.name
ORDER BY A.name;

登录后复制

代码解析：

WITH MonthlySaturdayCounts AS (…): 定义一个名为 MonthlySaturdayCounts 的CTE，它包含了我们第一步得到的按用户和月份统计的周六事件数量。
FROM Accounts AS A LEFT JOIN MonthlySaturdayCounts AS MSC ON A.ID = MSC.account_id:
- 我们从 Accounts 表开始，因为它包含了所有用户的名称，即使某些用户在特定月份没有周六事件，我们也希望他们在报表中显示（计数为0）。
- 使用 LEFT JOIN 确保所有用户都被包含在结果中。如果使用 INNER JOIN，则只会显示有周六事件的用户。
SUM(CASE WHEN MSC.month_number = 9 THEN MSC.saturday_count ELSE 0 END) AS September:
- 这是条件聚合的关键。对于每个用户，我们遍历他们所有月份的周六计数。
- CASE WHEN MSC.month_number = 9 THEN MSC.saturday_count ELSE 0 END：如果当前行的 month_number 是 9（九月），则取其 saturday_count 值；否则，取 0。
- SUM(…)：将所有符合条件的 saturday_count 值累加起来。由于其他月份的值是0，所以最终结果就是该用户在九月的周六总数。
- 对其他月份（10、11、12）也采用相同的逻辑。
GROUP BY A.ID, A.name: 再次按用户ID和名称进行分组，以确保每个用户只有一行结果。
ORDER BY A.name: 对结果按用户名称进行排序，使报表更易读。

4. 最终结果

执行上述完整的SQL查询，您将得到符合预期的报表：

+------+-----------+---------+----------+----------+
| Name | September | October | November | December |
+------+-----------+---------+----------+----------+
| Harry| 0         | 0       | 1        | 1        |
| Josh | 0         | 1       | 0        | 0        |
| Pete | 1         | 1       | 1        | 0        |
+------+-----------+---------+----------+----------+

登录后复制

5. 注意事项与扩展

DAYOFWEEK() 函数的返回值: 请注意，DAYOFWEEK() 在不同数据库系统中的返回值可能有所不同。在MySQL中，1是星期日，7是星期六。在SQL Server中，DATEPART(weekday, date) 默认1是星期日，7是星期六，但可以通过 SET DATEFIRST 改变。在PostgreSQL中，EXTRACT(DOW FROM date) 返回0是星期日，6是星期六。请根据您使用的数据库系统调整 WHERE 条件。
月份的扩展: 如果需要统计更多月份，只需在 SELECT 语句中添加更多的 SUM(CASE WHEN …) 列即可。
性能考量: 对于非常大的 events 表，DAYOFWEEK(date) 函数可能会导致全表扫描，影响查询性能。如果性能成为瓶颈，可以考虑在 date 列上创建索引，或者预先计算并存储星期几信息。
动态列名: 上述方法适用于列名（月份）已知且固定数量的情况。如果月份是动态变化的（例如，统计过去12个月），则需要结合编程语言（如PHP）来构建动态SQL查询，或者使用存储过程/函数来生成动态的PIVOT查询。
其他日期维度: 本教程以周六为例，但您可以轻松修改 DAYOFWEEK(date) = 7 条件来统计其他星期几的事件，或者使用 WEEK()、QUARTER() 等函数按周或季度进行统计。

通过上述步骤，您可以灵活地利用SQL对日期和事件数据进行复杂的聚合和透视分析，从而生成满足业务需求的统计报表。

以上就是SQL技巧：按用户和月份统计特定日期（如周六）的出现次数的详细内容，更多请关注php中文网其它相关文章！

https://www.php.cn/faq/1440394.html