REDSHIFT:如何在redshift(Postgres 8.0.2)中创建一个名为“numbers”的表而不生成一系列数字?
我需要为报告创建一个空的时间表系列,以便我可以将几个表中的活动留给它。一天中的每个小时不一定有数据,但我希望它显示为空或零,表示不活动,而不是忽略当天的那个小时。REDSHIFT:如何在redshift(Postgres 8.0.2)中创建一个名为“numbers”的表而不生成一系列数字?
在Postgres里(POST 8.0.2)的后续版本,这很容易在几个方面:
SELECT unnest(array[0,1,2,3,4...]) as numbers
OR
CROSS JOIN (select generate_series as hours from generate_series(now()::timestamp, now()::timestamp + interval '1 day', '1 hour'::interval )) date_series
红移可以运行一些命令,但是当您尝试与任何表一起运行时会引发错误。
我所需要的:
一个可靠的方式来产生一系列的数字(例如,0-23)为子查询将上红移运行(使用postgres的8.0.2)。
系列有号码,这是我过去的工作:
select
(row_number() over (order by 1)) - 1 as hour
from
large_table
limit 24
;
其中返回数字0-23
。
这是一个体面的解决方案,如果我使用类似pg_catalog.pg_operator的东西,我不必担心会破坏查询的模式更改。 – darbarinyc
使用系统表,好主意! – moertel
不幸的是,Amazon Redshift不允许将generate_series()
用于表格功能。解决方法似乎是创建一个数字表。
参见:
- Using sql function generate_series() in redshift
- Generate Series in Redshift and MySQL,这似乎不正确的,但只要你有一个比你需要更多行的表并介绍一些有趣的想法
创建一个数字表让我非常可怕。感觉就像在python脚本中逐个写出数字一样。这只是错误的。 – darbarinyc
您可以创建一个'日期'表,并且有诸如'DayOfWeek','PublicHoliday','Month','Quarter'等列。这是从日期中提取有用信息的常用操作。然后,您可以将此表重新用作“Numbers”表,而不会感到非常烦躁。 –
我不是一个查询系统表的好粉丝,只是为了得到一个行号列表。如果它的东西不变,并足够小如一日的时间,我会去与普通的旧UNION ALL
:
WITH
hours_in_day AS (
SELECT 0 AS hour
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
...
UNION ALL SELECT 23
)
然后再加入hours_in_day
到任何你想做的。
我实际上有更好的运气查询一个非常小的表并选择row_number()over()。对于重复的UNION ALL子查询,Redshift不会很好,甚至对于像一天中的几小时那样小的事情,我们已经看到row_number具有更好的性能。 – darbarinyc
http://*.com/a/34167753/3019685 – systemjack