每日记录数，包含0个值

Question

我正在尝试获取一个查询，该查询将显示过去7天内每天的访问量。我提出的问题工作，但它有局限性，我不知道如何摆脱。

想象一下，今天是2019年8月4日。我们的表visits保存了用户访问网站的时间戳：

ID | timestamp
1 | 2019-08-03
2 | 2019-08-03
3 | 2019-08-02
4 | 2019-07-31
5 | 2019-07-31
6 | 2019-07-31
7 | 2019-07-31
8 | 2019-07-30
9 | 2019-07-30
10 | 2019-07-28

目标:获取过去7天内网站的每日访问量。所以结果应该是这样的：

DATE | NumberOfVisitis
2018-08-04 | 0
2018-08-03 | 2
2018-08-02 | 1
2018-08-01 | 0
2018-07-31 | 4
2018-07-30 | 1
2018-07-29 | 0

我的查询只包括在数据库中注册的日期(它不包括没有访问的日期)。这是有意义的，因为查询依赖于数据，而不是日历。

SELECT DATE_FORMAT(`timestamp`, "%Y%m/%d") AS Date, COUNT(`id`) AS
NumberOfVisitis FROM `visits` WHERE `timestamp` >= DATE_ADD(NOW(),
INTERVAL -7 DAY) GROUP BY DAY(`timestamp`) ORDER BY `timestamp` DESC

您能告诉我如何修改我的查询以在查询结果中包含没有访问的天数吗？

Answer 1

MySQL缺少Postgres的generate_series，所以我们必须伪造它。

要做的最简单的事情就是制作一个包含一堆数字的表格。这对于生成很多东西是很有用的。

create table numbers ( number serial );
insert into numbers () values (), (), (), (), (), (), ();

由此，我们可以生成过去7天的列表。

select date_sub(date(now()), interval number-1 day) as date
from numbers
order by number
limit 7

然后使用它作为CTE (或子查询)，我们用left join访问它。左连接意味着所有日期都会出现。

with dates as (
    select date_sub(date(now()), interval number-1 day) as date
    from numbers
    order by number
    limit 7
)
select date, coalesce(sum(id), 0)
from dates
left join visits on date = timestamp
group by date
order by date