SQL Window FunctionsSQL 윈도우 함수

함수명() OVER (
  PARTITION BY 컬럼  -- 그룹 분할 (선택)
  ORDER BY 컬럼      -- 정렬 (선택)
  ROWS/RANGE BETWEEN -- 프레임 정의 (선택)
    start AND end
)

-- 프레임 옵션:
-- UNBOUNDED PRECEDING: 파티션 처음
-- CURRENT ROW: 현재 행
-- N PRECEDING/FOLLOWING: N행 이전/이후
-- UNBOUNDED FOLLOWING: 파티션 끝

-- 영업사원 매출 순위
SELECT
  name,
  department,
  sales,
  ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_num,
  RANK()       OVER (ORDER BY sales DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank,
  NTILE(4)     OVER (ORDER BY sales DESC) AS quartile,
  -- 부서 내 순위
  RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS dept_rank
FROM salespeople;

-- ROW_NUMBER: 1,2,3,4,5 (동점도 다른 번호)
-- RANK:       1,2,2,4,5 (동점 같은 번호, 건너뜀)
-- DENSE_RANK: 1,2,2,3,4 (동점 같은 번호, 안 건너뜀)

-- 누적 합계, 이동 평균
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
  revenue / SUM(revenue) OVER () * 100 AS pct_of_total
FROM daily_sales;

-- 1. 각 부서에서 상위 3명 (TOP-N per group)
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

-- 2. 연속 중복 제거 (Sessionization)
SELECT user_id, page,
  LAG(page) OVER (PARTITION BY user_id ORDER BY ts) AS prev_page
FROM page_views
WHERE page != COALESCE(LAG(page) OVER (PARTITION BY user_id ORDER BY ts), '');

-- 3. 갭과 아일랜드 (Gap and Islands)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_days
FROM (
  SELECT user_id, login_date,
    login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) * INTERVAL '1 day'
    AS grp
  FROM logins
) t
GROUP BY user_id, grp;