PostgreSQL에서 상위 N개 행 선택
- PostgreSQL에서 테이블 생성
-
FETCH절을 사용하여 PostgreSQL에서 상위 N개 행 선택 -
LIMIT절을 사용하여 PostgreSQL에서 상위 N개 행 선택 -
ROW_NUMBER()함수를 사용하여 PostgreSQL에서 상위 N개 행 선택 -
RANK()함수를 사용하여 PostgreSQL에서 상위 N개 행 선택 -
DENSE_RANK()함수를 사용하여 PostgreSQL에서 상위 N개 행 선택 - PostgreSQL의 테이블 인덱싱
- 성능 분석
테이블에서 가장 높은 값을 얻는 것은 알아야 할 중요한 쿼리입니다. 응용 분야 중 하나는 보고서를 생성하는 것입니다.
이 자습서에서는 PostgreSQL 테이블에서 N개의 최고 점수를 얻는 다양한 방법을 안내합니다.
PostgreSQL에서 테이블 생성
다음과 같이 N명의 학생과 각각의 점수가 있는 student_score 테이블을 고려하십시오.
id |
studentid |
점수 |
|---|---|---|
| 1 | 1 | 54 |
| 2 | 2 | 75 |
| 삼 | 삼 | 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 |
그러나 이것은 한 번의 실행 직후였습니다. 여러 번 실행하면 범위를 초과하지 않는 다양한 시간이 제공됩니다.
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;
어떤 접근 방식이 가장 효과적인지 결정하는 것은 귀하에게 달려 있으며 동일한 결과를 얻기 위해 어떤 접근 방식을 사용할 수 있습니다.