Simplify queries, WITH keyword

CoddyのSQLジャーニー「基礎」セクションの一部。レッスン 49/72。

クエリは多くの内部クエリを追加することによって複雑になりすぎる可能性があります。例えば、以下は多くのサブクエリを持つクエリの例です：

SELECT * FROM table1
WHERE col2 IN (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (
		SELECT col5 FROM table3
		WHERE col1 AND col3 OR col2
	)
)

作業を簡単にするために、WITH query_name AS (...) キーワードを使用できます。これにより、クエリに名前を付けて保存し、必要な場所でそれを使用できるようになります。

WITH query1 AS (
	SELECT col5 FROM table3
	WHERE col1 AND col3 OR col2
), query2 AS (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN query1
)
SELECT * FROM table1
WHERE col2 IN (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

ここでは、query1 を query2 内およびメインのクエリ内で再利用しました。

チャレンジ

簡単

利用可能なテーブルとカラム:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

以下の処理を行うクエリを記述してください:

最初に「大型デバイス」（width が 200 より大きいデバイス）を特定します
次に、これらの大型デバイスの平均スコアを計算します。このカラム名は average_score としてください

この問題を解決するには WITH 句を使用してください。

自分で試してみよう

-- ステップ1: 関心のあるデバイスのみを保持する一時結果セットに名前を付ける
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- ステップ2: メインクエリ内でその一時結果セットを再利用する
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);

腕試し

このレッスンには短いクイズがあります。レッスンを始めて解答し、進捗を記録しましょう。

基礎のすべてのレッスン

3Specific Return Format

Null values Sort Results Part 1 Sort Results Part 2 Recap - Cyber Security Firm Limit number of records Recap - Vehicle Factory

6Intro Challenges

Recap - Parliamentary Election Recap - Police Criminal Arrest Recap - Bar Beverage Container Recap - Engineer new columns

9Multiple tables

Basic Join Part 1 Basic Join Part 2 Recap - Join Self join Recap - Self Join UnionSimplify queries, WITH keywordRecap - With Queries Recap - Real Estate Contractor

12Window Functions part 2

RANK & DENSE_RANK Functions Recap - RANK & DENSE_RANK NTILE Function Aggregation Functions ROWS & RANGE Criterion

自分で練習してみよう: SQLプレイグラウンド