Community Pick: Many members of our community have endorsed this article.

A MySQL Tidbit: Quick Numbers Table Generation

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
Published:
Updated:

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
https://www.experts-exchange.com/Database/Miscellaneous/A_3570-A-SQLite-Tidbit-Quick-Numbers-Table-Generation.html
https://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
9,004 Views
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.

Comments (2)

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Author

Commented:
Numbers Utility in Action
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.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

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:
mysql> SELECT
    -> GROUP_CONCAT(
    ->    CONCAT(
    ->       IF(help_keyword_id=252,
    ->          REPLACE(RIGHT(`name`, 4), 'S', ' '),
    ->          `name`),
    ->       IF(help_keyword_id=43, ',', ''))
    ->    ORDER BY help_keyword_id
    ->    SEPARATOR ' ') AS `DBA Trash Talk`
    -> FROM mysql.help_keyword
    -> WHERE help_keyword_id IN (43, 86, 149, 252, 419, 449)
    -> ;
+-----------------------------------------+
| DBA Trash Talk                          |
+-----------------------------------------+
| DATA, ROLLBACK FOR I AM DATABASE MASTER |
+-----------------------------------------+

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:
SELECT help_topic_id AS n
FROM mysql.help_topic
WHERE help_topic_id BETWEEN 0 AND 9
;

Open in new window


Doubts that help_topic_id will remain sequential, then use our technique above:
SELECT @rownum:=COALESCE(@rownum+1, 0) AS n
FROM mysql.help_topic
JOIN (SELECT @rownum:=NULL) r
LIMIT 10

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:
-- 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 @rownum:=0) r
join (
   select help_topic_id 
   from mysql.help_topic 
   limit 100
) a
join (
   select help_topic_id 
   from mysql.help_topic 
   limit 100
) b
join (
   select help_topic_id 
   from mysql.help_topic 
   limit 100
) c
;

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.

Data, rollback for I am [your] database master!

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.