generate_series() эквивалент в снежинке
Я пытаюсь найти эквивалент снежинки для функции generate_series() (синтаксис PostgreSQL).
SELECT generate_series(timestamp '2017-11-01', CURRENT_DATE, '1 day')
7 ответов
Так я смог создать серию дат в "Снежинке". Я установил число строк в 1095, чтобы получить даты за 3 года, вы, конечно, можете изменить их на любой, который подходит вашему варианту использования.
select
dateadd(day, '-' || seq4(), current_date()) as dte
from
table
(generator(rowcount => 1095))
Просто хотел расширить комментарий Marcin Zukowski, чтобы сказать, что эти пробелы начали появляться почти сразу после использования диапазона дат, созданного таким образом вJOIN
.
В конечном итоге мы сделали это вместо этого!
select
dateadd(
day,
'-' || row_number() over (order by null),
dateadd(day, '+1', current_date())
) as date
from table (generator(rowcount => 90))
WITH RECURSIVE rec_cte AS (
-- start date
SELECT '2017-11-01'::DATE as dt
UNION ALL
SELECT DATEADD('day',1,dt) as dt
FROM rec_cte
-- end date (inclusive)
WHERE dt < current_date()
)
SELECT * FROM rec_cte
У меня была аналогичная проблема, и я нашел подход, который позволяет избежать проблемы генератора, требующего постоянного значения, за счет использования переменной сеанса в дополнение к уже отличным ответам здесь. На мой взгляд, это ближе всего к требованию ОП.
-- set parameter to be used as generator "constant" including the start day
set num_days = (Select datediff(day, TO_DATE('2017-11-01','YYYY-MM-DD'), current_date()+1));
-- use parameter in bcrowell's answer now
select
dateadd(
day,
'-' || row_number() over (order by null),
dateadd(day, '+1', current_date())
) as date
from table (generator(rowcount => ($num_days)));
-- clean up previously set variable
unset num_days;
С использованиемARRAY_GENERATE_RANGE
и арифметика дат:
SET (start_date, end_date) = (SELECT '2017-11-01', CURRENT_DATE());
SELECT $start_date::DATE + VALUE::INT AS value
FROM TABLE(FLATTEN(ARRAY_GENERATE_RANGE(0, DATEDIFF('DAY',$start_date,$end_date)+1)));
-- VALUE
-- 2017-11-01
-- 2017-11-02
-- ...
-- 2023-04-29
-- 2023-04-30
Добавление этого ответа для полноты, если у вас есть начальная и последняя дата:
select -1 + row_number() over(order by 0) i, start_date + i generated_date
from (select '2020-01-01'::date start_date, '2020-01-15'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date
Я обнаружил, что функция генератора в Snowflake весьма ограничена для всех случаев использования, кроме самых простых. Например, было непонятно, как взять спецификацию одной строки, разбить ее на таблицу дат и соединить обратно с исходной таблицей спецификации.
Вот альтернатива, использующая рекурсивные CTE.
-- A 2 row table that contains "specs" for a date range
create local temp table date_spec as
select 1 as id, '2022-04-01'::date as start_date, current_date() as end_date
union all
select 2, '2022-03-01', '2032-03-30'
;
with explode_date(id, date, next_date, end_date) as (
select
id
, start_date as date -- start_date is the first date
, date + 1 as next_date -- next_date is the date of for the subsequent row in the recursive cte
, end_date
from date_spec
union all
select
ds.id
, ed.next_date -- the current_date is the value of next_date from above
, ed.next_date + 1
, ds.end_date
from date_spec ds
join explode_date ed
on ed.id = ds.id
where ed.date <= ed.end_date -- keep running until you hit the end_date
)
select * from explode_date
order by id, date desc
;