ANSI SQL: 윈도우 함수 LAG()

※ ANSI SQL Standard Window Function LAG().
 
안녕하세요. 듀스트림입니다.
 
오늘의 포스팅은 오랜만에 SQL 관련이에요.
ANSI 표준 윈도우 함수인 LAG()에 대해 한번 알아보겠습니다.
 
이걸 어디다 쓰냐고요?
 
이제 어떤 시스템을 만들었을 때 정산 관련 처리를 해야 되잖아요? 거기에도 사용하고, 주식 종가 대비 변화율 계산과 어떤 데이터의 변화 시점 추적 등에 사용합니다. (시계열 데이터나 순차적인 데이터에서 이전 값과의 비교나 차이를 계산할 때 사용)

---

1. LAG() 함수란?

ANSI 표준 윈도우 함수(Window Function) 중 하나로, 이전 행의 값을 현재 행에서 참조할 수 있게 해주는 함수입니다
 
문법은 아래와 같습니다.

LAG(value_expression [, offset [, default_value]])
  OVER (
  [PARTITION BY partition_expression]
  ORDER BY sort_expression
)

파라미터	의미	비고
value_expression	컬럼, 계산식	컬럼, 계산식
offset	몇 번째 이전 행을 참조할지 (기본 1)	생략 가능
default	이전 행이 없을 때 반환할 기본값	생략 시 NULL
OVER 절 PARTITION BY	파티션 구분 기준	그룹화 (없으면 전체 대상)
OVER 절 ORDER BY	파티션 내 정렬 기준	이전 행 판단 기준

※ PostgreSQL에서는 LAG()를 ORDER BY 없이 사용하는 것이 문법적으로 허용됩니다. 그러나 그 경우 행의 순서가 불확실하며, 논리적으로 올바른 결과를 보장하지 않습니다.
(LAG()에 ORDER BY가 없으면, PostgreSQL은 내부적으로 디스크 스캔 순서 또는 플래너 순서대로 “이전 행”을 결정)

---

2. 예제

2.1. 샘플 데이터 생성

CREATE TEMP TABLE sales (
    sales_id SERIAL PRIMARY KEY,
    customer TEXT,
    sales_date DATE,
    amount NUMERIC
);

INSERT INTO sales (customer, sales_date, amount) VALUES
('곽두팔', '2024-01-01', 100000),
('곽두팔', '2024-01-15', 120000),
('곽두팔', '2024-02-01', 80000),
('곽두팔', '2024-02-20', 150000),
('최득출', '2024-01-05', 200000),
('최득출', '2024-01-20', 220000),
('최득출', '2024-02-10', 180000);

---

2.2. 예제 1: 고객별 전 거래 대비 금액 변화
▸ 전월 대비 증감 금액, 이상 탐지에 활용

SELECT
  customer,
  sales_date,
  amount,
  LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date) AS diff
FROM sales;

[그림 1] 예제 1

---

2.3. 예제 2: 증감률(%) 계산
▸ 매출 증감률, 투자 수익률 등 비율 기반 분석에 활용

SELECT
  customer,
  sales_date,
  amount,
  LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date) AS prev_amount,
  ROUND(
    CASE
      WHEN LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date) IS NULL THEN NULL
      ELSE ((amount - LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date)) 
             / LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date)) * 100
    END, 2
  ) AS diff_percent
FROM sales;

[그림 2] 예제 2

---

2.4. 예제 3: 월 첫 거래와 마지막 거래 비교
▸ 월간 시작/마감 매출 비교 등에 활용

SELECT
  customer,
  sales_date,
  amount,
  LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date) AS prev_amount,
  FIRST_VALUE(amount) OVER (PARTITION BY customer, DATE_TRUNC('month', sales_date) ORDER BY sales_date) AS month_first,
  LAST_VALUE(amount) OVER (PARTITION BY customer, DATE_TRUNC('month', sales_date) ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS month_last
FROM sales;

[그림 3] 예제 3

---

2.5. 예제 4: 거래 간 날짜 차이 계산
▸ 휴면 고객 탐지, 이벤트 알림 발송 대상 체크 등에 활용

SELECT
  customer,
  sales_date,
  amount,
  LAG(sales_date) OVER (PARTITION BY customer ORDER BY sales_date) AS prev_date,
  sales_date - LAG(sales_date) OVER (PARTITION BY customer ORDER BY sales_date) AS days_since_last
FROM sales;

[그림 4] 예제 4

---

2.6. 예제 5: 새로운 분기 시작 감지
▸ 정산 기간 분기 시작 알림, 기간별 마감 처리 등에 활용

SELECT *,
       CASE
         WHEN DATE_TRUNC('quarter', sales_date) != DATE_TRUNC('quarter', LAG(sales_date) OVER (PARTITION BY customer ORDER BY sales_date))
         THEN '신규 분기 시작'
         ELSE NULL
       END AS flag
FROM sales;

[그림 5] 예제 5

---

2.7. 정리

활용 목적	함수 활용	예시
거래 대비 증감	LAG() + 산술	금액, 날짜 차리
증감률 분석	LAG() + 나눗셈	ROI, 증감률
기간별 분석	LAG() + DATE_TRUNC()	월초, 분기 시작 판단
전 거래 참조	LAG()	누적 계산, 비교 분석
조건 분기 처리	CASE WHEN ... THEN ...	새 기간 감지

 
+ 추가로 LEAD() 함수는 다음 행을 참조하는 함수이고 LAG()와 같은 방식으로 사용됩니다.

---

3. 고려 사항

3.1. 성능 최적화: 대용량 데이터에서 LAG() 함수를 사용할 경우, 적절한 인덱스를 설정하여 성능을 최적화하는 것이 중요합니다.

-- 예시 LAG() 함수
LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date)

-- 유효 인덱스
CREATE INDEX idx_sales_customer_date ON sales (customer, sales_date);

---

3.2. NULL 처리: 이전 행이 존재하지 않을 경우 NULL이 반환되므로, 이를 처리하기 위한 COALESCE() 함수 등의 활용을 고려해야 합니다.

-- COALESCE 처리
SELECT
  customer,
  sales_date,
  amount,
  LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date) AS prev_amount,
  COALESCE(LAG(amount) OVER (PARTITION BY customer ORDER BY sales_date), 0) AS safe_prev_amount
FROM sales
ORDER BY customer, sales_date;

-- PostgreSQL은 LAG(..., ..., default_value) 형태의 세 번째 인자도 지원
SELECT
  customer,
  sales_date,
  amount,
  LAG(amount, 1, 0) OVER (PARTITION BY customer ORDER BY sales_date) AS prev_amount_with_default
FROM sales
ORDER BY customer, sales_date;

항목	설명
LAG(amount)	이전 행의 amount, 없으면 NULL
LAG(amount, 1)	1행 이전, 없으면 NULL
LAG(amount, 1, 0)	1행 이전, 없으면 0 반환

---

3.3. 다른 윈도우 함수와의 조합: LEAD(), FIRST_VALUE(), LAST_VALUE(), ROWS_BETWEEN, RANGE_BETWEEN 등과 함께 사용하여 다양한 분석을 수행할 수 있습니다.

---

벌써 5월입니다. 시간이 참 빠르게 흘러가는 거 같습니다.
 
오늘은 여기까지~