Amazon Redshift中的generate series - generate_series函数

Question

我试了如下:

SELECT * FROM generate_series(2,4);
generate_series
-----------------
           2
           3
           4
(3 rows)
SELECT * FROM generate_series(5,1,-2);                                                             
generate_series
-----------------
           5
           3
           1
(3 rows)

但是当我尝试的时候，

select * from generate_series('2011-12-31'::timestamp, '2012-12-31'::timestamp, '1 day');

生成错误。

ERROR:  function generate_series(timestamp without time zone, timestamp without time zone, "unknown") does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

我使用PostgreSQL 8.0.2在Redshift 1.0.757。
知道为什么会这样吗?

更新:

generate_series现在正在使用Redshift。

SELECT CURRENT_DATE::TIMESTAMP  - (i * interval '1 day') as date_datetime 
FROM generate_series(1,31) i 
ORDER BY 1

这将生成最近30天的日期

Answer 1

支持日期和时间戳的generate_series()版本是在Postgres 8.4中添加的。

由于Redshift是基于Postgres 8.0，你需要使用不同的方式:

select timestamp '2011-12-31 00:00:00' + (i * interval '1 day')
from  generate_series(1, (date '2012-12-31' - date '2011-12-31')) i;

如果你"只"需要日期，可以缩写为:

select date '2011-12-31' + i
from  generate_series(1, (date '2012-12-31' - date '2011-12-31')) i;

Answer 2

generate_series现在正在使用Redshift。

SELECT CURRENT_DATE::TIMESTAMP  - (i * interval '1 day') as date_datetime 
FROM generate_series(1,31) i 
ORDER BY 1

这将生成最近30天的日期

Answer 3

我在这里找到了一个解决方案，我的问题是无法使用generate_series()在红移上生成时间维度表。您可以使用下面的SQL代码片段生成一个临时序列。

with digit as (
    select 0 as d union all 
    select 1 union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9        
),
seq as (
    select a.d + (10 * b.d) + (100 * c.d) + (1000 * d.d) as num
    from digit a
        cross join
        digit b
        cross join
        digit c
        cross join
        digit d
    order by 1        
)
select (getdate()::date - seq.num)::date as "Date"
from seq;

generate_series()函数似乎还不完全支持Redshift。如果我运行DJo回答中提到的SQL，它可以工作，因为SQL只在leader节点上运行。

Answer 4

日期范围的红移中没有generate_series()函数，但您可以通过以下步骤生成系列…

步骤1:创建一个表genid，并插入常数值为1，表示需要生成序列的次数。如果需要生成12个月的序列，可以插入12次。最好可以插入更多的次数，比如100次，这样就不会遇到任何问题了。

create table genid(id int)

------------为月数Insert into genid values(1)

步骤2:需要为其生成序列的表。

create table pat(patid varchar(10),stdt timestamp, enddt timestamp);
insert into pat values('Pat01','2018-03-30 00:00:00.0','2018-04-30 00:00:00.0')
insert into pat values('Pat02','2018-02-28 00:00:00.0','2018-04-30 00:00:00.0')
insert into pat values('Pat03','2017-10-28 00:00:00.0','2018-04-30 00:00:00.0')

步骤3:该查询将为您生成序列。

with cte as 
(
select max(enddt) as maxdt
from pat
) ,
cte2 as(
select dateadd('month', -1 * row_number() over(order by 1),  maxdt::date ) as gendt  
from  genid , cte
) select * 
from pat, cte2
where gendt between stdt and enddt

Answer 5

generate_series()支持日期和时间戳是在Postgres 8.4中添加的。另外，目前AWS Redshift中的generate_series()函数只能在SELECT语句中使用。所以我的方法是，

WITH RECURSIVE date_series(d) AS (
  SELECT '2023-01-01'::timestamp -- Replace with your desired start date
  UNION ALL
  SELECT DATE_TRUNC('month', d + INTERVAL '31 day') FROM date_series WHERE d < '2023-08-01'::timestamp -- Replace with your desired end date
)
SELECT d FROM date_series;