高效实现统计分析（按日，月，周）查询功能

统计分析实现一个sql查询所有数据

业务场景：需要统计某个时间段内的所有数据，按照天，周，月进行分组显示
问题：如果只根据现有的表进行统计，会出现某个时间内没有数据
解决：将现有的数据外连接一个所有时间的表
比如：我要查询2019-02-02~2019-02-09之间所有的数据，如果只根据现有的数据查询，会出现时间空缺的情况

准备：一个实际记录表和一个辅助表
辅助表：用于查询指定时间内所有的天，周，年
SET FOREIGN_KEY_CHECKS=0;

---

– Table structure for num

---

DROP TABLE IF EXISTS num;
CREATE TABLE num (
i int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (i)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

– Records of num

---

INSERT INTO num VALUES (‘0’);
INSERT INTO num VALUES (‘1’);
INSERT INTO num VALUES (‘2’);
INSERT INTO num VALUES (‘3’);
INSERT INTO num VALUES (‘4’);
INSERT INTO num VALUES (‘5’);
INSERT INTO num VALUES (‘6’);
INSERT INTO num VALUES (‘7’);
INSERT INTO num VALUES (‘8’);
INSERT INTO num VALUES (‘9’);

1. 按天分
SELECT create_time,sum(chapter_studyTime) from (
SELECT temp.date as create_time ,
coalesce(u.unmber,0) as chapter_studyTime from(
– 统计所有天数查询
SELECT
adddate(‘2019-01-01’, numlist.id) AS ‘date’
FROM
(
SELECT
n1.i + n10.i * 10 + n100.i * 100 AS id
FROM
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
) AS numlist
WHERE
adddate(‘2019-01-01’, numlist.id) < date_add(‘2019-02-02’,interval 1 day)
– 统计所有天数查询
) temp
LEFT JOIN
(
– 统计指定天数内的所有记录
SELECT left(create_time,10)as udate,sum(chapter_studyTime) unmber FROM tb_study_log where is_delete=0 and create_time > ‘2019-01-01’ and create_time <‘2019-02-02’
GROUP BY udate,uuid HAVING unmber >= 600000
– 统计指定天数内的所有记录
) u on temp.date = u.udate order by temp.date asc

) s GROUP BY create_time;

按天分最终效果

2. 按周分

SELECT create_time,sum(chapter_studyTime) from (
SELECT tb2.,coalesce(tb1.chapter_studyTime,0) chapter_studyTime from
(
– 查询指定时间内按周分组之后的数据
SELECT yearweek(date_format(create_time,’%Y-%m-%d’)) as create_time,sum(chapter_studyTime) as chapter_studyTime from tb_study_log
where create_time >‘2019-01-01’ and create_time <= ‘2019-02-01’
and is_delete=0 GROUP BY yearweek(date_format(create_time,’%Y-%m-%d’))
– 查询指定时间内按周分组之后的数据
) tb1
RIGHT JOIN (
– 查询指定时间内所有的周
SELECT yearweek(date_format(t1.create_time,’%Y-%m-%d’)) as create_time from
(
select adddate(‘2019-01-01’ , numlist.id) as ‘create_time’ from (SELECT n1.i + n10.i10 + n100.i*100 AS id FROM num n1 cross join num as n10 cross
join num as n100) as numlist where adddate(‘2019-01-01’ , numlist.id) <=‘2019-02-01’ )t1
GROUP BY yearweek(date_format(t1.create_time,’%Y-%m-%d’))
– 查询指定时间内所有的周
) tb2 on tb1.create_time = tb2.create_time ORDER BY tb2.create_time asc

) s GROUP BY create_time;

按周分最终效果

3. 按月分

select yearweek(‘2019-01-01’);
SELECT create_time,sum(chapter_studyTime) from (
SELECT left(temp.date,7) as create_time ,
coalesce(u.unmber,0) as chapter_studyTime from(
– 查询指定时间内所有的月
SELECT adddate(‘2019-01-01’, interval numlist.id month) AS ‘date’ FROM
(
SELECT * from
(SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10) a
where a.id <=11
) AS numlist
WHERE adddate(‘2019-01-01’, interval numlist.id month) <= ‘2019-02-01’
– 查询指定时间内所有的月
) temp
LEFT JOIN
(
– 查询指定时间内按月分组之后的数据
SELECT left(create_time,7)as udate,sum(chapter_studyTime) unmber FROM tb_study_log
where create_time > ‘2019-01-01’ and create_time < ‘2019-02-01’ and is_delete=0 GROUP BY udate,uuid HAVING unmber >= 600000
– 查询指定时间内按月分组之后的数据
) u
on left(temp.date,7) = u.udate ORDER BY temp.date asc
) s GROUP BY create_time;

按月分最终效果