윈도우 함수

-- ============================================
-- 실무 분석 쿼리 예제
-- ============================================

-- 1. 부서별 Top 3 급여자 추출
WITH ranked AS (
    SELECT
        name,
        department,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;


-- 2. 고객별 최근 주문 3개
SELECT * FROM (
    SELECT
        customer_id,
        order_id,
        order_date,
        total_amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
    FROM orders
) t WHERE rn <= 3;


-- 3. 월별 매출 + 전월 대비 증감률
SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as revenue,
    LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as prev_month,
    ROUND(
        (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)))
        * 100.0 / NULLIF(LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)), 0),
        2
    ) as growth_rate_pct
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;


-- 4. 부서별 급여 분포 (본인 vs 부서 평균/최대)
SELECT
    name,
    department,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY department), 0) as dept_avg,
    MAX(salary) OVER (PARTITION BY department) as dept_max,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) as top_earner
FROM employees;


-- 5. 제품별 카테고리 내 순위 + 전체 순위
SELECT
    product_name,
    category,
    sales,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) as category_rank,
    RANK() OVER (ORDER BY sales DESC) as overall_rank,
    NTILE(4) OVER (ORDER BY sales DESC) as quartile  -- 4분위
FROM product_sales;

# pandas에서 윈도우 함수 구현
import pandas as pd

# 샘플 데이터
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
    'department': ['Sales', 'Sales', 'IT', 'IT', 'IT'],
    'salary': [60000, 55000, 70000, 65000, 70000]
})

# ROW_NUMBER: 그룹별 순번
df['dept_row_num'] = df.groupby('department').cumcount() + 1

# RANK: 그룹별 순위 (동점 시 같은 순위)
df['dept_rank'] = df.groupby('department')['salary'].rank(method='min', ascending=False)

# DENSE_RANK
df['dept_dense_rank'] = df.groupby('department')['salary'].rank(method='dense', ascending=False)

# LAG: 이전 행 값
df_sorted = df.sort_values(['department', 'salary'], ascending=[True, False])
df_sorted['prev_salary'] = df_sorted.groupby('department')['salary'].shift(1)

# 누적 합계 (Running Total)
df['cumsum_salary'] = df.groupby('department')['salary'].cumsum()

# 이동 평균 (Rolling)
sales_data = pd.DataFrame({
    'date': pd.date_range('2024-01-01', periods=10),
    'amount': [100, 120, 90, 110, 130, 100, 140, 120, 150, 130]
})
sales_data['moving_avg_3'] = sales_data['amount'].rolling(window=3).mean()

# 전체 대비 비율
df['pct_of_total'] = df['salary'] / df['salary'].sum() * 100
df['pct_of_dept'] = df['salary'] / df.groupby('department')['salary'].transform('sum') * 100

print("=== 부서별 순위 ===")
print(df[['name', 'department', 'salary', 'dept_rank', 'dept_dense_rank']])

print("\n=== 누적 합계 ===")
print(df[['name', 'department', 'salary', 'cumsum_salary']])

print("\n=== 이동 평균 ===")
print(sales_data[['date', 'amount', 'moving_avg_3']])