在redshift中使用sql函数generate_series（）

I'd like to use the generate series function in redshift, but have not been successful.

我想在redshift中使用generate系列函数，但还没有成功。

The redshift documentation says it's not supported. The following code does work:

redshift文档说它不受支持。以下代码确实有效：

select *
from generate_series(1,10,1)

outputs:

输出：

1
2
3
...
10

I'd like to do the same with dates. I've tried a number of variations, including:

我想对约会做同样的事情。我尝试了很多变种，包括：

select *
from generate_series(date('2008-10-01'),date('2008-10-10 00:00:00'),1)

kicks out:

踢出去：

 ERROR: function generate_series(date, date, integer) does not exist
 Hint: No function matches the given name and argument types.
 You may need to add explicit type casts. [SQL State=42883]

Also tried:

还尝试过：

select *
from generate_series('2008-10-01 00:00:00'::timestamp,
'2008-10-10 00:00:00'::timestamp,'1 day')

And tried:

并试过：

select *
from generate_series(cast('2008-10-01 00:00:00' as datetime),
cast('2008-10-10 00:00:00' as datetime),'1 day')

both kick out:

两个都踢了出来：

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. [SQL State=42883]

If not looks like I'll use this code from another post:

如果看起来不像我将从另一篇文章中使用此代码：

SELECT to_char(DATE '2008-01-01'
+ (interval '1 month' * generate_series(0,57)), 'YYYY-MM-DD') AS ym

PostgreSQL generate_series() with SQL function as arguments

PostgreSQL generate_series（），SQL函数作为参数

4 个解决方案

#1

Amazon Redshift seems to be based on PostgreSQL 8.0.2. The timestamp arguments to generate_series() were added in 8.4.

Amazon Redshift似乎基于PostgreSQL 8.0.2。 generate_series（）的时间戳参数在8.4中添加。

Something like this, which sidesteps that problem, might work in Redshift.

这样可以回避这个问题的东西可能会在Redshift中起作用。

SELECT current_date + (n || ' days')::interval
from generate_series (1, 30) n

It works in PostgreSQL 8.3, which is the earliest version I can test. It's documented in 8.0.26.

它适用于PostgreSQL 8.3，这是我可以测试的最早版本。它记录在8.0.26中。

Later . . .

后来。。。

It seems that generate_series() is unsupported in Redshift. But given that you've verified that select * from generate_series(1,10,1) does work, the syntax above at least gives you a fighting chance. (Although the interval data type is also documented as being unsupported on Redshift.)

似乎在Redshift中不支持generate_series（）。但鉴于您已经验证select * from generate_series（1,10,1）确实有效，上面的语法至少会给你一个战斗机会。（虽然间隔数据类型也记录为在Redshift上不受支持。）

Still later . . .

还是晚一点。。。

You could also create a table of integers.

您还可以创建一个整数表。

create table integers (
  n integer primary key
);

Populate it however you like. You might be able to use generate_series() locally, dump the table, and load it on Redshift. (I don't know; I don't use Redshift.)

随便填充它。您可以在本地使用generate_series（），转储表并在Redshift上加载它。（我不知道;我不使用Redshift。）

Anyway, you can do simple date arithmetic with that table without referring directly to generate_series() or to interval data types.

无论如何，您可以使用该表进行简单的日期算术，而无需直接引用generate_series（）或间隔数据类型。

select (current_date + n)
from integers
where n < 31;

That works in 8.3, at least.

至少在8.3中有效。

#2

Using Redshift today, you can generate a range of dates by using datetime functions and feeding in a number table.

今天使用Redshift，您可以使用日期时间函数生成一系列日期并输入数字表。

select (getdate()::date - generate_series)::date from generate_series(1,30,1)

Generates this for me

为我生成这个

#3

The generate_series() function is not fully supported by Redshift. See the Unsupported PostgreSQL functions section of the developer guide.

Redshift不完全支持generate_series（）函数。请参阅开发人员指南的不支持的PostgreSQL函数部分。

#4

I needed to do something similar, but with 5 minutes intervals over 7 days. So here's a CTE based hack (ugly but not too verbose)

我需要做类似的事情，但是在7天内间隔5分钟。所以这是一个基于CTE的黑客（丑陋但不太冗长）

INSERT INTO five_min_periods
WITH 
periods  AS (select 0 as num UNION select 1 as num UNION select 2 UNION select 3 UNION select 4 UNION select 5 UNION select 6 UNION select 7 UNION select 8 UNION select 9 UNION select 10 UNION select 11),
hours    AS (select num from periods UNION ALL select num + 12 from periods),
days     AS (select num from periods where num <= 6),
rightnow AS (select CAST( TO_CHAR(GETDATE(), 'yyyy-mm-dd hh24') || ':' || trim(TO_CHAR((ROUND((DATEPART (MINUTE, GETDATE()) / 5), 1) * 5 ),'09')) AS TIMESTAMP) as start)
select  
  ROW_NUMBER() OVER(ORDER BY d.num DESC, h.num DESC, p.num DESC) as idx
  , DATEADD(minutes, -p.num * 5, DATEADD( hours, -h.num, DATEADD( days, -d.num, n.start ) ) ) AS period_date
from days d, hours h, periods p, rightnow n

Should be able to extend this to other generation schemes. The trick here is using the Cartesian product join (i.e. no JOIN/WHERE clause) to multiply the hand-crafted CTE's to produce the necessary increments and apply to an anchor date.

应该能够将其扩展到其他生成方案。这里的技巧是使用笛卡尔积连接（即没有JOIN / WHERE子句）来乘以手工制作的CTE以产生必要的增量并应用于锚定日期。

#1