PostgreSQL: Wie findet man Id's mit maximalem Geld in einer wöchentlichen Tabelle

Question

Ich habe die folgende Tabelle mit virtuellem Geld für wöchentliche Turniere:

# select * from pref_money limit 5;
       id       | money |   yw
----------------+-------+---------
 OK32378280203  |   -27 | 2010-44
 OK274037315447 |   -56 | 2010-44
 OK19644992852  |     8 | 2010-44
 OK21807961329  |   114 | 2010-44
 FB1845091917   |   774 | 2010-44
(5 rows)

In meinem PHP-Skript, das Benutzer auflistet, muss ich wissen, wer ein wöchentlicher Gewinner ist, damit ich Medaillen neben ihren Namen anzeigen kann. Also versuche ich diese SQL-Anweisung:

# find the weekly winners
$sth = $db->prepare('select id from pref_money 
    where money in 
    (select max(money) from pref_money group by yw)');

$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
        @$medals[$row['id']]++;

Das funktioniert meistens, aber manchmal bekomme ich Falschmeldungen wenn ein Nutzer in einer Woche zufällig den gleichen Geldbetrag hat (aber nicht den Höchstbetrag) wie der Gewinner in einer anderen Woche.

Hat jemand eine Idee, wie man die SQL-Anweisung so ändern kann, dass wirklich nur Gewinner-IDs ausgewählt werden?

Wenn ich das Folgende versuche, erhalte ich eine Fehlermeldung:

# select id, max(money) from pref_money group by yw;
ERROR:  column "pref_money.id" must appear in the 
  GROUP BY clause or be used in an aggregate function

Ich danke Ihnen! Alex

UPDATE: PostgreSQL 8.4.5 / CentOS und es kann Krawatten geben :-)

Answer 1

Welche Version von PostgreSQL? Mit den in 8.4 eingeführten Windowing-Funktionen ist dies einfach:

select id, yw from (
    select id,
           yw,
           row_number() over(partition by yw order by money desc) as ranking
    from pref_money
) x
where x.ranking = 1

In älteren Versionen reicht das gerade so aus:

select id, yw
from pref_money
     join (select yw, max(money) max_money
           from pref_money
           group by yw) max_money
          on pref_money.yw = max_money.yw and pref_money.money = max_money.max_money

Beachten Sie jedoch, dass Wochen, in denen zwei Nutzer den gleichen Betrag erhalten, in der Ausgabe doppelt angezeigt werden. Wenn Sie das wirklich wollen, können Sie das auch mit den Fensterfunktionen erreichen, indem Sie rank() anstelle von row_number()

Answer 2

Unter der Annahme, dass PostgreSQL 8.4+, und niemand für die Gewinnerposition in einer bestimmten Woche - dies wird die wöchentlichen Gewinner auflisten:

WITH sample AS (
  SELECT t.id,
         t.yw,
         ROW_NUMBER() OVER(PARTITION BY t.yw
                               ORDER BY t.money DESC) AS rank
    FROM PREF_MONEY t)
SELECT s.id
  FROM sample s
 WHERE s.rank = 1

Wenn es eine Verbindung geben kann, verwenden Sie diese:

WITH sample AS (
  SELECT t.id,
         t.yw,
         DENSE_RANK() OVER(PARTITION BY t.yw
                               ORDER BY t.money DESC) AS rank
    FROM PREF_MONEY t)
SELECT s.id
  FROM sample s
 WHERE s.rank = 1

Ich habe die yw Spalte, falls Sie nach dieser Spalte filtern wollen/müssen.