您好,登錄后才能下訂單哦!
這篇文章主要介紹了MySQL如何實現按時間統計數據,具有一定借鑒價值,需要的朋友可以參考下。希望大家閱讀完這篇文章后大有收獲。下面讓小編帶著大家一起了解一下。
在做數據庫的統計時,經常會需要根據年、月、日來統計數據,然后配合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) |
%p | AM 或 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')
感謝你能夠認真閱讀完這篇文章,希望小編分享MySQL如何實現按時間統計數據內容對大家有幫助,同時也希望大家多多支持億速云,關注億速云行業資訊頻道,遇到問題就找億速云,詳細的解決方法等著你來學習!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。