如何用一条sql语句过去一段时间的总用户数

最近有统计用户数折线图的需求，就是说比如最近一周的用户数，这样就得返回最近一周每天的用户总数
比如有一张用户表，字段分别为名字和注册时间

我要拿到最近一周每天的用户总数，以当前这张表为例，我要拿到的数据为

或者这样的

搜索了很多只有获取增量的，而且如果某一天没有新增用户数查到的结果就没有这一天，代码中去做处理还比较麻烦

SELECT DATE_FORMAT(time, "%Y-%m-%d"), COUNT(*) 
FROM user
GROUP BY DATE_FORMAT(time, "%Y-%m-%d") ;

测试数据

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` (`name`, `time`) VALUES ('a', '2017-12-11 15:39:09');
INSERT INTO `user` (`name`, `time`) VALUES ('b', '2017-12-13 15:39:20');
INSERT INTO `user` (`name`, `time`) VALUES ('c', '2017-12-13 15:39:26');
INSERT INTO `user` (`name`, `time`) VALUES ('d', '2017-12-14 15:39:31');
INSERT INTO `user` (`name`, `time`) VALUES ('e', '2017-12-15 15:39:36');

5 个解决方案

#1

先生成一个连续日期的临时表，然后 left join现在的表，条件是日期相等，然后根据日期分组

#2

只能自己维护一个日期表

#3

与北京日期表相匹配。

#4

引用 1 楼 wandier 的回复:

先生成一个连续日期的临时表，然后 left join现在的表，条件是日期相等，然后根据日期分组

给了我一些灵感，好像只有这种办法，但左连接的条件不是日期相等，日期相等拿到的只是增量，条件应该是user表的日期小于等于临时表的日期，这样分组后就可以拿到每一天的总用户数了。

假设日期临时表是  temp_date，字段 f_date

select *, case when name is null then 0 else count(*) end as counts from temp_date left join user on date_format(user.time, '%Y-%m-%d')<=temp_date.f_date group by f_date;

#5

引用 4 楼 Listening_倾听的回复:

Quote: 引用 1 楼 wandier 的回复:

先生成一个连续日期的临时表，然后 left join现在的表，条件是日期相等，然后根据日期分组

给了我一些灵感，好像只有这种办法，但左连接的条件不是日期相等，日期相等拿到的只是增量，条件应该是user表的日期小于等于临时表的日期，这样分组后就可以拿到每一天的总用户数了。

假设日期临时表是  temp_date，字段 f_date

select *, case when name is null then 0 else count(*) end as counts from temp_date left join user on date_format(user.time, '%Y-%m-%d')<=temp_date.f_date group by f_date;

是的，应该是小于等于