-- create database|schema for utility objects
create schema if not exists `util`;
-- drop any existing copies of numbers
drop table if exists `util`.`numbers`;
-- create numbers table structure (very basic)
create table `util`.`numbers` (
`n` int(11) not null,
primary key (`n`)
) engine=InnoDB default charset=latin1;
-- insert generated number sequence (e.g., 1-1,000,000)
insert into `util`.`numbers`(n)
select @rownum:=@rownum+1
from (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) a, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) b, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) c, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) d, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) e, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) f, (select @rownum:=0) r
;
Voilà !
select n from util.numbers limit 10;
select dates.dt, sum(coalesce(bks.bk_amount, 0)) as total_sales
from (
-- generate table of dates
select date_add(str_to_date('2009-09-01','%Y-%m-%d'), interval n day) as dt
from util.numbers
where n between 0 and 60
) dates
left outer join bookings bks on bks.bk_date = dates.dt
group by dates.dt
;
Notice how easily we generated all the days from September 1st to October 31st. Now from there we simply use a left join to bring in matching sales data and aggregate to our heart’s content.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Author
Commented:A MySQL Tidbit: In-line XML Parsing
A MySQL Tidbit: In-line CSV Parsing (Using XML)
The next article in my current series of MySQL tidbits is:
A MySQL Tidbit: Dynamic Export To_XML Stored Procedure
This one doesn't utilize the numbers utility, but is a nice prep to the next article on the horizon which is Import From_XML Stored Procedure, which will take advantage of our numbers utility.
Author
Commented:BONUS: "RTFM" -- Fun With Help Topics
First, let's agree that I am using the term "BONUS" very loosely here and move on.
Okay. I needed a very small set of numbers today for SQL code I plan to pass along later. Because of the latter bit involving others, I got to thinking that for a short example one may not be interested in first having to create a numbers table but also felling like the SELECT ... UNION SELECT ... approach is tedious. As I am also working in T-SQL, I started to feel like there has to be something like master..spt_values that is on every -- or at least a vast majority of -- MySQL installation, right? There is. It is called help.
It just so happens, the help information for MySQL is stored in the system database 'mysql'. Consequently, there are several tables relating to help, including a table named 'help_topic'. The contents may differ on each version of MySQL, but on versions 5.1 and 5.5 it is consist that the help_topic_id starts at 0 and increments without gaps to 505. This is not intended to be our full numbers table, so 506 numbers is plenty.
As a quick digression, the 'help_keyword' table has a similar trait to 'help_topic' and so can be used in a similar fashion. In addition, on MySQL 5.5.8, it is also good for trash talking your data:
Open in new window
But as I said, I digress...
So, back to 'help_topic' to generate numbers quickly. For example, you can get 0-9 like so:
Open in new window
Doubts that help_topic_id will remain sequential, then use our technique above:
Open in new window
Same trick, just without having to type out all that UNION SELECT mess.
So an even quicker way to generate 1-1,000,000 in a util.numbers table could be:
Open in new window
There you have it! Who says reading the manual cannot be any fun. It surely is helpful. If you use it wisely, you too can taunt your data.