행 비교 분석 함수

2. 핵심 원리

2.1 함수 네 가지

LAG·LEAD 는 OVER 안에 ORDER BY 가 필수이고, PARTITION BY 는 선택입니다. 두 번째 인자 n 은 오프셋으로 생략하면 1, 세 번째 인자 d 는 기본값으로 생략하면 NULL 입니다.

LAG(컬럼 [, 오프셋 [, 기본값]]) OVER ([PARTITION BY ...] ORDER BY ...)

2.2 NULL 이 나오는 자리

그룹의 첫 행에는 앞 행이 없으므로 LAG 가 NULL 을 돌려줍니다. 마지막 행의 LEAD 도 같습니다. PARTITION BY 를 쓰면 그룹마다 첫 행이 NULL 이 됩니다.

NULL 이 계산에 섞이면 결과도 NULL 이므로, 0 으로 두고 싶으면 세 번째 인자를 씁니다. LAG(sales, 1, 0) 은 앞 행이 없을 때 0 을 돌려줍니다. 앞 행의 값 자체가 NULL 이면 기본값이 아니라 NULL 이 나옵니다.

핵심

LAG·LEAD 는 프레임의 영향을 받지 않고 항상 정렬 순서의 앞뒤 행을 봅니다. FIRST_VALUE·LAST_VALUE 는 프레임의 영향을 받으므로, 프레임을 생략하면 기본 프레임(현재 행까지)으로 계산됩니다.

2.3 LAST_VALUE 함정

고급 02 에서 본 대로 ORDER BY 만 쓰고 프레임을 생략하면 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 입니다. 프레임의 끝이 현재 행이므로 LAST_VALUE 는 "현재 행까지의 마지막 값", 즉 자기 자신을 돌려줍니다.

정렬 값이 같은 동점 행이 있으면 그 행들 중 하나의 값이 나옵니다. 그룹의 진짜 마지막 값을 원하면 프레임 끝을 UNBOUNDED FOLLOWING 으로 늘립니다.

FIRST_VALUE 는 프레임 시작이 그룹의 첫 행이라 기본 프레임으로도 정상입니다.

2.4 DB 별 지원 범위

MSSQL 2005~2008 에는 이 함수들이 없습니다. 셀프 조인이나 ROW_NUMBER() 로 번호를 매긴 뒤 번호 = 번호 - 1 로 조인해 대체합니다. MySQL 5.7 이하도 윈도 함수가 없습니다.

IGNORE NULLS 는 NULL 을 건너뛰고 가장 가까운 비어 있지 않은 값을 가져오는 옵션입니다. H2 로는 실행하지 않고 위 표로만 정리합니다.