MySql-日期分组

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

数据库的 request_time字段

数据类型：timestamp

默认值：CURRENT_TIMESTAMP

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

原数据：

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

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

   SELECT
            DATE(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 DATE(request_time)
        ORDER BY DATE(request_time) DESC

输出结果：

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

SELECT
    MONTH(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函数）

SELECT
    DATE_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、将数据按月汇总输出

SELECT
    DATE_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是周）

SELECT
    DATE_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')

输出结果：