MYSQL8. window function 정리

쿼리

SELECT id                         AS id     
     , parent_id                  AS parent_id     
     , value                      AS value
     
     # 1부터 순서대로 - 공동순위 없음
     , row_number() OVER w        AS rownumber

     # 순위 %
     , PERCENT_RANK() OVER w      AS 'percent_rank'

     # 순위 - 공동1위 있으면 다음순위 3등
     , RANK() OVER w              AS 'RANK'

     # 순위 - 공동1위 있어도 다음순위 2등
     , DENSE_RANK() OVER w        AS 'DENSE_RANK'

     # 첫 번째 값
     , FIRST_VALUE(value) OVER w  AS 'FIRST_VALUE'

     # 마지막 값
     , LAST_VALUE(value) OVER w   AS 'LAST_VALUE'

     # 이전 값
     , LAG(value) OVER w          AS 'LAG'

     # 이후 값
     , LEAD(value) OVER w         AS 'LEAD'

     # 파티션 내 3번째 'value'값
     , NTH_VALUE(value, 3) OVER w AS 'NTH_VALUE'

     # 파티션을 N개로 나눈 후 현재 분할된 파티션의 번호
     , NTILE(3) OVER w            AS 'NTILE'
FROM (
         VALUES
             /* parent_id = 1 */
             ROW(1, 1, 100)
            ,ROW(2, 1, 100)
            ,ROW(3, 1, 300)
            ,ROW(4, 1, 400)
            ,ROW(5, 1, 500)
            ,ROW(6, 1, 600)
            /* parent_id = 2 */
            ,ROW(7, 2, 900)
            ,ROW(8, 2, 100)
            ,ROW(9, 2, 500)
            ,ROW(10, 2, 700)
            ,ROW(11, 2, 600)
            ,ROW(12, 2, 300)
            ,ROW(13, 2, 300)
            ,ROW(14, 2, 300)
            ,ROW(15, 2, 300)
            ,ROW(16, 2, 300)
            ,ROW(17, 2, 300)
            ,ROW(18, 2, 300)
         ) AS t(id, parent_id, value)
    WINDOW w AS (
    	PARTITION BY parent_id 
        ORDER BY value
        # 기본값 - 첫 번째 행부터 현재 행까지
#         RANGE BETWEEN UNBOUNDED PRECEDING
#             AND CURRENT ROW

        # 1행 이후부터 2행 이후까지
#         RANGE BETWEEN 1 FOLLOWING
#                   AND 2 FOLLOWING

        # 첫 번째 행부터 마지막 행까지
#         RANGE BETWEEN UNBOUNDED PRECEDING
#                   AND UNBOUNDED FOLLOWING

        # 1행 전부터 1행 이후까지
#         RANGE BETWEEN 1 PRECEDING
#                   AND 1 FOLLOWING
    )
ORDER BY id

order by가 있을 경우

처음 행부터 현재 행까지 집계

order by가 없을 경우

처음 행부터 마지막 행까지 집계

결과

id	parent_id	value	rownumber	percent_rank	RANK	DENSE_RANK	FIRST_VALUE	LAST_VALUE	LAG	LEAD	NTH_VALUE	NTILE
1	1	100	1	0	1	1	100	100		100		1
2	1	100	2	0	1	1	100	100	100	300		1
3	1	300	3	0.4	3	2	100	300	100	400	300	2
4	1	400	4	0.6	4	3	100	400	300	500	300	2
5	1	500	5	0.8	5	4	100	500	400	600	300	3
6	1	600	6	1	6	5	100	600	500		300	3
7	2	900	6	1	6	6	100	900	700		500	3
8	2	100	1	0	1	1	100	100		300		1
9	2	500	3	0.4	3	3	100	500	300	600	500	2
10	2	700	5	0.8	5	5	100	700	600	900	500	3
11	2	600	4	0.6	4	4	100	600	500	700	500	2
12	2	300	2	0.2	2	2	100	300	100	500		1

---

참조
https://dev.mysql.com/doc/refman/8.0/en/window-functions.html