MySQL如何實現按時間統計數據

135次閱讀

沒有評論

共計 2574 個字符，預計需要花費 7 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章主要介紹了 MySQL 如何實現按時間統計數據，具有一定借鑒價值，需要的朋友可以參考下。希望大家閱讀完這篇文章后大有收獲。下面讓丸趣 TV 小編帶著大家一起了解一下。

在做數據庫的統計時，經常會需要根據年、月、日來統計數據，然后配合 echarts 來制作可視化效果。

數據庫：MySQL

思路

按照時間維度進行統計的前提是需要數據庫中有保留時間信息，建議是使用 MySQL 自帶的 datetime 類型來記錄時間。

`timestamp` datetime DEFAULT NULL,

在 MySQL 中對于時間日期的處理的函數主要是 DATE_FORMAT(date,format)。可用的參數如下

格式描述 %a 縮寫星期名 %b 縮寫月名 %c 月，數值 %D 帶有英文前綴的月中的天 %d 月的天，數值 (00-31)%e 月的天，數值 (0-31)%f 微秒 %H 小時 (00-23)%h 小時 (01-12)%I 小時 (01-12)%i 分鐘，數值 (00-59)%j 年的天 (001-366)%k 小時 (0-23)%l 小時 (1-12)%M 月名 %m 月，數值 (00-12)%pAM 或 PM%r 時間，12- 小時（hh:mm:ss AM 或 PM）%S 秒 (00-59)%s 秒 (00-59)%T 時間, 24- 小時 (hh:mm:ss)%U 周 (00-53) 星期日是一周的第一天 %u 周 (00-53) 星期一是一周的第一天 %V 周 (01-53) 星期日是一周的第一天，與 %X 使用 %v 周 (01-53) 星期一是一周的第一天，與 %x 使用 %W 星期名 %w 周的天（0= 星期日, 6= 星期六）%X 年，其中的星期日是周的第一天，4 位，與 %V 使用 %x 年，其中的星期一是周的第一天，4 位，與 %v 使用 %Y 年，4 位 %y 年，2 位

注：當涉及到按日統計是，需要使用 %j，而如果使用 %d, %e, %w 的話，那么不同月份 / 周里的相同值會統計在一起。

涉及到獲取當前時間，則可以通過 now() 或者 sysdate() 來獲取。

SELECT SYSDATE() FROM DUAL;
SELECT NOW() FROM DUAL;

按照實際需求使用 group by 查詢即可。

結論

需統計的表結構如下：

CREATE TABLE `apilog` (`id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(64) DEFAULT NULL,
 `action` varchar(64) DEFAULT NULL,
 `params` text,
 `result` text,
 `timestamp` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
)

統計時間范圍內不同分類 action 的數量

# 當日
SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`, %j) = DATE_FORMAT(now(), %j ) ORDER BY count desc;
SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`, %u) = DATE_FORMAT(now(), %u ) ORDER BY count desc;
SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`, %m) = DATE_FORMAT(now(), %m ) ORDER BY count desc;
SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`, %Y) = DATE_FORMAT(now(), %Y ) ORDER BY count desc;

統計某分類 action 的時間維度數量

# 按日
SELECT action, DATE_FORMAT(`timestamp`, %j), COUNT(id) count FROM apilog WHERE action = xxx GROUP BY DATE_FORMAT(`timestamp`, %j)
SELECT action, DATE_FORMAT(`timestamp`, %u), COUNT(id) count FROM apilog WHERE action = xxx GROUP BY DATE_FORMAT(`timestamp`, %u)
SELECT action, DATE_FORMAT(`timestamp`, %m), COUNT(id) count FROM apilog WHERE action = xxx GROUP BY DATE_FORMAT(`timestamp`, %m)
SELECT action, DATE_FORMAT(`timestamp`, %Y), COUNT(id) count FROM apilog WHERE action = xxx GROUP BY DATE_FORMAT(`timestamp`, %Y)

同時按 action 和時間維度統計

# 按日
SELECT action, DATE_FORMAT(`timestamp`, %j), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`, %j)
SELECT action, DATE_FORMAT(`timestamp`, %u), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`, %u)
SELECT action, DATE_FORMAT(`timestamp`, %m), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`, %m)
SELECT action, DATE_FORMAT(`timestamp`, %Y), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`, %Y)

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享 MySQL 如何實現按時間統計數據內容對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，遇到問題就找丸趣 TV，詳細的解決方法等著你來學習!

向 AI 問一下細節

丸趣 TV 網 – 提供最優質的資源集合！

正文完