Anwendungsfälle für verschachtelte Daten - Amazon Redshift

Anwendungsfälle für verschachtelte Daten

Sie können die zuvor beschriebenen Erweiterungen mit den gewöhnlichen SQL-Funktionen kombinieren. Die folgenden Anwendungsfälle veranschaulichen einige häufige Kombinationen. Anhand dieser Beispiele soll demonstriert werden, wie Sie verschachtelte Daten anwenden können. Sie sind nicht Teil des Tutorials.

Aufnehmen verschachtelter Daten

Sie können eine CREATE TABLE AS-Anweisung verwenden, um Daten aus einer externen Tabelle mit komplexen Datentypen aufzunehmen. Die folgende Abfrage extrahiert alle Kunden und ihre Telefonnummern aus der externen Tabelle mithilfe von LEFT JOIN und speichert sie in der Amazon-Redshift-Tabelle 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

Aggregieren verschachtelter Daten mit Unterabfragen

Sie können eine Unterabfrage zum Aggregieren von verschachtelten Daten verwenden. Die folgende Abbildung veranschaulicht dieses Konzept.

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

Die folgenden Daten werden zurückgegeben.

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

Wenn Sie verschachtelte Daten durch Gruppieren nach der übergeordneten Zeile aggregieren, ist die effizienteste Methode dafür die, die im vorherigen Beispiel veranschaulicht wird. Im diesem Beispiel werden die verschachtelten c.orders-Zeilen nach der ihnen übergeordneten Zeile c gruppiert. Wenn Ihnen bekannt ist, dass id für jeden customer-Eintrag eindeutig und jeder o.shipdate-Eintrag niemals null ist, können Sie alternativ dazu wie im folgenden Beispiel gezeigt aggregieren. Dieser Ansatz ist in der Regel jedoch nicht so effizient wie das vorherige Beispiel.

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

Sie können die Abfrage auch mit einer Unterabfrage in der FROM-Klausel schreiben, die sich auf einen Alias (c) der Vorgängerabfrage bezieht und Arraydaten extrahiert. Das folgende Beispiel illustriert diese Herangehensweise.

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

Verknüpfen von Amazon Redshift und verschachtelten Daten

Sie können Amazon-Redshift-Daten auch mit verschachtelten Daten in einer externen Tabelle verknüpfen. Angenommen, Sie haben die folgenden verschachtelten Daten in 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>> )

Nehmen wir außerdem an, Sie haben die folgende Tabelle in Amazon Redshift.

CREATE TABLE prices ( id int, price double precision )

Die folgende Abfrage findet basierend auf dem Vorangehenden die Gesamtzahl und Gesamtmenge der Käufe eines jeden Kundens. Das folgende Beispiel dient nur zur Veranschaulichung. Es gibt nur Daten zurück, wenn Sie die Tabellen wie zuvor beschrieben erstellt haben.

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