/*
** 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
;