巢狀資料使用案例

您可以將前述的延伸模組與一般的 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 群組。或者，如果您知道每個 customer 的 id 是唯一的，並且 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;

您也可以在參照上階查詢別名 (c) 的 FROM 子句中使用子查詢來編寫查詢，並擷取陣列資料。下列範例示範此方法。

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;