mysql根据日周月分组计算

最新推荐文章于 2023-07-25 10:28:10 发布

chenjiang_

最新推荐文章于 2023-07-25 10:28:10 发布

阅读量913

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/chenjiang_/article/details/126221254

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql根据日周月分组计算

一、mysql生成默认当前日前往前的日期

1、按月份默认生成

(SELECT DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) date FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 MONTH ) FROM `mysql`.`help_topic`  LIMIT 14) a)

生成结果
在这里插入图片描述

2、按天默认生成

(select (curdate() + interval (cast(`mysql`.`help_topic`.`help_topic_id` as signed) - 10) day) AS date from `mysql`.`help_topic` having (date <= date_format(curdate(),'%Y-%m-%d')))

生成结果
在这里插入图片描述

二、mysql按日周月分组统计

1、获取原始数据

SELECT wgwl_date_event_volume,date_processing_capacity,date_unprocessed_volume,zjg_date_event_volume,date_case_volume, overView_time FROM task_status_statistics

获取结果
在这里插入图片描述
2、按日统计默认最近10天的数据

SELECT   SUM(b.zjg_date_event_volume)zjg_date_event_volume,SUM(b.date_case_volume)date_case_volume,temp.date overView_time
FROM
     (select (curdate() + interval (cast(`mysql`.`help_topic`.`help_topic_id` as signed) - 10) day) AS date from `mysql`.`help_topic` having (date <= date_format(curdate(),'%Y-%m-%d'))) temp
LEFT JOIN (select zjg_date_event_volume,date_case_volume, overView_time from task_status_statistics where overview_time >=DATE_SUB(CURDATE(), INTERVAL 10  DAY))
      b on temp.date = DATE_FORMAT(b.overview_time,'%Y-%m-%d')
GROUP BY temp.date ORDER BY temp.date DESC LIMIT 10

统计结果
在这里插入图片描述
3、按周统计默认最近10个周的数据

SELECT
       SUM(b.zjg_date_event_volume) zjg_date_event_volume,SUM(b.date_case_volume) date_case_volume,CONCAT(SUBSTR(DATE_FORMAT(temp.date,'%Y-%u') FROM 1 FOR 4),'年第',SUBSTR(DATE_FORMAT(temp.date,'%Y-%u'),6),'周') weeks,temp.date overView_time
FROM
       (select (curdate() + interval (cast(`mysql`.`help_topic`.`help_topic_id` as signed) - 100) day) AS date from `mysql`.`help_topic` having (date <= date_format(curdate(),'%Y-%m-%d'))) temp
LEFT JOIN (select zjg_date_event_volume,date_case_volume, overView_time from task_status_statistics where overview_time >=DATE_SUB(CURDATE(), INTERVAL 76  DAY))
        b on temp.date = DATE_FORMAT(b.overview_time,'%Y-%m-%d')
GROUP BY date_format(temp.date, '%Y-%u') ORDER BY temp.date DESC LIMIT 10

统计结果
在这里插入图片描述
4、按月统计默认最近10个月的数据

SELECT 
    SUM(b.zjg_date_event_volume) zjg_date_event_volume,SUM(b.date_case_volume) date_case_volume,aa.date overView_time
FROM
    (SELECT DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) date FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 MONTH ) FROM `mysql`.`help_topic`  LIMIT 14) a) aa 
LEFT JOIN (SELECT zjg_date_event_volume,date_case_volume,mon overView_time FROM (SELECT zjg_date_event_volume,date_case_volume,overView_time, DATE_FORMAT(overView_time,'%Y-%m') mon FROM task_status_statistics WHERE overView_time >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR)) a) b on aa.date = b.overView_time
GROUP BY aa.date order by aa.date desc LIMIT 10