1. 首页
  2. 软件开发
  3. 数据库
  4. MYSQL按时间段(按小时分组)分组查询当天小时内数据

MYSQL按时间段(按小时分组)分组查询当天小时内数据

  • 发布于 2024-09-25
  • 1 次阅读

运营一个需求，要查询一些数据，每1小时一次的数据 ，Excel表格如下：

第一肯定要是用 GROUP BY 然后

一、单表

SELECT
    DATE_FORMAT( create_time, '%Y-%m-%d %H' ) as time,
    COUNT( DISTINCT member_id) as count 
FROM
    tmp_wp_table 
GROUP BY
    time 
ORDER BY
    time

结果：

二、多表

多表查询就应该用到union连接， 这里根据需求选择 union 还是 union all 注意union 和 union all 的区别

SELECT DATE_FORMAT(x.fenDate, '%Y-%m-%d %H' ) as time, count(DISTINCT x.member_id) as count
    FROM(
    SELECT DISTINCT b.user_id, FROM_UNIXTIME(b.intime) as fenDate
    FROM
        wp_table_pay_1 b 
    WHERE
        b.pay1_status = 1 
        AND b.remarks IS NULL 
        AND FROM_UNIXTIME( b.intime )>'2019-06-27' AND FROM_UNIXTIME( b.intime )<'2019-06-28' UNION ALL

    SELECT DISTINCT b.user_id, b.date as fenDate
    FROM
        wp_table_pay_2 b 
    WHERE
        b.pay2_status = 1 
        AND b.remarks IS NULL  
        AND b.date>'2019-06-27' AND b.date<'2019-06-28' UNION ALL

    SELECT DISTINCT b.user_id , b.create_time as fenDate
    FROM
        wp_table_pay_3 b 
    WHERE
        b.pay3_status = 1 
    AND b.remarks IS NULL 
    AND b.create_time>'2019-06-27' AND b.create_time<'2019-06-28' 

    ) x GROUP BY time  ORDER BY time asc

查询结果：

如果有更好的处理方式。欢迎评论留言进行交流。

补充：这样查出来的结果如果是null，就不会显示在结果集列表中

解决思路

  1. 思路一： 可以在自己的程序中做额外的补零处理
  2. 思路二：可以自己新建时间列表，把未来10年的日期放进去，然后再跟统计表作连接查询
  3. 思路三：构建一个最近一天（或几天）的结果集，然后和查询的结果集合做left join(本文采用思路三的方式)
select a.time,b.count as count
from (
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 1 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 2 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 3 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 4 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 5 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 6 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 7 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 8 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 9 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 10 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 11 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 12 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 13 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 14 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 15 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 16 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 17 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 18 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 19 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 20 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 21 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 22 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 23 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 24 HOUR), '%Y-%m-%d %H' ) as time
) a left join (
    SELECT
      DATE_FORMAT( create_time, '%Y-%m-%d %H' ) as time,
      COUNT( DISTINCT member_id) as count
  FROM
      tmp_wp_table 
  WHERE create_time>'2019-11-20' and create_time<'2019-11-20 23:59:59'
  GROUP BY time
) b on a.time = b.time ORDER BY a.time;

这样查询的结果为空， 需要把NULL设置为0，这时我们可以利用ifnull函数

select a.time,ifnull(b.count,0) as count
from (
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 1 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 2 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 3 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 4 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 5 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 6 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 7 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 8 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 9 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 10 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 11 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 12 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 13 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 14 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 15 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 16 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 17 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 18 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 19 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 20 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 21 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 22 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 23 HOUR), '%Y-%m-%d %H' ) as time union all
    SELECT DATE_FORMAT(date_sub(DATE('2019-11-21'), interval 24 HOUR), '%Y-%m-%d %H' ) as time
) a left join (
    SELECT
      DATE_FORMAT( create_time, '%Y-%m-%d %H' ) as time,
      COUNT( DISTINCT member_id) as count
  FROM
      tmp_wp_table
  WHERE create_time>'2019-11-20' and create_time<'2019-11-20 23:59:59'
  GROUP BY time
) b on a.time = b.time ORDER BY a.time;

查询结果：

这时构建7天的结果集

    SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date


原文链接: https://onlyou.blog.csdn.net//article/details/93980085