/* ** 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 l.kuerzel, SUM(4-t.platz) FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY l.kuerzel ) ; SELECT * FROM medaillen_punkte; /* ** 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 */ -- Alternativen: SELECT m1.land, m1.punkte, m1.punkte-AVG(m2.punkte) FROM medaillen_punkte m1, medaillen_punkte m2 GROUP BY m1.land, m1.punkte, m1.punkte ; SELECT m.land, m.punkte, m.punkte - mp.avgpunkte 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) FROM medaillen_punkte m ; CREATE VIEW abweichung (land, punkte, abweichung) AS SELECT m.land, m.punkte, m.punkte-(SELECT AVG(punkte) FROM medaillen_punkte) FROM medaillen_punkte m ; SELECT * FROM abweichung ORDER BY land; /* ** Schritt 5 ** ** 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 l_id) FROM e_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 l_id) FROM e_sportler s JOIN r_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 <=4 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 <=4 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 <=4 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 <=4 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 l.kuerzel, SUM(4-t.platz) FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY l.kuerzel ) AS m (land, punkte) -- mögliche Optimierung: -- land und damit enum_land und GROUP BY -- werden hier nicht benötigt ) FROM (SELECT l.kuerzel, SUM(4-t.platz) FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY l.kuerzel ) AS m (land, punkte) ) AS abweichung (land, punkte, abweichung) ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang <=4 ORDER BY rang ; -- Optimiert: 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 SUM(4-t.platz) FROM e_sportler s JOIN r_teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 ) AS m (punkte) ) FROM (SELECT l.kuerzel, SUM(4-t.platz) FROM e_sportler s JOIN enum_land l ON s.l_id = l.id JOIN r_teilnahme t ON s.id = t.s_id WHERE t.platz BETWEEN 1 AND 3 GROUP BY l.kuerzel ) AS m (land, punkte) ) AS abweichung (land, punkte, abweichung) ) AS platzierungs_raenge (land, punkte, abweichung, rang) WHERE rang <=4 ORDER BY rang ;