SQL-Beispiele aus dem Miro-Board (04 SQL-Vertiefung, Fortsetzung)
Join: Vertiefung
-- Beispiel: Welche Waren liefert Maier aus Königsbrunn?
SELECT DISTINCT
--h.h_id, h_name, h_ortschaft,
w.w_id, w_typ, w_bezeichnung
FROM haendler h
JOIN liefert l ON h.h_id = l.h_id
JOIN ware w ON l.w_id = w.w_id
WHERE h.h_name = 'Maier'
AND h.h_ortschaft = 'Königsbrunn'
;
Komplexer Mehrfachjoin
-- Beispiel: Welche Waren liefert Maier aus Königsbrunn?
SELECT DISTINCT
--h.h_id, h_name, h_ortschaft,
w.w_id, w_typ, w_bezeichnung
FROM haendler h
JOIN liefert l ON h.h_id = l.h_id
JOIN ware w ON l.w_id = w.w_id
WHERE h.h_name = 'Maier'
AND h.h_ortschaft = 'Königsbrunn'
;
-- Wer liefert welche Waren billiger als Maier aus Königsbrunn?
-- Die Antwort auf diese Frage wird schrittweise erarbeitet.
-- Welche Waren liefert Maier aus Königsbrunn zu welchem Preis?
SELECT h.h_id, h_name, h_ortschaft, l.l_preis,
w.w_id, w_typ, w_bezeichnung
FROM haendler h
JOIN liefert l ON h.h_id = l.h_id
JOIN ware w ON l.w_id = w.w_id
WHERE h.h_name = 'Maier'
AND h.h_ortschaft = 'Königsbrunn'
;
-- Welche Waren liefert die Anderen zu welchem Preis?
SELECT
h.h_id, h_name, h_ortschaft, l.l_preis,
w.w_id, w_typ, w_bezeichnung
FROM haendler h
JOIN liefert l ON h.h_id = l.h_id
JOIN ware w ON l.w_id = w.w_id
WHERE NOT ( h.h_name = 'Maier'
AND h.h_ortschaft = 'Königsbrunn'
)
;
-- Die Anderen liefern dieselben Ware wie M/K billiger (zu einem günstigeren Preis).
-- Wer liefert welche Waren billiger als Maier aus Königsbrunn?
SELECT ha.h_id, ha.h_name, ha.h_ortschaft,
la.l_preis AS preis_anderer,
lm.l_preis AS preis,
wm.w_id, wm.w_typ, wm.w_bezeichnung,
wa.w_id, wa.w_typ, wa.w_bezeichnung
FROM haendler hm
JOIN liefert lm ON hm.h_id = lm.h_id
JOIN ware wm ON lm.w_id = wm.w_id
, -- SQL-89-Join über beide großen Tabellen
haendler ha
JOIN liefert la ON ha.h_id = la.h_id
JOIN ware wa ON la.w_id = wa.w_id
WHERE hm.h_name = 'Maier'
AND hm.h_ortschaft = 'Königsbrunn'
AND NOT ( ha.h_name = 'Maier'
AND ha.h_ortschaft = 'Königsbrunn'
)
AND wm.w_id = wa.w_id -- dieselbe Ware
AND la.l_preis <= lm.l_preis; -- billiger als Maier aus Königsbrunn
-- < -- falls "echt billliger" gemeint ist
;
Komplexer Mehrfachjoin: Optimierungen
-- Wer liefert welche Waren billiger als Maier aus Königsbrunn?
-- Optimale Lösung: Ursprüngliche Lösung mit den hier verwendeten Vereinfachungen.
-- Die Tabelle "ware" wird nur einmal verwendet,
-- weil beide dieselben Waren liefern.
SELECT ha.h_id, ha.h_name, ha.h_ortschaft,
la.l_preis AS preis_anderer,
lm.l_preis AS preis,
wm.w_id, wm.w_typ, wm.w_bezeichnung --,
--wa.w_id, wa.w_typ, wa.w_bezeichnung -- Unnötige doppelte Ausgabe der Warenattributwerte
FROM haendler hm
JOIN liefert lm ON hm.h_id = lm.h_id
JOIN ware wm ON lm.w_id = wm.w_id
, -- SQ-89-Join über beide großen Tabellen
haendler ha
JOIN liefert la ON ha.h_id = la.h_id
--JOIN ware wa ON la.w_id = wa.w_id -- Die Warenattributewerte sind auch in wa enthalten,
-- da wa.w_id = wm.w_wid
WHERE hm.h_name = 'Maier'
AND hm.h_ortschaft = 'Königsbrunn'
AND NOT ( ha.h_name = 'Maier'
AND ha.h_ortschaft = 'Königsbrunn'
)
AND lm.w_id = la.w_id -- Dieselbe Ware, wa.w_id existiert nicht mehr.
-- Es gilt aber wm.w_id = lm.w_id = la.w_id = wa.w_id.
-- Es kann also für den Join über die beiden
-- großen Tabellen wa.w_id durch la.w_id ersetzt werden.
AND la.l_preis <= lm.l_preis; -- < ist "echt billiger"
;
SELECT ha.h_id, ha.h_name, ha.h_ortschaft,
la.l_preis AS preis_anderer,
lm.l_preis AS preis,
wm.w_id, wm.w_typ, wm.w_bezeichnung
FROM haendler hm
JOIN liefert lm ON hm.h_id = lm.h_id
JOIN ware wm ON lm.w_id = wm.w_id
, -- SQ-89-Join über beide großen Tabellen
haendler ha
JOIN liefert la ON ha.h_id = la.h_id
WHERE hm.h_name = 'Maier'
AND hm.h_ortschaft = 'Königsbrunn'
AND lm.h_id <> la.h_id -- Maier ist verschieden von den Anderen
AND lm.w_id = la.w_id -- dieselbe Ware
AND la.l_preis <= lm.l_preis; -- < ist "echt billiger"
;
Großeltern
DROP TABLE IF EXISTS eltern CASCADE;
CREATE TABLE eltern
( kind VARCHAR NOT NULL,
elter VARCHAR NOT NULL,
PRIMARY KEY (kind, elter)
);
DELETE FROM eltern;
INSERT INTO eltern(kind, elter)
VALUES
('Ja', 'Si'), ('Ol', 'Si'), ('Li', 'So'), ('La', 'So'),
('Si', 'Ma'), ('So', 'Ma'), ('Ve', 'Ma'),
('Si', 'Wo'), ('So', 'Wo'), ('Ve', 'Wo'),
('Ma', 'Fr'), ('Ma', 'JaS'),
('Fr', 'Th'),
('Wo','Il'), ('Wo','Ka'),
('Il','Ro'), ('Ka', 'Cä')
;
DELETE FROM eltern;
INSERT INTO eltern(kind, elter)
VALUES
('Ol', 'Si'), ('Li', 'So'),
('Si', 'Ma'), ('So', 'Ma'),
('Ma', 'Fr'),
('Fr', 'Th')
;
-- Gibt zu jeder Person den Großelter aus.
-- Großeltern = Eltern der Eltern
SELECT e1.kind AS person, e2.elter AS großelter
FROM eltern e1 JOIN eltern e2 ON e1.elter = e2.kind
;
SELECT e1.kind AS person, e1.elter AS elter,
e2.kind AS elter_als_kind, e2.elter AS großelter
FROM eltern e1 JOIN eltern e2 ON e1.elter = e2.kind
;
Urgroßeltern
-- Urgroßeltern = Eltern der Großeltern
-- = Eltern der Eltern der Eltern
SELECT e1.kind AS person, e3.elter AS urgrosselter
FROM eltern e1
JOIN eltern e2 ON e1.elter = e2.kind
JOIN eltern e3 ON e2.elter = e3.kind
;
Ahnen
SELECT e1.kind AS person, e1.elter AS vorfahre, 1 AS generation
FROM eltern e1
UNION
SELECT e1.kind AS person, e2.elter AS vorfahre, 2 AS generation
FROM eltern e1
JOIN eltern e2 ON e1.elter = e2.kind
UNION
SELECT e1.kind AS person, e3.elter AS vorfahre, 3 AS generation
FROM eltern e1
JOIN eltern e2 ON e1.elter = e2.kind
JOIN eltern e3 ON e2.elter = e3.kind
UNION
SELECT e1.kind AS person, e4.elter AS vorfahre, 4 AS generation
FROM eltern e1
JOIN eltern e2 ON e1.elter = e2.kind
JOIN eltern e3 ON e2.elter = e3.kind
JOIN eltern e4 ON e3.elter = e4.kind
ORDER BY person, generation
;
Rekursive Views (Vorfahren)
CREATE RECURSIVE VIEW vorfahren(person, vorfahre, generation)
AS
SELECT e.kind, e.elter, 1
FROM eltern e
UNION
SELECT e.kind, v.vorfahre, v.generation+1
FROM eltern e JOIN vorfahren v ON e.elter = v.person
;
SELECT person, vorfahre, generation FROM vorfahren
ORDER BY person, generation
FETCH FIRST 1000 ROWS ONLY
;
Aggregation
SELECT COUNT(*) AS anzahl
FROM ware
;
SELECT MIN(l_preis) AS min_preis,
MAX(l_preis) AS max_preis,
SUM(l_preis) AS sum_preis,
AVG(l_preis) AS avg_preis,
ROUND(AVG(l_preis), 0) AS round_avg_preis,
AVG(ROUND(l_preis, 0)) AS avg_round_preis,
COUNT(*) AS count_star,
COUNT(l_lieferzeit) AS count_lieferzeit,
COUNT(DISTINCT l_lieferzeit) AS count_distinct_lieferzeit,
COUNT(*)-COUNT(l_lieferzeit) AS lieferzeiten_gleich_null
FROM liefert
;
-- Wie viel unterschiedliche Waren sind lieferbar?
SELECT COUNT(*)
FROM (SELECT DISTINCT w_id FROM liefert) AS dummy
;
SELECT COUNT(DISTINCT w_id)
FROM liefert
;
Gruppierung der Tabelle „liefert”
SELECT ROUND(AVG(l_preis),2) AS avg, MIN(l_preis), MAX(l_preis)
FROM liefert l
WHERE l.w_id = 1
;
SELECT ROUND(AVG(l_preis),2) AS avg, MIN(l_preis), MAX(l_preis)
FROM liefert l
WHERE l.w_id = 2
;
SELECT ROUND(AVG(l_preis),2) AS avg, MIN(l_preis), MAX(l_preis)
FROM liefert l
WHERE l.w_id = 12
;
SELECT ROUND(AVG(l_preis),2) AS avg, MIN(l_preis), MAX(l_preis),
w_id
FROM liefert
GROUP BY w_id
;
SELECT ROUND(AVG(l_preis),2) AS avg, MIN(l_preis), MAX(l_preis),
w_typ, w_bezeichnung
FROM liefert l JOIN ware w ON l.w_id = w.w_id
GROUP BY w_typ, w_bezeichnung
;
-- SQL 3: w_id -> w_typ, w_bezeichnung
-- Wenn w_id eindeutig, dann sind auch w_typ und w_bezeichnung eindeutig
-- GROUP BY w.w_id -- funktioniert auch
-- ;
SELECT ROUND(AVG(l_preis),2) AS avg, MIN(l_preis), MAX(l_preis),
w_typ, w_bezeichnung
FROM liefert l JOIN ware w ON l.w_id = w.w_id
GROUP BY w_typ, w_bezeichnung
;
SELECT l_preis, l_lieferzeit, w_typ, w_bezeichnung
FROM liefert l JOIN ware w ON l.w_id = w.w_id
ORDER BY w_typ, w_bezeichnung
;
Beispiel
SELECT ROUND(AVG(l_preis),2) AS durchschnittspreis,
ROUND(AVG(l_lieferzeit),1) AS durchschnittslieferzeit,
w_typ, w_bezeichnung
FROM liefert l JOIN ware w ON l.w_id = w.w_id
GROUP BY w_typ, w_bezeichnung
ORDER BY w_typ, w_bezeichnung
;
Array-Agg und JSON-Agg
SELECT DISTINCT
h_name, h_ortschaft, w_typ, w_bezeichnung
FROM haendler h JOIN liefert l ON h.h_id = l.h_id
JOIN ware w ON l.w_id = w.w_id
ORDER BY h_name, h_ortschaft
;
SELECT h_name, h_ortschaft,
ARRAY_AGG(DISTINCT w_typ) AS typen,
ARRAY_AGG(DISTINCT w_bezeichnung) AS bezeichnungen
FROM haendler h JOIN liefert l ON h.h_id = l.h_id
JOIN ware w ON l.w_id = w.w_id
GROUP BY h_name, h_ortschaft
ORDER BY h_name, h_ortschaft
;
SELECT h_name, h_ortschaft,
JSON_AGG(DISTINCT w_typ) AS typen,
JSON_OBJECT_AGG(DISTINCT w_bezeichnung, w_typ) AS waren
FROM haendler h JOIN liefert l ON h.h_id = l.h_id
JOIN ware w ON l.w_id = w.w_id
GROUP BY h_name, h_ortschaft
ORDER BY h_name, h_ortschaft
;
Having-Klausel
/* Durchschnittspreis aller Waren mit einer w_id kleiner < 100,
die billiger als 1000 Euro sind und für die mindestens zwei
verschiedene Preise (< 1000 Euro) existieren.
Achtung:
l_preis < 1000 darf nur in der Where-Klausel stehen
count (DISTINCT l_preis) >= 2 darf nur in der
Having-Klausel stehen.
w_id < 100 darf in beiden Klauseln stehen
(es reicht, sie in eine zu schreiben)
*/
SELECT w_id, ROUND(AVG(l_preis),2) AS durchschnittspreis
FROM liefert
WHERE l_preis < 1000 AND w_id < 100
GROUP BY w_id
HAVING COUNT(DISTINCT l_preis) >= 2 AND w_id < 100
;
Windowing
SELECT w_typ, w_bezeichnung,
COUNT(*) OVER (ORDER BY w_typ, w_bezeichnung) AS nr,
ROW_NUMBER(*) OVER () as nr2,
COUNT(*) OVER () AS anzahl,
COUNT(*) OVER (PARTITION BY w_typ) AS nr_typ,
COUNT(*) OVER (PARTITION BY w_typ ORDER BY w_typ,
w_bezeichnung
) AS nr2_typ,
ROW_NUMBER(*) OVER (PARTITION BY w_typ) AS nr3_typ,
COUNT(*) OVER (ORDER BY w_typ) AS rank_false,
RANK(*) OVER (ORDER BY w_typ) AS rank,
DENSE_RANK(*) OVER (ORDER BY w_typ) AS dense_rank,
MIN(w_bezeichnung) OVER (PARTITION BY w_typ) AS min,
MAX(w_bezeichnung) OVER (PARTITION BY w_typ) AS max
FROM ware
ORDER BY w_typ
;
SELECT l_preis, l_lieferzeit, w_id,
ROW_NUMBER(*) OVER (PARTITION BY w_id) AS row_nr,
ROUND(AVG(l_preis) OVER (PARTITION BY w_id),2) AS avg_preis,
ROUND(AVG(l_preis) OVER (PARTITION BY w_id
ORDER BY l_lieferzeit
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
, 2
) AS gleitender_durchschnitt
FROM liefert
ORDER BY w_id
;
