How to generate series in BigQuery Standard SQL

Question

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

Answer 1

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;

Answer 2

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