Wählen Sie in PostgreSQL die obersten N Zeilen aus

1. Erstellen Sie eine Tabelle in PostgreSQL
2. Verwenden Sie die FETCH-Klausel, um die obersten N Zeilen in PostgreSQL auszuwählen
3. Verwenden Sie die LIMIT-Klausel, um die oberen N Zeilen in PostgreSQL auszuwählen
4. Verwenden Sie die Funktion ROW_NUMBER(), um die oberen N Zeilen in PostgreSQL auszuwählen
5. Verwenden Sie die Funktion RANK(), um die obersten N Zeilen in PostgreSQL auszuwählen
6. Verwenden Sie die Funktion DENSE_RANK(), um die oberen N Zeilen in PostgreSQL auszuwählen
7. Tabellenindizierung in PostgreSQL
8. Leistungsanalyse

Das Abrufen der höchsten Werte in einer Tabelle ist eine wichtige Abfrage, die Sie kennen sollten. Einer seiner Anwendungsbereiche ist die Erstellung von Berichten.

Dieses Tutorial führt Sie durch verschiedene Möglichkeiten, um die N höchsten Punktzahlen in einer PostgreSQL-Tabelle zu erhalten.

Erstellen Sie eine Tabelle in PostgreSQL

Stellen Sie sich eine student_score-Tabelle mit N Schülern und ihren jeweiligen Ergebnissen vor, wie hier gezeigt:

Anfrage:

CREATE TABLE public.student_score
(
    id bigint NOT NULL
        GENERATED ALWAYS AS IDENTITY
        ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    studentid bigint NOT NULL,
    score integer NOT NULL,
    CONSTRAINT student_score_pkey PRIMARY KEY (id)
)

Hier ist die INSERT-Anweisung, um die Tabelle mit 1 Million Reihen von Testdaten zu füllen:

INSERT INTO student_score (studentid, score)
SELECT i, floor(random()*(100-50+1))+50
FROM generate_series(1,1000000) i

Verwenden Sie die FETCH-Klausel, um die obersten N Zeilen in PostgreSQL auszuwählen

Die FETCH-Klausel gibt nur die angegebene Anzahl von Scores zurück. Die Tabelle ist in absteigender Reihenfolge sortiert, wobei der höchste Wert ganz oben steht und die Punktzahl abnimmt, wenn es in der Tabelle nach unten geht.

Dann wird die Klausel verwendet, um nur 10 der höchsten Punktzahlen zurückzugeben, wie in der Abfrage unten angegeben.

SELECT * FROM student_score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY

Ausgang:

id   | studentid | score
-----|---------- |--------
324  | 324       | 100
379  | 379       | 100
282  | 282       | 100
79   | 79        | 100
335  | 335       | 100
91   | 91        | 100
13   | 13        | 100
108  | 108       | 100
52   | 52        | 100
414  | 414       | 100

Diese Abfrage gibt jedoch die 10 besten Schüler mit den höchsten Punktzahlen zurück. In diesem Beispiel ist 100 die höchste Punktzahl, die ein Schüler erreichen kann, und die Ausgabe der Abfrage zeigt, dass mindestens 10 Schüler 100 Punkte erzielt haben.

Es gibt nicht die 10 verschiedenen höchsten Werte, die ein Schüler erreicht hat. Wenn es erforderlich ist, die 10 höchsten von einem Schüler erzielten Einzelpunktzahlen zu erreichen, wird die Klausel DISTINCT wie hier gezeigt verwendet:

SELECT DISTINCT score FROM student_score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY

Wenn außerdem bekannt sein muss, wie viele Schüler die 10 höchsten Punktzahlen erzielt haben, kann diese Abfrage verwendet werden, um dies zu erreichen:

SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY

Ausgang:

score | count
----- |-------
100   | 19518
99    | 19719
98    | 19412
97    | 19588
96    | 19652
95    | 19396
94    | 19649
93    | 19427
92    | 19880
91    | 19580

Verwenden Sie die LIMIT-Klausel, um die oberen N Zeilen in PostgreSQL auszuwählen

Die Klausel LIMIT gibt die angegebene maximale Anzahl von Zeilen zurück. In diesem Fall ist 10 die maximale Anzahl der zurückzugebenden Zeilen.

Die Verwendung dieser Klausel erfordert auch, dass die Zeilen in absteigender Reihenfolge sortiert werden. Hier ist die Abfrage:

SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
LIMIT 10

Verwenden Sie die Funktion ROW_NUMBER(), um die oberen N Zeilen in PostgreSQL auszuwählen

Die Funktion ROW_NUMBER() kann verwendet werden, um das gleiche Ergebnis zu erhalten. Die Abfrage ist unten angegeben.

SELECT score, student_count
FROM (
    SELECT DISTINCT score AS score,
    COUNT(studentid) AS student_count,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS score_rank
    FROM student_score
    GROUP BY score
) subquery
WHERE score_rank <= 10
ORDER BY score_rank

