Postgres implements function generate_series, a function that returns a table of a set of integers. Unfortunately, MySQL lacks this function and Redshifts implementation is pretty useless (It only works on the leader node?? Really?).

In a combination with a CROSS JOIN, number tables are often used to build map tables, from attributes stored in lists in strings. They are also used to build date table (see below).

I’ve seen a few implementations of numeric series generating SELECT statements, but they are usually more complex and larger than this one.

The SQL code makes a bunch of two row tables and cross joins them, using them as binary counters. The code will generate all the numbers between 0 and 255 (2^8-1)

`SELECT `

p0.n

+ p1.n*2

+ p2.n * POWER(2,2)

+ p3.n * POWER(2,3)

+ p4.n * POWER(2,4)

+ p5.n * POWER(2,5)

+ p6.n * POWER(2,6)

+ p7.n * POWER(2,7)

as number

FROM

(SELECT 0 as n UNION SELECT 1) p0,

(SELECT 0 as n UNION SELECT 1) p1,

(SELECT 0 as n UNION SELECT 1) p2,

(SELECT 0 as n UNION SELECT 1) p3,

(SELECT 0 as n UNION SELECT 1) p4,

(SELECT 0 as n UNION SELECT 1) p5,

(SELECT 0 as n UNION SELECT 1) p6,

(SELECT 0 as n UNION SELECT 1) p7

Its pretty easy to create a derived table for general use. **Play with this Code on Learn.looker.com**

`- view: numbers`

derived_table:

persist_for: 5 hours

indexes: [number]

sql: |

SELECT

p0.n

+ p1.n*2

+ p2.n * POWER(2,2)

+ p3.n * POWER(2,3)

+ p4.n * POWER(2,4)

+ p5.n * POWER(2,5)

+ p6.n * POWER(2,6)

+ p7.n * POWER(2,7)

as number

FROM

(SELECT 0 as n UNION SELECT 1) p0,

(SELECT 0 as n UNION SELECT 1) p1,

(SELECT 0 as n UNION SELECT 1) p2,

(SELECT 0 as n UNION SELECT 1) p3,

(SELECT 0 as n UNION SELECT 1) p4,

(SELECT 0 as n UNION SELECT 1) p5,

(SELECT 0 as n UNION SELECT 1) p6,

(SELECT 0 as n UNION SELECT 1) p7

fields:

- dimension: number

type: number

Another useful form of this table is to generate dates.

`- view: dates`

derived_table:

persist_for: 5 hours

indexes: [series_date]

sql: |

SELECT

DATE_ADD('2001-01-01', INTERVAL numbers.number DAY)

as series_date

FROM ${numbers.SQL_TABLE_NAME} AS numbers

fields:

- dimension: series_date

type: date