mysql如何方便查詢一天多个时间段的数据

需求如图查询一天多个时间段的不同数据做折线图
如图：

当天数据

mysql可以这样写 DATE_FORMAT(create_time,'%Y-%m-%d') = CURDATE();意思是等于当天时间取时间段
%H是24小时制
%h是12小时制

    SELECT
     DATE_FORMAT(now(),'%Y-%m-%d') create_time,
    SUM(IF(DATE_FORMAT(create_time,"%H")>=21 AND DATE_FORMAT(create_time,"%H")<24,1,0))'00:00',
    SUM(IF(DATE_FORMAT(create_time,"%H")>=0 AND DATE_FORMAT(create_time,"%H")<3,1,0))'03:00',
    SUM(IF(DATE_FORMAT(create_time,"%H")>=3 AND DATE_FORMAT(create_time,"%H")<6,1,0))'06:00',
    SUM(IF(DATE_FORMAT(create_time,"%H")>=6 AND DATE_FORMAT(create_time,"%H")<9,1,0))'09:00',
    SUM(IF(DATE_FORMAT(create_time,"%H")>=9 AND DATE_FORMAT(create_time,"%H")<12,1,0))'12:00',
    SUM(IF(DATE_FORMAT(create_time,"%H")>=12 AND DATE_FORMAT(create_time,"%H")<15,1,0))'15:00',
    SUM(IF(DATE_FORMAT(create_time,"%H")>=15 AND DATE_FORMAT(create_time,"%H")<18,1,0))'18:00',
    SUM(IF(DATE_FORMAT(create_time,"%H")>=18 AND DATE_FORMAT(create_time,"%H")<21,1,0))'21:00'
    from channel_ip_statistic
    WHERE
    channel = 'DY0101' and DATE_FORMAT(create_time,'%Y-%m-%d') = CURDATE();

如果查询最近7天数据呢：

7天数数据该如何查询呢

 SELECT a.click_date,b.count
  FROM (
  SELECT CURDATE() AS click_date
  UNION ALL
  SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY),"") AS click_date
  UNION ALL
  SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL 2 DAY),"") AS click_date
  UNION ALL
  SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL 3 DAY),"") AS click_date
  UNION ALL
  SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL 4 DAY),"") AS click_date
  UNION ALL
  SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL 5 DAY),"") AS click_date
  UNION ALL
  SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL 6 DAY),"") AS click_date
  ) a LEFT JOIN (
  SELECT DATE(create_time) AS DATETIME, COUNT(*) AS COUNT
  FROM channel_ip_statistic where 1=1 $condition1  $condition
  GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')
  ) b ON a.click_date = b.datetime;

数据