巢狀資料使用案例 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

巢狀資料使用案例

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