Verwenden Sie die Funktion RANK(), um die obersten N Zeilen in PostgreSQL auszuwählen

Hier ist eine Abfrage mit der Funktion RANK():

SELECT score, student_count
FROM (
    SELECT DISTINCT score AS score,
    COUNT(studentid) AS student_count,
    RANK() OVER (ORDER BY score DESC) AS score_rank
    FROM student_score
    GROUP BY score
) subquery
WHERE score_rank <= 10
ORDER BY score_rank

Verwenden Sie die Funktion DENSE_RANK(), um die oberen N Zeilen in PostgreSQL auszuwählen

Hier ist eine Abfrage mit der Funktion DENSE_RANK():

SELECT score, student_count
FROM (
    SELECT DISTINCT score AS score,
    COUNT(studentid) AS student_count,
    DENSE_RANK() OVER (ORDER BY score DESC) AS score_rank
    FROM student_score
    GROUP BY score
) subquery
WHERE score_rank <= 10
ORDER BY score_rank

Tabellenindizierung in PostgreSQL

Um die Leistung einer großen Datenbank zu verbessern, können Sie Indizes verwenden. In diesem Beispiel kann die absteigend sortierte Score-Spalte indiziert werden.

Dazu erstellen Sie einen Index und fügen bestimmte Bedingungen hinzu, in diesem Fall sortieren Sie die Ergebnisspalte in absteigender Reihenfolge, wie unten gezeigt.

CREATE INDEX score_desc_idx ON student_score(score DESC)

Wie bereits erwähnt, verbessert die Indizierung die Abfrageleistung für Tabellen mit größeren Datensätzen am effektivsten. Es ist jedoch nicht ratsam, es zu verwenden, wenn die Tabelle häufig geändert wird (mit häufigen Einfügungen und Aktualisierungen).

Außerdem verwendet PostgreSQL in bestimmten Fällen keine Indizes, z. B. wenn ein Tabellenscan schneller ist.

Leistungsanalyse

Vor dem Erstellen des Index score_desc_idx wurde die Ausführungszeit für jede Operation wie folgt angegeben:

Dies war jedoch nur nach einem einzigen Lauf. Das mehrmalige Ausführen ergab unterschiedliche Zeiten, die einen Bereich nicht überschritten.

Mit der Klausel EXPLAIN ANALYZE können Sie bestimmen, welcher Ansatz für Ihre Datenbank kostengünstiger ist.

EXPLAIN ANALYZE SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
LIMIT 10

Ausgang:

Limit  (cost=13636.35..13636.42 rows=10 width=12) (actual time=763.166..797.358 rows=10 loops=1)
->  Unique  (cost=13636.35..13636.73 rows=51 width=12) (actual time=763.163..797.351 rows=10 loops=1)"
        ->  Sort  (cost=13636.35..13636.48 rows=51 width=12) (actual time=763.161..797.342 rows=10 loops=1)"
             Sort Key: score DESC, (count(studentid))"
              Sort Method: quicksort  Memory: 27kB"
             ->  Finalize GroupAggregate  (cost=13621.98..13634.90 rows=51 width=12) (actual time=762.733..797.231 rows=51 loops=1)"
                   Group Key: score"
                   ->  Gather Merge  (cost=13621.98..13633.88 rows=102 width=12) (actual time=762.715..797.047 rows=153 loops=1)"
                          Workers Planned: 2"
                          Workers Launched: 2"
                          ->  Sort  (cost=12621.96..12622.09 rows=51 width=12) (actual time=632.708..632.724 rows=51 loops=3)
                                Sort Key: score DESC
                                Sort Method: quicksort  Memory: 27kB"
                                Worker 0:  Sort Method: quicksort  Memory: 27kB"
                                Worker 1:  Sort Method: quicksort  Memory: 27kB"
                                ->  Partial HashAggregate  (cost=12620.00..12620.51 rows=51 width=12) (actual time=632.509..632.546 rows=51 loops=3)"
                                      Group Key: score
                                      ->  Parallel Seq Scan on student_score  (cost=0.00..10536.67 rows=416667 width=12) (actual time=0.067..176.426 rows=333333 loops=3)
Planning Time: 3.228 ms
Execution Time: 799.928 ms

Es ist ersichtlich, dass PostgreSQL den zuvor erstellten Index nicht verwendet hat, da das Scannen der Tabelle in diesem Fall relativ schnell ist. Da das Hinzufügen eines Index keine wesentliche Änderung der Ausführungszeit bewirkt, kann der Index durch Ausführen dieses Befehls entfernt werden:

DROP INDEX score_desc_idx;

Es liegt an Ihnen, zu entscheiden, welcher Ansatz am besten funktioniert, und jeder Ansatz kann verwendet werden, um das gleiche Ergebnis zu erzielen.