DROP TABLE IF EXISTS ratings;
DROP TABLE IF EXISTS dummy;

CREATE TABLE ratings
(
 id      INTEGER NOT NULL,
 name    CHAR(1) NOT NULL,
 PRIMARY KEY (id)
);

CREATE TABLE dummy
(
 id      INTEGER NOT NULL,
 PRIMARY KEY (id)
);

INSERT INTO ratings VALUES (1,  'A');
INSERT INTO ratings VALUES (5,  'B');
INSERT INTO ratings VALUES (3,  'B');
INSERT INTO ratings VALUES (4,  'D');
INSERT INTO ratings VALUES (2,  'E');

INSERT INTO dummy VALUES (1);


/****************************************************************/
/* Aufgabe: Nummerieren Sie die Tabelle Ratings gemäß den       */
/*          Platzierungen durch:                                */
/*           Rating A = 1. Platz                                */
/*           Rating B = 2. Platz                                */
/*           Rating D = 4. Platz (da es 2 zweite Plätze gibt)   */ 
/*           Rating E = 5. Platz                                */
/****************************************************************/

/****************************************************************/
/* Erste fehlerhafte Loesung:                                   */
/*   Zweimal Platz 3 anstelle von Platz 2                       */
/****************************************************************/

SELECT   r2.id, r2.name, COUNT(r1.id) AS pos
FROM     ratings r1, ratings r2
WHERE    r1.name <= r2.name
GROUP BY r2.id, r2.name
ORDER BY pos, id
;

-- So sieht die zugrundeliegende Tabelle ohne Grouping aus:
SELECT   r2.id, r2.name, r1.id as r1id
FROM     ratings r1, ratings r2
WHERE    r1.name <= r2.name
ORDER BY name, r1id
;

/****************************************************************/
/* Zweite ebenfalls fehlerhafte Loesung:                        */
/*   Platz 1 fehlt (da die zugehoerige Gruppe leer ist)         */
/****************************************************************/

SELECT   r2.id, r2.name, COUNT(r1.id) + 1 AS pos
FROM     ratings r1, ratings r2
WHERE    r1.name < r2.name
GROUP BY r2.id, r2.name
ORDER BY pos, id
;

-- So sieht die zugrundeliegende Tabelle ohne Grouping aus:
SELECT   r2.id, r2.name, r1.id as r1id
FROM     ratings r1, ratings r2
WHERE    r1.name < r2.name
ORDER BY name, r1id
;

/****************************************************************/
/* Erste korrekte Loesung:                                      */
/*   Platz 1 wird separat berechnet.                            */
/****************************************************************/


(SELECT   r2.id, r2.name, COUNT(r1.id) + 1 AS pos
 FROM     ratings r1, ratings r2
 WHERE    r1.name < r2.name
 GROUP BY r2.id, r2.name
)
UNION
(SELECT   r2.id, r2.name, 1 AS pos
 FROM     ratings r2
 WHERE    r2.name = (SELECT min(r1.name) FROM ratings r1)
)
ORDER BY pos, id
;

-- Berechnung des ersten Platzes:
SELECT   r2.id, r2.name, 1 AS pos
FROM     ratings r2
WHERE    r2.name = (SELECT min(r1.name) FROM ratings r1)
;

/****************************************************************/
/* Zweite korrekte Loesung:                                     */
/*   Es wird ein kuenstlicher erster Platz eingefuegt, der      */
/*   dann vom zweiten fehlerhaften Algorithmus ignoriert wird.  */
/****************************************************************/

SELECT   r2.id, r2.name, COUNT(r1.id) AS pos
FROM     ((SELECT id, name FROM ratings)
          union
          (SELECT 0, '@'   FROM dummy) 
         ) r1,
         ratings r2
WHERE    r1.name < r2.name
GROUP BY r2.id, r2.name
ORDER BY pos, id
;

-- So sieht die zugrundeliegende Tabelle ohne Grouping aus:
SELECT   r2.id, r2.name, r1.id as r1id 
FROM     ((SELECT id, name FROM ratings) 
          union 
          (SELECT 0, '@'   FROM dummy) 
         ) r1,
         ratings r2
WHERE    r1.name < r2.name
ORDER BY name, r1id
;

/****************************************************************/
/* Dritte korrekte Loesung:                                     */
/*   Es werden mit dem ersten fehlerhaften Algorithmus die      */
/*   die schlechtesten Werte ermittelt. Diese werden in um-     */
/*   gekehrter Reihenfolge durchnummeriert.                     */
/****************************************************************/

SELECT   r2.id, r2.name, 
         (SELECT COUNT(*)+1 FROM ratings) - COUNT(r1.id) AS pos
FROM     ratings r1, ratings r2
WHERE    r1.name >= r2.name
GROUP BY r2.id, r2.name
ORDER BY pos, id
;

