在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以SQL函数作为参数的generate_series()
推荐答案
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中有效.
这篇关于在Redshift中使用SQL函数generate_series()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!