mysql groupBy 按日期分组

需求中经常会有按日期,月份,年份分组统计的操作

那么groupBy直接是实现不了的,需要转化一下,话不多说,看代码

SELECT
	FROM_UNIXTIME(create_time, '%Y-%m-%d') as day, create_time,
	count( DISTINCT uid ) AS amount 
FROM
	`black_production_user` 
WHERE
	create_time >= 1588262400 
	AND create_time <= 1607529599
	GROUP BY day

以上代码可以按每天分组并统计每天的新增数据量,按月或年的只要修改日期格式就好

但上面只统计到了有数据的天数,某天没有时就不返回该天,实际情况中某天没有希望返回0

那按上面的结果在程序中循环赋默认值也可以

其实SQL也可以实现,具体看下面代码:

select day,sum(count) as add_num from (
select count(DISTINCT uid) count, FROM_UNIXTIME(create_time,'%Y-%m-%d') day from `black_production_user` a
where a.create_time >= 1588262400 and a.create_time < 1607529599
GROUP BY day
UNION ALL
select @uu:=0 as count,day from (
select @num:=@num+1 as number,date_format(adddate('2020-05-01 00:00:00', INTERVAL @num DAY),'%Y-%m-%d') as day
from `black_production_user` a ,(select @num:=-1) t
order by day ) rr
) sss GROUP BY sss.day ORDER BY day

这条SQL稍微有点绕,需要自己理解便可,查询结果如下,某天没有数据默认有 0 填充

####注意: FROM_UNIXTIME 针对于数据时间存储格式为时间戳, 若库中时间存储的日期格式 : 如 : 2020-12-10 10:03:24 ,这样的使用 DATE_FORMAT 即可