-- RANGE returns a list of ints or timestamps.
-- select range(n); -- list of ints from [0,n)
-- select range(n,m); -- list of ints from [m,n)
-- select range(n,m,k); -- list of ints from [m,n) with interval k
-- generate_series does the same, but include the stop value, e.g. [0,n].
select range(5) union
select range(3,4) union
select range(0,10,2) union
select generate_series(5);
┌────────────────────┐
│ range(5) │
│ int64[] │
├────────────────────┤
│ [0, 1, 2, 3, 4, 5] │
│ [0, 2, 4, 6, 8] │
│ [3] │
│ [0, 1, 2, 3, 4] │
└────────────────────┘
-- timestamps require a start, stop, and interval.
select range(timestamp '2023-01-01', timestamp '2023-01-03', interval 1 day) as x
union
select generate_series(timestamp '2023-01-01', timestamp '2023-01-03', interval 1 day) as x;
┌─────────────────────────────────────────────────────────────────┐
│ x │
│ timestamp[] │
├─────────────────────────────────────────────────────────────────┤
│ [2023-01-01 00:00:00, 2023-01-02 00:00:00] │
│ [2023-01-01 00:00:00, 2023-01-02 00:00:00, 2023-01-03 00:00:00] │
└─────────────────────────────────────────────────────────────────┘
-- unnest() can be used to convert the list into rows.
select unnest(range(3)) as using_unnest;
┌──────────────┐
│ using_unnest │
│ int64 │
├──────────────┤
│ 0 │
│ 1 │
│ 2 │
└──────────────┘
-- select can also be used to convert the list into rows.
-- to name the column from a select, use AS _(name).
-- select * from unnest(range(2)) as x; -- doesn't work
select * from unnest(range(2)) as _(using_select);
┌──────────────┐
│ using_select │
│ int64 │
├──────────────┤
│ 0 │
│ 1 │
└──────────────┘
-- use with/select/unnest to generate columns of different types
with t as (select * from range(3) _(x))
select x, 'foo'||cast(x as text) as x_as_text,
x*.01 as x_as_decimal from t;
┌───────┬───────────┬───────────────┐
│ x │ x_as_text │ x_as_decimal │
│ int64 │ varchar │ decimal(21,2) │
├───────┼───────────┼───────────────┤
│ 0 │ foo0 │ 0.00 │
│ 1 │ foo1 │ 0.01 │
│ 2 │ foo2 │ 0.02 │
└───────┴───────────┴───────────────┘
-- Interval can be either hard coded or specified as a string
select range(timestamp '2023-01-01', timestamp '2023-01-03', interval 1 day) as x
union
select range(timestamp '2023-01-01', timestamp '2023-01-03', interval '1 day') as x;
┌────────────────────────────────────────────┐
│ x │
│ timestamp[] │
├────────────────────────────────────────────┤
│ [2023-01-01 00:00:00, 2023-01-02 00:00:00] │
└────────────────────────────────────────────┘
-- Use POSITIONAL JOIN to combine ranges. A not null check can be useful.
select * from
range(timestamp '2023-01-01', timestamp '2023-01-04', interval 1 day) as d(ts)
positional join
range(11,44,11) as t(x);
┌─────────────────────┬───────┐
│ ts │ x │
│ timestamp │ int64 │
├─────────────────────┼───────┤
│ 2023-01-01 00:00:00 │ 11 │
│ 2023-01-02 00:00:00 │ 22 │
│ 2023-01-03 00:00:00 │ 33 │
└─────────────────────┴───────┘
select * from
range(timestamp '2023-01-01', timestamp '2023-01-04', interval 1 day) as d(ts)
positional join range(9999) as t(x)
where ts is not null;
┌─────────────────────┬───────┐
│ ts │ x │
│ timestamp │ int64 │
├─────────────────────┼───────┤
│ 2023-01-01 00:00:00 │ 0 │
│ 2023-01-02 00:00:00 │ 1 │
│ 2023-01-03 00:00:00 │ 2 │
└─────────────────────┴───────┘
-- Combine all of the above to generate data sets.
with t as (
select * from
range(timestamp '2023-01-01', timestamp '2023-01-04', interval 1 day) as foo1(ts)
positional join range(999) as foo2(x)
where ts is not null
)
select ts, x, 100-x as y, x*2.2 as z, 'foo'||x::text as text_x
from t;
┌─────────────────────┬───────┬───────┬───────────────┬─────────┐
│ ts │ x │ y │ z │ text_x │
│ timestamp │ int64 │ int64 │ decimal(21,1) │ varchar │
├─────────────────────┼───────┼───────┼───────────────┼─────────┤
│ 2023-01-01 00:00:00 │ 0 │ 100 │ 0.0 │ foo0 │
│ 2023-01-02 00:00:00 │ 1 │ 99 │ 2.2 │ foo1 │
│ 2023-01-03 00:00:00 │ 2 │ 98 │ 4.4 │ foo2 │
└─────────────────────┴───────┴───────┴───────────────┴─────────┘
