윈도우 함수 기초

함수(인자) OVER (
    PARTITION BY 그룹_컬럼     -- 윈도우를 나누는 기준 (생략 시 전체)
    ORDER BY 정렬_컬럼         -- 윈도우 내 정렬 순서
    ROWS/RANGE BETWEEN ...    -- 프레임 범위 (고급, 다음 절에서)
)

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

SELECT name, salary,
       AVG(salary) OVER () AS total_avg,
       salary - AVG(salary) OVER () AS diff_from_total
FROM employees;

-- ORDER BY 없음: 전체 합계
SELECT name, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

-- ORDER BY 있음: 누적 합계 (Running Total)
SELECT name, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total
FROM employees;

-- 부서 + 직급별 평균 급여
SELECT name, department, position, salary,
       AVG(salary) OVER (
           PARTITION BY department, position
       ) AS dept_pos_avg
FROM employees;

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees;

SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

SELECT student_name, score,
       CASE NTILE(5) OVER (ORDER BY score DESC)
           WHEN 1 THEN 'A'
           WHEN 2 THEN 'B'
           WHEN 3 THEN 'C'
           WHEN 4 THEN 'D'
           WHEN 5 THEN 'F'
       END AS grade
FROM exam_results;

SELECT name, department, salary,
       -- 부서별 통계 (개별 행 유지)
       COUNT(*) OVER (PARTITION BY department) AS dept_count,
       SUM(salary) OVER (PARTITION BY department) AS dept_total,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       MAX(salary) OVER (PARTITION BY department) AS dept_max,
       MIN(salary) OVER (PARTITION BY department) AS dept_min
FROM employees;

SELECT name, department, salary,
       -- 부서 내 급여 비율
       ROUND(COALESCE(salary, 0) * 100.0 /
             NULLIF(SUM(COALESCE(salary, 0)) OVER (PARTITION BY department), 0), 1)
           AS dept_pct,
       -- 전체 급여 비율
       ROUND(COALESCE(salary, 0) * 100.0 /
             NULLIF(SUM(COALESCE(salary, 0)) OVER (), 0), 1)
           AS total_pct
FROM employees;

SELECT order_date, order_id, amount,
       SUM(amount) OVER (
           ORDER BY order_date, order_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       AVG(amount) OVER (
           ORDER BY order_date, order_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_avg
FROM daily_sales;

SELECT * FROM (
    SELECT p.*,
           ROW_NUMBER() OVER (
               PARTITION BY category
               ORDER BY price DESC, product_id
           ) AS rn
    FROM products p
) ranked
WHERE rn <= 3;

-- 부서별 최근 입사 3명
SELECT * FROM (
    SELECT emp.*,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY hire_date DESC, employee_id
           ) AS rn
    FROM employees emp
) t WHERE rn <= 3;

-- 고객별 최대 금액 주문 1건
SELECT * FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY total_amount DESC, order_id
           ) AS rn
    FROM orders o
) t WHERE rn = 1;

-- ROW_NUMBER: 정확히 2개만 반환 (타이가 있어도)
SELECT * FROM (
    SELECT p.*, ROW_NUMBER() OVER (ORDER BY price DESC, product_id) AS rn
    FROM products p
) t WHERE rn <= 2;
-- 결과: 2행 (product_id로 동률 내 순서를 고정)

-- RANK: 동점자 모두 포함 (3개 이상 반환 가능)
SELECT * FROM (
    SELECT p.*, RANK() OVER (ORDER BY price DESC) AS rnk
    FROM products p
) t WHERE rnk <= 2;
-- 결과: 공동 2위가 있으면 3행 이상

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

-- 해결: 서브쿼리 또는 CTE 사용
SELECT * FROM (
    SELECT name, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;     -- 정상 동작!

-- CTE 사용
WITH ranked AS (
    SELECT name, salary,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

-- 중복되는 OVER 절
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
       SUM(salary)  OVER (PARTITION BY department ORDER BY salary DESC) AS running
FROM employees;

-- WINDOW 절로 정리
SELECT name, department, salary,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rnk,
       SUM(salary)  OVER w AS running
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

-- 위험: ORDER BY가 없으면 번호가 매번 달라질 수 있음
SELECT name, ROW_NUMBER() OVER () AS rn FROM employees;

-- 올바른 사용: 반드시 ORDER BY 지정
SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS rn FROM employees;

-- GROUP BY 먼저 실행되고, 그 결과에 윈도우 함수 적용
SELECT department, COUNT(*) AS cnt,
       RANK() OVER (ORDER BY COUNT(*) DESC) AS popularity_rank
FROM employees
GROUP BY department;
-- 윈도우 함수는 GROUP BY 이후의 결과 행에 대해 동작

-- 윈도우 함수 최적화를 위한 인덱스
CREATE INDEX idx_emp_dept_salary
ON employees (department, salary DESC);

-- 이 인덱스는 아래 쿼리에서 정렬 비용을 줄이는 데 도움될 수 있음
SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees;