{"id":119,"date":"2018-08-04T18:00:43","date_gmt":"2018-08-04T18:00:43","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=119"},"modified":"2018-06-17T17:49:57","modified_gmt":"2018-06-17T17:49:57","slug":"number-sequences","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/08\/04\/number-sequences\/","title":{"rendered":"Number sequences"},"content":{"rendered":"

Here is a quick look at number sequences, which are a brilliant (and fairly database neutral) method to create numbers (and rows) to build datasets from.<\/p>\n

While working on the dates articles, I used number sequences a lot and thought they were worth coming back to.<\/p>\n

What is a number sequence<\/h1>\n

A number sequence can be any series of numbers in a pattern. Here I’m talking about incrementing the number by 1 in each row. In many DBMS environments (SQL Server included) there is a row number metadata attribute which takes care of this. This method gives developers the opportunity to create rows from no existing data, and apply calculations to manipulate values.<\/p>\n

For this sequence, I create a dataset with numbers 0 - 9<\/code>, and then cartesian join that dataset to itself n<\/em> times. This will create all numbers between 0<\/em> and (10n<\/sup>)-1<\/em>. So, join the number table 3<\/em> times times for 0 - 999<\/code>. See the below code example, I’ve made it deliberately verbose in nature to demonstrate the idea.<\/p>\n

SQL Server (and MySQL with minimal changes)<\/strong><\/h3>\n
--------------------------------------------------------------------------------\r\n-- First get a recurring list of numbers\r\n--------------------------------------------------------------------------------\r\nWITH num_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n)\r\nSELECT t1.a\r\n  FROM num_tbl t1\r\n;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Create a cartesian product\r\n--------------------------------------------------------------------------------\r\nWITH num_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n)\r\nSELECT t1.a AS t1a,\r\n       t2.a AS t2a\r\n\u00a0FROM\u00a0num_tbl\u00a0t1\r\n  CROSS JOIN num_tbl t2\r\n;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Create a cartesian product (3 digits with calculation)\r\n--------------------------------------------------------------------------------\r\nWITH num_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n)\r\nSELECT t1.a AS t1a,\r\n       t2.a AS t2a,\r\n       t3.a AS t3a,\r\n       (t1.a + (10 * t2.a) + (100 * t3.a)) AS concatenate_calculation\r\n  FROM num_tbl t1\r\n  CROSS JOIN num_tbl t2\r\n  CROSS JOIN num_tbl t3\r\n;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Create Date list\r\n--------------------------------------------------------------------------------\r\nWITH number_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n), number_sequences AS (\r\nSELECT t1.a AS t1a,\r\n       t2.a AS t2a,\r\n       t3.a AS t3a,\r\n       (t1.a\u00a0+\u00a0(10\u00a0*\u00a0t2.a)\u00a0+\u00a0(100\u00a0*\u00a0t3.a))\u00a0AS\u00a0concatenate_calculation\r\n  FROM number_tbl t1\r\n  CROSS JOIN number_tbl t2\r\n  CROSS JOIN number_tbl t3\r\n)\r\nSELECT t1a,\r\n       t2a,\r\n       t3a,\r\n       concatenate_calculation,\r\n       CAST(GetDate() AS DATE)                             AS todays_date_SQL_Server,\r\n       CAST(GetDate() + (concatenate_calculation) AS DATE) AS calculated_date_range_SQL_Server\r\n     \/*CURDATE()                                           AS todays_date_MySQL,\r\n       CAST(CURDATE() + (concatenate_calculation) AS DATE) AS calculated_date_range_MySQL *\/\r\n  FROM number_sequences\r\n ORDER BY concatenate_calculation\r\n;\r\n<\/pre>\n
Oracle<\/h3>\n
The method is the same, the key difference for oracle is the use of dual as a virtual table.<\/p>\n
WITH number_tbl AS (\r\nSELECT 0 AS a FROM dual UNION ALL\r\nSELECT 1 AS a FROM dual UNION ALL\r\nSELECT 2 AS a FROM dual UNION ALL\r\nSELECT 3 AS a FROM dual UNION ALL\r\nSELECT 4 AS a FROM dual UNION ALL\r\nSELECT 5 AS a FROM dual UNION ALL\r\nSELECT 6 AS a FROM dual UNION ALL\r\nSELECT 7 AS a FROM dual UNION ALL\r\nSELECT 8 AS a FROM dual UNION ALL\r\nSELECT 9 AS a FROM dual\r\n), number_sequences AS (\r\nSELECT t1.a AS t1a,\r\n       t2.a AS t2a,\r\n       t3.a AS t3a,\r\n       (t1.a + (10 * t2.a) + (100 * t3.a)) AS concatenate_calculation\r\n  FROM number_tbl t1\r\n  CROSS JOIN number_tbl t2\r\n  CROSS JOIN number_tbl t3\r\n)\r\nSELECT t1a,\r\n       t2a,\r\n       t3a,\r\n       concatenate_calculation,\r\n     TRUNC(SysDate)                           AS todays_date_Oracle,\r\n     TRUNC(SysDate) + concatenate_calculation AS calculated_date_range_Oracle\r\n  FROM number_sequences\r\n ORDER BY concatenate_calculation\r\n;<\/pre>\n
Manipulating the resultset<\/h1>\n
The resultset from this method is very simple, but can be used anywhere we want to create a sequence of numbers\/rows:<\/p>\n