Abfragen vereinfachen, WITH-Schlüsselwort

Teil des Abschnitts Grundlagen der SQL-Journey von Coddy. Lektion 49 von 72.

Abfragen können durch das Hinzufügen vieler innerer Abfragen zu unübersichtlich werden. Hier ist zum Beispiel eine Abfrage mit vielen Unterabfragen:

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
	)
)

Um es einfacher zu machen, können wir das Schlüsselwort WITH query_name AS (...) verwenden. Damit können wir eine Abfrage unter einem Namen speichern und sie überall dort verwenden, wo wir möchten:

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)

Hier haben wir query1 in query2 und in der Hauptabfrage wiederverwendet.

Aufgabe

Einfach

Verfügbare Tabellen und Spalten:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

Schreibe eine Abfrage, die:

Zuerst „große Geräte“ identifiziert (Geräte mit einer width größer als 200)
Anschließend den durchschnittlichen Score für diese großen Geräte berechnet. Nenne diese Spalte average_score

Verwende die WITH-Klausel, um dieses Problem zu lösen.

Probier es selbst

-- Schritt 1: benenne eine temporäre Ergebnismenge, die nur die Geräte behält, die dich interessieren
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- Schritt 2: verwende diese temporäre Ergebnismenge in der Hauptabfrage erneut
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);