How to generate data in MySQL?

Question

Here is my SQL:

SELECT 
  COUNT(id),
  CONCAT(YEAR(created_at), '-', MONTH(created_at), '-', DAY(created_at))
FROM my_table
GROUP BY YEAR(created_at), MONTH(created_at), DAY(created_at)

I want a row to show up even for days where there was no ID created. Right now I'm missing a ton of dates for days where there was no activity.

Any thoughts on how to change this query to do that?

Answer 1

SQL is notoriously bad at returning data that is not in the database. You can find the beginning and ending values for gaps of dates, but getting all the dates is hard.

The solution is to create a calendar table with one record for each date and OUTER JOIN it to your query.

Here is an example assuming that created_at is type DATE:

SELECT calendar_date, COUNT(`id`)
FROM calendar LEFT OUTER JOIN my_table ON calendar.calendar_date = my_table.created_at
GROUP BY calendar_date

(I'm guessing that created_at is really DATETIME, so you'll have to do a bit more gymnastics to JOIN the tables).

Answer 2

General idea

There are two main approaches to generating data in MySQL. One is to generate the data on the fly when running the query and the other one is to have it in the database and using it when necessary. Of course, the second one would be faster than the first one if you're going to run your query frequently. However, the second one will require a table in the database which only purpose will be to generate the missing data. It will also require you to have privileges enough to create that table.

Dynamic data generation

This approach involves making UNIONs to generate a fake table that can be used to join the actual table with. The awful and repetitive query is:

select aDate from (
  select @maxDate - interval (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) day aDate 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) a, /*10 day range*/
  (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) b, /*100 day range*/
  (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) c, /*1000 day range*/
  (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) d, /*10000 day range*/
  (select @minDate := '2001-01-01', @maxDate := '2002-02-02') e
) f
where aDate between @minDate and @maxDate

Anyway, it is simpler than it seems. It makes cartesian products of derived tables with 10 numeric values so the result will have 10^X rows where X is the amount of derived tables in the query. In this example there is 10000 day range so you would be able to represent periods of over 27 years. If you need more, add another UNION to the query and update the interval, and if you don't need so many you can remove UNIONs or individual values from the derived tables. Just to clarify, you can fine tune the date period by applying a filter with a WHERE clause on @minDate and @maxDate variables (but don't use a longer period than the one you created with the cartesian products).

Static data generation

This solution will require you to generate a table in your database. The approach is similar to the previous one. You'll have to first insert data into that table: a range of integers ranging from 1 to X where X is the maximum needed range. Again, if you are unsure just insert 100000 values and you'll be able to create day ranges for over 273 years. So, once you've got the integer sequence, you can transform it into a date range like this:

select '2012-01-01' + interval value - 1 day aDay from seq
having aDay <= '2012-01-05'

Assuming a table named seq with a column named value. On top the from date and at the bottom the to date.

Turning this into something useful

Ok, now we have our date periods generated but we're still missing a way to query data and display the missing values as an actual 0. This is where left join comes to the rescue. To make sure we're all on the same page, a left join is similar to an inner join but with only one difference: it will preserve all records from the left table of the join, regardless of whether there is a matching record on the table of the right. In other words, an inner join will remove all non-matched rows on the join while the left join will keep the ones on the left table and, for the records on the left that have no matching record on the right table, the left join will fill that "space" with a null value.

So we should join our domain table (the one that has "missing" data) with our newly generated table putting the latter on the left part of the join and the former on the right, so that all elements are considered, regardless of their presence in the domain table.

For example, if we had a table domainTable with fields ID, birthDate and we would like to see a count of all the birthDate in the first 5 days of 2012 per day and if the count is 0 to show that value, then this query could be run:

select allDays.aDay, count(dt.id) from (
  select '2012-01-01' + interval value - 1 day aDay from seq
  having aDay <= '2012-01-05'
) allDays
left join domainTable dt on allDays.aDay = dt.birthDate
group by allDays.aDay

This generates a derived table with all the requried days (notice I'm using the static data generation) and performs a left join against our domain table, so all days will be displayed, regardless of whether they have a matching values in our domain tables. Also note the count should be done on the field that will have null values as those are not counted.

Notes to be considered

1) The queries can be used to query other intervals (months, years) performing small changes to the code

2) Instead of hardcoding the dates you can query for min and max values from the domain tables like this:

select (select min(aDate) from domainTable) + interval value - 1 day aDay
from seq
having aDay <= (select max(aDate) from domainTable)

This would avoid generating more records than necessary.

Actually answering your question

I think you should have already figured out how to do what you want. Anyway, here are the steps so that others can benefit from them too. Firstly, create the integer table. Secondly, run this query:

select allDays.aDay, count(mt.id) aCount from (
    select (select date(min(created_at)) from my_table) + interval value - 1 day aDay
    from seq s
    having aDay <= (select date(max(created_at)) from my_table)
) allDays
left join my_table mt on allDays.aDay = date(mt.created_at)
group by allDays.aDay

I guess created_at is a datetime and that's why you're concatenating that way. However, that happens to be the way MySQL natively stores dates, so I'm just grouping by the date field but casting the created_at to an actual date datatype. You can play with it using this fiddle.

And here is the solution generating data dynamically:

select allDays.aDay, count(mt.id) aCount from (
  select @maxDate - interval a.a day aDay 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) a, /*10 day range*/
  (select @minDate := (select date(min(created_at)) from my_table),
          @maxDate := (select date(max(created_at)) from my_table)) e
   where @maxDate - interval a.a day between @minDate and @maxDate
) allDays
left join my_table mt on allDays.aDay = date(mt.created_at)
group by allDays.aDay

As you can see the skeleton of the query is the same as the previous one. The only thing that changes is how the derived table allDays is generated. Now, the way the derived table is generated is also slightly different from the one I added before. This is because in the example filddle I only needed a 10-day range. As you can see, it is more readable than adding a 1000 day range. Here is the fiddle for the dynamic solution so that you can play with it too.

Hope this helps!