Update top-N-Werte mit PostgreSQL

Ich soll ein update der top-10-Werte von einer Spalte in Tabelle. Ich habe drei Spalten; id, account und accountrank. Um die top 10 der Werte, die ich verwenden können, die folgenden:

SELECT  * FROM accountrecords    
ORDER BY account DESC
LIMIT 10;

Was ich möchte zu tun ist, um den Wert in accountrank werden eine Reihe von 1 - 10, basierend auf der Größenordnung von account. Ist das möglich zu tun in PostgreSQL?

Wenn Ihr poatgres version 8.4 oder höher, könnten Sie Window-Funktionen + Rang() oder row_number().

InformationsquelleAutor djq | 2012-11-02

  1. 26
    WITH cte AS (
   SELECT id, row_number() OVER (ORDER BY account DESC NULLS LAST) AS rn
   FROM   accountrecords    
   ORDER  BY account DESC NULLS LAST
   LIMIT  10
   )
UPDATE accountrecords a
SET    accountrank = cte.rn
FROM   cte
WHERE  cte.id = a.id;

    Beitritt in einer Tabelle Ausdruck ist in der Regel schneller als korrelierte Unterabfragen. Es ist auch kürzer.

    Mit der Fenster-Funktion row_number() verschiedene zahlen sind garantiert. Verwenden rank() (oder vielleicht dense_rank()) wenn Sie möchten, dass Zeilen mit gleichen Werten für account teilen die gleiche Anzahl.

    Nur, wenn es NULL Werte in account, müssen Sie anfügen NULL-LAST für absteigend Sortieren, oder NULL Werte Sortieren nach top:

    Wenn es gleichzeitigen schreib-Zugriff, die Abfrage ist Gegenstand einer race-condition. Bedenken Sie:

    Jedoch, wenn das der Fall war, das ganze Konzept von hard-coding der top-ten wäre ein zweifelhafter Ansatz, um mit zu beginnen.

    Verwenden Sie eine CTE-statt einer einfachen Unterabfrage (wie auch, hatte ich zuerst), um die Durchsetzung der LIMIT zuverlässig. Siehe links oben.

    InformationsquelleAutor Erwin Brandstetter

  2. 1

    Sicher, Sie können Ihre select-Anweisung in einer Unterabfrage. Die Generierung der Rang-Ordnung ist nicht trivial, aber hier ist zumindest ein Weg, es zu tun. Habe ich noch nicht getestet, aber aus der Spitze von meinem Kopf:

    update accountrecords
set accountrank =
    (select count(*) + 1 from accountrecords r where r.account > account)
where id in (select id from accountrecords order by account desc limit 10);

    Dieser hat die Eigenart, dass, wenn zwei Datensätze den gleichen Wert für account, dann erhalten Sie den gleichen Rang. Man könnte erwägen, dass ein feature... 🙂

    InformationsquelleAutor Jamey Sharp

Schreibe einen Kommentar