I am trying to develop an array, a list or a sequence in Snowflake that has smaller increments than just moving by 1. So instead of creating something like:
SELECT seq4()
FROM table(generator(rowcount => 6))
That would display:
0
1
2
3
4
5
I want to be able set the steps or increments it moves between 1 and 5 by something like 0.01, so the result set would be:
0
0.01
0.02
0.03
0.04
0.05
.......
4.97
4.98
4.99
5
What would the best way to do this be? I am essentially trying to match a similar function to GENERATE_ARRAY in BigQuery where I can set the start, the end and the step sizes. Any ideas? Thanks a bunch
CodePudding user response:
The value could still be computed:
SELECT seq4()/100
FROM table(generator(rowcount => 501));
To get output as array:
SELECT ARRAY_AGG(seq4()/100)
FROM table(generator(rowcount => 501));
[ 0, 0.01, 0.02, 0.03, 0.04, 0.05, 0.06, 0.07, 0.08, 0.09, 0.1, 0.11, 0.12, 0.13, ... 4.97, 4.98, 4.99, 5]
CodePudding user response:
Lukasz answer is good, but as always, never use SEQx if you want gap free values, because SEQ can have gaps, use ROW_NUMBER
This function uses sequences to produce a unique set of increasing integers, but does not necessarily produce a gap-free sequence. When operating on a large quantity of data, gaps can appear in a sequence. If a fully ordered, gap-free sequence is required, consider using the ROW_NUMBER window function.
SELECT (row_number()over(order by null)-1)/100
FROM table(generator(rowcount => 501));
Array
And the ARRAY_AGG can put the values in any order, so if you want them "sorted" you should always use the ORDER BY clause
SELECT ARRAY_AGG(v) within group(order by v)
FROM (SELECT (row_number()over(order by null)-1)/100 as v
FROM table(generator(rowcount => 501)));
ARRAY_AGG(V) WITHIN GROUP(ORDER BY V) |
---|
[ 0, 0.01, 0.02, 0.03, 0.04, 0.05, 0.06, ... |