日统计
SELECT DISTINCT(DATE_FORMAT(create_time,'%Y-%m-%d')) as day ,count(*) as daycount from 表名 GROUP BY day
近30天
select COUNT(1) as 30days from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)
近20天
SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as day ,count(*) as count from 表名 where date(时间字段名) >=date_sub(curdate(), interval 20 DAY) GROUP BY DAY ORDER BY DAY asc
近10年
SELECT DATE_FORMAT(create_time,'%Y') as year ,count(*) as count from 表名 where date(时间字段名) >=date_sub(curdate(), interval 10 YEAR) GROUP BY YEAR ORDER BY YEAR asc
当天
select COUNT(1) as today from 表名 where to_days(时间字段名) = to_days(now());
昨天
select COUNT(1) as Yesterday from 表名 where to_days(now())-to_days(时间字段名) <= 1
本季度
select COUNT(1) as ThisQuarter from 表名 where QUARTER(时间字段名)=QUARTER(now());
上季度
select COUNT(1) as LastQuarter from 表名 where QUARTER(时间字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
这周
SELECT COUNT(1) as thisWeek FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now());
上周
SELECT COUNT(1) as lastWeek FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;
每五分钟统计
SELECT concat( date_format(create_time, '%Y-%m-%d %H:' ) , floor( date_format(create_time, '%i' ) /5 ) ) AS 时间段, count( * ) as 数量 FROM 表名 GROUP BY 时间段