[MySQL] 윈도우 함수(Window Functions) - 3. N번째 행의 값 구하기

활용 5. 첫번째, 마지막 행의 값 추출하기

-- 모양새
FIRST_VALUE(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
LAST_VALUE(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)

- FIRST_VALUE( ) : 윈도우 프레임 내에서 첫 번째 행의 컬럼 값을 반환.

- LAST_VALUE( ) : 윈도우 프레임 내에서 마지막 행의 컬럼 값을 반환. (단, LAST_VALUE() 의 경우 원하는 결과를 얻으려면 윈도우 프레임을 명시적으로 설정해주는 것이 중요하다)

 

 

-- 예제
SELECT time
     , subject
     , val
     , FIRST_VALUE(val) OVER w AS 'first'
     , LAST_VALUE(val) OVER w AS 'last',
FROM observations
WINDOW w AS (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING);

-- OUTPUT:
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+

 

 

FIRST_VALUE와 LAST_VALUE 함수의 작동 방식에서 이런 차이가 나는 이유

 

윈도우 프레임의 기본값(default)
: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

=> 현재 행까지의 모든 이전 행을 포함한다.

- RANGE: 값의 '범위'를 기준으로 설정.
- UNBOUNDED PRECEDING: 프레임의 시작을 첫 번째 행으로 설정.
- CURRENT ROW: 프레임의 끝을 현재 행으로 설정.

 

윈도우 프레임을 명시적으로 설정하지 않는 경우, 윈도우 프레임의 기본값(default)으로 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 가 적용된다. 윈도우 프레임을 명시적으로 설정해주지 않아 윈도우 프레임이 기본값으로 적용되어 있는 경우 FIRST_VALUE는 적용된 윈도우 프레임에 따라 항상 첫 번째 행의 값을 반환하지만, LAST_VALUE는 현재 행의 값을 반환하게 된다. 따라서 LAST_VALUE를 통해 전체 행 범위 내에서 마지막 행의 값을 반환하도록 하려면, 윈도우 프레임이 전체 행 범위로 적용되도록 'RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' 와 같이 윈도우 프레임을 명시적으로 설정해주어야 한다.

 

 

 

 

[참고] N번째 행의 값 추출하기

NTH_VALUE( ) : 

 

-- 모양새
NTH_VALUE(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼)