LAG, LEAD와 시계열 패턴

LAG와 LEAD: 앞뒤 행 살펴보기

이전 행

SELECT id, user_id, created_at, total,
       LAG(total) OVER (
         PARTITION BY user_id ORDER BY created_at
       ) AS prev_total
FROM orders;

전일 대비 변화량

SELECT day, revenue,
       revenue - LAG(revenue) OVER (ORDER BY day) AS delta
FROM daily_revenue
ORDER BY day;

전년 동월 대비

SELECT month, revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year_revenue,
       revenue / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) - 1 AS yoy
FROM monthly_revenue;

누락 구간 찾기

WITH d AS (
  SELECT day, LAG(day) OVER (ORDER BY day) AS prev_day FROM daily_signups
)
SELECT day, prev_day, day - prev_day AS gap_days
FROM d
WHERE day - prev_day > 1;

세션화

WITH gaps AS (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
                 > INTERVAL '30 min'
              THEN 1 ELSE 0 END AS new_session
  FROM events
)
SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps;

그룹의 첫 값과 마지막 값

SELECT id, total,
       FIRST_VALUE(total) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order,
       LAST_VALUE(total)  OVER (PARTITION BY user_id ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order
FROM orders;
-- LAST_VALUE needs the explicit frame, otherwise default frame ends at current row.

NTH_VALUE

SELECT id,
       NTH_VALUE(total, 2) OVER (
         PARTITION BY user_id ORDER BY total DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_largest
FROM orders;

시계열의 기본값

SELECT day, revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM daily_revenue;
-- delta on first day = revenue itself (vs NULL)

셀프 조인으로 동일하게 표현하기

성능

요약

빠른 확인