グループ毎にランクを振る（RANK, DENSE_RANK, ROW_NUMBER）

rank_per_group_window_function.sql

BEGIN;

CREATE TEMPORARY TABLE scores (uid integer, job varchar, "name" varchar, score integer);
INSERT INTO scores (uid, job, "name", score) VALUES
(9991, 'ANALYST', 'TEST1', 100),
(9992, 'SCOREESMAN', 'TEST2', 200),
(1, 'ANALYST', 'FORD', 3000),
(2, 'ANALYST', 'SCOTT', 3000),
(3, 'CLERK', 'ADAMS', 1100),
(4, 'CLERK', 'JAMES', 950),
(5, 'CLERK', 'SMITH', 800),
(6, 'MANAGER', 'BLAKE', 2850),
(7, 'MANAGER', 'CLARK', 2450),
(8, 'MANAGER', 'JONES', 2975),
(9, 'PRESIDENT', 'KING', 5000),
(10, 'SCOREESMAN', 'ALLEN', 1600),
(11, 'SCOREESMAN', 'MARTIN', 1250),
(12, 'SCOREESMAN', 'TURNER', 1500),
(13, 'SCOREESMAN', 'WARD', 1250);

SELECT * FROM scores;

SELECT uid, job, "name", score,
       RANK() OVER (PARTITION BY job
                    ORDER BY score DESC) AS ranking
FROM scores;

SELECT uid, job, "name", score,
       DENSE_RANK() OVER (PARTITION BY job
                          ORDER BY score DESC) AS ranking
FROM scores;

SELECT uid, job, "name", score,
       ROW_NUMBER() OVER (PARTITION BY job
                          ORDER BY score DESC) AS ranking
FROM scores;

COMMIT;

```
BEGIN
CREATE TABLE
INSERT 0 15
 uid  |    job     |  name  | score
------+------------+--------+-------
 9991 | ANALYST    | TEST1  |   100
 9992 | SCOREESMAN | TEST2  |   200
    1 | ANALYST    | FORD   |  3000
    2 | ANALYST    | SCOTT  |  3000
    3 | CLERK      | ADAMS  |  1100
    4 | CLERK      | JAMES  |   950
    5 | CLERK      | SMITH  |   800
    6 | MANAGER    | BLAKE  |  2850
    7 | MANAGER    | CLARK  |  2450
    8 | MANAGER    | JONES  |  2975
    9 | PRESIDENT  | KING   |  5000
   10 | SCOREESMAN | ALLEN  |  1600
   11 | SCOREESMAN | MARTIN |  1250
   12 | SCOREESMAN | TURNER |  1500
   13 | SCOREESMAN | WARD   |  1250
(15 rows)

 uid  |    job     |  name  | score | ranking
------+------------+--------+-------+---------
    2 | ANALYST    | SCOTT  |  3000 |       1
    1 | ANALYST    | FORD   |  3000 |       1
 9991 | ANALYST    | TEST1  |   100 |       3
    3 | CLERK      | ADAMS  |  1100 |       1
    4 | CLERK      | JAMES  |   950 |       2
    5 | CLERK      | SMITH  |   800 |       3
    8 | MANAGER    | JONES  |  2975 |       1
    6 | MANAGER    | BLAKE  |  2850 |       2
    7 | MANAGER    | CLARK  |  2450 |       3
    9 | PRESIDENT  | KING   |  5000 |       1
   10 | SCOREESMAN | ALLEN  |  1600 |       1
   12 | SCOREESMAN | TURNER |  1500 |       2
   11 | SCOREESMAN | MARTIN |  1250 |       3
   13 | SCOREESMAN | WARD   |  1250 |       3
 9992 | SCOREESMAN | TEST2  |   200 |       5
(15 rows)

 uid  |    job     |  name  | score | ranking
------+------------+--------+-------+---------
    2 | ANALYST    | SCOTT  |  3000 |       1
    1 | ANALYST    | FORD   |  3000 |       1
 9991 | ANALYST    | TEST1  |   100 |       2
    3 | CLERK      | ADAMS  |  1100 |       1
    4 | CLERK      | JAMES  |   950 |       2
    5 | CLERK      | SMITH  |   800 |       3
    8 | MANAGER    | JONES  |  2975 |       1
    6 | MANAGER    | BLAKE  |  2850 |       2
    7 | MANAGER    | CLARK  |  2450 |       3
    9 | PRESIDENT  | KING   |  5000 |       1
   10 | SCOREESMAN | ALLEN  |  1600 |       1
   12 | SCOREESMAN | TURNER |  1500 |       2
   11 | SCOREESMAN | MARTIN |  1250 |       3
   13 | SCOREESMAN | WARD   |  1250 |       3
 9992 | SCOREESMAN | TEST2  |   200 |       4
(15 rows)

 uid  |    job     |  name  | score | ranking
------+------------+--------+-------+---------
    2 | ANALYST    | SCOTT  |  3000 |       1
    1 | ANALYST    | FORD   |  3000 |       2
 9991 | ANALYST    | TEST1  |   100 |       3
    3 | CLERK      | ADAMS  |  1100 |       1
    4 | CLERK      | JAMES  |   950 |       2
    5 | CLERK      | SMITH  |   800 |       3
    8 | MANAGER    | JONES  |  2975 |       1
    6 | MANAGER    | BLAKE  |  2850 |       2
    7 | MANAGER    | CLARK  |  2450 |       3
    9 | PRESIDENT  | KING   |  5000 |       1
   10 | SCOREESMAN | ALLEN  |  1600 |       1
   12 | SCOREESMAN | TURNER |  1500 |       2
   11 | SCOREESMAN | MARTIN |  1250 |       3
   13 | SCOREESMAN | WARD   |  1250 |       4
 9992 | SCOREESMAN | TEST2  |   200 |       5
(15 rows)

COMMIT
```