MySQL按年/月/日/周分组统计数据的SQL实现

使用 DATE_FORMAT 按年、月、日、周分组

最通用的方法是利用 DATE_FORMAT(date, format) 函数将日期字段格式化为特定的字符串，然后作为 GROUP BY 的子句。这种方法直观且灵活，适用于绝大多数时间维度的汇总需求。

第1步：准备测试数据

假设有一张学生表 student，包含基础信息和创建时间，用于演示后续的统计查询：

第2步：编写各周期查询语句

以下示例展示了如何针对同一张表，分别按年、按月、按周、按日进行统计。核心逻辑是将 create_time 格式化后，再执行聚合操作。

按年统计：

SELECT 
    DATE_FORMAT(create_time, '%Y') AS years, 
    AVG(age) AS avg_age, 
    COUNT(gender) AS user_count 
FROM student 
WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-07-19 19:58:03'
GROUP BY years;

按月统计：

SELECT 
    DATE_FORMAT(create_time, '%Y-%m') AS months, 
    AVG(age), 
    COUNT(gender) 
FROM student 
WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-07-19 19:58:03'
GROUP BY months;

按周统计：

SELECT 
    DATE_FORMAT(create_time, '%Y-%u') AS weeks, 
    AVG(age), 
    COUNT(gender) 
FROM student 
WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-07-19 19:58:03'
GROUP BY weeks;

按日统计：

SELECT 
    DATE_FORMAT(create_time, '%Y-%m-%d') AS days, 
    AVG(age), 
    COUNT(gender) 
FROM student 
WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-07-19 19:58:03'
GROUP BY days;

常用日期占位符说明

DATE_FORMAT 函数的效果取决于第二个参数中的占位符。以下是 MySQL 中最常用的日期和时间格式化标记：

进阶案例：按指定日期计算自然周并分类统计

在某些业务场景下，周的计算可能不依赖标准的“周一至周日”，而是从某个特定日期（如项目启动日）开始计算的第几周。此外，还需要对这些周内的用户活跃度进行分类汇总。

目标：统计自指定日期以来，每周用户的提问次数分布情况。

计算逻辑：

当无法直接使用 WEEK() 函数满足自定义起始日的需求时，可以通过 DATEDIFF 计算天数差，除以7得到周数。公式如下：

-- 当前记录的第N周
FLOOR(DATEDIFF(create_time, '指定起始日期') / 7) + 1

具体操作步骤

1. 子查询：获取每用户每周的活跃次数

首先，我们需要确定每个用户在每一个自定义周内的行为总数。这里使用一个临时子查询 topic_table 来完成基础的分组和计数。

(
    SELECT 
        FLOOR(DATEDIFF(create_time, '2021-01-01')/7)+1 AS weeks,
        create_time, 
        uid, 
        COUNT(*) AS nums 
    FROM yourTable 
    WHERE create_time > '2021-01-01' 
    GROUP BY weeks, uid
    ORDER BY create_time ASC
    LIMIT 100000 -- 防止数据量过大导致性能问题，实际可根据情况调整或移除
) AS topic_table

2. 主查询：按周汇总并进行条件分类

在子查询的基础上，外层查询按 weeks 分组，并利用 CASE WHEN 结构将活跃次数划分为不同的区间（例如：≤5次，6-10次，>10次），最后合并显示周的时间范围。

SELECT 
    weeks,
    CONCAT(LEFT(MIN(create_time),10),"至",LEFT(MAX(create_time),10)) AS date_range,
    SUM(CASE WHEN nums <= 5 THEN 1 ELSE 0 END) AS '低活跃(<=5)',
    SUM(CASE WHEN nums > 5 AND nums<=10 THEN 1 ELSE 0 END) AS '中活跃(6-10)',
    SUM(CASE WHEN nums > 10 THEN 1 ELSE 0 END) AS '高活跃(>10)' 
FROM (
    -- 上面的子查询 SQL 放在这里
    ...
) AS topic_table 
GROUP BY weeks;

执行结果能够清晰地展示不同周次内，用户活跃程度的分布形态，便于运营人员分析长期趋势。

总结

通过灵活运用 DATE_FORMAT 及其占位符，配合 DATEDIFF 等数学运算函数，可以解决 MySQL 中绝大部分复杂的时间分组统计需求。掌握这些技巧，能够显著提升数据报表开发效率和准确性。

以上就是MySQL按年/月/日/周分组统计数据的详细内容，更多关于MySQL分组统计数据内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！