Simplify queries, WITH keyword

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. Zum Beispiel hier 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 WITH query_name AS (...) Schlüsselwort verwenden. Es ermöglicht uns, eine Abfrage mit einem Namen zu speichern und sie überall zu verwenden:

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 query2 AND col4 IN 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 „large devices“ identifiziert (Geräte mit width größer als 200)
Dann den durchschnittlichen Score für diese large devices berechnet. Nenne diese Spalte als average_score

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

Spickzettel

Verwenden Sie WITH query_name AS (...), um benannte Unterabfragen zu erstellen, die wiederverwendet werden können:

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 query2 AND col4 IN query1

Dieser Ansatz macht komplexe Abfragen mit mehreren Unterabfragen lesbarer und ermöglicht die Wiederverwendung derselben Unterabfrage mehrmals.

Probier es selbst