SQL WITH

SQL の単一のステートメントでは簡単に回答できない複雑な質問に直面することがあります。

単一のステートメントで回答しようとすることもできますが、複数のインラインビューまたは複数のサブクエリを使用することになり、これはSQLを理解しにくくする可能性があります。また、Hive SQL などの一部のSQLバリエーションでは、1つ以上のサブクエリを許可しない場合もあります。

2つ目のアプローチは、複雑さをいくつかの異なるステップに分解することです。これを行う方法の一つは、各ステップの結果を格納する複数のテーブルを作成することです。これは通常、デバッグを容易にし、コードの実行方法を追跡することをより簡単にします。

ただし、望ましくない副作用は、分析が完了した後、これらのテーブルを削除することを覚えておく必要があることです。それ以外の場合、これらのテーブルはデータベース内に残り、データベース管理の問題を引き起こす可能性があります。

複数のテーブルを作成する代替方法は、SQL の WITH 句を使用することです。

構文

WITH 句の基本構文は次のようになります：

WITH 句の使用方法は、テーブルの作成に非常に似ています。テーブルを作成するとき、名前を付けます。同様に、WITH 句を使用する場合も名前を付け、この名前は実質的にメインの SQL ステートメント内でのテーブル名のように機能します。

WITH はテーブルまたはビューを作成しないため、WITHステートメントに関連するオブジェクトはメインの SQL ステートメントの実行後に消え、クリーンアップする必要はありません。

複数の WITH 句を持つこともできます。構文は以下のようになります：

複数のWITH句を持つために、WITH を複数回指定する必要はありません。代わりに、最初の WITH 句が完了した後に、コンマを追加し、次の句を指定することができます。次の句は<query_name>で始め、ASに続けます。最後の WITH 句とメインのSQLクエリの間にはコンマはありません。

例

私たちはこのテーブルを例に使用します。

Table Store_Sales

Store_Name	Sales
Los Angeles	15000
San Diego	2500
New York	3000
Boston	7000

例 1: SELECT 文で WITH を使用してください

平均よりも多くの売上を持つすべての店舗をリストアップしたいとします。それを実現するために、次の WITH ステートメントを使用できます。

の結果ができます、

WITH ステートメントは平均売上額（$6,875）を計算し、その後、主要なSQLクエリは、Sales列の値がこの平均値よりも大きいすべての行を単純に返します。

インラインビューを使用した同等の SQL は次のようになります。

同様に、サブクエリを使用した以下のSQLを使用してこれを実現できます。

サブクエリのバージョンは、Hive SQL がサブクエリをサポートする方法により、Hive SQL では動作しません。

例2： CREATE TABLEステートメントでWITHを使用する

WITH を CREATE TABLE ステートメントと一緒に使用することもできます。前の例で WITH 句を使用してテーブルを作成したいとしましょう。以下のように入力します。

クエリの WITH 部分は CREATE TABLE AS の後に配置します。WITH 句を最初に配置しないでください。以下のクエリはエラーを発生させます：