嵌套数据使用案例

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

主题

提取嵌套数据
使用子查询聚合嵌套数据
联接 Amazon Redshift 和嵌套数据

提取嵌套数据

您可以使用 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

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