Amazon Redshift
数据库开发人员指南 (API 版本 2012-12-01)
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

嵌套数据使用案例

您可以使用常用的 SQL 功能组合前面介绍的扩展。下面的使用案例阐述了一些常见的组合。这些示例帮助演示如何使用嵌套数据。它们不是本教程的组成部分。

提取嵌套数据

您可以使用 CREATE TABLE AS 语句从包含复杂数据类型的外部表中提取数据。以下查询使用 LEFT JOIN 从外部表中提取所有客户及其电话号码,并将它们存储在 Amazon Redshift 表 CustomerPhones 中。

CREATE TABLE CustomerPhones AS SELECT c.name.given, c.name.family, p AS phone FROM spectrum.customers c LEFT JOIN c.phones p ON true

使用子查询聚合嵌套数据

您可以使用子查询聚合嵌套数据。以下示例说明了此方法。

SELECT c.name.given, c.name.family, (SELECT COUNT(*) FROM c.orders o) AS ordercount FROM spectrum.customers c

将返回以下数据。

given | family | ordercount --------|----------|-------------- Jenny | Doe | 0 John | Smith | 2 Andy | Jones | 1 (3 rows)

注意

当您通过按父行进行分组来聚合嵌套数据时,最高效的方法是前面示例中所示的方法。在该示例中,c.orders 的嵌套行按其父行 c 分组。或者,如果您知道 id 对于每个 customer 是唯一的且 o.shipdate 从不为 Null,则您可以按下例中所示进行聚合。但一般而言,这种方法的效率比不上前面的示例。

SELECT c.name.given, c.name.family, COUNT(o.shipdate) AS ordercount FROM spectrum.customers c LEFT JOIN c.orders o ON true GROUP BY c.id, c.name.given, c.name.family

您也可以在 FROM 子句中使用一个子查询来编写查询,此子查询引用原级查询的别名 (c) 并提取数组数据。以下示例演示了此方法。

SELECT c.name.given, c.name.family, s.count AS ordercount FROM spectrum.customers c, (SELECT count(*) AS count FROM c.orders o) s

联接 Amazon Redshift 和嵌套数据

还可以在外部表中联接 Amazon Redshift 数据以及嵌套数据。例如,假设您在 Amazon S3 中具有以下嵌套数据。

CREATE EXTERNAL TABLE spectrum.customers2 ( id int, name struct<given:varchar(20), family:varchar(20)>, phones array<varchar(20)>, orders array<struct<shipdate:timestamp, item:int>> )

此外,假设您在 Amazon Redshift 中具有下面的表。

CREATE TABLE prices ( id int, price double precision )

以下查询根据前面的内容查找每个客户的采购总数和购买金额。下例仅用于举例说明。仅当您创建了前面介绍的表时,它才返回数据。

SELECT c.name.given, c.name.family, COUNT(o.date) AS ordercount, SUM(p.price) AS ordersum FROM spectrum.customers2 c, c.orders o, prices p ON o.item = p.id GROUP BY c.id, c.name.given, c.name.family