[PostgreSQL] Rank()

rank()

rank() 함수는 순위를 매겨주는 sql 함수입니다.

rank () over (order by <column>)에서 column 순으로 정렬하여 매긴 순위를 출력합니다.

예제

1. 테이블 생성

testdb=> create table test(
testdb(> id int,
testdb(> name varchar(15),
testdb(> rating int);
CREATE TABLE
testdb=> \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | testuser
(1 row)

2. 데이터 추가

testdb=> insert into test values (1, 'john', 100);
INSERT 0 1
testdb=> insert into test values (2, 'james', 150);
INSERT 0 1
testdb=> insert into test values (3, 'peet', 75);
INSERT 0 1
testdb=> insert into test values (4, 'bread', 100);
INSERT 0 1
testdb=> select * from test;
 id | name  | rating
----+-------+--------
  1 | john  |    100
  2 | james |    150
  3 | peet  |     75
  4 | bread |    100
(4 rows)

3. rating 순으로 정렬(rank())

testdb=> select id, name, rating, rank () over (order by rating) from test;
 id | name  | rating | rank
----+-------+--------+------
  3 | peet  |     75 |    1
  1 | john  |    100 |    2
  4 | bread |    100 |    2
  2 | james |    150 |    4
(4 rows)

---

https://new-hero.tistory.com/22