공공부하자개발 · 영어 학습 노트
홈 › SQL 고급 › 03 / 10

행 비교 분석 함수

LAG, LEAD, FIRST_VALUE, LAST_VALUE
섹션 6진행 0 / 10

3. 코드 예제

소스: sql-src/adv_03_lag_lead/01_lag_lead.sql, 02_first_last.sql. 01 은 4개월에 걸친 주문 10건, 02 는 직원(emp) 6명을 씁니다. 월별 매출은 아래 CTE 로 만들어 이후 예제에서 재사용합니다.

예제 1: 월별 매출 집계

sql
WITH monthly AS (
  SELECT
         EXTRACT(MONTH FROM ord_date) AS mon
       , SUM(amt)                     AS sales
    FROM orders
   GROUP BY EXTRACT(MONTH FROM ord_date)
)
SELECT
       mon
     , sales
  FROM monthly
 ORDER BY mon;
text
MON | SALES
----+------
1   | 1200
2   | 700
3   | 800
4   | 600
(4행)

이후 예제는 CTE 부분을 -- monthly CTE 동일 로 줄여 씁니다. 실행 파일에는 전부 들어 있습니다.

예제 2: LAG 로 전월 대비

sql
-- monthly CTE 동일
SELECT
       mon
     , sales
     , LAG(sales) OVER (ORDER BY mon)                                   AS prev_sales
     , sales - LAG(sales) OVER (ORDER BY mon)                           AS diff
     , ROUND((sales - LAG(sales) OVER (ORDER BY mon)) * 100.0
             / LAG(sales) OVER (ORDER BY mon), 1)                       AS pct
  FROM monthly
 ORDER BY mon;
text
MON | SALES | PREV_SALES | DIFF | PCT
----+-------+------------+------+------
1   | 1200  | NULL       | NULL | NULL
2   | 700   | 1200       | -500 | -41.7
3   | 800   | 700        | 100  | 14.3
4   | 600   | 800        | -200 | -25.0
(4행)

1월은 앞 달이 없어서 세 컬럼이 모두 NULL 입니다. 증감률은 * 100.0 으로 소수를 만들어 MSSQL 의 정수 나눗셈 잘림을 피합니다.

예제 3: LEAD, 기본값 인자, 오프셋 2

sql
-- monthly CTE 동일
SELECT
       mon
     , sales
     , LAG(sales, 1, 0) OVER (ORDER BY mon) AS prev_or_zero
     , LAG(sales, 2)    OVER (ORDER BY mon) AS prev2
     , LEAD(sales, 2)   OVER (ORDER BY mon) AS next2
  FROM monthly
 ORDER BY mon;
text
MON | SALES | PREV_OR_ZERO | PREV2 | NEXT2
----+-------+--------------+-------+------
1   | 1200  | 0            | NULL  | 800
2   | 700   | 1200         | NULL  | 600
3   | 800   | 700          | 1200  | NULL
4   | 600   | 800          | 700   | NULL
(4행)

LEAD 는 방향만 반대인 LAG 입니다. LAG(sales, 1, 0) 은 1월의 NULL 을 0 으로 바꾸고, 오프셋 2 는 두 달 전·후를 보므로 양 끝이 NULL 입니다.

기본값을 0 으로 두면 1월 증감률이 0 으로 나누는 계산이 됩니다. 증감률 계산에는 기본값을 쓰지 말고 NULL 로 두는 편이 안전합니다.

예제 4: 사원별 직전 주문과의 간격

sql
SELECT
       emp_id
     , id
     , ord_date
     , LAG(ord_date) OVER (PARTITION BY emp_id ORDER BY ord_date, id) AS prev_date
     , DATEDIFF(DAY, LAG(ord_date) OVER (PARTITION BY emp_id ORDER BY ord_date, id), ord_date) AS gap_days
  FROM orders
 ORDER BY emp_id, ord_date, id;
text
EMP_ID | ID | ORD_DATE   | PREV_DATE  | GAP_DAYS
-------+----+------------+------------+---------
2      | 1  | 2024-01-05 | NULL       | NULL
2      | 3  | 2024-01-15 | 2024-01-05 | 10
2      | 5  | 2024-02-05 | 2024-01-15 | 21
2      | 8  | 2024-03-01 | 2024-02-05 | 25
2      | 10 | 2024-04-03 | 2024-03-01 | 33
3      | 2  | 2024-01-08 | NULL       | NULL
3      | 6  | 2024-02-10 | 2024-01-08 | 33
3      | 9  | 2024-03-12 | 2024-02-10 | 31
5      | 4  | 2024-01-20 | NULL       | NULL
5      | 7  | 2024-02-18 | 2024-01-20 | 29
(10행)

PARTITION BY emp_id 로 사원마다 따로 앞 행을 찾아서, 사원별 첫 주문(1·2·4번)은 NULL 입니다. ORDER BY ord_date, id 로 정렬을 유일하게 만들어 같은 날짜 주문의 순서도 고정합니다.

DATEDIFF(DAY, 앞 날짜, 뒤 날짜) 는 H2 와 MSSQL 형식입니다. DB 별 날짜 차이 표기는 4절 변형 3 에서 비교합니다.