REDSHIFT：如何在Redshift（Postgres 8.0.2）中生成一系列数字而不创建名为“numbers”的表？

Question

我需要为报告创建一个空的时间表系列，以便我可以将来自多个表的活动左连接到其中。一天中的每个小时不一定都有数据，但我希望它显示无活动的null或零，而不是省略那一天的小时。

在较新版本的Postgres（post 8.0.2）中，这有几种简单的方法：

SELECT unnest(array [0,1,2,3,4 ...])作为数字

或

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

Redshift可以运行其中一些命令，但在尝试与任何表一起运行时会抛出错误。

我需要的是：

可靠的方法生成一系列数字（例如0-23）作为子查询，可以在redshift上运行（使用postgres 8.0.2）。

- Dharam

---

https://dev59.com/6mEh5IYBdhLWcg3wIgfN#34167753 - systemjack

因为Redshift没有版本控制，所以旧版本永远不能被任何人使用，我不明白为什么这个问题应该保持开放状态。我投票支持关闭。现在Redshift已经支持了这个功能。 - Evan Carroll

Evan Carroll，Redshift不支持在生成系列的子查询中进行交叉连接。它会显示错误“指定的类型或函数（每个INFO消息一个）不支持Redshift表；”。 - user2455668

@EvanCarroll 这个支持文档在哪里可以找到？我仍然看到在 Redshift 文档中 generate_series 被列为“不支持”。我们已经迁移到 BigQuery，所以我无法自行测试。 - Dharam

这个回答解决了你的问题吗？AWS Redshift中的序列号生成函数 - Steve Chambers

不是@SteveChambers，问题非常相似，但不够具体。现有的解决方法和建议都很麻烦，因为它们都不可靠或多用途。我已经一两年没有使用Redshift了，所以他们可能已经添加了功能来解决这个问题。 - Dharam

Answer 1

只要您有一个比所需系列数字更多的行的表格，这就是我过去使用的方法：

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

返回数字 0-23。

- moertel

---

这是一个不错的解决方案，如果我使用像pg_catalog.pg_operator这样的东西，我就不必担心模式更改会破坏查询。 - Dharam

丑陋但有效 - Merlin

再次进行交叉连接以获取更多值（pg_operator仅有647个对象）。 - mbourgon

Answer 2

很遗憾，Amazon Redshift不允许在表函数中使用generate_series()。解决方法似乎是创建一个数字表。

另请参阅:

• 在Redshift中使用sql函数generate_series()
• 在Redshift和MySQL中生成系列，这似乎不正确，但介绍了一些有趣的想法

- John Rotenstein

---

创建一个数字表格让我感到非常糟糕。这就像在Python脚本中逐个写出数字一样，感觉很不对劲。 - Dharam

你可以创建一个日期表，包括星期几、公共假日、月份、季度等列。这是从日期中提取有用信息的常见做法。然后，你可以将此表重复使用作为一个数字表，而不会感到烦恼。 - John Rotenstein

Answer 3

在2021年4月，Redshift发布了递归支持。现在你可以使用以下代码生成数字序列（甚至是表）。

with recursive numbers(NUMBER) as
(
select 1 UNION ALL
select NUMBER + 1 from numbers where NUMBER < 28
)

- Pirate X

Answer 4

我不太喜欢查询系统表以获取行号列表。 如果它是像一天的小时数这样的固定且足够小的东西，我会选择普通的 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与您想要的任何内容连接起来。

- pcv

---

我查询一个非常小的表并选择 row_number() over () 的运行效果更好。Redshift 对于重复的 UNION ALL 子查询表现不佳，即使是像一天中的小时数这样小的查询，使用 row_number 也可以获得更好的性能表现。 - Dharam

这个答案还描述了如何通过使用“CROSS JOIN”来生成更多的数字。 - Steve Chambers