윈도우 함수 심화

LAG(컬럼, N, 기본값) OVER (
    [PARTITION BY 그룹컬럼]
    ORDER BY 정렬컬럼
)
-- N: 몇 행 전인지 (기본값 1)
-- 기본값: NULL 대신 사용할 값 (선택)

SELECT month, revenue,
       LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_sales;

-- 결과:
-- 1월 | 1000 | NULL | NULL
-- 2월 | 1200 | 1000 | 200
-- 3월 | 900  | 1200 | -300

SELECT name, hire_date,
       LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS next_hire,
       LEAD(hire_date, 1) OVER (ORDER BY hire_date) - hire_date AS gap_days
FROM employees;

-- 다음 입사까지 며칠 걸렸는지 계산

-- 기본값 지정으로 NULL 방지
SELECT month, revenue,
       LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month
FROM monthly_sales;

-- 2행 전 참조
SELECT date, price,
       LAG(price, 2) OVER (ORDER BY date) AS two_days_ago
FROM stock_prices;

-- 부서별 이전 사원 급여 비교
SELECT dept, name, salary,
       salary - LAG(salary) OVER (PARTITION BY dept ORDER BY salary DESC)
           AS diff_from_higher
FROM employees;

SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS cumulative
FROM monthly_sales;

-- 결과:
-- 1월 | 1000 | 1000
-- 2월 | 1200 | 2200
-- 3월 | 900  | 3100
-- 4월 | 1500 | 4600

SELECT dept, month, revenue,
       SUM(revenue) OVER (
           PARTITION BY dept ORDER BY month
       ) AS dept_cumulative
FROM dept_sales;
-- 부서별로 독립적인 누적합 계산

SELECT dept, revenue,
       SUM(revenue) OVER () AS total,
       ROUND(revenue * 100.0 / SUM(revenue) OVER (), 1) AS pct
FROM dept_revenue;

-- SUM() OVER ()는 ORDER BY 없이 전체 합계
-- 결과: 영업 | 5000 | 12000 | 41.7%

-- 부서별 입사 순서별 인원 누적
SELECT dept, name, hire_date,
       COUNT(*) OVER (PARTITION BY dept ORDER BY hire_date) AS running_count,
       SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS running_total
FROM employees;

-- 앞뒤 1행을 포함한 이동 평균 (3행 윈도우)
AVG(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

-- 처음부터 현재까지 누적합 (기본 프레임)
SUM(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

-- 현재부터 끝까지의 합
SUM(revenue) OVER (
    ORDER BY month
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)

ROWS: 물리적 행 수 기준
  → ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  → 정확히 이전 2행 + 현재 행

RANGE: 논리적 값 범위 기준
  → RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
  → ORDER BY 컬럼 값이 현재 - 2 이상인 행

차이가 나는 경우:
  ORDER BY 컬럼에 동일 값이 있을 때
  ROWS: 물리적 순서에 따라 정확히 N행
  RANGE: 같은 값의 행들을 모두 포함

Oracle, PostgreSQL은 RANGE 지원
MySQL 8.0+도 RANGE 지원

-- 급여 기준 4분위
SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- 결과 (8명 기준):
-- 김과장 | 9000 | 1 (상위 25%)
-- 이과장 | 8000 | 1
-- 박대리 | 7000 | 2
-- 최대리 | 6000 | 2
-- 정사원 | 5000 | 3
-- 한사원 | 4000 | 3
-- 오인턴 | 3000 | 4
-- 서인턴 | 2000 | 4 (하위 25%)

-- 10분위 (Decile) 분류
SELECT name, score,
       NTILE(10) OVER (ORDER BY score DESC) AS decile
FROM test_results;

-- 성과 등급 자동 부여
SELECT name, sales,
       CASE NTILE(5) OVER (ORDER BY sales DESC)
           WHEN 1 THEN 'S'
           WHEN 2 THEN 'A'
           WHEN 3 THEN 'B'
           WHEN 4 THEN 'C'
           WHEN 5 THEN 'D'
       END AS grade
FROM employee_sales;

SELECT dept, name, salary,
       FIRST_VALUE(name) OVER (
           PARTITION BY dept ORDER BY salary DESC
       ) AS highest_earner,
       LAST_VALUE(name) OVER (
           PARTITION BY dept ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS lowest_earner
FROM employees;

-- LAST_VALUE 주의: 기본 프레임이 CURRENT ROW까지이므로
-- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 명시 필수!

-- Oracle, PostgreSQL, MySQL 8.0+ 지원
SELECT dept, name, salary,
       NTH_VALUE(name, 2) OVER (
           PARTITION BY dept ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_highest
FROM employees;
-- 각 부서에서 두 번째로 높은 급여를 받는 사람

-- 0~1 사이 값: (순위 - 1) / (총 행 - 1)
SELECT name, salary,
       RANK() OVER (ORDER BY salary) AS rnk,
       ROUND(PERCENT_RANK() OVER (ORDER BY salary), 2) AS pct_rank
FROM employees;

-- 결과 (5명):
-- 최인턴 | 2000 | 1 | 0.00
-- 한사원 | 4000 | 2 | 0.25
-- 박대리 | 6000 | 3 | 0.50
-- 이과장 | 8000 | 4 | 0.75
-- 김부장 | 10000| 5 | 1.00

-- 오류! WHERE에서 윈도우 함수 사용 불가
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WHERE rnk <= 3;  -- ERROR!

-- 해결: 서브쿼리 또는 CTE 사용
WITH ranked AS (
    SELECT name, salary,
           RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT name, salary, rnk
FROM ranked
WHERE rnk <= 3;

-- 매출이 전월 대비 연속 증가한 구간
WITH growth AS (
    SELECT month, revenue,
           CASE WHEN revenue > LAG(revenue) OVER (ORDER BY month)
                THEN 1 ELSE 0 END AS is_growth
    FROM monthly_sales
),
grouped AS (
    SELECT month, revenue, is_growth,
           SUM(CASE WHEN is_growth = 0 THEN 1 ELSE 0 END)
               OVER (ORDER BY month) AS grp
    FROM growth
)
SELECT MIN(month) AS start_month, MAX(month) AS end_month,
       COUNT(*) AS streak_length
FROM grouped
WHERE is_growth = 1
GROUP BY grp;

SELECT date, value,
       value - AVG(value) OVER (
           ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS deviation,
       CASE WHEN ABS(value - AVG(value) OVER (
           ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       )) > 2 * STDDEV(value) OVER (
           ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) THEN 'OUTLIER' ELSE 'NORMAL' END AS status
FROM sensor_data;

WITH product_sales AS (
    SELECT product, SUM(amount) AS total
    FROM sales
    GROUP BY product
)
SELECT product, total,
       SUM(total) OVER (ORDER BY total DESC) AS running_total,
       ROUND(SUM(total) OVER (ORDER BY total DESC) * 100.0
           / SUM(total) OVER (), 1) AS cumulative_pct
FROM product_sales
ORDER BY total DESC;
-- 상위 20% 상품이 매출의 80%를 차지하는지 확인 (파레토 법칙)

SELECT month, revenue,
       -- 전월 대비 성장률
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month) * 100, 1) AS growth_pct,
       -- 누적 매출
       SUM(revenue) OVER (ORDER BY month) AS cumulative,
       -- 3개월 이동 평균
       ROUND(AVG(revenue) OVER (
           ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       )) AS ma3,
       -- 전체 비중
       ROUND(revenue / SUM(revenue) OVER () * 100, 1) AS pct_of_total,
       -- 4분위
       NTILE(4) OVER (ORDER BY revenue) AS quartile
FROM monthly_sales;