3 ms·
Interesting read, thanks! Regarding generating missing ids (quote from the blogpost: "If you know how one would generate the missing ids between the gaps (which
by fuy 6y ago
Interesting read, thanks!
Regarding generating missing ids (quote from the blogpost: "If you know how one would generate the missing ids between the gaps (which could be of variable size)":
I don't have access to snowflake instance, but the following works in Postgres (if I understood the problem correctly):
with lead as (
select id, lead(id, 1) over (order by id) as lead_by_one
from gaps_table),
gaps as (
select id + 1 as start_gap,
lead_by_one - 1 as end_gap
from lead where lead_by_one - id > 1)
select generate_series(start_gap, end_gap) as missing_ids from gaps
So if Snowflake has a generating function similar to generate_series, it should do the trick.
- thomasdziedzic 6y agoUnfortunately snowflake doesn't have a generate_series function equivalent. The closest thing it has is a generator function which only accepts constants: https://docs.snowflake.com/en/sql-reference/functions/generator.html https://docs.snowflake.com/en/sql-reference/functions/genera... I could probably have written my own javascript udf equivalent of generate_series though.