mysql 分组统计

文章包含mysql多种分组统计年 月 日 周 天 附带sql表结构以及说明

• mysql表结构如下

 1. CREATE TABLE `数据库名称`.`Untitled`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `dev_id` bigint(0) NULL DEFAULT NULL COMMENT '设备id',
  `dev_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '设备名称',
  `type` int(0) NULL DEFAULT NULL COMMENT '统计类别',
  `kwh` decimal(10, 2) NULL DEFAULT NULL COMMENT '日发电量',
  `earnings` decimal(10, 0) NULL DEFAULT NULL COMMENT '收益',
  `electric_hour` decimal(10, 0) NULL DEFAULT NULL COMMENT '发电小时',
  `about` int(0) NULL DEFAULT NULL COMMENT '左右',
  `create_time` date NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 64 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

首先回顾一下使用的mysql函数以及语句

1. ⽤户变量

#mysql⽤户变量，mysql中⽤户变量不⽤提前申明，在⽤的时候直接⽤“@变量名”使⽤就可以了。
其作⽤域为当前连接。
-- 第⼀种⽤法，使⽤set时可以⽤“=”或“:=”两种赋值符号赋值
set @age=19;
set @age:=20;
-- 第⼆种⽤法，使⽤select时必须⽤“:=”赋值符号赋值
select @age:=22;
select @age:=StuAge 
from demo.student 
where StuNo='A001';

2. mysql日期相关函数

#1 DATE_SUB() 函数从日期减去指定的时间间隔 [详细说明连接](https://www.runoob.com/sql/func-date-sub.html)。
#2 DATE_ADD() 函数向日期添加指定的时间间隔 [详细说明连接](https://www.runoob.com/sql/func-date-add.html)
#3 一下常用函数。
#3.1  DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
#3.2  IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

对于数据统计往往在统计报表和柱形图上展示使用，大部分都是一个周期范围做统计，有时需要根据日期进行动态的统计，在统计过程中动态日期选择范围 有可能会没有数据/或者对应的日期导致数据不全，那么就需要对数据进行补零填充，对日期进行加载。

• 统计 近七日的数据 第一种方式 缺点 需要每次select一个日期不灵活

 SELECT
    DATE_FORMAT( a.timeDay, '%m-%d' ) AS time,
    IFNULL( b.photovoltaic, 0 ) AS photovoltaic,
    IFNULL( b.fan, 0 ) AS fan,
    IFNULL( b.storageEnergy, 0 ) AS storageEnergy 
FROM
    (
    SELECT
        curdate( ) AS timeDay UNION ALL
    SELECT
        date_sub( curdate( ), INTERVAL 1 DAY ) AS timeDay UNION ALL
    SELECT
        date_sub( curdate( ), INTERVAL 2 DAY ) AS timeDay UNION ALL
    SELECT
        date_sub( curdate( ), INTERVAL 3 DAY ) AS timeDay UNION ALL
    SELECT
        date_sub( curdate( ), INTERVAL 4 DAY ) AS timeDay UNION ALL
    SELECT
        date_sub( curdate( ), INTERVAL 5 DAY ) AS timeDay UNION ALL
    SELECT
        date_sub( curdate( ), INTERVAL 6 DAY ) AS timeDay 
    ) a
    LEFT JOIN (
    SELECT
        date( d.create_time ) AS time,
        #这里用到了when 我说用来根据类别统计的 可以根据自己的业务调整
        sum( CASE WHEN d.type = '0' OR d.type='2'  THEN kwh ELSE 0 END ) AS photovoltaic,
        sum( CASE WHEN d.type = '1' THEN kwh ELSE 0 END ) AS fan,
        sum( CASE WHEN d.type = '5' THEN kwh ELSE 0 END ) AS storageEnergy 
    FROM
        statistical_intra_daily d 
    GROUP BY
        date( d.create_time ) 
    ) b ON a.timeDay = b.time 
ORDER BY
    time
		

• 统计 近七日的数据 第二种方式

SELECT IFNULL(b.kwh, 0 ) kwh,aa.date ts
              FROM (SELECT @cdate := date_add( @cdate, INTERVAL - 1 DAY ) date
              FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 DAY ) FROM      statistical_intra_daily LIMIT 7 ) a
             ) aa LEFT JOIN (select id,SUM(kwh) kwh, create_time from statistical_intra_daily
                             where   create_time >=DATE_SUB(CURDATE(), INTERVAL 7  DAY)  
        ) b on aa.date = b.create_time order by  aa.date asc
        ORDER BY aa.date DESC;

部分讲解

• 统计 本年每月的数据

	SELECT
    DATE_FORMAT( a.timeDay, '%y-%m') AS time,
    IFNULL( b.photovoltaic, 0 ) AS photovoltaic,
    IFNULL( b.fan, 0 ) AS fan,
    IFNULL( b.storageEnergy, 0 ) AS storageEnergy
FROM
    (
     SELECT
                 DATE_FORMAT(
                    @cdate := DATE_ADD(@cdate, INTERVAL - 1 MONTH),
                    '%y-%m-%d'
                 ) timeDay
              FROM
                 (
                    SELECT
                       @cdate := DATE_ADD(
                         #给定一个七日
                          DATE_FORMAT('2022-12-31', '%y-%m-%d'),
                          #需要先加上一次数据 用第一个子查询递减
                          INTERVAL + 1 MONTH
                       )
                    FROM
                       statistical_intra_daily
                 ) timeDay
                 #一年中12个月
              LIMIT 12
    ) a
    LEFT JOIN (
    SELECT
      DATE_FORMAT(create_time, '%y-%m') time,
        sum( CASE WHEN d.type = '0' OR d.type='2'  THEN kwh ELSE 0 END ) AS photovoltaic,
        sum( CASE WHEN d.type = '1' THEN kwh ELSE 0 END ) AS fan,
        sum( CASE WHEN d.type = '5' THEN kwh ELSE 0 END ) AS storageEnergy 
    FROM
        statistical_intra_daily d 
    GROUP BY
        date( d.create_time ) 
        
    ) b ON DATE_FORMAT(a.timeDay, '%y-%m') = b.time
ORDER BY
    time

执行结果

对执日期获取讲解，动态指定日期思路，以查询每月数据为例

SELECT
     #使用子查询的变量进行递减 然后看到limit了吗? 是取几个, 
     #如果条件是动态的比如2022年-2021年1月的数据,
     #只需要记住2022年到2021年1月有几个月 就做为limit条件既可,
     #如果你是做每月统计每日的, 更换日期既可 MONTH,
     #如果你要统计每个小时 每分钟的数据怎么办,
     #方法和这个类似都是需要得到这个分段日期 或者使用日历表进行关联,
     #为什么要怎么做应为日期不存在无法统计补零,
     #本人统计目前是怎么做的如果有其他方法可以评论 大家一起学习 加油.
	DATE_FORMAT( @cdate := DATE_ADD( @cdate, INTERVAL - 1 MONTH ), '%y-%m' ) HOUR 
FROM
	( SELECT @cdate := DATE_ADD( DATE_FORMAT( '2022-12-31', '%y-%m-%d' ), INTERVAL + 1 MONTH ) FROM statistical_intra_daily ) t0 
	LIMIT 12