시간 데이터용 윈도우 함수: LEAD(), LAG()

이제 우리 목표는 한 단계 더 나아가서 시간 데이터 분석에 윈도우 함수를 써보는 거야. 준비됐지? 커피 한 잔 챙겼으면 좋겠네, 왜냐면 이거 꽤 재밌을 거거든.

자, 항상 그렇듯이 먼저 제일 중요한 질문부터 답하자: 우리한테 윈도우 함수(LEAD(), LAG())가 왜 필요할까? 예를 들어, 너가 시간 데이터, 즉 이벤트 로그, 근무 시간, 시계열 데이터 등, 이벤트 순서가 중요한 데이터를 다루고 있다고 해봐.

예를 들어, 이런 걸 하고 싶을 때:

• 현재 이벤트 다음에 언제 이벤트가 일어났는지 알고 싶을 때.
• 현재 이벤트와 이전 이벤트 사이의 시간 차이를 계산하고 싶을 때.
• 데이터를 정렬해서 레코드 간 차이를 계산하고 싶을 때.

여기서 두 개의 멋진 함수가 등장하지: LEAD()랑 LAG(). 이 함수들은 특정 윈도우 안에서 이전 혹은 다음 행의 데이터를 가져올 수 있게 해줘. 마치 마법의 책처럼, 현재 페이지 넘기지 않고도 다음 페이지 내용을 볼 수 있는 거지.

LEAD()와 LAG(): 문법이랑 기본 원리

둘 다 비슷한 문법을 써:

LEAD(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)
LAG(column_name, [offset], [default_value]) OVER (PARTITION BY column_name ORDER BY column_name)

• column_name — 우리가 데이터를 가져오고 싶은 컬럼이야.
• offset (옵션) — 현재 행 기준으로 얼마나 떨어진 행을 볼지 정하는 거야. 기본값은 1이야.
• default_value (옵션) — 만약 원하는 만큼 떨어진 행이 없으면(예: 마지막 행일 때) 이 값을 반환해.
• OVER() — 여기서 "윈도우"를 지정해. 보통 ORDER BY를 쓰고, 가끔 PARTITION BY로 그룹을 나누기도 해.

예시: 그냥 LEAD()랑 LAG() 써보기

우리 실험용으로 events라는 간단한 테이블을 만들어보자:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_date TIMESTAMP NOT NULL
);

INSERT INTO events (event_name, event_date)
VALUES
    ('이벤트 A', '2023-10-01 10:00:00'),
    ('이벤트 B', '2023-10-01 11:00:00'),
    ('이벤트 C', '2023-10-01 12:00:00'),
    ('이벤트 D', '2023-10-01 13:00:00');

이제 각 이벤트 기준으로 이전/다음 이벤트가 언제였는지 보고 싶어:

SELECT
    id,
    event_name,
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS 이전_이벤트,
    LEAD(event_date) OVER (ORDER BY event_date) AS 다음_이벤트
FROM events;

결과는 이렇게 나와:

여기서 LAG()는 이전 행에서 데이터를 가져오고, LEAD()는 다음 행에서 가져와. 첫 번째 이벤트는 이전 게 없고, 마지막 이벤트는 다음 게 없으니까 NULL이 나오는 거지.

예시: 이벤트 간 시간 차이

가끔 이벤트 사이에 얼마나 시간이 지났는지 알고 싶을 때가 있어. 이럴 땐 그냥 시간끼리 빼주면 돼:

SELECT
    id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (ORDER BY event_date) AS 지난_이벤트_이후_시간
FROM events;

결과:

예시: PARTITION BY 사용하기

예를 들어, 여러 명의 사용자가 있고 각자 이벤트가 있다고 해보자. 각 사용자별로 이벤트 간 시간 차이를 구하고 싶어.

테이블을 업데이트해서 user_id 컬럼을 추가해보자:

ALTER TABLE events ADD COLUMN user_id INT;

UPDATE events SET user_id = 1 WHERE id <= 2;
UPDATE events SET user_id = 2 WHERE id > 2;

이제 사용자 두 명이 생겼어. PARTITION BY로 각 그룹 안에서 계산해보자:

SELECT
    user_id,
    event_name,
    event_date,
    event_date - LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS 지난_이벤트_이후_시간
FROM events;

결과:

실전에서 어떻게 쓰는지 예시

1. 이벤트 로그: 예를 들어 로그인/로그아웃 사이 시간 분석.
2. 타임 트래킹: 특정 작업에 쓴 시간 계산.
3. 행동 분석: 온라인 쇼핑몰에서 고객 행동 순서 분석.
4. 누적 지표 계산: 시계열 데이터 다룰 때 윈도우 함수 활용.

흔히 하는 실수들

LEAD()랑 LAG() 쓸 때 주의해야 할 점:

• OVER() 안에 ORDER BY를 빼먹는 경우. 이거 없으면 함수가 행 순서를 모름.
• 시간 간격이나 데이터 타입 문제 (TIMESTAMP vs DATE).
• 윈도우 범위 처음/끝에서 나오는 NULL값을 무시하는 경우.

이런 실수 피하려면, 항상 데이터 체크하고, 연산할 윈도우를 제대로 지정했는지 꼭 확인하자!

2

과제

SQL SELF, 레벨 32, 레슨 3

잠금

이전 및 다음 이벤트 추출

이전 및 다음 이벤트 추출

열림