«

MySQL怎么按天分组统计一定时间内的数据

时间:2024-8-5 09:34     作者:韩俊     分类: Mysql


今天小编给大家分享一下MySQL怎么按天分组统计一定时间内的数据的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下吧。

    思路分析

      如果能每天都有数据,可以直接使用最简单的查询节省时间。

      要能够展示每天的日期就要创建一张日期虚拟表作为连接表。

      要使每天数据不为null,使用

      IFNULL(count,0)
      函数进行判断是否补零。

    SQL实现

    下面以查询近一个月每天的数据为示例展示SQL实现。

    按天统计数据

    1.没数据的一天过滤

    SELECT DATE(CREATE_DATE) as date, COUNT(1) as count
    FROM 表
    WHERE 字段 = '1'
      AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') >=
          DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d')
    GROUP BY DATE(CREATE_DATE);

    查询结果:

    2.没数据的一天有日期总数为null

    SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,data.num AS count
    FROM (
             SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
             FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
                   FROM 表
                  ) day
             WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
                   DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
             ORDER BY date
         ) dates
             LEFT JOIN (
        SELECT  COUNT(1) AS num, DATE(CREATE_DATE) AS time
        FROM audit_work_sheet
        WHERE 字段 = '1'
          AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
              DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
        GROUP BY DATE(CREATE_DATE)
    ) data ON DATE(time) = date
    ORDER BY date;

    查询结果:

    3.没数据的一天有日期总数为0

    SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,IFNULL(data.num, 0) AS count
    FROM (
             SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
             FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
                   FROM 表
                  ) day
             WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
                   DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
             ORDER BY date
         ) dates
             LEFT JOIN (
        SELECT  COUNT(1) AS num, DATE(CREATE_DATE) AS time
        FROM audit_work_sheet
        WHERE 字段 = '1'
          AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
              DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
        GROUP BY DATE(CREATE_DATE)
    ) data ON DATE(time) = date
    ORDER BY date;

    查询结果

    4.加入其它分组字段没数据的一天有日期总数为0

    SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,data.level AS level, IFNULL(data.num, 0) AS count
    FROM (
             SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
             FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
                   FROM 表
                  ) day
             WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
                   DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
             ORDER BY date
         ) dates
             LEFT JOIN (
        SELECT RISK_LEVEL AS level, COUNT(1) AS num, DATE(CREATE_DATE) AS time
        FROM audit_work_sheet
        WHERE 字段 = '1'
          AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
              DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
        GROUP BY DATE(CREATE_DATE),level
    ) data ON DATE(time) = date
    ORDER BY date,level;

    查询结果

    (中间过多数据不展示)

    以上就是按天统计数据没有数据补零的SQL语句,按月按年等其它时间段,或其它分组条件,都可以直接修改参数后查询出结果。

    附:Mysql按日、周、月进行分组统计

    DATE_FORMAT 是 MySQL 内置的一个函数,作用是以不同的格式显示日期/时间数据。具体的语法如下:

    DATE_FORMAT(date,format)

    其中

      date:合法的日期

      format:规定日期/时间的输出格式,其中format可使用的格式可以查看以下链接

    下面我们通过具体例子来看如何通过 DATE_FORMAT 进行分组统计:

    下表两列分别代表产品买出的准确时间(精确到秒),和买出的产品类型。

    start_time product_no

    2017/12/1 00:00:112A

    2017/12/3 07:51:113C

    2017/12/3 07:59:253C

    2017/12/5 15:40:456C

    现在我们需要对每天,每周,每月各个产品的销量进行统计,

    1)按天统计:

    select DATE_FORMAT(start_time,'%Y%m%d') days,count(product_no) count from test group by days;

    2)按周统计:

    select DATE_FORMAT(start_time,'%Y%u') weeks,count(product_no) count from test group by weeks;

    3)按月统计:

    select DATE_FORMAT(start_time,'%Y%m') months,count(product_no) count from test group bymonths;

    标签: mysql

    热门推荐