一、场景描述：

在工作中我们常常需要按照天、周、月、年统计一些信息，或计算同比环比、或利用ECharts数据展示、或利用Pandas 数据分析等等

二、基本方案

将数据库中带时间点的信息，通常为​​creat_time​​​、​​update_time​​ 字段，利用date_format转换日期格式后，然后再用group by分组查询

三、示例查询

  • 查询每天fans 数量
select date_format(create_time,'%Y-%m-%d') as date, count(*) as total from fans_user  group by date order by date desc;
  • 查询最近30天每天的fans数量
select date_format(create_time,'%Y-%m-%d') as date, count(*) as total from fans_user  group by date order by date desc limit 30;
  • 查询每周的fans数量
select week(create_time) as weeks, count(*) as total from fans_user  group by weeks order by weeks desc
  • 查询每月fans的数量
select month(create_time) as months, count(*) as total from fans_user  group by months order by months desc
  • 查询每年fans数量
select year(create_time) as years, count(*) as total from fans_user  group by years order by years desc

四、补充

如果查询某天的fans数量

  • 正确写法，速度快
select * from fans_user where date_format(create_time, '%Y-%m-%d') = '2020-09-10'
  • 错误写法,数据量大时非常慢，花费的时间是上面的5倍
select * from fans_user where create_time between '2020-09-10 00:00:00'  AND '2020-09-10 23:59:59'