mysql按照某個(gè)時(shí)間段分組統(tǒng)計(jì)

今天剛好是碰到一個(gè)報(bào)表需求，要求根據(jù)時(shí)段統(tǒng)計(jì)各工單的數(shù)量。這個(gè)就有點(diǎn)皮實(shí)了，以前都沒(méi)搞過(guò)這玩意。于是研究了幾下。

準(zhǔn)備開始

首先一個(gè)問(wèn)題，想根據(jù)時(shí)間段進(jìn)行統(tǒng)計(jì)，這個(gè)問(wèn)題先不考慮，先考慮，如果按照一小時(shí)統(tǒng)計(jì)呢，該怎么去做，于是就一頓操作，寫出以下sql，這個(gè)基本的相信沒(méi)什么難度，主要是說(shuō)，怎么去單獨(dú)拿到小時(shí)，這里用到“DATE_FORMAT”函數(shù)。

SELECT
	DATE_FORMAT( create_order_time, '%H' ) AS h,
	COUNT( 1 ) 
FROM
	order_item 
GROUP BY
	DATE_FORMAT( create_order_time, '%H' )
ORDER BY
	DATE_FORMAT( create_order_time, '%H' )

到這里，小時(shí)統(tǒng)計(jì)沒(méi)問(wèn)題了，那么怎么改造成符合我的需求呢，請(qǐng)看圖片

相信聰明的人已經(jīng)感覺(jué)出來(lái)的，就是其實(shí)把我們?nèi)〉降男r(shí)去除以時(shí)間段的區(qū)間取整數(shù)，那么就可以得到同一區(qū)間的數(shù)據(jù)，然后這個(gè)數(shù)字在乘回去我們的區(qū)間，就可以得到上限，在加區(qū)間就是下限，最終我們得到的SQL

-- 	區(qū)間數(shù)字SQL
SELECT
	(FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2) * 2) AS h2,
	COUNT( 1 ) 
FROM
	order_item 
GROUP BY
	FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2)
ORDER BY
	FLOOR(DATE_FORMAT( create_order_time, '%H' ) / 2)

-- 最終SQL
SELECT
	 CONCAT(
	 FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) * 2,
	 '-',
	 FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) * 2 + 2)
	 AS h2,
	COUNT( 1 ) 
FROM
	order_item 
GROUP BY
	FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 ) 
ORDER BY
	FLOOR( DATE_FORMAT( create_order_time, '%H' ) / 2 )

樣圖

mysql區(qū)間分組統(tǒng)計(jì)

場(chǎng)景

表cases,有完成時(shí)間(finish_time)，id等字段

現(xiàn)在將finish_time按時(shí)間區(qū)間劃分年份

例 2021-11-01 00:00:00 ~2022-10-31 23:59:59 為2022年

下面是模擬數(shù)據(jù)：

統(tǒng)計(jì)結(jié)果應(yīng)該如下所示：

因?yàn)椴荒茉赾ases表中添加字段，上網(wǎng)搜尋后找到兩個(gè)方法:

• 1、利用了mysql提供的interval和elt函數(shù)實(shí)現(xiàn)了效果。
• 2、是建一張中間表用來(lái)分組

利用了mysql提供的interval和elt函數(shù)實(shí)現(xiàn)了效果

interval

interval(N,N1,N2,N3) ,比較列表中的N值，該函數(shù)如果N<N1返回0，如果N<N2返回1，如果N<N3返回2 等等。

elt

elt(n,str1,str2,str3,…) 如果n=1，則返回str1,如果n=2，則返回str2,依次類推

兩個(gè)函數(shù)結(jié)合，再加上group，實(shí)現(xiàn)了這種范圍分組的效果

select elt(interval(finish_ime,'2019-11-01','2020-11-01','2021-11-01','2022-11-01'),2019,2020,2021,2022) as i,count(*)
from cases
group by i

但實(shí)際效果不盡人意，也許是因?yàn)閙ysql中interval也是處理日期的關(guān)鍵字吧，interval()函數(shù)對(duì)日期進(jìn)行劃分的時(shí)候每次都劃分到最后一個(gè)區(qū)間，因此使用第二種辦法即建一張中間表用來(lái)劃分

建中間表

創(chuàng)建如下一張中間表（tmp）

cases表通過(guò)join連接這張表就可以實(shí)現(xiàn)分組效果

?? ?select count(*) as '數(shù)量', t.`year` as "年份"
?? ?from cases c left join tmp t on c.finish_time>=t.start_date and?
?? ??? ??? ??? ??? ??? ??? ??? ??? ?c.finish_time<=t.end_date?
?? ?group by t.`year`

這樣就可以在不修改原有表結(jié)構(gòu)的基礎(chǔ)上實(shí)現(xiàn)區(qū)間分組統(tǒng)計(jì)。

通過(guò)函數(shù)來(lái)劃分區(qū)間

創(chuàng)建函數(shù)get_year()

create function get_year(finishTime datetime) returns int
begin
? ? declare d_year int(4);
? ? set d_year = year(finishTime);
? ? if (finishTime >= concat(d_year, '-11-01 00:00:00')) then
? ? ? ? return d_year + 1;
? ? else
? ? ? ? return d_year;
? ? end if;
end;

?? ?select count(*) as '數(shù)量',get_year(c.finish_ime) as "年份"
?? ?from cases c?
?? ?group by get_year(c.finish_ime)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論