generate_series()相当于雪花

Question

我试图找到generate_series（）（PostgreSQL语法）的雪花等价物。

SELECT generate_series(timestamp '2017-11-01', CURRENT_DATE, '1 day')

Answer 1

只是

想扩展Marcin Zukowski的评论，说这些差距在使用JOIN中以这种方式生成的日期范围后几乎立即开始出现。

我们最终还是这样做了！

select
  dateadd(
    day,
    '-' || row_number() over (order by null),
    dateadd(day, '+1', current_date())
  ) as date
from table (generator(rowcount => 90))

Answer 2

我遇到了类似的问题，并找到了一种方法，除了这里已经很好的答案之外，该方法还通过使用会话变量避免了生成器需要常量值的问题。这最接近我心目中的OP的要求。

-- 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;

Answer 3

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

Answer 4

使用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

Answer 5

添加此答案以完成，以防您有初始和最后一个日期：

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

Answer 6

我发现 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
;

Answer 7

这就是我能够在雪花中生成一系列日期的方式。我将行计数设置为 1095 以获得 3 年的日期，您当然可以将其更改为适合您的用例的任何日期

select 
    dateadd(day, '-' || seq4(), current_date()) as dte 
from 
    table 
       (generator(rowcount => 1095))

最初发现于此处

编辑：此解决方案不正确。 seq4 不保证没有间隙的序列。请遵循其他答案，而不是这个。感谢@Marcin Zukowski指出这一点。