How do I create a sequence of dates?

Question

I want to count the number of actions per day in my dataset.

date         action_id
2010-01-01   id00
2010-01-03   id01
2010-01-05   id02

This is just a sample, but the point is that my data does not include actions for every day and I want to include days where there are zero actions in my result.

My plan is to do this.

with dates as (
select [sequence of dates from 2010-01-01 to 2010-02-01] as day)

select day, coalesce(count(distinct action_id), 0) as actions
from dates
left join my_table
on dates.date = my_table.date

How do I create the sequence of dates?

You can create a date table with all the dates in it and join to it. — Brad
– Brad, Commented Apr 22, 2019 at 18:14

Gordon Linoff · Accepted Answer · 2019-04-22 18:22:10Z

4

You example shows a CTE. So, you can use a recursive CTE:

with recursive dates as (
      select date('2010-01-01') as day
      union all
      select day + interval 1 day
      from dates
      where day < '2010-02-01'
     )
select d.day, count(distinct t.action_id) as actions
from dates d left join
     my_table t
     on d.day = my_table.date
group by d.day;

Note that COUNT() never returns NULL, so COALESCE() is unnecessary.

In older versions, you can use a calendar table or generate the data on the fly. Assuming your table has enough rows:

select d.day, count(distinct t.action_id) as actions
from (select date('2010-01-01') + interval (@rn := @rn + 1) - 1 day as day
  from my_table cross join
       (select @rn := 0) params
  limit 31
 ) d left join
     my_table t
     on d.day = my_table.date
group by d.day;

edited Apr 22, 2019 at 18:22

answered Apr 22, 2019 at 18:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tadon11Aaa Over a year ago

How do I make the common table expression recursive? It can't find the dte object

Gordon Linoff Over a year ago

@tadon11Aaa . . . The simpler solution is to fix the typo -- which I just did.

tadon11Aaa Over a year ago

Is recursive a category of common table expressions?

Gordon Linoff Over a year ago

@tadon11Aaa . . . Yes it is: dev.mysql.com/doc/refman/8.0/en/….

Zaynul Abadin Tuhin · Accepted Answer · 2019-04-22 18:14:31Z

0

it seems just you need group by and count

 select date, count(distinct action_id) as action
 from my_table left join
 dates on dates.date = my_table.date
  group by date

answered Apr 22, 2019 at 18:14

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Comments

BlackSwan · Accepted Answer · 2019-04-22 18:25:27Z

with dates as
(
select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a 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 union all select 8 union all select 9) as a
    cross join (select 0 as a 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 union all select 8 union all select 9) as b
    cross join (select 0 as a 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 union all select 8 union all select 9) as c
    cross join (select 0 as a 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 union all select 8 union all select 9) as d
) a
where a.Date between '<start_date>' and '<end_date>' )
select day, count(distinct action_id) as actions
from dates
left join my_table
on dates.date = my_table.date

Collectives™ on Stack Overflow

How do I create a sequence of dates?

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related