REDSHIFT:如何在不创建称为“数字"的表格的情况下生成一系列数字?在redshift(Postgres 8.0.2)中? [英] REDSHIFT: How can I generate a series of numbers without creating a table called "numbers" in redshift (Postgres 8.0.2)?

问题描述

我需要为报告创建一个空的时间表系列，以便可以将多个表的联接活动留给它.一天中的每个小时不一定都具有数据，但是我希望它在不活动时显示为空或零，而不是忽略一天中的那个小时.

I need to create an empty time table series for a report so I can left join activity from several tables to it. Every hour of the day does not necessarily have data, but I want it to show null or zero for inactivity instead of omitting that hour of the day.

在更高版本的Postgres(版本8.0.2)中，这可以通过多种方式实现:

In later versions of Postgres (post 8.0.2), this is easy in several ways:

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

CROSS JOIN (select generate_series as hours from generate_series(now()::timestamp, now()::timestamp + interval '1 day', '1 hour'::interval )) date_series

Redshift可以运行其中一些命令，但是当您尝试与任何表结合运行时，会引发错误.

Redshift can run some of these commands, but throws an error when you attempt to run it in conjunction with any of the tables.

我需要什么:

一种可靠的方式来生成一系列数字(例如0-23)作为子查询，该子查询将在redshift上运行(使用postgres 8.0.2).

A reliable way to generate a series of numbers (e.g. 0-23) as a subquery that will run on redshift (uses postgres 8.0.2).

推荐答案

只要您的表中的行数超过了所需序列的行数，这在过去对我有用:

As long as you have a table that has more rows than your required series has numbers, this is what has worked for me in the past:

select
    (row_number() over (order by 1)) - 1 as hour
from
    large_table
limit 24
;

哪个返回数字0-23.

这篇关于REDSHIFT:如何在不创建称为“数字"的表格的情况下生成一系列数字?在redshift(Postgres 8.0.2)中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！