MySql-日期分组

ops/2024/10/10 12:52:04/

一、分别统计各时间各类型数据条数

数据库的 request_time字段

数据类型：timestamp

默认值：CURRENT_TIMESTAMP

例子： 2024-01-26 08:25:48

原数据：

1、将数据按照日期（年月日）形式输出

按照request_time字段分组以（年月日形式）输出

   SELECTDATE(request_time) AS date,SUM(CASE WHEN task_command_value = 1 THEN 1 ELSE 0 END) AS count_1SUM(CASE WHEN task_command_value = 2 THEN 1 ELSE 0 END) AS count_2,SUM(CASE WHEN task_command_value = 3 THEN 1 ELSE 0 END) AS count_3,SUM(CASE WHEN task_command_value = 4 THEN 1 ELSE 0 END) AS count_4,SUM(CASE WHEN task_command_value = 5 THEN 1 ELSE 0 END) AS count_5FROM `数据库名`.表名GROUP BY DATE(request_time)ORDER BY DATE(request_time) DESC

输出结果：

2、将数据按照每月（月）形式输出

SELECTMONTH(request_time) AS date,SUM(CASE WHEN task_command_value = 1 THEN 1 ELSE 0 END) AS count_1,SUM(CASE WHEN task_command_value = 2 THEN 1 ELSE 0 END) AS count_2,SUM(CASE WHEN task_command_value = 3 THEN 1 ELSE 0 END) AS count_3,SUM(CASE WHEN task_command_value = 4 THEN 1 ELSE 0 END) AS count_4,SUM(CASE WHEN task_command_value = 5 THEN 1 ELSE 0 END) AS count_5
FROM `数据库名`.表名
GROUP BY MONTH(request_time)
ORDER BY MONTH(request_time) DESC

输出结果：缺点是如果两年的同月数据无法区别

3、将数据按照年月形式输出（使用DATE_Format函数）

SELECTDATE_FORMAT(request_time,'%y-%m') AS date,SUM(CASE WHEN task_command_value = 1 THEN 1 ELSE 0 END) AS count_1,SUM(CASE WHEN task_command_value = 2 THEN 1 ELSE 0 END) AS count_2,SUM(CASE WHEN task_command_value = 3 THEN 1 ELSE 0 END) AS count_3,SUM(CASE WHEN task_command_value = 4 THEN 1 ELSE 0 END) AS count_4,SUM(CASE WHEN task_command_value = 5 THEN 1 ELSE 0 END) AS count_5
FROM `数据库名`.表名
GROUP BY DATE_FORMAT(request_time,'%y-%m')
ORDER BY DATE_FORMAT(request_time,'%y-%m') DESC

输出结果：

二、分别统计各时间各类型数据总值

数据库的 date字段

数据类型：date

例子： 2024-04-09

原数据：

1、将数据按月汇总输出

SELECTDATE_FORMAT(date,'%y-%m') as date,SUM(everyday_1_bill) AS num1,SUM(everyday_2_bill) AS num2,SUM(everyday_3_bill) AS num3,Sum(everyday_4_bill) AS num4,SUM(everyday_5_count) AS num5,SUM(everyday_6_count) AS num6,SUM(everyday_7_count) AS num7,SUM(everyday_8_count) AS num8
FROM `数据库名`.数据库表名
GROUP BY DATE_FORMAT(date,'%y-%m')

输出结果：

2、将数据按周汇总输出（%U是周）

SELECTDATE_FORMAT(date,'%y-%m %U') as date,SUM(everyday_1_bill) AS num1,SUM(everyday_2_bill) AS num2,SUM(everyday_3_bill) AS num3,Sum(everyday_4_bill) AS num4,SUM(everyday_5_count) AS num5,SUM(everyday_6_count) AS num6,SUM(everyday_7_count) AS num7,SUM(everyday_8_count) AS num8
FROM `数据库名`.数据库表名
GROUP BY DATE_FORMAT(date,'%y-%m %U')

输出结果：