/* ** Olympiade (Teil 4) ** ** Medaillenspiegel */ DROP VIEW IF EXISTS platzierungs_raenge; DROP VIEW IF EXISTS abweichung; DROP VIEW IF EXISTS medaillen_punkte; /* ** Schritt 1 ** ** Erstellen Sie eine View ** medaillen_punkte(land, punkte), ** in der die Punkte der einzelnen Laender berechnet werden. SELECT * FROM medaillen_punkte ORDER BY punkte DESC; land punkte ------------ GER 40 ITA 5 BLR 3 GRE 3 RUS 3 FRA 2 */ CREATE VIEW medaillen_punkte (land, punkte) AS (SELECT s.land, SUM(4-t.platz) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY s.land ) ; SELECT * FROM medaillen_punkte ORDER BY punkte DESC; /* ** Schritt 2 ** ** Beantworten Sie mit Hilfe dieser View folgende Frage: ** Welche durchschnittliche Punktzahl haben alle Laender zusammen erreicht? avg ------------------ 9.3333333333333333 */ SELECT AVG(punkte) FROM medaillen_punkte ; /* ** Schritt 3 ** ** Definieren Sie eine View ** abweichung(land, punkte, abweichung) ** basierend auf der View medaillen_punkte, mit der Sie die ** Abweichungen der Punkte eines Landes vom Durchschnitt ermitteln koennen. SELECT * FROM abweichung ORDER BY land; land punkte abweichung --------------------------------- BLR 3 -6.3333333333333333 FRA 2 -7.3333333333333333 GER 40 30.6666666666666667 GRE 3 -6.3333333333333333 ITA 5 -4.3333333333333333 RUS 3 -6.3333333333333333 */ CREATE VIEW abweichung (land, punkte, abweichung) AS SELECT m.land, m.punkte, m.punkte-(SELECT AVG(punkte) FROM medaillen_punkte) AS abweichung FROM medaillen_punkte m ; SELECT * FROM abweichung ORDER BY land; -- Alternativen: SELECT m.land, m.punkte, m.punkte - mp.avgpunkte AS abweichung FROM medaillen_punkte m, (SELECT AVG(punkte) FROM medaillen_punkte) AS mp(avgpunkte) ; SELECT m.land, m.punkte, m.punkte-(SELECT AVG(punkte) FROM medaillen_punkte) AS abweichung FROM medaillen_punkte m ; -- Karl Diedrichsen -- Eine sehr schöne Lösung, da nur einmal auf Medaillenpunkte zugegriffen wird. -- Damit vereinfacht sich der Code in Schritt 6 noch einmal signifikant. SELECT land, punkte, punkte - AVG(punkte) OVER () AS abweichung FROM medaillen_punkte ; -- Umständlich und teuer SELECT m1.land, m1.punkte, m1.punkte-AVG(m2.punkte) AS abweichung FROM medaillen_punkte m1, medaillen_punkte m2 GROUP BY m1.land, m1.punkte, m1.punkte ; /* ** Schritt 4 ** ** Erstellen Sie eine View ** platzierungs_raenge (land, punkte, abweichung, rang) ** mit laufender Rangnummer, d.h. rang=1 fuer das Land mit ** den meisten Medaillen-Punkten, rang=2 fuer das Land mit den ** zweitmeisten Medaillen-Punkten etc. SELECT * FROM platzierungs_raenge ORDER BY rang; land punkte abweichung rang --------------------------------------- GER 40 30.6666666666666667 1 ITA 5 -4.3333333333333333 2 BLR 3 -6.3333333333333333 3 GRE 3 -6.3333333333333333 3 RUS 3 -6.3333333333333333 3 FRA 2 -7.3333333333333333 6 ** Tipp: Windowing ** Siehe auch: ** https://kowa.hs-augsburg.de/beispiele/db/postgresql/counter/counter.sql */ -- Vorüberlegungen SELECT land, punkte, abweichung, RANK() OVER (ORDER BY punkte DESC) AS rang FROM abweichung ORDER BY punkte DESC ; /****************/ /* Alternativen */ /****************/ SELECT a1.land, a1.punkte, a1.abweichung, (SELECT COUNT(*)+1 FROM abweichung a2 WHERE a1.punkte < a2.punkte ) AS rang FROM abweichung a1 ORDER BY punkte DESC ; (SELECT a1.land, a1.punkte, a1.abweichung, 1 AS rang FROM abweichung a1 WHERE a1.punkte = (SELECT max(a2.punkte) FROM abweichung a2) ) UNION (SELECT a1.land, a1.punkte, a1.abweichung, COUNT(*)+1 AS rang FROM abweichung a1, abweichung a2 WHERE a1.punkte < a2.punkte group by a1.land, a1.punkte, a1.abweichung ) ORDER BY punkte DESC ; /* funktioniert, wenn medaillenpunkte für Länder ohne Medaillen den Wert 0 ausgibt */ SELECT a1.land, a1.punkte, a1.abweichung, (SELECT COUNT(distinct land) FROM sportler) + 1 - COUNT(*) AS rang FROM abweichung a1, abweichung a2 WHERE a1.punkte >= a2.punkte GROUP BY a1.land, a1.punkte, a1.abweichung ORDER BY punkte DESC ; /* funktioniert immer */ SELECT a1.land, a1.punkte, a1.abweichung, (SELECT COUNT(distinct land) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE platz BETWEEN 1 AND 3) - COUNT(*) + 1 AS rang FROM abweichung a1, abweichung a2 WHERE a1.punkte >= a2.punkte GROUP BY a1.land, a1.punkte, a1.abweichung ORDER BY punkte DESC ; -- View CREATE VIEW platzierungs_raenge (land, punkte, abweichung, rang) AS SELECT land, punkte, abweichung, RANK() OVER (ORDER BY punkte DESC) FROM abweichung ORDER BY punkte DESC ; SELECT * FROM platzierungs_raenge ; /* ** Schritt 5 ** ** Selektieren Sie die Laender auf den Plaetzen eins bis vier. land punkte abweichung rang --------------------------------------- GER 40 30.6666666666666667 1 ITA 5 -4.3333333333333333 2 BLR 3 -6.3333333333333333 3 GRE 3 -6.3333333333333333 3 RUS 3 -6.3333333333333333 3 */ SELECT land, punkte, abweichung, rang FROM platzierungs_raenge WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; /* ** Schritt 6 ** ** Ersetzen Sie (händisch rekursiv) die Views in dieser Anfrage ** durch die jeweiligen Definitionen, so dass Sie schliesslich ** eine Anfrage erhalten, in der keine Views mehr vorkommen. land punkte abweichung rang --------------------------------------- GER 40 30.6666666666666667 1 ITA 5 -4.3333333333333333 2 BLR 3 -6.3333333333333333 3 GRE 3 -6.3333333333333333 3 RUS 3 -6.3333333333333333 3 */ SELECT land, punkte, abweichung, rang FROM platzierungs_raenge WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "platzierungs_raenge" wird ersetzt. SELECT land, punkte, abweichung, rang FROM (SELECT land, punkte, abweichung, RANK() OVER (ORDER BY punkte DESC) FROM abweichung ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "abweichung" wird ersetzt. SELECT land, punkte, abweichung, rang FROM (SELECT land, punkte, abweichung, RANK() OVER (ORDER BY punkte DESC) FROM (SELECT m.land, m.punkte, m.punkte - (SELECT AVG(punkte) FROM medaillen_punkte ) FROM medaillen_punkte m ) AS abweichung (land, punkte, abweichung) ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "medaillen_punkte" wird zweimal ersetzt. SELECT land, punkte, abweichung, rang FROM (SELECT land, punkte, abweichung, RANK() OVER (ORDER BY punkte DESC) FROM (SELECT m.land, m.punkte, m.punkte - (SELECT AVG(punkte) FROM (SELECT s.land, SUM(4-t.platz) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY land ) AS m (land, punkte) ) FROM (SELECT s.land, SUM(4-t.platz) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY land ) AS m (land, punkte) ) AS abweichung (land, punkte, abweichung) ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- -- Schritt 6 mit der Berechnung der Abweichung -- gemäß Karl Diedrichsen -- SELECT land, punkte, abweichung, rang FROM platzierungs_raenge WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "platzierungs_raenge" wird ersetzt. SELECT land, punkte, abweichung, rang FROM (SELECT land, punkte, abweichung, RANK() OVER (ORDER BY punkte DESC) FROM abweichung ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "abweichung" wird ersetzt. SELECT land, punkte, abweichung, rang FROM (SELECT land, punkte, abweichung, RANK() OVER (ORDER BY punkte DESC) FROM (SELECT land, punkte, punkte - AVG(punkte) OVER () AS abweichung FROM medaillen_punkte ) AS abweichung (land, punkte, abweichung) ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "medaillen_punkte" muss nur EINMAL ersetzt werden. SELECT land, punkte, abweichung, rang FROM (SELECT land, punkte, abweichung, RANK() OVER (ORDER BY punkte DESC) FROM (SELECT land, punkte, punkte - AVG(punkte) OVER () AS abweichung FROM (SELECT s.land, SUM(4-t.platz) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY land ) AS m (land, punkte) ) AS abweichung (land, punkte, abweichung) ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- -- Schritt 6 ohne Verwendung von Windowing -- Diese Version ist wesentlich aufwändiger! -- SELECT land, punkte, abweichung, rang FROM platzierungs_raenge WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "platzierungs_raenge" wird ersetzt. SELECT land, punkte, abweichung, rang FROM (SELECT a1.land, a1.punkte, a1.abweichung, (SELECT COUNT(*)+1 FROM abweichung a2 WHERE a1.punkte < a2.punkte ) AS rang FROM abweichung a1 ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "abweichung" wird ersetzt. SELECT land, punkte, abweichung, rang FROM (SELECT a1.land, a1.punkte, a1.abweichung, (SELECT COUNT(*)+1 FROM (SELECT m.land, m.punkte, m.punkte - mp.avgpunkte FROM medaillen_punkte m, (SELECT AVG(punkte) FROM medaillen_punkte ) AS mp(avgpunkte) ) AS a2 (land, punkte, abweichung) WHERE a1.punkte < a2.punkte ) AS rang FROM (SELECT m.land, m.punkte, m.punkte - mp.avgpunkte FROM medaillen_punkte m, (SELECT AVG(punkte) FROM medaillen_punkte ) AS mp(avgpunkte) ) AS a1 (land, punkte, abweichung) ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ; -- Die View "medaillen_punkte" muss zweimal ersetzt werden. SELECT land, punkte, abweichung, rang FROM (SELECT a1.land, a1.punkte, a1.abweichung, (SELECT COUNT(*)+1 FROM (SELECT m.land, m.punkte, m.punkte - mp.avgpunkte FROM (SELECT s.land, SUM(4-t.platz) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY s.land ) AS m (land, punkte), (SELECT AVG(punkte) FROM (SELECT s.land, SUM(4-t.platz) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY s.land ) AS m (land, punkte) ) AS mp(avgpunkte) ) AS a2 (land, punkte, abweichung) WHERE a1.punkte < a2.punkte ) AS rang FROM (SELECT m.land, m.punkte, m.punkte - mp.avgpunkte FROM (SELECT s.land, SUM(4-t.platz) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY s.land ) AS m (land, punkte) , (SELECT AVG(punkte) FROM (SELECT s.land, SUM(4-t.platz) FROM sportler s JOIN teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY s.land ) AS m (land, punkte) ) AS mp(avgpunkte) ) AS a1 (land, punkte, abweichung) ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang BETWEEN 1 AND 3 ORDER BY rang ;