常用的按日/月/周以及数字区间统计SQL

#数字分区间统计
select interval(id, 10, 20, 30) stage, count(1) amount
from t_meta_value
group by interval(id, 10, 20, 30);

#按天统计
SELECT DATE_FORMAT(create_time, '%y-%m-%d') dt, COUNT(1) amount
FROM t_meta_value
WHERE create_time BETWEEN '2019-01-17 00:00:00' AND '2019-04-22 23:59:59'
GROUP BY DATE(create_time)
ORDER BY create_time ASC;

#按周统计
SELECT YEAR(create_time) yr, WEEK(create_time,1) wk, 
CONCAT(
DATE_FORMAT(DATE_SUB(create_time,INTERVAL WEEKDAY(create_time) DAY), '%y%m%d'),'-',
DATE_FORMAT(DATE_ADD(create_time,INTERVAL 6-WEEKDAY(create_time) DAY), '%y%m%d')
) AS week_range,
count(1) amount
FROM t_meta_value
WHERE create_time BETWEEN '2019-01-17 00:00:00' AND '2019-04-22 23:59:59'
GROUP BY YEAR(create_time),WEEK(create_time,1)
ORDER BY create_time ASC;

#按月统计
SELECT DATE_FORMAT(create_time ,'%y-%m') mth, COUNT(1) amount
FROM t_meta_value
WHERE create_time BETWEEN '2019-01-17 00:00:00' AND '2019-04-22 23:59:59'
GROUP BY YEAR(create_time), MONTH(create_time)
ORDER BY create_time ASC;