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

CREATE VIEW medaillen_punkte (land, punkte) AS
(SELECT s.land, SUM(4-t.platz)
 FROM   teilnahme t, sportler s
 WHERE  t.platz BETWEEN 1 AND 3 AND
        t.s_id=s.s_id
 GROUP BY land
)
;

SELECT * FROM medaillen_punkte;

/*
** Schritt 2 
**
** Beantworten Sie mit Hilfe dieser View folgende Frage:
** Welche durchschnittliche Punktzahl haben alle Laender zusammen erreicht?
*/ 

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

CREATE VIEW abweichung (land, punkte, abweichung) AS
(SELECT m.land, m.punkte, m.punkte - mp.avgpunkte
 FROM   medaillen_punkte m, 
        (SELECT AVG(punkte) FROM medaillen_punkte) AS mp(avgpunkte)  
)
;

SELECT * FROM abweichung;

/*
** Schritt 4 
**
** Ueberlegen Sie sich an einem einfachen Beispiel, wie
** Sie die Tupel einer Relation bzgl. der Ordnung auf einem
** Attribut (hier: Attribut "punkte" der View "abweichung")
** mit einer laufenden Nummer versehen koennen. 
**  
** Beruecksichtigen Sie Folgendes: 
**  Gleiche Punktzahl bedeutet gleiche laufende Nummer.
**  Die Platzierung der nachfolgenden Laender verschiebt sich 
**  dadurch nicht.
**  Wenn es z.B. zwei Zweit-Platzierte gibt, gibt es keinen
**  Dritt-Platzierten, sondern nur einen Viert-Platzierten.
**
** Tipp: http://mmdb.hs-augsburg.de/beispiele/postgresql/counter/counter.sql
*/ 

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 fuer Laender 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 JOIN teilnahme USING (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
;


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

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 6 
**
** Selektieren Sie die Laender auf den Plaetzen eins bis vier.
*/ 

SELECT   land, punkte, abweichung, rang 
FROM     platzierungs_raenge
WHERE    rang <=4
ORDER BY rang
;


/*
** Schritt 7 
**
** Ersetzen Sie (rekursiv) die Views in dieser Anfrage durch die
** jeweilige Definition, so dass Sie schliesslich eine Anfrage 
** erhalten, in der keine Views mehr vorkommen.
*/ 

SELECT   land, punkte, abweichung, rang 
FROM     platzierungs_raenge
WHERE    rang <=4
ORDER BY rang
;


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
;


SELECT   land, punkte, abweichung, rang
FROM     (SELECT land, punkte, abweichung,
                 RANK() OVER (ORDER BY punkte DESC)
          FROM  (SELECT m.land, m.punkte, m.punkte - mp.avgpunkte
                  FROM   medaillen_punkte m,
                         (SELECT AVG(punkte) FROM medaillen_punkte) AS mp(avgpunkte)
                 ) AS abweichung (land, punkte, abweichung)
         )
         AS platzierungs_raenge (land, punkte, abweichung, rang)
WHERE    rang <=4
ORDER BY rang
;


SELECT   land, punkte, abweichung, rang
FROM     (SELECT land, punkte, abweichung,
                 RANK() OVER (ORDER BY punkte DESC)
          FROM  (SELECT m.land, m.punkte, m.punkte - mp.avgpunkte
                  FROM   (SELECT s.land, SUM(4-t.platz)
                          FROM   teilnahme t, sportler s
                          WHERE  t.platz BETWEEN 1 AND 3 AND
                                 t.s_id=s.s_id
                          GROUP BY land
                         ) m (land, punkte),
                         (SELECT AVG(punkte) 
                          FROM   (SELECT s.land, SUM(4-t.platz)
                                  FROM   teilnahme t, sportler s
                                  WHERE  t.platz BETWEEN 1 AND 3 AND
                                         t.s_id=s.s_id
                                  GROUP BY land
                                 ) m2 (land, punkte)
                         ) AS mp(avgpunkte)
                 ) AS abweichung (land, punkte, abweichung)
         )
         AS platzierungs_raenge (land, punkte, abweichung, rang)
WHERE    rang <=4
ORDER BY rang
;
