How to create a series in the standard SQL standard BigQuery

Question

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

Answer 1

Standard SQLQuery 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 configure the logic, for example, to get consecutive days and other sequences

Answer 2

Try it GENERATE_ARRAYin standard SQL:

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

Edit: if you need more than a million elements, you can use several calls GENERATE_ARRAY, although keep in mind that a query can take a long time if you create too many elements:

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