순위 함수
PostgreSQL을 활용한 시계열 분석
Jasmin Ludolf
Content Developer, DataCamp
행 번호는 순차적으로 매겨집니다
SELECT
station_id,
year_month,
t_monthly_min,
ROW_NUMBER() OVER (
PARTITION BY station_id
ORDER BY t_monthly_min) AS rank
FROM temperatures_monthly AS tm
WHERE year_month BETWEEN '2018-01-01'
AND '2018-12-31'
ORDER BY station_id, t_monthly_min;
|station_id|year_month|t_monthly_min|rank|
|----------|----------|-------------|----|
| 1|2018-12-01| 5.4| 1|
| 1|2018-02-01| 6.2| 2|
| 1|2018-01-01| 7.4| 3|
| 1|2018-11-01| 9.0| 4|
| 1|2018-03-01| 10.6| 5|
| 1|2018-04-01| 14.7| 6|
| 1|2018-10-01| 16.6| 7|
| 1|2018-05-01| 17.2| 8|
| 1|2018-06-01| 21.9| 9|
| 1|2018-09-01| 24.4| 10|
| 1|2018-07-01| 28.1| 11|
...
순위
RANK():ORDER BY에 따라 각 파티션 내 행마다 순위를 부여하며,1부터 시작하고 동일한 값에는 같은 순위를 반복 부여합니다
...
RANK() OVER (
PARTITION BY station_id
ORDER BY t_monthly_min DESC) AS rank
...
...
ROW_NUMBER() OVER (
PARTITION BY station_id
ORDER BY t_monthly_min DESC) AS row
...
순위와 행 번호 비교
RANK()
|year_month|t_monthly_min|rank|
|----------|-------------|----|
|2018-02-01| 1.6| 1|
|2018-01-01| 1.6| 1|
|2018-03-01| 2| 3|
|2018-11-01| 2.5| 4|
|2018-12-01| 2.7| 5|
|2018-04-01| 3.4| 6|
|2018-10-01| 4.1| 7|
|2018-09-01| 4.7| 8|
...
ROW_NUMBER()
|year_month|t_monthly_min|rank|
|----------|-------------|----|
|2018-02-01| 1.6| 1|
|2018-01-01| 1.6| 2|
|2018-03-01| 2| 3|
|2018-11-01| 2.5| 4|
|2018-12-01| 2.7| 5|
|2018-04-01| 3.4| 6|
|2018-10-01| 4.1| 7|
|2018-09-01| 4.7| 8|
...
밀집 순위
DENSE_RANK():ORDER BY에 따라 각 파티션 내 행마다 순위를 부여하며,1부터 시작하고 동일한 값에는 같은 순위를 반복 부여합니다- 순위를 건너뛰지 않습니다
...
DENSE_RANK() OVER (
PARTITION BY station_id
ORDER BY t_monthly_min DESC) AS rank
...
순위와 밀집 순위 비교
RANK()
|year_month|t_monthly_min|rank|
|----------|-------------|----|
|2018-02-01| 1.6| 1|
|2018-01-01| 1.6| 1|
|2018-03-01| 2| 3|
|2018-11-01| 2.5| 4|
|2018-12-01| 2.7| 5|
|2018-04-01| 3.4| 6|
|2018-10-01| 4.1| 7|
|2018-09-01| 4.7| 8|
...
DENSE_RANK()
|year_month|t_monthly_min|rank|
|----------|-------------|----|
|2018-02-01| 1.6| 1|
|2018-01-01| 1.6| 1|
|2018-03-01| 2| 2|
|2018-11-01| 2.5| 3|
|2018-12-01| 2.7| 4|
|2018-04-01| 3.4| 5|
|2018-10-01| 4.1| 6|
|2018-09-01| 4.7| 7|
...
퍼센트 순위
PERCENT_RANK:ORDER BY에 따라 각 파티션 내 행마다 백분율로 순위를 부여합니다- (순위 - 1) / (전체 파티션 행 수 - 1)
0에서1사이의 실수 값
...
PERCENT_RANK() OVER (
PARTITION BY station_id
ORDER BY t_monthly_min DESC) AS percent_rank
...
퍼센트 순위 결과
|year_month|t_monthly_min|percent_rank|
|----------|-------------|------------|
|2018-02-01| 1.6| 0|
|2018-01-01| 1.6| 0|
|2018-03-01| 2| 0.18|
|2018-11-01| 2.5| 0.27|
|2018-12-01| 2.7| 0.36|
|2018-04-01| 3.4| 0.45|
|2018-10-01| 4.1| 0.54|
|2018-09-01| 4.7| 0.63|
...
연습해 봅시다!
PostgreSQL을 활용한 시계열 분석
