mysql實現按照某個時間段分組統計

2022-10-14 14:05:25

mysql按照某個時間段分組統計

今天剛好是碰到一個報表需求，要求根據時段統計各工單的數量。這個就有點皮實了，以前都沒搞過這玩意。於是研究了幾下。

準備開始

首先一個問題，想根據時間段進行統計，這個問題先不考慮，先考慮，如果按照一小時統計呢，該怎麼去做，於是就一頓操作，寫出以下sql，這個基本的相信沒什麼難度，主要是說，怎麼去單獨拿到小時，這裡用到“DATE_FORMAT”函數。

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' )

到這裡，小時統計沒問題了，那麼怎麼改造成符合我的需求呢，請看圖片

相信聰明的人已經感覺出來的，就是其實把我們取到的小時去除以時間段的區間取整數，那麼就可以得到同一區間的資料，然後這個數位在乘回去我們的區間，就可以得到上限，在加區間就是下限，最終我們得到的SQL

-- 	區間數位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區間分組統計

場景

表cases,有完成時間(finish_time)，id等欄位

現在將finish_time按時間區間劃分年份

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

下面是模擬資料：

id	finish_time
1	2019-01-23 23:23:23
2	2019-02-28 12:12:12
3	2020-08-12 08:12:12
4	2021-05-12 22:12:43
5	2022-05-12 23:23:23

統計結果應該如下所示：

年份	數量
2019	2
2020	1
2021	1
2022	1

因為不能在cases表中新增欄位，上網搜尋後找到兩個方法:

1、利用了mysql提供的interval和elt函數實現了效果。
2、是建一張中間表用來分組

利用了mysql提供的interval和elt函數實現了效果

interval

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

elt

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

兩個函數結合，再加上group，實現了這種範圍分組的效果

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

但實際效果不盡人意，也許是因為mysql中interval也是處理日期的關鍵字吧，interval()函數對日期進行劃分的時候每次都劃分到最後一個區間，因此使用第二種辦法即建一張中間表用來劃分

建中間表

建立如下一張中間表（tmp）

start_date	end_date	year
2018-11-01 00:00:00	2019-10-31 23:59:59	2019
2019-11-01 00:00:00	2020-10-31 23:59:59	2020
2020-11-01 00:00:00	2021-10-31 23:59:59	2021
2021-11-01 00:00:00	2022-10-31 23:59:59	2022

cases表通過join連線這張表就可以實現分組效果

    select count(*) as '數量', 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`

這樣就可以在不修改原有表結構的基礎上實現區間分組統計。

通過函數來劃分割區間

建立函數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 '數量',get_year(c.finish_ime) as "年份"
    from cases c 
    group by get_year(c.finish_ime)

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。