mysql如何分别按年/月/日/周分组统计数据详解

2022-12-15 12:00

短信预约 -IT技能 免费直播动态提醒

1.统计

我们可以使用date_format()函数格式化时间，然后进行分组操作

例如有一个学生表，结构如下

id	name	age	height	gender	create_time
1	pan	27	169	1	2022-01-13 10:20:22
2	yang	18	177	1	2022-03-14 09:16:42
3	daisy	25	156	2	2022-07-19 19:58:03

-- 按年
select date_format(create_time, '%Y') years,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by years;
-- 按月
select date_format(create_time, '%Y-%m') months,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by months;
-- 按周
select date_format(create_time, '%Y-%u') weeks,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by weeks;
-- 按日
select date_format(create_time, '%Y-%m-%d') days,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by days;

如果不想用date_format函数，可以使用对应的year()/month()/week()/day()函数替代

2.占位符

date_format()需要传入一个特定的占位符，mysql常用的占位符可参考下表

占位符	说明
%Y	年(4位)
%y	年(2位)
%M	月（英文名，如January）
%m	月（数字，如01）
%D	日（英文名，如1st）
%d	日（数字，如01）
%e	日（数字，如1）
%U	一年中的第几周，从0开始，周日是第一天
%u	一年中的第几周，从0开始，周一是第一天
%H	时，24小时制，例如15
%h	时，12小时制，例如01
%i	分
%s	秒

补充：Mysql如何指定日期按周分组，并按次数分类统计

需要统计今年以来，每周用户的提问次数，并按提问次数进行分类统计，格式如下：

大佬需求的格式

需求不能按自然周算，所以不适合用week()函数计算周数，只能根据提问时间和指定日期的时间差相除

第几周 =floor((提问时间-指定日期)/7)+1

确定了计算方式，首先按周和用户ID分组，查询周，提问次数，然后通过case when对查询结果按提问次数分类，sql如下：

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 '5<提问次数<=10',
 SUM(CASE WHEN nums > 10 THEN 1 ELSE 0 END) AS '提问次数>10' 
 FROM 
 (SELECT FLOOR(DATEDIFF(create_time,'2021-01-01')/7)+1 weeks,
 create_time,uid,COUNT(*) AS nums 
  FROM `youTable` 
   WHERE create_time > "2021-01-01" 
   GROUP BY weeks,uid ORDER BY create_time ASC LIMIT 100000)
    AS topic_table GROUP BY weeks

mysql如何分别按年/月/日/周分组统计数据详解