快捷導(dǎo)航

mysql按天/小時(shí)/半小時(shí)/N分鐘/分鐘進(jìn)行數(shù)據(jù)分組統(tǒng)計(jì)功能

更新時(shí)間：2024年04月24日 10:24:21 作者：愛(ài)碼猿

我們?cè)谧鲰?xiàng)目或者數(shù)據(jù)分析時(shí),經(jīng)常遇到這樣的需求：統(tǒng)計(jì)不同時(shí)間粒度下的數(shù)據(jù)分布情況,例如,每一天中每個(gè)小時(shí)網(wǎng)站的訪(fǎng)問(wèn)量,某路口每半個(gè)小時(shí)通過(guò)的車(chē)輛數(shù)量等,下面給大家分享mysql按天/小時(shí)/半小時(shí)/N分鐘/分鐘進(jìn)行數(shù)據(jù)分組統(tǒng)計(jì)功能,感興趣的朋友跟隨小編一起看看吧

mysql不同時(shí)間粒度下的分組統(tǒng)計(jì)

我們?cè)谧鲰?xiàng)目或者數(shù)據(jù)分析時(shí)，經(jīng)常遇到這樣的需求：統(tǒng)計(jì)不同時(shí)間粒度下的數(shù)據(jù)分布情況，例如，每一天中每個(gè)小時(shí)網(wǎng)站的訪(fǎng)問(wèn)量，某路口每半個(gè)小時(shí)通過(guò)的車(chē)輛數(shù)量等。對(duì)于此類(lèi)的問(wèn)題，一個(gè)sql簡(jiǎn)單的查詢(xún)就能實(shí)現(xiàn)，故特此記錄下，方便以后使用。
在MySQL中，我的表為：track 數(shù)據(jù)結(jié)構(gòu)如下所示：

表track的數(shù)據(jù)結(jié)構(gòu)示意

按天統(tǒng)計(jì)

SELECT DATE(TimeStart) AS date, COUNT(*) AS num
FROM track
WHERE Flag = 0 AND Duration >= 300 
GROUP BY date
ORDER BY date;

按小時(shí)統(tǒng)計(jì)

SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS num
FROM track
WHERE Flag = 0 AND Duration >= 300
GROUP BY time
ORDER BY time;

一小時(shí)結(jié)果

按半小時(shí)統(tǒng)計(jì)

SELECT time, COUNT( * ) AS num 
FROM
	(
	SELECT Duration,
		DATE_FORMAT(
			concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 30 ) * 30 ),
			'%Y-%m-%d %H:%i' 
		) AS time 
	FROM tarck
	WHERE Flag = 0  AND Duration >= 300 
	) a 
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BY time;

半小時(shí)查詢(xún)結(jié)果

按N分鐘統(tǒng)計(jì)

SELECT time, COUNT( * ) AS num 
FROM
	(
	SELECT Duration,
		DATE_FORMAT(
			concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 10 ) * 10 ),
			'%Y-%m-%d %H:%i' 
		) AS time 
	FROM tarck
	WHERE Flag = 0  AND Duration >= 300 
	) a 
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BY time;

基本思路：將datetime類(lèi)型的時(shí)間轉(zhuǎn)化為相應(yīng)時(shí)間片的時(shí)間，例如將‘2017-03-01 01:08:19’ 轉(zhuǎn)化為‘2017-03-01 01:00:00’，然后group by即可。

按分鐘統(tǒng)計(jì)

將按小時(shí)統(tǒng)計(jì)的SQL語(yǔ)句稍微修改下，就可以實(shí)現(xiàn)按分鐘統(tǒng)計(jì)

SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:%i:00') AS time, COUNT(*) AS num
FROM track 
WHERE Flag = 0 AND Duration >= 300
GROUP BY time
ORDER BY time;

參考資料： https://blog.csdn.net/u010946448/article/details/83752984

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

您可能感興趣的文章: