在 redshift 中使用 sql 函数 generate_series() [英] Using sql function generate_series() in redshift

问题描述

我想在 redshift 中使用 generate series 功能，但没有成功.

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

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

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

select *
from generate_series(1,10,1)

输出:

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)

踢出:

 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]

也尝试过:

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

并尝试过:

select *
from generate_series(cast('2008-10-01 00:00:00' as datetime),
cast('2008-10-10 00:00:00' as datetime),'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. [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() 以 SQL 函数为参数

推荐答案

Amazon Redshift 似乎基于 PostgreSQL 8.0.2.generate_series() 的时间戳参数是在 8.4 中添加的.

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

类似这样的东西，可以避免这个问题， 可能在 Redshift 中工作.

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

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

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

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

稍后...

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

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.)

再晚一点...

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

You could also create a table of integers.

create table integers (
  n integer primary key
);

随心所欲地填充它.您也许可以在本地使用 generate_series()，转储表，然后将其加载到 Redshift 上.(我不知道；我不使用 Redshift.)

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() 或区间数据类型.

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

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

这至少适用于 8.3.

That works in 8.3, at least.

这篇关于在 redshift 中使用 sql 函数 generate_series()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！