PostgreSQL の上位 N 行を選択
- PostgreSQL でテーブルを作成する
-
FETCH句を使用して PostgreSQL の上位 N 行を選択する -
LIMIT句を使用して PostgreSQL の上位 N 行を選択する -
ROW_NUMBER()関数を使用して PostgreSQL の上位 N 行を選択する -
RANK()関数を使用して PostgreSQL の上位 N 行を選択する -
DENSE_RANK()関数を使用して PostgreSQL の上位 N 行を選択する - PostgreSQL でのテーブルのインデックス作成
- パフォーマンス分析
テーブル内の最大値を取得することは、知っておくべき重要なクエリです。 アプリケーションの 1つの分野は、レポートの生成です。
このチュートリアルでは、PostgreSQL テーブルで N 個の最高スコアを取得するさまざまな方法について説明します。
PostgreSQL でテーブルを作成する
次に示すように、N 人の学生とそれぞれのスコアを含む student_score テーブルを考えてみましょう。
id |
studentid |
" スコア" |
|---|---|---|
| 1 | 1 | 54 |
| 2 | 2 | 75 |
| 3 | 3 | 52 |
| 4 | 4 | 55 |
| 5 | 5 | 93 |
| 6 | 6 | 74 |
| 7 | 7 | 92 |
| 8 | 8 | 64 |
| 9 | 9 | 89 |
| 10 | 10 | 81 |
クエリ:
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)
)
テーブルに 100 万行のテスト データを入力する INSERT ステートメントを次に示します。
INSERT INTO student_score (studentid, score)
SELECT i, floor(random()*(100-50+1))+50
FROM generate_series(1,1000000) i
FETCH 句を使用して PostgreSQL の上位 N 行を選択する
FETCH 句は、指定された数のスコアのみを返します。 テーブルは降順で並べ替えられ、最も高い値が一番上に表示され、テーブルの下に進むにつれてスコアが減少します。
次に、句を使用して、以下のクエリで指定されているように、最高スコアの 10 のみを返します。
SELECT * FROM student_score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY
出力：
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
ただし、このクエリは、スコアが最も高い上位 10 人の学生を返します。 この例では、100 が学生が達成できる最高得点であり、クエリの出力は、少なくとも 10 人の学生が 100 を獲得したことを示しています。
これは、学生が達成した 10 の異なる最高値を示すものではありません。 任意の学生が取得した最高 10 の一意のスコアを取得する必要がある場合は、次のように DISTINCT 句が使用されます。
SELECT DISTINCT score FROM student_score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY
さらに、最高点 10 点を記録した生徒の数を知る必要がある場合は、次のクエリを使用してそれを達成できます。
SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
FETCH NEXT 10 ROWS ONLY
出力：
score | count
----- |-------
100 | 19518
99 | 19719
98 | 19412
97 | 19588
96 | 19652
95 | 19396
94 | 19649
93 | 19427
92 | 19880
91 | 19580
LIMIT 句を使用して PostgreSQL の上位 N 行を選択する
LIMIT 句は、指定された最大行数を返します。 この場合、返される行の最大数は 10 です。
この句を使用するには、行を降順でソートする必要もあります。 クエリは次のとおりです。
SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
LIMIT 10
ROW_NUMBER() 関数を使用して PostgreSQL の上位 N 行を選択する
ROW_NUMBER() 関数を使用して同じ結果を得ることができます。 以下にクエリを示します。
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
RANK() 関数を使用して PostgreSQL の上位 N 行を選択する
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
DENSE_RANK() 関数を使用して PostgreSQL の上位 N 行を選択する
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
PostgreSQL でのテーブルのインデックス作成
大規模なデータベースでのパフォーマンスを向上させるために、インデックスを使用できます。 この例では、降順で並べ替えられたスコア列にインデックスを付けることができます。
これは、インデックスを作成し、特定の条件を追加することによって行われます。この場合、以下に示すように、スコア列を降順に並べ替えます。
CREATE INDEX score_desc_idx ON student_score(score DESC)
前述のように、インデックスを作成すると、より大きなレコードを持つテーブルのクエリ パフォーマンスが最も効果的に向上します。 ただし、テーブルが頻繁に変更される (挿入と更新が頻繁に行われる) 場合は、使用しないことをお勧めします。
また、PostgreSQL は、テーブル スキャンがより高速な場合など、特定のケースではインデックスを使用しません。
パフォーマンス分析
score_desc_idx インデックスを作成する前に、各操作の実行時間は次のように与えられました。
| 節/機能 | 実行時間 (ミリ秒) |
|---|---|
FETCH |
844 |
LIMIT |
797 |
ROW_NUMBER() |
745 |
RANK() |
816 |
DENSE_RANK() |
701 |
ただし、これは1回の実行の直後でした。 複数回実行すると、範囲を超えないさまざまな時間が得られました。
EXPLAIN ANALYZE 句を使用して、データベースにとってコストがかからないアプローチを判断できます。
EXPLAIN ANALYZE SELECT DISTINCT score, COUNT(studentid) FROM student_score
GROUP BY score
ORDER BY score DESC
LIMIT 10
出力：
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
この場合、テーブルのスキャンが比較的高速であるため、PostgreSQL は以前に作成されたインデックスを使用していないことがわかります。 インデックスを追加しても実行時間に大きな変化はないため、次のコマンドを実行してインデックスを削除できます。
DROP INDEX score_desc_idx;
どのアプローチが最適かを判断するのはユーザー次第であり、どのアプローチを使用しても同じ結果を得ることができます。