ネストデータのユースケース

このトピックでは、ネストデータのユースケースについて説明します。ネストデータは、ネストされたフィールドを含むデータです。ネストされたフィールドは、配列、構造体、オブジェクトなど、単一のエンティティとして結合されるフィールドです。

以前に説明した拡張は、通常の 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;