快捷導(dǎo)航

mysql如何分別按年/月/日/周分組統(tǒng)計(jì)數(shù)據(jù)詳解

更新時(shí)間：2022年12月15日 11:21:05 作者：冰冷的希望

我們?cè)谟肕ysql抽取數(shù)據(jù)時(shí)候,經(jīng)常需要按照天、周、月等不同的粒度對(duì)數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì),下面這篇文章主要給大家介紹了關(guān)于mysql如何分別按年/月/日/周分組統(tǒng)計(jì)數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下

1.統(tǒng)計(jì)

我們可以使用date_format()函數(shù)格式化時(shí)間，然后進(jìn)行分組操作

例如有一個(gè)學(xué)生表，結(jié)構(gòu)如下

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函數(shù)，可以使用對(duì)應(yīng)的year()/month()/week()/day()函數(shù)替代

2.占位符

date_format()需要傳入一個(gè)特定的占位符，mysql常用的占位符可參考下表

占位符	說(shuō)明
%Y	年(4位)
%y	年(2位)
%M	月（英文名，如January）
%m	月（數(shù)字，如01）
%D	日（英文名，如1st）
%d	日（數(shù)字，如01）
%e	日（數(shù)字，如1）
%U	一年中的第幾周，從0開(kāi)始，周日是第一天
%u	一年中的第幾周，從0開(kāi)始，周一是第一天
%H	時(shí)，24小時(shí)制，例如15
%h	時(shí)，12小時(shí)制，例如01
%i	分
%s	秒

補(bǔ)充：Mysql如何指定日期按周分組，并按次數(shù)分類統(tǒng)計(jì)

需要統(tǒng)計(jì)今年以來(lái)，每周用戶的提問(wèn)次數(shù)，并按提問(wèn)次數(shù)進(jìn)行分類統(tǒng)計(jì)，格式如下：

大佬需求的格式

需求不能按自然周算，所以不適合用week()函數(shù)計(jì)算周數(shù)，只能根據(jù)提問(wèn)時(shí)間和指定日期的時(shí)間差相除

第幾周 =floor((提問(wèn)時(shí)間-指定日期)/7)+1

確定了計(jì)算方式，首先按周和用戶ID分組，查詢周，提問(wèn)次數(shù)，然后通過(guò)case when對(duì)查詢結(jié)果按提問(wèn)次數(shù)分類，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 '提問(wèn)次數(shù)<=5',
 SUM(CASE WHEN nums > 5 AND nums<=10 THEN 1 ELSE 0 END) AS '5<提問(wèn)次數(shù)<=10',
 SUM(CASE WHEN nums > 10 THEN 1 ELSE 0 END) AS '提問(wèn)次數(shù)>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

總結(jié)

到此這篇關(guān)于mysql如何分別按年/月/日/周分組統(tǒng)計(jì)數(shù)據(jù)的文章就介紹到這了,更多相關(guān)mysql分組統(tǒng)計(jì)數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: