<

A MySQL Tidbit: Quick Numbers Table Generation

Published on
17,601 Points
6,701 Views
4 Endorsements
Last Modified:
Approved
Community Pick
Kevin Cross
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.

Introduction

In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation: generating the very useful sequential integer numbers utility table.

Hopefully, by this point, the value of such a table is not in question. So, you are ready for the code that will add a numbers table to MySQL for use in queries like those where you have a table of sales, but would like to report sales for every day between January to today and you want days of non-existent sales to still show with a zero value…but we can talk about that later.

Utility: Numbers Table

-- 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
; 

Open in new window

Voilà !

Hopefully the above speaks for itself, but here is a look at what it is doing:

The inner most query uses the same Cartesian product approach as  the SQLite Article, so the 6 derived tables a-f should look familiar.
For those that have been on SQL journeys with me in the past, the additional cross joined derived table "(select @rownum:=0) r" may look familiar as it is a trick we learned in my previous EE Article, Analytical SQL : Where do you rank?, regarding simulating Oracle’s rownum and MS SQL Server’s row_number() function. Specifically for those new to the concept, we are defining a user variable and initializing its value to 0.
With the @rownum variable defined and our Cartesian product, the select statement simply increments @rownum through similar assignment we saw in the declaration of the variable: "@rownum:=@rownum+1". Like other calculation based columns in SQL, the evaluated value is selected for a given row; therefore, we have, in one step, incremented @rownum for use as row number for this row while making the next row aware of its position.
Clear as mud, I am sure …

As usual, please feel free to drop a comment below for clarification.

Reusing Numbers Utility

As you will see above, I made the assumption I don’t need to convince anyone at this point of the advantages to having the numbers utility table in a utility database; therefore, the creation script above already creates this as “util.numbers” where util is the schema for all our MySQL tools. Querying across schemas in MySQL is as simple as we saw before with SQLite but without having to attach anything.
select n from util.numbers limit 10;

Open in new window


Numbers Utility In Action

Circling back to my hypothetical scenario in the introduction, let’s look at some sales data. To ease the setup, let’s use our bookings script from the rank article.
rnkmysql.sql.txt

Take a moment to glance over the data and you should see that there are sales in September to October 2009, but there is not data for every day of each of those months. Therefore, consider a report that needs to reflect the total sales per day from ‘2009-09-01’ to ‘2009-10-31’. A neat trick to doing this now we have a numbers table is as follows:
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
;

Open in new window

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.

 

Conclusion

We now have another MySQL tool to add to our tool belts, the numbers utility table and associated user-defined variable trick to simulate the functionality of rowid column in SQLite, so use them both well. In a future article, I am going to explore XML parsing in MySQL and this numbers table came in quite handy, so until that adventure…

Thank you all for reading.

Best regards and happy coding,

Kevin C. Cross, Sr. (mwvisa1)

References

Related Articles
http://www.experts-exchange.com/Database/Miscellaneous/A_3570-A-SQLite-Tidbit-Quick-Numbers-Table-Generation.html
http://www.experts-exchange.com/Database/Miscellaneous/A_1555-Analytical-SQL-Where-do-you-rank.html

Related MySQL Documentation
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you found this article helpful, please click the Yes button after the question just below. This will give me (the author) a few points and might encourage me to write more articles.

If you didn't or otherwise feel the need to vote No, please first leave a comment to give me a chance to answer and perhaps to improve this article.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 
4
Author:Kevin Cross
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free