Mysql日期格式聚合統計的方法是什麼

介紹

時間日期作為常用的分組條件，在實際開發中經常會用到，以便進行資料的統計。在 MySQL 中，我們可以使用日期格式化的函數將日期轉換成指定格式的字串，然後按照所需的時間粒度進行分組統計。

準備工作

在開始之前，我們需要先準備一張測試數據表，並插入一些數據，以便於我們後續的實驗和測試。

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test` (`name`, `created_at`) VALUES
('test1', '2022-01-01 00:00:00'),
('test2', '2022-01-02 00:00:00'),
('test3', '2022-01-02 12:00:00'),
('test4', '2022-01-03 00:00:00'),
('test5', '2022-01-07 00:00:00'),
('test6', '2022-01-08 00:00:00'),
('test7', '2022-01-09 00:00:00'),
('test8', '2022-01-10 00:00:00'),
('test9', '2022-01-14 00:00:00'),
('test10', '2022-01-15 00:00:00'),
('test11', '2022-01-16 00:00:00'),
('test12', '2022-01-17 00:00:00'),
('test13', '2022-02-01 00:00:00'),
('test14', '2022-02-02 00:00:00'),
('test15', '2022-02-03 00:00:00'),
('test16', '2022-03-01 00:00:00'),
('test17', '2022-03-02 00:00:00'),
('test18', '2022-03-03 00:00:00'),
('test19', '2022-04-01 00:00:00'),
('test20', '2022-04-02 00:00:00'),
('test21', '2022-04-03 00:00:00'),
('test22', '2022-05-01 00:00:00'),
('test23', '2022-05-02 00:00:00'),
('test24', '2022-05-03 00:00:00');

這裡我們建立了一張名為test 的測試表，包含三個欄位id、name 和created_at 。其中 created_at 表示記錄建立時間的日期時間類型欄位。我們插入了一些測試數據，包括從2022 年1月份到5月份的數據.

實現原理

當我們需要根據日期進行分組統計時，MySQL提供了很多內置的日期函數，如YEAR()、MONTH()、WEEK()、DAY()、HOUR()等。這些函數能夠根據日期對資料進行分組並統計相應的數量。

針對此需求，我們要依日期分組並計算出7天、4週和3個月的資料。因此，我們需要結合使用日期格式化和日期函數。

首先，我們需要將日期格式化成對應的格式。你可以用DATE_FORMAT()函數來達成，函數包含日期和格式化字串兩個參數。如下所示可將日期格式化為「yyyy-MM-dd」形式，例如：

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM table_name;

實際操作

按天統計

接下來，我們需要根據日期分組並統計數量。可以使用GROUP BY子句和對應的日期函數來實作。例如，我們可以根據日期分組統計每天的數量，如下所示：

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date, COUNT(*) AS count FROM table_name GROUP BY formatted_date;

按週統計

SELECT DATE_FORMAT(date_column, '%x-%v') AS formatted_week, COUNT(*) AS count FROM table_name GROUP BY formatted_week;

或

SELECT CONCAT(YEAR(date_column), '-', WEEK(date_column)) AS formatted_week, COUNT(*) AS count FROM table_name GROUP BY formatted_week;`

按月統計

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_month, COUNT(*) AS count FROM table_name GROUP BY formatted_month;

或

SELECT CONCAT(YEAR(date_column), '-', MONTH(date_column)) AS formatted_month, COUNT(*) AS count FROM table_name GROUP BY formatted_month;

以上是Mysql日期格式聚合統計的方法是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！