嵌套数据使用案例

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