CODEWITHSUNDEEP
google-bigquery
Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

How to generate series in BigQuery Standard SQL

I need to generate table with say 600 consecutive numbers (starting with 51) in each row
How I do this with BigQuery Standard SQL?

Upvotes: 23

Views: 33138

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Try GENERATE_ARRAY in standard SQL:

SELECT num FROM UNNEST(GENERATE_ARRAY(51, 650)) AS num;

Edit: if you want more than about a million elements, you can use multiple calls to GENERATE_ARRAY, although be warned that the query can end up taking a long time if you produce too many elements:

SELECT num1 * num2 AS num
FROM UNNEST(GENERATE_ARRAY(1, 1000000)) AS num1,
  UNNEST(GENERATE_ARRAY(1, 100)) AS num2;

Upvotes: 39

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

BigQuery Standard SQL 

SELECT 50 + ROW_NUMBER() OVER() AS num
FROM UNNEST((SELECT SPLIT(FORMAT("%600s", ""),'') AS h FROM (SELECT NULL))) AS pos
ORDER BY num

BigQuery Legacy SQL 

SELECT 50 + pos AS pos FROM (
  SELECT ROW_NUMBER() OVER() AS pos, * 
  FROM (FLATTEN((SELECT SPLIT(RPAD('', 600, '.'),'') AS h FROM (SELECT NULL)), h))
) WHERE pos BETWEEN 1 AND 600

From there you can adjust logic for example to get consecutive days and other sequences

Upvotes: 8

Related Questions