临沂培训学校网站建设,做餐饮网站价格,网站扁平化布局,庆云网站seo一、分别统计各时间各类型数据条数
数据库的 request_time字段
数据类型#xff1a;timestamp
默认值#xff1a;CURRENT_TIMESTAMP
例子#xff1a; 2024-01-26 08:25:48
原数据#xff1a; 1、将数据按照日期#xff08;年月日#xff09;形式输出
按照request_…一、分别统计各时间各类型数据条数
数据库的 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)
输出结果
