モジュール 7 · レッスン 1 / 4コース内の 25/57~12 min

モジュールのレッスン (1/4)

CTE: WITH を使用した複数ステップのクエリ

実際のクエリは増大する傾向があります: 3 つの JOIN、1 つの GROUP BY、サブクエリ、別のサブクエリ…すると、それらは読めなくなります。 CTE (「共通テーブル」式」、WITH 句) に 名前を付けることができます。中間ステップで、メインクエリのテーブルであるかのように使用します。

基本的な構文

SQL

WITH nome_cte AS (
  SELECT …    -- query interna
)
SELECT …      -- query principale, usa nome_cte come fosse una tabella
FROM   nome_cte;

CTE は それに続くクエリに対してのみ有効です: CTE は存続しません。処刑。

例

最も多くの費用を支払った 3 人の顧客 (配送された注文の合計) を見つけます。

SQL

WITH total_spent AS (
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM   orders      o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY o.customer_id
)
SELECT c.email,
       s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT  3;

英語で読む: 「最初に顧客ごとの支出を計算し、次に結合します。」顧客を獲得し、上位 3" を取得します。 CTE がなければインラインが必要になります FROM のサブクエリ (正当ですが、ノイズが多くなります)。

カスケード接続された複数の CTE

カンマで区切って複数の CTE を定義でき、それぞれが参照できます。以前のものへ:

SQL

WITH total_spent AS (
  SELECT customer_id, SUM(quantity * unit_price) AS total
  FROM   order_items oi
  JOIN   orders      o ON oi.order_id = o.id
  WHERE  o.status = 'delivered'
  GROUP BY customer_id
),
average AS (
  SELECT AVG(total) AS total_average
  FROM   total_spent
)
SELECT c.email, s.total
FROM   total_spent s
JOIN   customers c ON c.id = s.customer_id
CROSS JOIN average
WHERE  s.total > average.total_average
ORDER BY s.total DESC;

CROSS JOIN average は、average には常に 単一行 があるためです。デカルト積は、結果のすべての行に total_average を追加するだけです。

CTE とサブクエリ: いつどちらを使用するか

CTE は次の場合に輝きます。

同じサブクエリがメインクエリで 2 回以上使用されています。
構造を名前付きステップで明示的にしたい場合(自己文書化)。
これらは 再帰的 CTE の 前提条件です (WITH RECURSIVE, このモジュールの次のレッスン)。

1 回だけ使用されるステップの場合、インラインサブクエリはより簡潔です。

試してみてください

運動#sql.m7.l1.e1

試行回数: 0読み込み中…

「total_spent」という名前の CTE を使用して、顧客ごとに、配送された注文の合計支出 (SUM(数量 * 単位価格)) を計算します。次に、メインクエリで、上位 3 人の支出者の電子メールと合計を表示します。合計を降順で並べます。3 個までです。

エディターを読み込み中…

ヒントを表示

CTE 合計数量 * 単位価格内。メインクエリでは、ID で顧客を結合します。

3 回の試行後に解決策が利用可能になります

復習演習

運動#sql.m7.l1.e2