Skip to main content

WINDOW


OVER

window를 사용하는 또는 할 수 있는 함수에 OVER를 사용하여 window를 넘겨줄 수 있습니다.

OVER(
[<window_name>]
[PARTITION BY
<expr>[, <expr>]]
[ORDER BY
<expr> [ASC|DESC] [, <expr> [ASC|DESC]]]
[<frame_clause>]
)

RANK, DENSE_RANK, ROW_NUMBER

SELECT
val,
ROW_NUMBER() OVER(w) AS 'row_number',
RANK() OVER(w) AS 'rank',
DENSE_RANK() OVER(w) AS 'dense_rank'
FROM
numbers WINDOW w AS (
ORDER BY
val
)
+------+------------+------+------------+
| val | row_number | rank | dense_rank |
+------+------------+------+------------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 1 | 1 |
| 2 | 3 | 3 | 2 |
| 3 | 4 | 4 | 3 |
| 3 | 5 | 4 | 3 |
| 3 | 6 | 4 | 3 |
| 4 | 7 | 7 | 4 |
| 4 | 8 | 7 | 4 |
| 5 | 9 | 9 | 5 |
+------+------------+------+------------+