How to edit this query to get last 7 days visits even if there are no visits on specific days?

Question

I have the following query that returns the dates first_visit starting from today and 7 days back, as well as the visitors hash per day:

SET time_zone= '{$company_timezone}';
SELECT DATE( first_visit ) AS day , COUNT( DISTINCT hash ) AS total
FROM table
WHERE company = 1 and first_visit > SUBDATE( CURDATE( ) , 7 )
GROUP BY day

The flaw with this is that if company = 1 have visitors only today and three days ago, I will get this:

day --------- total
2020-03-08 ----- 30
2020-03-05 ----- 40

leaving out all other dates inbetween.

What I want is to get all the past 7 days, even there are no visitors at all. If there are no visitors, then it should just show 0.

How to edit my query in order to achieve this?

Thank you

Seriously consider handling issues of data display in application code — Strawberry, Mar 08 '20 at 23:47

score 0 · Answer 1 · edited Mar 09 '20 at 07:14

Some solutions involve a table of numbers.

Here is one way with a recursive query, available in MySQL 8.0:

with d as (select 0 n union all select n + 1 where n < 6)
select
    current_date - interval n day myday,
    count(distinct t.hash) total
from d
left join mytable t
    on  t.company = 1
    and t.first_visit >= current_date - inteval n day
    and t.first_visit <  current_date - interval (n - 1) day
group by d.n

In earlier version, you can enumerate the numbers as a derived table:

select
    current_date - interval n day myday,
    count(distinct t.hash) total
from (
    select 0 n union all select 1 union all select 2 union all select 3
    union all select 4 union all select 5 union all select 6 union all select 7
) d
left join mytable t
    on  t.compatny = 1
    and t.first_visit >= current_date - inteval n day
    and t.first_visit <  current_date - interval (n - 1) day
group by d.n

score 0 · Answer 2 · answered Mar 08 '20 at 21:49

0

Perform an outer join with a derived table that contains desired dates:

select b.date as day, count(distinct hash) as total
from table
right join (select @now := @now - interval 1 day as date from (select @now := curdate()) a, table limit 7) b
on b.date = date(first_visit) and company = 1
group by b.date

This assumes that table has at least 7 rows.

Note: there are two occurrences of table.

answered Mar 08 '20 at 21:49

revo

47,783
14
74
117

Note that nobody ever uses right join. Just sayin' – Strawberry Mar 08 '20 at 22:12
@Strawberry Poor nobody. – revo Mar 08 '20 at 22:13
They're getting over it – Strawberry Mar 08 '20 at 22:24
Also, without an ORDER BY clause, this solution is arbitrary – Strawberry Mar 08 '20 at 23:48

score 0 · Answer 3 · answered Mar 08 '20 at 22:00

If you have data for each day -- but not for that company -- then conditional aggregation is a pretty simply approach:

SELECT DATE( first_visit ) AS day ,
       COUNT( DISTINCT CASE WHEN company = 1 THEN hash END ) AS total
FROM table
WHERE first_visit > SUBDATE( CURDATE( ) , 7 )
GROUP BY day;

This only works if all days are represented in your table for some company.

How to edit this query to get last 7 days visits even if there are no visits on specific days?

3 Answers3