編寫sql統(tǒng)計(jì)一段時(shí)間內(nèi)，每天、每月、每年的統(tǒng)計(jì)數(shù)據(jù)（PostgreSQL）
前言
在做數(shù)據(jù)統(tǒng)計(jì)頁面時(shí)，總會遇到統(tǒng)計(jì)某段時(shí)間內(nèi)，每天、每月、每年的數(shù)據(jù)視圖（柱狀圖、折線圖等）。這些統(tǒng)計(jì)數(shù)據(jù)一眼看過去也簡單呀，不就是按照時(shí)間周期（天、月、年）對統(tǒng)計(jì)數(shù)據(jù)進(jìn)行分個(gè)組就完了嘛？但是會有一個(gè)問題，簡單的寫個(gè)sql對周期分組，獲取到的統(tǒng)計(jì)數(shù)據(jù)是缺失的，即沒有數(shù)據(jù)的那天，整條記錄也都沒有了。如下圖
需求：以當(dāng)前月份（2023年2月）為起點(diǎn)，往后倒推一年，查詢之前一年里每個(gè)月的統(tǒng)計(jì)數(shù)據(jù)。
可見圖中的數(shù)據(jù)其實(shí)是缺少的，這條sql只查詢到了有數(shù)據(jù)的月份（23年的1月、2月，22年的12月）
如果我想要沒有數(shù)據(jù)的那個(gè)月或者那一天、那一年也顯示統(tǒng)計(jì)記錄出來，即統(tǒng)計(jì)數(shù)據(jù)字段設(shè)為0.該怎么實(shí)現(xiàn)呢？
文章來源地址http://www.zghlxwxcb.cn/news/detail-530624.html
解決思路
- 先單表查詢目標(biāo)表單，按時(shí)間周期分組，統(tǒng)計(jì)出每個(gè)月對應(yīng)的統(tǒng)計(jì)數(shù)據(jù)。設(shè)為表e,這時(shí)結(jié)果數(shù)據(jù)是有缺失的
- 再單表查詢出倒推一年內(nèi)，每個(gè)月份的字段。設(shè)為表 w
- 最后將兩張表根據(jù) 月份字段進(jìn)行關(guān)聯(lián)查詢，其中關(guān)聯(lián)查詢的側(cè)重表應(yīng)是w
編寫sql
SELECT COALESCE(e.counts,0),w.wmonth
from (
SELECT COUNT(*) counts,to_char(create_time, 'yyyy-MM') months
FROM employee
GROUP BY months
) e
RIGHT JOIN
(
SELECT to_char(t, 'yyyy-MM') as wmonth
from generate_series(now() - INTERVAL '1 year' ,now(), '1 MONTH') as t
) w on e.months = w.wmonth
GROUP BY w.wmonth,e.counts
ORDER BY w.wmonth desc
函數(shù)解析
- COALESCE(e.counts,0)：在查詢表單的結(jié)果里，如果e.counts為null，則設(shè)置為0
- to_char(create_time, ‘yyyy-MM’)： 這個(gè)函數(shù)可以將時(shí)間對象轉(zhuǎn)化為字符串形式，有點(diǎn)像java內(nèi)的SimpleDateFormat.format()函數(shù)
- now()：獲取當(dāng)前的時(shí)間戳
- generate_series(startTime,endTime ‘1 MONTH’)：這個(gè)函數(shù)就是實(shí)現(xiàn)數(shù)據(jù)完整檢查的關(guān)鍵之一。作用是專門獲取一個(gè)時(shí)間區(qū)段內(nèi)，每一天、每個(gè)月、每一年的時(shí)間’對象‘。該函數(shù)一共有三個(gè)參數(shù)
(起始時(shí)間，末尾時(shí)間，時(shí)間跨度)
-SELECT to_char(t, 'yyyy-MM') as wmonth
from generate_series(now() - INTERVAL '1 year' ,now(), '1 MONTH') as t
文章來源：http://www.zghlxwxcb.cn/news/detail-530624.html
到了這里，關(guān)于編寫sql統(tǒng)計(jì)一段時(shí)間內(nèi)，每天、每月、每年的統(tǒng)計(jì)數(shù)據(jù)（PostgreSQL）的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
