前言

ps： 網上看了一大堆文章， 介紹的東西真的是很夠嗆， 就沒一個能真正用起來的， 各個都是自動補，然后很多都是不好用的。

我自己整理一篇，這是真能用。

本篇內容 ：

① 按照 日 、周、月 、年 ?的維度 去對數(shù)據 做分組統(tǒng)計?

② 不存在的數(shù)據自動補充 0 ?（實用）

正文

不多說，開搞。

結合實例 ：

先看我們的表 student?

?建表sql：

CREATE TABLE `student` (
?? ?`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
?? ?`name` VARCHAR(50) NULL DEFAULT NULL COMMENT '名字' COLLATE 'utf8_general_ci',
?? ?`admission_time` DATETIME NULL DEFAULT NULL COMMENT '入學時間',
?? ?PRIMARY KEY (`id`) USING BTREE
)

現(xiàn)在我們就舉個簡單的業(yè)務場景：

根據admission_time 入學時間，按照年月日周這些維度?去統(tǒng)計 學生數(shù)量。

我們來做一些模擬數(shù)據，8條數(shù)據：

第一部分 ， 按照時間維度統(tǒng)計的SQL 寫法

我們先簡單看看 按照 日 、周 ?、月 、年 的統(tǒng)計 sql怎么寫 ：

日 維度?

sql：

SELECT date_format(admission_time, '%Y-%m-%d') date_time, COUNT(*) num
FROM student
GROUP BY date_time ;

?看看效果：

?
ps ： 注意了 這些 ‘-’ 是我們定義的格式， 我們不要 ‘-’ 換成其他的也是可以的，但是 不能不要 ymd 這些關鍵字。

舉個小栗子：
?
?
比如 ，date_format(admission_time, '收藏%Y點%m贊%d')

SELECT date_format(admission_time, '收藏%Y點%m贊%d') date_time, COUNT(*) num
FROM student
GROUP BY date_time ;

看看效果 ：

周維度?

?sql：

SELECT DATE_FORMAT(admission_time,'%Y-%u 周') ?AS ?date_time, COUNT(*) num FROM student GROUP BY date_time;

ps ： 為了讓你們知道 這個周的概念， 我故意加了個中文。 而且特意把一條數(shù)據 時間改成 22年的最后一周的一天。

?
看看效果 ：

月維度?

sql：

SELECT DATE_FORMAT(admission_time, '%Y-%m') date_time, COUNT(*) num
FROM student
GROUP BY date_time ;

看看效果 ：

年維度

sql：

SELECT DATE_FORMAT(admission_time, '%Y') date_time, COUNT(*) num
FROM student
GROUP BY date_time ;

看看效果:

好了 知道這些基礎的 時間分組sql 用法之后， 那么我們接下來就 來 玩下怎么 解決自動補 0 的這個問題。

第二部分 ， 自動填充不存在的數(shù)據 數(shù)值 0

首先，如果說 sql查出來的數(shù)據沒有， 我們拿月維度的來 做個示例 ，就像這樣 ：

?

這里突然想到個點，很多人說那這里面混了 2022年， 2023年的數(shù)據， 我想要指定查詢某段時間的怎么搞？

其實一樣的，就根據時間查詢就行：

比如像這樣傳入我們的篩選時間范圍，按照月就這樣 ：

sql：

SELECT * FROM (

select date_format(admission_time, '%Y-%m') date_time, count(*) num
from student
group by date_format(admission_time, '%Y-%m')

)t WHERE t.date_time BETWEEN '2023-01' AND ? '2023-03'

效果：

?
回到剛才，可以看到統(tǒng)計出來的數(shù)據， 比如說23年的，有1月的，2月的， 3月的， 那么 4，5，6，7后面這些月份，沒數(shù)據，那怎么辦？

如何默認去填充 0呢 ？
?

寫代碼填充，后端拿到查數(shù)據庫返回的數(shù)據，for循環(huán)遍歷，檢測時間段內的日期， 比如說 12個月，看看哪個月沒有，就填充。

確實 這是可以的， 但是今天這一篇介紹的是通過sql返回 ， 不考慮代碼上面的填補。

接下來看看SQL怎么玩 。

思路&想法?：?
?

我們能查出來 student 現(xiàn)有的日期數(shù)據， 那么缺少的數(shù)據 我也得給整出來 。

那我們肯定不能去改 student表的數(shù)據呀， 現(xiàn)在就是單純少了一些 空白月份的數(shù)據 。

所以我們選擇 臨時數(shù)據表的思維。

ps ： 網上一大堆文章，都讓咱們去跑個存儲過程 ?強行生成一個表..

你看看（一萬個拒絕）?

?還有這種，按照目前時間拼接出來的：

（5000個拒絕，這種now 直接切割到現(xiàn)在，還得寫一大堆這種01，02，03，04；
? ?如果我是要填充 日維度的數(shù)據，那我這sql代碼量不就爆炸了？
）?

?這種也是：

OK， 我們來看看我們的SQL ：

?

select DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2023-12-01','%Y-%m')

效果 ：
?

ps： 這里用了月舉例， 要弄年或周或者日的 在文末有補充。

sql 作用簡析 ：

簡析點一?

?

?簡析點二

?可以看到 ，這里面 我寫了2行這個玩意??。

(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t
?

簡單說下：
?

寫一行這個玩意， 代表能搞出?10 條 符合 范圍時間內日期格式時間數(shù)據?；

當寫多一行呢（2行的時候），代表 10 * 10 =100 條?

當再寫多一行呢（3行的時候）？? 10*10*10 =1000條

所以我們上面的sql，我寫了2行，

代表 在我傳入的時間范圍 2023-01-01 到 2323-12-01? 內

我寫的時間格式是取月， 一共其實就12 個月數(shù)據，也就是12條， 但是只寫一次10條不夠用，我也就用了2次（100條）。

言下之意， 其實你跟我這樣寫2次，100條， 什么概念， 1年12個月 相當于12條，這樣 100條相當于可以查跨度 8年的時間了 （8*12-96）

如果你不是要查日， 業(yè)務需求一般不會讓咱們寫跨度這么大的。

當然了，如果就是有， 那么我們大不了直接寫 4條， 相當于 10*10*10*10 =1 萬??條。

OK，不啰嗦，回到我們的示例 ：

我們現(xiàn)在 如果說是查跨度 2年的數(shù)據， 比如現(xiàn)在按照我們part的sql 查出來是這樣子的 。

可以看到結果集， 22年數(shù)據 缺了很多 需要補0的， 23年也缺了很多。

所以我們這時候需要做一個 left join 即可解決自動補 0 的事情。

sql操作圖析:
?

sql:

SELECT  A.date_time, COALESCE(B.num, 0) as num  FROM 


(

SELECT DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') date_time FROM
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
WHERE DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2023-12-01','%Y-%m')

) A


LEFT JOIN 

(

SELECT DATE_FORMAT(admission_time, '%Y-%m') date_time, COUNT(*) num
FROM student
GROUP BY DATE_FORMAT(admission_time, '%Y-%m')

) B


ON    A.date_time= B.date_time

看看效果 ：

這盛世如我所愿， 好了， 該篇就到這。?

文末補充 日、年、周 維度的 列出完整數(shù)據條sql ：

按日 列出范圍內日期的sql ：
?

select date_add('2023-01-01', interval row DAY) date from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	  (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	   (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,   
    (SELECT @row:=-1) r
 ) se
 where date_add('2023-01-01', interval row DAY) <= '2023-01-20'

?效果：

按周 列出范圍內日期的sql ：

按照周要注意一點，當傳入每年的01-01這一天的時候?會出現(xiàn)0周 ，可以做一下處理。

 select DATE_FORMAT(date_add('2023-01-01', interval row WEEK),'%Y-%u') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2023-01-01', interval row WEEK),'%Y-%u') <= DATE_FORMAT('2023-01-06','%Y-%u')

按年?列出范圍內日期的sql ：

select DATE_FORMAT(date_add('2020-01-01', interval row YEAR),'%Y') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2020-01-01', interval row YEAR),'%Y') <= DATE_FORMAT('2023-12-01','%Y')
 

效果：
文章來源地址http://www.zghlxwxcb.cn/news/detail-775072.html

到了這里，關于【實用】Mysql 按照時間（年月周日）維度統(tǒng)計，不存在時間數(shù)據 自動補充 0 數(shù)值的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！