CODEWITHSUNDEEP
sqlgoogle-bigquery
Olsgaard
Olsgaard

Reputation: 1592

Generate series / range / array in SQL (Big Query) with min and max values taken from another table

I need to generate table t1 with N consecutive numbers in each row, starting with the smallest value in another table t and ending with the largest value in the table t.

How I do this with Big Query Standard SQL?

For simplicitys sake, imagine t is created in the following way (except that you do not know the start and end value beforehand)

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

Somehow I would like to do something to the effect of

SELECT num FROM UNNEST(GENERATE_ARRAY(MIN(t.num), MAX(t.num))) AS t1;

This question is very similar to [1], with the difference that the start and end of the series dependent on the min/max values of another table.

[1] How to generate series in BigQuery Standard SQL

Upvotes: 2

Views: 4986

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You can use a subquery:

SELECT tt
FROM (SELECT MIN(t.num) as min_num, MAX(t.num) as max_num
      FROM t
     ) t CROSS JOIN
     UNNEST(GENERATE_ARRAY(t.min_num, t.max_num)) tt

Upvotes: 5

Related Questions