1,对一天24小时内每小时的数据量求和:
SELECT
IFNULL(SUM(CASE HOUR(时间字段) WHEN 0 THEN 1 ELSE 0 END),0)as '0',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 1 THEN 1 ELSE 0 END),0)as '1',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
FROM 表名称
WHERE date(时间字段) >= CURDATE() and date(时间字段) <= CURDATE()
2,对昨天24小时内每小时的数据量求和:
SELECT
IFNULL(SUM(CASE HOUR(时间字段) WHEN 0 THEN 1 ELSE 0 END),0)as '0',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 1 THEN 1 ELSE 0 END),0)as '1',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
IFNULL(SUM(CASE HOUR(时间字段) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
FROM 表名称
WHERE date(access_time) >= date_sub(CURDATE() , INTERVAL 1 DAY) AND date(access_time) < CURDATE()
这个红色的1 就是前进的天数,比如说昨天 是1,前天就是2,以此类推。
效果:
3,对一天内的数据量求和查询:
我们需要生成一个日历的表,然后和原来的数据,联合查询,如下:
CREATE TABLE if not exists calendar(datelist date); -- 生成一个存储日期的表,datalist是字段名
-- 这里是生成并插入日期数据
INSERT INTO calendar(datelist) SELECT
adddate(
( -- 这里的起始日期,你可以换成当前日期
DATE_FORMAT("2016-1-1", '%Y-%m-%d')
),
numlist.id
) AS `date`
FROM
(
SELECT
n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
FROM
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
CROSS JOIN num AS n1000
CROSS JOIN num AS n10000
) AS numlist;
联合查询sql如下:
SELECT
date(dday) ddate,
count(*) - 1 as num
FROM
(
SELECT
datelist as dday
FROM
calendar_data
-- 这里是限制返回最近7天的数据
where DATE_SUB(CURDATE(), INTERVAL 6 DAY)<= date(datelist)&&date(datelist)<=CURDATE()UNION ALL
SELECT
时间字段
FROM
表名称
where DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(时间字段)AND date(时间字段) <= CURDATE()and access_time is not null
) a
GROUP BY ddate
结果如下:
上面的6是控制查询的天数,有需要更改条件即可。
3,对限定日期内的数据去重复求和查询:
SELECT
DATE_FORMAT(时间字段,'%Y-%m-%d') as ddate,
count(DISTINCT 去重复字段) as num
FROM
表名
where DATE_SUB(CURDATE(), INTERVAL 7 DAY)<= date(时间字段) AND date(时间字段) <= CURDATE()and 时间字段 is not null
GROUP BY
ddate
结果: