Anwendungsfälle für verschachtelte Daten

In diesem Thema werden Anwendungsfälle für verschachtelte Daten beschrieben. Verschachtelte Daten sind Daten, die verschachtelte Felder enthalten. Verschachtelte Felder sind Felder, die zu einer Einheit zusammengefügt sind, z. B. Arrays, Strukturen oder Objekte.

Sie können die zuvor beschriebenen Erweiterungen mit den üblichen Funktionen kombinieren. SQL 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 Array-Daten 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;