-- So sieht die zugrundeliegende Tabelle ohne Grouping aus:
SELECT   r2.id, r2.name, r1.id as r1id
FROM     ratings r1, ratings r2
WHERE    r1.name >= r2.name
ORDER BY name, r1id
;

/****************************************************************/
/* Vierte korrekte Loesung:                                     */
/*   Es wird fuer jedes Element direkt die Anzahl der besseren  */
/*   Elemente ermittelt.                                        */
/****************************************************************/

SELECT  r2.id, r2.name,
        (SELECT COUNT(*)+1
         FROM   ratings r1
         WHERE  r1.name < r2.name
        ) AS pos
FROM     ratings r2
ORDER BY pos, id
;

/****************************************************************/
/* Fünfte korrekte Loesung:                                     */
/*   Verwendung von Windowing Functions (seit SQL:2003).        */
/*   Wesentlich effizienter als alle vorangegangenen Versionen: */
/*   O(n) an Stelle von O(n^2).                                 */
/****************************************************************/

-- fehlerhafte Platzierung
SELECT id, name,  COUNT (*) OVER (ORDER BY name) AS pos
FROM   ratings
ORDER BY pos, id
;

-- korrekt Platzierung
SELECT id, name,  (SELECT COUNT(*)+1 FROM ratings) 
                - COUNT (*) OVER (ORDER BY name DESC) AS pos
FROM   ratings
ORDER BY pos, id
;

SELECT id, name,  
       RANK() OVER (ORDER BY name) AS pos
FROM   ratings
ORDER BY pos, id
;

-- weitere Durchnummerierungsoperatoren
SELECT id, name,  
       RANK() OVER (ORDER BY name) AS pos,
       DENSE_RANK() OVER (ORDER BY name) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY name) AS nr
FROM   ratings
ORDER BY pos, id
;

/****************************************************************/
/* Sechste korrekte Lösung: Mit Rekursion                       */
/****************************************************************/

-- fehlerhafte Platzierung
WITH RECURSIVE standings (id, name, pos)
AS
(
  SELECT id, name, 1
  FROM   ratings
  WHERE  name = (SELECT MIN(name) FROM ratings)
  UNION
  SELECT r.id, r.name, s.pos+1
  FROM   ratings r, standings s
  WHERE  r.name = (SELECT MIN(r2.name) 
                   FROM   ratings r2
                   WHERE  r2.name > s.name
                  )
)
SELECT id, name, pos
FROM standings
ORDER BY pos, id
LIMIT 1000
;

-- korrekte Platzierung
WITH RECURSIVE standings (id, name, pos)
AS
(
  SELECT id, name, CAST (1 AS BIGINT)
  FROM   ratings
  WHERE  name = (SELECT MIN(name) FROM ratings)
  UNION
  SELECT r.id, r.name,
         s.pos + (SELECT COUNT(*) FROM ratings r2 WHERE r2.name = s.name)
  FROM   ratings r, standings s
  WHERE  r.name = (SELECT MIN(r3.name)
                   FROM   ratings r3
                   WHERE  r3.name > s.name
                  )
)
SELECT id, name, pos
FROM standings
ORDER BY pos, id
LIMIT 1000
;

/****************************************************************/
/* Siebte, unsaubere, aber in PostgreSQL funktionierende        */
/* Loesung. Diese Loesung verwendet nicht-funktionale und       */
/* nicht-standard-konforme Features:                            */
/* - einen Sequence-Generator                                   */
/* - ORDER BY ist in Subqueries nicht erlaubt. Dies ist         */
/*   hier aber notwendig, um die Elemente in korrekter          */
/*   Reihenfolge durchzumummerieren.                            */
/*                                                              */
/* Diese Loesung hat gegenueber den Loesungen 1 bis 4           */
/* allerdings den Vorteil, dass sie wesentlich effizienter      */
/* ist:                                                         */
/*   O(n) an Stelle von O(n^2).                                 */
/*                                                              */
/* VERALTET: Windowing ist genauso effizient, aber standard-    */
/*           konform.                                           */
/****************************************************************/

-- fehlerhafte Platzierung
CREATE TEMPORARY SEQUENCE seq_counter_1;

SELECT   r.id, r.name, nextval('seq_counter_1') AS pos
FROM     ratings r
ORDER BY pos, id
;

-- korrekte Platzierung
CREATE TEMPORARY SEQUENCE seq_counter_2;

SELECT r.id, r.name, tmp2.pos2 AS pos
FROM   ratings r,
       (SELECT   MIN(pos1), name1
        FROM     (SELECT   nextval('seq_counter_2') as pos , r.name
                  FROM     ratings r
                  ORDER BY name
                 ) tmp1(pos1, name1)
        GROUP BY name1
       ) tmp2(pos2, name2)
WHERE  r.name = tmp2.name2
ORDER BY pos, id
;