MySQL按時間統計數據的方法介紹（代碼示例）

本篇文章給大家帶來的內容是關于mysql按時間統計數據的方法介紹（代碼示例），有一定的參考價值，有需要的朋友可以參考一下，希望對你有所幫助。

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

• 數據庫：MySQL（推薦教程：MySQL教程）

思路

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

`timestamp` datetime DEFAULT NULL,

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

注：當涉及到按日統計是，需要使用%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')