[MySQL] FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

윈도우 함수 중 FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() 함수에 대해 알아보겠습니다.

 

이용할 데이터 예시

swimmer	event_date	race_time
woo	2024-10-01	52.3
woo	2024-10-03	50.5
woo	2024-10-05	51.1
gyoung	2024-10-01	56.7
gyoung	2024-10-02	55.9

 

FIRST_VALUE()

• 윈도우 내에서 첫 번째 값을 반환합니다.

SELECT 
    swimmer, event_date, race_time, 
    FIRST_VALUE(race_time) OVER (PARTITION BY swimmer ORDER BY event_date) AS first_race_time
FROM swimming_races;

swimmer	event_date	race_time	first_race_time
woo	2024-10-01	52.3	52.3
woo	2024-10-03	50.5	52.3
woo	2024-10-05	51.1	52.3
gyoung	2024-10-01	56.7	56.7
gyoung	2024-10-02	55.9	56.7

 

 

LAST_VALUE()

• 윈도우 내 마지막 값을 반환합니다.
• 윈도우 범위에 따라 반환되는 값이 달라질 수 있습니다.

SELECT 
    swimmer, event_date, race_time, 
    LAST_VALUE(race_time) OVER (PARTITION BY swimmer ORDER BY event_date 
    	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_race_time
FROM swimming_races;

swimmer	event_date	race_time	last_race_time
woo	2024-10-01	52.3	51.1
woo	2024-10-03	50.5	51.1
woo	2024-10-05	51.1	51.1
gyoung	2024-10-01	56.7	55.9
gyoung	2024-10-02	55.9	55.9

 

 

NTH_VALUE()

• 윈도우 내에서 n번째 값을 반환합니다.
• 해당 위치에 값이 없다면 NULL이 반환됩니다.

SELECT 
    swimmer, event_date, race_time, 
    NTH_VALUE(race_time, 2) OVER (PARTITION BY swimmer ORDER BY event_date) AS second_race_time
FROM swimming_races;

swimmer	event_date	race_time	second_race_time
woo	2024-10-01	52.3	NULL
woo	2024-10-03	50.5	50.5
woo	2024-10-05	51.1	50.5
gyoung	2024-10-01	56.7	NULL
gyoung	2024-10-02	55.9	55.9

 

- 첫 번째 행에서는 두 번째 값이 존재하지 않기 때문에 NULL이 반환됩니다.

 

 

전체 결과 비교

swimmer	event_date	race_time	first_race_time	last_race_time	second_race_time
woo	2024-10-01	52.3	52.3	51.1	NULL
woo	2024-10-03	50.5	52.3	51.1	50.5
woo	2024-10-05	51.1	52.3	51.1	50.5
gyoung	2024-10-01	56.7	56.7	55.9	NULL
gyoung	2024-10-02	55.9	56.7	55.9	55.9

 

WINDOW 이해를 위한 추가

SELECT
    time, stroke, race_time,
    FIRST_VALUE(race_time)  OVER w AS 'first',
    LAST_VALUE(race_time)   OVER w AS 'last',
    NTH_VALUE(race_time, 2) OVER w AS 'second',
    NTH_VALUE(race_time, 4) OVER w AS 'fourth'
FROM swimming_races
WINDOW w AS (PARTITION BY stroke ORDER BY race_time ROWS UNBOUNDED PRECEDING);

 

time	stroke	race_time	first	last	second	fourth
07:00	freestyle	20	20	20	NULL	NULL
07:15	freestyle	24	20	24	9	NULL
07:30	freestyle	19	20	19	9	NULL
07:45	freestyle	21	20	21	9	21
07:00	butterfly	35	35	35	NULL	NULL
07:15	butterfly	38	35	38	10	NULL
07:30	butterfly	34	35	34	10	NULL

 

WINDOW > 윈도우 함수의 동작 범위 결정

UNBOUNDED PRECEDING: 처음부터 현재 행까지의 모든 행

UNBOUNDED FOLLOWING: 현재 행부터 마지막 행까지의 모든 행

N PRECEDING/FOLLOWING: 현재 행부터 N번째 앞/뒤 까지의 행

 

BETWEEN을 이용하여 표현 가능

ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING : 이전 행부터 윈도우 마지막 행까지

 

즉, stroke=freestyle 인 행들은 다음과 같은 의미를 가짐

FIRST_VALUE(val) OVER w : 처음부터 현재까지의 행들 중, 첫 번째 행의 값
LAST_VALUE(val) OVER w : 처음부터 현재까지의 행들 중, 마지막 행의 값
NTH_VALUE(val, 2) OVER w : 처음부터 현재까지의 행들 중, 두 번째 행의 값 (1행에서는 해당 값이 없으므로 NULL표시)
NTH_VALUE(val, 4) OVER w : 처음부터 현재까지의 행들 중, 네 번째 행의 값 (1,2,3 행에서는 해당 값이 없으므로 NULL표시)

MySQL에서는 IGNORE NULLS 와 같은 옵션 제공하지 않으므로, 서브쿼리나 COALESCE()를 이용하여 처리 가능

 

ROWS / RANGE

• ROWS: 각 행은 고유하게 처리되며, 값이 동일해도 개별 행으로 취급
• RANGE: 정렬된 값의 범위에 따라 계산, 동일한 값이 여러 개 있을 경우 모두 하나의 범위로 처리.
• MySQL 기본 윈도우 프레임: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW