A MySQL Tidbit: Quick Numbers Table Generation

AID: 3573

Status: Published

5920 points

Bymwvisa1
TypeTips/Tricks
Posted on2010-08-15 at 19:57:12

Awards

Community Pick

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
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

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

1:

Select all 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

15 KB
Bookings Sample Data

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
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Select all 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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Asked On: 2010-08-15 at 19:57:12ID3573
Tags: MySQL
,; SQL
,; cartesian join
,; numbers utility table
,; row number
,; rownum
,; row_number()
,; rowid
Topic: MySQL Server
Views: 1341

About The Author

mwvisa1

Kevin has been in IT since 1999 as an administrator, an analyst, an architect, a developer and, most recently, a manager. A passionate SQL and Java developer, his motto is "keep learning and/or keep smiling; there's nothing worse than a sad fool..."

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

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

1:
2:
3:
4:

Select all 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

1:
2:
3:
4:

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Select all 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!