Amazon Redshift は、パッチ 198 以降、新しい Python UDF の作成をサポートしなくなります。既存の Python UDF は、2026 年 6 月 30 日まで引き続き機能します。詳細については、[ブログ記事](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)を参照してください。 # FROM 句 クエリ内の FROM 句は、データの選択下のテーブル参照 (テーブル、ビュー、サブクエリ) を一覧表示します。複数のテーブル参照が一覧表示されている場合、FROM 句または WHERE 句のいずれかの適切な構文を使って、テーブル参照を結合する必要があります。結合基準を指定していない場合、クエリはクロス結合 (デカルト積) として処理されます。 **Topics** + [構文](#r_FROM_clause30-synopsis) + [パラメータ](#r_FROM_clause30-parameters) + [使用に関する注意事項](#r_FROM_clause_usage_notes) + [PIVOT と UNPIVOT の例](r_FROM_clause-pivot-unpivot-examples.md) + [JOIN 句の例](r_Join_examples.md) + [UNNEST の例](r_FROM_clause-unnest-examples.md) ## 構文 ``` FROM table_reference [, ...] ``` ここで *table\$1reference* は、次のいずれかになります。 ``` with_subquery_table_name [ table_alias ] table_name [ * ] [ table_alias ] ( subquery ) [ table_alias ] table_reference [ NATURAL ] join_type table_reference [ ON join_condition | USING ( join_column [, ...] ) ] table_reference join_type super_expression [ ON join_condition ] table_reference PIVOT ( aggregate(expr) [ [ AS ] aggregate_alias ] FOR column_name IN ( expression [ AS ] in_alias [, ...] ) ) [ table_alias ] table_reference UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ] ( value_column_name FOR name_column_name IN ( column_reference [ [ AS ] in_alias ] [, ...] ) ) [ table_alias ] UNPIVOT expression AS value_alias [ AT attribute_alias ] ( super_expression.attribute_name ) AS value_alias [ AT index_alias ] UNNEST ( column_reference ) [AS] table_alias ( unnested_column_name ) UNNEST ( column_reference ) WITH OFFSET [AS] table_alias ( unnested_column_name, [offset_column_name] ) ``` オプションの *table\$1alias* を使用して、テーブルと複雑なテーブル参照 (および必要に応じてその列) に、次のような一時名を付けることができます。 ``` [ AS ] alias [ ( column_alias [, ...] ) ] ``` ## パラメータ *with\$1subquery\$1table\$1name* [WITH 句](r_WITH_clause.md) のサブクエリで定義されるテーブル。 *table\$1name* テーブルまたはビューの名前。 *alias* テーブルまたはビューの一時的な代替名。エイリアスは、サブクエリから生成されたテーブルに対して提供する必要があります。他のテーブル参照では、エイリアスはオプションです。AS キーワードは常にオプションです。テーブルエイリアスは、WHERE 句など、クエリの別の部分のテーブルを識別するため、便利なショートカットを提供します。次に例を示します。 ``` select * from sales s, listing l where s.listid=l.listid ``` *column\$1alias* テーブルまたはビュー内の列に対する一時的な代替名。 *subquery* テーブルに対して評価を実行するクエリ式。テーブルは、クエリの有効期間中のみ存在し、通常は名前またはエイリアス**が与えられます。ただし、エイリアスは必須ではありません。また、サブクエリから生成されたテーブルに対して、列名を定義することもできます。サブクエリの結果を他のテーブルに結合する場合、および列をクエリ内のどこかで選択または拘束する場合、列のエイリアスの命名は重要です。 サブクエリには ORDER BY 句が含まれることがありますが、LIMIT または OFFSET 句も併せて指定しない場合、この句には効力がありません。 NATURAL 2 つのテーブル内で同じ名前を付けられた列のペアをすべて結合列として、自動的に使用する結合を定義します。明示的な結合条件は必要ありません。例えば、CATEGORY と EVENT の両方のテーブルに CATID という名前の列が存在する場合、これらのテーブルの NATURAL 結合は CATID 列による結合です。 NATURAL 結合を指定しても、結合対象のテーブルに同じ名前の列ペアが存在しない場合、クエリはデフォルト設定のクロス結合になります。 *join\$1type* 以下のいずれかの結合タイプを指定します。 + [INNER] JOIN + LEFT [OUTER] JOIN + RIGHT [OUTER] JOIN + FULL [OUTER] JOIN + CROSS JOIN クロス結合は非限定の結合で、2 つの表のデカルト積を返します。 内部結合と外部結合は限定結合です。これらの結合は、FROM 句の ON または USING 構文、または WHERE 句条件を使った (Natural 結合での) 黙示的な結合です。 内部結合は、結合条件、また結合列のリストに基づいて、一致する行だけを返します。外部結合は、同等の内部結合が返すすべての行に加え、「左側の」表、「右側の」表、または両方の表から一致しない行を返します。左の表は最初に一覧表示された表で、右の表は 2 番目に一覧表示された表です。一致しない行には、出力列のギャップを埋めるため、NULL が含まれます。 ON join\$1condition** 結合列を ON キーワードに続く条件として記述する、結合タイプの指定。次に例を示します。 ``` sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid ``` USING ( join\$1column** [, ...] ) 結合列をかっこで一覧表示する結合の指定タイプ。複数の結合列を指定する場合、カンマによって区切ります。USING キーワードは、リストより前に指定する必要があります。例えば、次のようになります。 ``` sales join listing using (listid,eventid) ``` PIVOT 表形式のデータを読みやすい形式で表現するために、出力を行から列に変更します。出力が複数の列にわたって水平に表されます。PIVOT は、(集計式を使用して出力形式を指定する)集計が含まれる GROUP BY クエリに似ています。ただし GROUP BY とは対照的に、結果は行ではなく列で返されます。 PIVOT および UNPIVOT を使用してクエリする方法の例については、「[PIVOT と UNPIVOT の例](r_FROM_clause-pivot-unpivot-examples.md)」をご確認ください。 UNPIVOT *UNPIVOT による列から行への変換* – この演算子は、入力テーブルまたはクエリ結果の結果列を行に変換して、出力を読みやすくします。UNPIVOT は、入力列のデータを 2 つの結果列 (name 列と value 列) として統合します。name 列には、入力の列名が行エントリとして格納されています。value 列には、集計の結果など、入力列の値が含まれます。例えばこれは、さまざまなカテゴリについての項目数などです。 *UNPIVOT (SUPER) によるオブジェクトのピボット解除* – オブジェクトのピボット解除を実行できます。ここで、*expression* は別の FROM 句項目を参照する SUPER 式です。詳細については、「[オブジェクトのピボット解除](query-super.md#unpivoting)」を参照してください。また、JSON 形式のデータなど、半構造化データをクエリする方法を示す例も含まれています。 *super\$1expression* 有効な SUPER 式。Amazon Redshift は、指定された属性の値ごとに 1 行を返します。SUPER データ型の詳細については、「[SUPER タイプ](r_SUPER_type.md)」を参照してください。ネストされていない SUPER 値の詳細については、「[ネストされていないクエリ](query-super.md#unnest)」を参照してください。 *attribute\$1name* SUPER 式の属性の名前。 *index\$1alias* SUPER 式内の値の位置を示すインデックスのエイリアス。 UNNEST ネストされた構造 (通常は SUPER 配列) を、ネストされていない要素を含む列に展開します。SUPER データのネスト解除の詳細については、「[半構造化データのクエリ](query-super.md)」を参照してください。例については「[UNNEST の例](r_FROM_clause-unnest-examples.md)」を参照してください。 *unnested\$1column\$1name* ネストされていない要素を含む列の名前。 UNNEST ... WITH OFFSET オフセット列をネストされていない出力に追加します。オフセットは配列内の各要素のゼロベースのインデックスを表します。このバリアントは、配列内の要素の位置を確認する場合に便利です。SUPER データのネスト解除の詳細については、「[半構造化データのクエリ](query-super.md)」を参照してください。例については「[UNNEST の例](r_FROM_clause-unnest-examples.md)」を参照してください。 *offset\$1column\$1name* オフセット列のカスタム名。インデックス列を出力に表示する方法を明示的に定義できます。このパラメータはオプションです。デフォルトでは、オフセット列名は `offset_col` です。 ## 使用に関する注意事項 列を結合するには、データ型に互換性がなければなりません。 NATURAL または USING 結合は、中間結果セットの結合列の各ペアの一方だけを保持します。 ON 構文を使った結合は、中間結果セットの両方の結合列を保持します。 「[WITH 句](r_WITH_clause.md)」も参照してください。 # PIVOT と UNPIVOT の例 PIVOT と UNPIVOT は FROM 句のパラメータで、クエリ出力をそれぞれ行から列、列から行にローテーションします。クエリ結果は読みやすい表形式で示されます。次の例では、テストデータとクエリを使用してその使用方法を示します。 これらのパラメータおよび他のパラメータの詳細については、「[FROM 句](https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html)」を参照してください。 ## PIVOT の例 サンプルテーブルとデータをセットアップし、それを使用して後続のサンプルクエリを実行します。 ``` CREATE TABLE part ( partname varchar, manufacturer varchar, quality int, price decimal(12, 2) ); INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00); INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00); INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00); INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50); INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75); INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90); INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50); INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20); INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80); ``` `price` で `AVG` を集計した `partname` の PIVOT。 ``` SELECT * FROM (SELECT partname, price FROM part) PIVOT ( AVG(price) FOR partname IN ('prop', 'rudder', 'wing') ); ``` このクエリでは次の出力が生成されます。 ``` prop | rudder | wing ---------+----------+--------- 10.33 | 2.71 | 11.50 ``` 前の例では、結果が列に変換されています。次の例は、平均価格を列ではなく行で返す `GROUP BY` クエリを示しています。 ``` SELECT partname, avg(price) FROM (SELECT partname, price FROM part) WHERE partname IN ('prop', 'rudder', 'wing') GROUP BY partname; ``` このクエリでは次の出力が生成されます。 ``` partname | avg ----------+------- prop | 10.33 rudder | 2.71 wing | 11.50 ``` `manufacturer` を暗黙の列として扱う `PIVOT` の例。 ``` SELECT * FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) FOR quality IN (1, 2, NULL) ); ``` このクエリでは次の出力が生成されます。 ``` manufacturer | 1 | 2 | null -------------------+----+----+------ local parts co | 1 | 1 | 1 big parts co | 1 | 1 | 1 small parts co | 1 | 0 | 2 ``` `PIVOT` 定義で参照されていない入力テーブルの列は、結果テーブルに暗黙的に追加されます。前出の例での `manufacturer` 列が、これに当てはまります。またこの例からは、`NULL`が `IN` 演算子で有効な値であることもわかります。 `PIVOT`上記の例の では、を含む下記のクエリと同様の情報を返します。`GROUP BY`相違点は、列 `2` と manufacturer `small parts co` に対し、`PIVOT` が値 `0` を返していることです。`GROUP BY` クエリには対応する行が含まれていません。行が特定の列に入力するデータを持たない場合、ほとんどのケースで `PIVOT` は `NULL` を挿入します。ただし、カウント集計は `NULL` を返さないので、`0` がデフォルト値として使用されます。 ``` SELECT manufacturer, quality, count(*) FROM (SELECT quality, manufacturer FROM part) WHERE quality IN (1, 2) OR quality IS NULL GROUP BY manufacturer, quality ORDER BY manufacturer; ``` このクエリでは次の出力が生成されます。 ``` manufacturer | quality | count ---------------------+---------+------- big parts co | | 1 big parts co | 2 | 1 big parts co | 1 | 1 local parts co | 2 | 1 local parts co | 1 | 1 local parts co | | 1 small parts co | 1 | 1 small parts co | | 2 ``` PIVOT 演算子は、集計式および `IN` 演算子の各値に関して、オプションのエイリアスを受け入れます。エイリアスは、列名をカスタマイズするために使用されます。集計のエイリアスがない場合は、`IN`リストのエイリアスのみが使用されます。それ以外の場合は、集計のエイリアスが列名に (名前を区切るために) アンダースコアとともに追加されます。 ``` SELECT * FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na) ); ``` このクエリでは次の出力が生成されます。 ``` manufacturer | high_count | low_count | na_count -------------------+-------------+-----------+---------- local parts co | 1 | 1 | 1 big parts co | 1 | 1 | 1 small parts co | 1 | 0 | 2 ``` 次のサンプルテーブルとデータを設定し、これらを使用して後続のサンプルクエリを実行します。データは、複数のホテルの予約日を表しています。 ``` CREATE TABLE bookings ( booking_id int, hotel_code char(8), booking_date date, price decimal(12, 2) ); INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12); INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00); INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54); INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00); INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00); INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00); INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00); INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00); INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00); INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00); INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00); INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00); INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00); INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00); INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00); INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00); INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00); INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00); INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00); INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00); INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00); INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00); INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00); INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00); INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00); INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00); INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00); INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00); INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00); INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00); INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00); INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00); INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00); INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00); INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00); INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00); ``` このサンプルクエリでは、予約レコードを集計し、週ごとの合計を示します。各週の終了日が列名になります。 ``` SELECT * FROM (SELECT booking_id, (date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate, hotel_code AS "hotel code" FROM bookings ) PIVOT ( count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18') ); ``` このクエリでは次の出力が生成されます。 ``` hotel code | 2023-02-04 | 2023-02-11 | 2023-02-18 ------------+-------------+------------+---------- FOREST_L | 3 | 2 | 1 DESERT_S | 4 | 3 | 2 OCEAN_WV | 3 | 3 | 3 CITY_BLD | 3 | 1 | 2 ``` Amazon Redshift は、複数の列を組み合わせたクロス集計をサポートしていません。ただし、次のようなクエリを使用して、PIVOT による集計と同様に、行データを列に変換できます。ここでは、前の例と同じ予約サンプルデータを使用します。 ``` SELECT booking_date, MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L, MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S, MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END) AS OCEAN_WV FROM bookings GROUP BY booking_date ORDER BY booking_date asc; ``` サンプルクエリを実行すると、予約されたホテルを示す短いフレーズの横に予約日が表示されます。 ``` booking_date | forest_l | desert_s | ocean_wv ---------------+------------------+------------------+-------------------- 2023-02-01 | forest is booked | desert is booked | ocean is booked 2023-02-02 | forest is booked | desert is booked | ocean is booked 2023-02-04 | forest is booked | desert is booked | ocean is booked 2023-02-05 | | desert is booked | 2023-02-06 | | desert is booked | ``` `PIVOT` の使用に関する注意事項を以下に示します。 + `PIVOT` を適用可能なのはテーブル、サブクエリ、および共通テーブル式 (CTE) です。`JOIN` 式、再帰的 CTE、`PIVOT`、および `UNPIVOT` 式のいずれにも `PIVOT` を適用することはできません。さらに、ネストされていない `SUPER` 式、ならびに Redshift Spectrum のネストされたテーブルもサポートされません。 + `PIVOT` では `COUNT`、`SUM`、`MIN`、`MAX`、および `AVG` 集計関数がサポートされています。 + `PIVOT` 集計式は、サポートされた集計関数に対する呼び出しである必要があります。集計の上位にある複雑な式はサポートされていません。集計引数には、`PIVOT`入力テーブル以外のテーブルへの参照を含めることはできません。親クエリに対する相関参照もサポートされません。集計引数にはサブクエリを含めることができます。これらは内部的に相関させることも、`PIVOT` 入力テーブル上で相関させることもできます。 + `PIVOT IN` リスト値は、列参照またはサブクエリにすることはできません。各値は、`FOR`列参照と型互換である必要があります。 + `IN` リスト値にエイリアスがない場合には、`PIVOT` によりデフォルトの列名が生成されます。`IN` 値が 'abc' や 5 などの定数の場合、デフォルトの列名自体も定数となります。いずれの複雑な式でも、列名は Amazon Redshift で標準のデフォルト名 (`?column?` など) になります。 ## UNPIVOT の例 サンプルデータを設定し、それを使用して後続の例を実行します。 ``` CREATE TABLE count_by_color (quality varchar, red int, green int, blue int); INSERT INTO count_by_color VALUES ('high', 15, 20, 7); INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40); INSERT INTO count_by_color VALUES ('low', 10, 23, NULL); ``` `UNPIVOT`red、green、blue の入力列で を実行します。 ``` SELECT * FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT ( cnt FOR color IN (red, green, blue) ); ``` このクエリでは次の出力が生成されます。 ``` color | cnt -------+----- red | 15 red | 35 red | 10 green | 20 green | 23 blue | 7 blue | 40 ``` デフォルトでは、入力列の `NULL` 値はスキップされ、これに対する結果行は生成されません。 次に、`INCLUDE NULLS`を含む `UNPIVOT` の例を示します。 ``` SELECT * FROM ( SELECT red, green, blue FROM count_by_color ) UNPIVOT INCLUDE NULLS ( cnt FOR color IN (red, green, blue) ); ``` その出力を次に示します。 ``` color | cnt -------+----- red | 15 red | 35 red | 10 green | 20 green | green | 23 blue | 7 blue | 40 blue | ``` `INCLUDING NULLS` パラメータが設定されている場合には、`NULL`入力値は結果行を生成します。 暗黙の列として `quality` を含む `The following query shows UNPIVOT`。 ``` SELECT * FROM count_by_color UNPIVOT ( cnt FOR color IN (red, green, blue) ); ``` このクエリでは次の出力が生成されます。 ``` quality | color | cnt ---------+-------+----- high | red | 15 normal | red | 35 low | red | 10 high | green | 20 low | green | 23 high | blue | 7 normal | blue | 40 ``` `UNPIVOT` 定義で参照されていない入力テーブルの列は、結果テーブルに暗黙的に追加されます。この例での `quality` 列が、これに当てはまります。 次に、`IN`リスト内の値のためのエイリアスを含む `UNPIVOT` の例を示します。 ``` SELECT * FROM count_by_color UNPIVOT ( cnt FOR color IN (red AS r, green AS g, blue AS b) ); ``` 前述のクエリでは、次の出力が生成されます。 ``` quality | color | cnt ---------+-------+----- high | r | 15 normal | r | 35 low | r | 10 high | g | 20 low | g | 23 high | b | 7 normal | b | 40 ``` `UNPIVOT` 演算子は、`IN`リストの各値に関するオプションのエイリアスを受け入れます。各エイリアスでは、それぞれの `value` 列にあるデータをカスタマイズできますます。 以下に、`UNPIVOT`の使用に関する注意事項を示します。 + `UNPIVOT` を適用可能なのはテーブル、サブクエリ、および共通テーブル式 (CTE) です。`JOIN` 式、再帰的 CTE、`PIVOT`、および `UNPIVOT` 式のいずれにも `UNPIVOT` を適用することはできません。さらに、ネストされていない `SUPER` 式、ならびに Redshift Spectrum のネストされたテーブルもサポートされません。 + `UNPIVOT IN` リストには、入力テーブルの列への参照のみを含める必要があります。`IN` リスト列には、その全体との互換性がある共通の型が必要です。`UNPIVOT` 値列には、下記の共通の型があります。`UNPIVOT` 名前列の型は `VARCHAR` です。 + `IN` リストの値がエイリアスを持たない場合、`UNPIVOT`では、デフォルト値として列名が使用されます。 # JOIN 句の例 SQL JOIN 句は、共通のフィールドに基づいて 2 つ以上のテーブルのデータを結合するために使用されます。結果は、指定した結合方法によって変わる場合もあります。JOIN 句の詳細については、「[パラメータ](r_FROM_clause30.md#r_FROM_clause30-parameters)」を参照してください。 次の例では、`TICKIT`サンプルデータのデータを使用します。データベーススキーマの詳細については、「[サンプルデータベース](c_sampledb.md)」を参照してください。サンプルデータをロードする方法については、「Amazon Redshift 入門ガイド」の「[データのロード](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html)」を参照してください。** 次のクエリは、LISTING テーブルと SALES テーブル間の内部結合です (JOIN キーワードを除く)。ここで、LISTING テーブルの LISTID は 1～5 です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 1、4、および 5 が基準に一致することを示しています。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing, sales where listing.listid = sales.listid and listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 次のクエリは左外部結合です。左と右の外部結合は、もう一方のテーブルに一致するものが見つからない場合に、結合したどちらかのテーブルの値を保持します。左のテーブルと右のテーブルは、構文に一覧表示されている最初と 2 番目のテーブルです。NULL 値は、結果セットの「ギャップ」を埋めるために使われます。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 2 および 3 にセールスがないことを示しています。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing left outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 次のクエリは右外部結合です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 1、4、および 5 が基準に一致することを示しています。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing right outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 次のクエリは完全結合です。完全結合は、もう一方のテーブルに一致するものが見つからない場合に、結合したテーブルの値を保持します。左のテーブルと右のテーブルは、構文に一覧表示されている最初と 2 番目のテーブルです。NULL 値は、結果セットの「ギャップ」を埋めるために使われます。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 2 および 3 にセールスがないことを示しています。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 次のクエリは完全結合です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。セールスがない行 (LISTID 2 および 3) のみが結果に表示されます。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 and (listing.listid IS NULL or sales.listid IS NULL) group by 1 order by 1; listid | price | comm -------+--------+-------- 2 | NULL | NULL 3 | NULL | NULL ``` 次の例は、ON 句を含む内部結合です。この場合、NULL 行は返されません。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 次のクエリは、LISTING テーブルと SALES テーブルのクロス結合またはデカルト結合で、結果を制限する述語が使用されています。このクエリは、両方のテーブルの LISTID 1、2、3、4、および 5 について、SALES テーブルと LISTING テーブルの LISTID 列の値と一致します。結果は、20 行が基準に一致することを示しています。 ``` select sales.listid as sales_listid, listing.listid as listing_listid from sales cross join listing where sales.listid between 1 and 5 and listing.listid between 1 and 5 order by 1,2; sales_listid | listing_listid -------------+--------------- 1 | 1 1 | 2 1 | 3 1 | 4 1 | 5 4 | 1 4 | 2 4 | 3 4 | 4 4 | 5 5 | 1 5 | 1 5 | 2 5 | 2 5 | 3 5 | 3 5 | 4 5 | 4 5 | 5 5 | 5 ``` 次の例は、2 つのテーブル間の自然結合です。この場合、listid、sellerid、eventid、および dateid の各列は、両方のテーブルで同じ名前とデータ型を持つため、結合列として使用されます。結果は 5 行に制限されます。 ``` select listid, sellerid, eventid, dateid, numtickets from listing natural join sales order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+-----------+---------+--------+----------- 113 | 29704 | 4699 | 2075 | 22 115 | 39115 | 3513 | 2062 | 14 116 | 43314 | 8675 | 1910 | 28 118 | 6079 | 1611 | 1862 | 9 163 | 24880 | 8253 | 1888 | 14 ``` 次の例は、USING 句が含まれている 2 つのテーブル間の結合です。この場合、listid と eventid の各列が結合列として使用されます。結果は 5 行に制限されます。 ``` select listid, listing.sellerid, eventid, listing.dateid, numtickets from listing join sales using (listid, eventid) order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+----------+---------+--------+----------- 1 | 36861 | 7872 | 1850 | 10 4 | 8117 | 4337 | 1970 | 8 5 | 1616 | 8647 | 1963 | 4 5 | 1616 | 8647 | 1963 | 4 6 | 47402 | 8240 | 2053 | 18 ``` 次のクエリは、FROM 句の 2 つのサブクエリを内部結合したものです。このクエリは、イベント (コンサートとショー) の異なるカテゴリのチケットの販売数と売れ残り数を検出します。この FROM 句サブクエリはテーブル**サブクエリです。これらは、複数の列と行を返すことができます。 ``` select catgroup1, sold, unsold from (select catgroup, sum(qtysold) as sold from category c, event e, sales s where c.catid = e.catid and e.eventid = s.eventid group by catgroup) as a(catgroup1, sold) join (select catgroup, sum(numtickets)-sum(qtysold) as unsold from category c, event e, sales s, listing l where c.catid = e.catid and e.eventid = s.eventid and s.listid = l.listid group by catgroup) as b(catgroup2, unsold) on a.catgroup1 = b.catgroup2 order by 1; catgroup1 | sold | unsold ----------+--------+-------- Concerts | 195444 |1067199 Shows | 149905 | 817736 ``` # UNNEST の例 UNNEST は FROM 句のパラメータで、ネストされたデータを、データのネストされていない要素を保持する列に展開します。データのネスト解除の詳細については、「[半構造化データのクエリ](query-super.md)」を参照してください。 次のステートメントは、製品 ID の配列を含む `products` 列を含む `orders` テーブルを作成して入力します。このセクションの例では、このテーブルのサンプルデータを使用します。 ``` CREATE TABLE orders ( order_id INT, products SUPER ); -- Populate table INSERT INTO orders VALUES (1001, JSON_PARSE('[ { "product_id": "P456", "name": "Monitor", "price": 299.99, "quantity": 1, "specs": { "size": "27 inch", "resolution": "4K" } } ] ')), (1002, JSON_PARSE(' [ { "product_id": "P567", "name": "USB Cable", "price": 9.99, "quantity": 3 }, { "product_id": "P678", "name": "Headphones", "price": 159.99, "quantity": 1, "specs": { "type": "Wireless", "battery_life": "20 hours" } } ] ')); ``` 以下は、PartiQL 構文を使用してサンプルデータでクエリをネスト解除する例です。 ## OFFSET 列のない配列のネスト解除 次のクエリは、製品列の SUPER 配列をネスト解除します。各行は `order_id` の注文品目を表します。 ``` SELECT o.order_id, unnested_products.product FROM orders o, UNNEST(o.products) AS unnested_products(product); order_id | product ----------+----------------------------------------------------------------------------------------------------------------------------- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} (3 rows) ``` 次のクエリでは、各注文で最も高価な製品を見つけます。 ``` SELECT o.order_id, MAX(unnested_products.product) FROM orders o, UNNEST(o.products) AS unnested_products(product); order_id | product ----------+----------------------------------------------------------------------------------------------------------------------------- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} (2 rows) ``` ## 暗黙的な OFFSET 列を持つ配列のネスト解除 次のクエリでは、`UNNEST ... WITH OFFSET` パラメータを使用して、注文配列内の各製品のゼロベースの位置を表示します。 ``` SELECT o.order_id, up.product, up.offset_col FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product); order_id | product | offset_col ----------+-----------------------------------------------------------------------------------------------------------------------------+------------ 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1 (3 rows) ``` ステートメントはオフセット列のエイリアスを指定していないため、Amazon Redshift はデフォルトで `offset_col` という名前を付けます。 ## 明示的な OFFSET 列を持つ配列のネスト解除 次のクエリでも、`UNNEST ... WITH OFFSET` パラメータを使用して注文配列内の製品を表示します。このクエリが前の例のクエリと違う点は、オフセット列に明示的にエイリアス `idx` という名前を付けることです。 ``` SELECT o.order_id, up.product, up.idx FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product, idx); order_id | product | idx ----------+-----------------------------------------------------------------------------------------------------------------------------+----- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1 (3 rows) ```