Generating a Numbers Table in MySQL and Redshift

[lloydtabb]

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