MYSQL按时间段(按小时分组)分组查询当天小时内数据_mysql按小时分组_zuihongyan518的博客

相关文章推荐
有腹肌的上铺 · 买超级经济舱被安排坐最后一排，网红：空姐乘客 ...· 1 年前 ·
天涯 · 我想知道笔记本电脑rtx3050显卡是什么水 ...· 2 年前 ·
文雅的数据线 · 爱驰获滴滴投资背后：高管卖车难解销量不佳 ...· 2 年前 ·
唠叨的豆芽 · 溜云库4.0怎么切换企业版 - 抖音· 2 年前 ·
强健的啄木鸟 · 中国古代“萌物”大赏_表演· 2 年前 ·
SELECT
	DATE_FORMAT( create_time, '%Y-%m-%d %H' ) as time,
	COUNT( DISTINCT member_id) as count 
	tmp_wp_table 
GROUP BY
ORDER BY
多表查询就应该用到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
		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
		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
		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，就不会显示在结果集列表中 
 思路一： 可以在自己的程序中做额外的补零处理
思路二：可以自己新建时间列表，把未来10年的日期放进去，然后再跟统计表作连接查询
思路三：构建一个最近一天（或几天）的结果集，然后和查询的结果集合做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
	  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
	  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 
                    运营一个需求，要查询一些数据，每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 ...
SELECT COUNT(log_id) AS u_count FROM logdb.`log`
GROUP BY TIMESTAMP-TIMESTAMP%(1*60) 
其中timestamp 是数据库时间字段。 括号中的1 是间隔时间，1分钟。 如果是1小时，就写60，如果是30s 就写0.5
那被分组的id 如何解决呢？分完组我们还需要 哪些记录被分到哪些组了。
我们可以这么做。
SELECT COUNT(log_id) AS u_count ,GROUP_CONCAT
this is incompatible with sql_mode=only_full_group_by
那么在此执行这两句
select @@global.sql_mode;
set sql_mode = (SELECT REPLACE(@@sql_mode, ‘ONLY_FULL_GROUP_BY’, ‘’));
%y:表示显示两位的年份
%m:表示月份
%d:表示天数
这样就可以了。DATE_FORMAT()
select DATE_FORMAT(create_time,’%Y%u’) weeks,count(caseid) count from tc_case group by weeks;
select DATE_FORMAT(create_time,’%Y%m’) months,count(caseid) count from tc_case group by months;
				mysql按天，小时，半小时，N分钟，分钟进行数据分组统计mysql不同时间粒度下的分组统计按天统计按小时统计按半小时统计按N分钟统计按分钟统计
mysql不同时间粒度下的分组统计
我们在做项目或者数据分析时，经常遇到这样的需求：统计不同时间粒度下的数据分布情况，例如，每一天中每个小时网站的访问量，某路口每半个小时通过的车辆数量等。对于此类的问题，一个sql简单的查询就能实现，故特此记录下，方便以...
				SELECT
DATE_FORMAT( order_created_at, ‘%Y-%m-%d %h’ ) AS orderCreatedAt,
business_mail_status AS businessMailStatus,
count(*) AS subscribeErrorCount
t_order
WHERE
( order_created_at >= ‘2021-12-05 17:00:00.0’ AND order_created_at <= ‘2021-12-18 
				要实现这样一个功能
 select count(1),小时 from user where createDate=今天 group by 每小时
原本以为很简单的一个聚合很容易找到相关资料，没想到花了好几个小时
 首先建一个测试的index和type，分别为ksearch_test和user，
建立mapping
http://ip:port/ksearch_test/user...
今天刚好是碰到一个报表需求，要求根据时段统计各工单的数量。这个就有点皮实了，以前都没搞过这玩意。于是研究了几下。
二、准备开始
首先一个问题，想根据时间段进行统计，这个问题先不考虑，先考虑，如果按照一小时统计呢，该怎么去做，于是就一顿操作，写出以下sql，这个基本的相信没什么难度，主要是说，怎么去单独拿到小时，这里用到“DATE_FORMAT”函数。
SELECT
	DATE_FORMAT( create_order_time, '%H' ) AS h,
	COUNT( 1 )