How to generate series in BigQuery Standard SQL
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;
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