How can I create an incremental sequence in Snowflake?

Mitch71 0
Viewed 38

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

sequence list snowflake-cloud-data-platform arrays
2 Answers

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]

Lukasz Szozda0

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, ...
Simeon Pilgrim0
Related