mysql查詢過去24小時內(nèi)每小時數(shù)據(jù)量的方法(精確到分鐘)

更新時間：2023年03月04日 16:49:06 作者：_Aimy

我們經(jīng)常遇到類似這樣的需求,查詢最近N秒、N分鐘、N小時的數(shù)據(jù)及N天的數(shù)據(jù),下面這篇文章主要給大家介紹了關于mysql查詢過去24小時內(nèi)每小時數(shù)據(jù)量(精確到分鐘)的相關資料,需要的朋友可以參考下

項目場景：

獲取過去24小時每小時數(shù)據(jù)量（沒有為0）

問題描述

時間段要求精確到分鐘，即：10.33 ~ 11.33，11.33~12.33……

解決方案：

第一步：獲取24個時間點：

1.使用數(shù)據(jù)量一定大于24的表（可以是數(shù)據(jù)庫自帶的，也可以是自己項目的）

SET @i=-1;
SELECT DATE_SUB( NOW(),INTERVAL ( (@i:=@i+1) ) HOUR ) AS 'time' 
FROM  mysql.help_category 
WHERE @i<23

2.建一個24個數(shù)據(jù)量的表

SET @i=-1;
SELECT DATE_SUB( NOW(), INTERVAL((@i:=@i+1)) HOUR) AS 'time' 
FROM 
(SELECT *  FROM 
	(SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4'   ) AS a
 	JOIN 
	( SELECT  '1' UNION  '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b
 ) AS c 
 WHERE @i<23

NOW() 獲取當前時間，也是最后一個時間節(jié)點

DATE_SUB() 函數(shù)從日期減去指定的時間間隔

INTERVAL 關鍵字可以用于計算時間間隔

HOUR 間隔類型（小時）

使用格式：DATE_SUB(date,INTERVAL expr type)

獲取的時間點如下：

查詢結(jié)果

第二步：將要查詢的表的時間向右偏移至整點，并查詢時段內(nèi)數(shù)據(jù)量：

將10.33 ~ 11.33之間的數(shù)據(jù)的時間，顯示為：11.00~12.00

這樣小時為11的數(shù)據(jù)就是原數(shù)據(jù)庫中10.33 ~ 11.33的數(shù)據(jù)，方便之后的查詢

SELECT DATE_FORMAT(DATE_SUB( create_time,INTERVAL MINUTE(NOW())-60 MINUTE ) ,'%Y-%m-%d %H') AS date, count(*) AS num 
FROM mytable GROUP BY date

結(jié)果如下：

查詢結(jié)果

第二步：多表連接查詢：

SET  @i=-1;
SELECT t.time, ifnull(num,0) AS count FROM 
	(SELECT DATE_SUB( NOW(),INTERVAL ( (@i:=@i+1) ) HOUR ) AS 'time' 
	FROM  mysql.help_category where @i<23) 
AS t 
LEFT JOIN 
	(SELECT DATE_FORMAT(DATE_SUB( create_time,INTERVAL MINUTE(NOW())-60 MINUTE ) ,'%Y-%m-%d %H') AS date, count(*) AS num 
	FROM mytable GROUP BY date ) 
AS sa 
ON sa.date = DATE_FORMAT( t.time,'%Y-%m-%d %H')  GROUP BY t.time

結(jié)果如下：

查詢結(jié)果

count表示的是時間節(jié)點前一個小時的數(shù)據(jù)量，不包含此節(jié)點，如果要包含此節(jié)點，可以將上面的60改成59

附：Mysql 查詢每小時數(shù)據(jù)的總數(shù)

1.通過sql查詢每小時的數(shù)據(jù)總數(shù)，沒有先不管

 SELECT
      hour(表_時間)  hour,
      count(表_id) count
    FROM 表_name
    //限制年月日（current_date  2022-03-05）
    WHERE  date_format(表_時間, '%Y-%m-%d') = current_date//格式化時間
    GROUP BY date_format(表_時間, '%Y%m%d-%H'), hour//按每小時分組

2.通過map，數(shù)組工具把沒有數(shù)據(jù)的小時置為0；