题目组三(MySQL5.1版本)
函数小练习 求每七天的平均数
原始数据:
insert into test(datetime,sum) values(‘2018-6-1’,‘10’);
insert into test(datetime,sum) values(‘2018-6-2’,‘11’);
insert into test(datetime,sum) values(‘2018-6-3’,‘11’);
insert into test(datetime,sum) values(‘2018-6-4’,‘12’);
insert into test(datetime,sum) values(‘2018-6-5’,‘14’);
insert into test(datetime,sum) values(‘2018-6-6’,‘15’);
insert into test(datetime,sum) values(‘2018-6-7’,‘13’);
insert into test(datetime,sum) values(‘2018-6-8’,‘37’);
insert into test(datetime,sum) values(‘2018-6-9’,‘18’);
insert into test(datetime,sum) values(‘2018-6-10’,‘19’);
insert into test(datetime,sum) values(‘2018-6-11’,‘10’);
insert into test(datetime,sum) values(‘2018-6-12’,‘11’);
insert into test(datetime,sum) values(‘2018-6-13’,‘11’);
insert into test(datetime,sum) values(‘2018-6-14’,‘12’);
最后结果:
2018-06-01~2018-06-07 12
2018-06-08~2018-06-14 15
DATE_ADD(OrderDate,INTERVAL 2 DAY)
答案
解法一:
select concat_ws(’~’,
date_add(‘2018-06-01’,interval floor(datediff(datetime,‘2018-06-01’)/7)*7 day),
date_add(‘2018-06-01’,interval floor(datediff(datetime,‘2018-06-01’)/7)*7+6 day)
) as time,floor(sum(sum)/7)
from test
group by time;
解法二:
select CONCAT_WS(’~’,DATE_ADD(‘2018-06-01’,INTERVAL FLOOR(DATEDIFF(datetime,‘2018-6-1’)/7)*7 day),
DATE_ADD(‘2018-06-01’,INTERVAL FLOOR(DATEDIFF(datetime,‘2018-6-1’)/7)*7+6 day)) as date,FLOOR(avg(sum))
from test group by date;