SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id;

SELECT
    product_name,
    category_id,
    price,
    AVG(price) OVER (PARTITION BY category_id) AS category_avg
FROM products;

실행 완료 또는 조회 결과가 표시됩니다.

함수명() OVER (
    PARTITION BY 그룹기준컬럼
    ORDER BY 정렬기준컬럼 [ASC|DESC]
    ROWS BETWEEN 시작 AND 끝
)

SELECT
    product_name,
    category_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num,
    RANK()       OVER (PARTITION BY category_id ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS dense_rank,
    NTILE(4)     OVER (PARTITION BY category_id ORDER BY price DESC) AS quartile
FROM products;

WITH ranked_products AS (
    SELECT
        product_name,
        category_id,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY category_id
            ORDER BY price DESC
        ) AS rn
    FROM products
)
SELECT product_name, category_id, price
FROM ranked_products
WHERE rn <= 3;

SELECT
    order_id,
    customer_id,
    total_amount,
    AVG(total_amount) OVER ()                              AS overall_avg,
    SUM(total_amount) OVER (PARTITION BY customer_id)      AS customer_total,
    total_amount / SUM(total_amount) OVER () * 100         AS pct_of_total
FROM orders;

[FROM·WHERE·GROUP BY·HAVING까지 끝난 행 집합]   (윈도우는 그 다음 단계)
   │
   ① 대상 행 집합 확정              (WHERE·HAVING 다 지난 뒤의 행들)
   │
   ② PARTITION BY로 분할            (cat별로 창을 나눔 / 없으면 전체가 한 창)
   │
   ③ ORDER BY로 창 안 정렬          (순위·누적·LAG의 '순서'가 여기서 정해짐)
   │
   ④ 프레임 확정 (ROWS·RANGE)       (행마다 계산에 포함할 '범위'를 정함)
   │     ORDER BY만 있으면 기본 = 창 시작 ~ 현재 행
   │
   ⑤ 각 행에 함수 적용 (행은 유지)   (그 행의 프레임을 보고 값 하나 산출)
   ▼
[입력과 같은 행 수 + 윈도우 컬럼이 붙은 결과]

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month)          AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS mom_change,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100,
        2
    ) AS mom_growth_pct
FROM monthly_sales
ORDER BY month;

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY DATE_TRUNC('year', order_date)
        ORDER BY order_date
    ) AS ytd_revenue
FROM daily_sales;

SELECT
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS moving_avg_30d
FROM daily_sales
ORDER BY sale_date;

SELECT
    product_name,
    category_id,
    price,
    FIRST_VALUE(product_name) OVER (
        PARTITION BY category_id
        ORDER BY price DESC
    ) AS most_expensive_in_category,
    LAST_VALUE(product_name) OVER (
        PARTITION BY category_id
        ORDER BY price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS least_expensive_in_category
FROM products;

SELECT
    rep_name,
    region,
    monthly_sales,
    RANK() OVER (ORDER BY monthly_sales DESC)                           AS national_rank,
    RANK() OVER (PARTITION BY region ORDER BY monthly_sales DESC)       AS regional_rank,
    ROUND(PERCENT_RANK() OVER (ORDER BY monthly_sales) * 100, 1)       AS percentile,
    ROUND(
        monthly_sales / AVG(monthly_sales) OVER (PARTITION BY region) * 100,
        1
    )                                                                   AS vs_region_avg_pct,
    monthly_sales - LAG(monthly_sales) OVER (
        PARTITION BY rep_name ORDER BY month
    )                                                                   AS mom_change
FROM sales_summary
WHERE month = '2024-03-01';

WITH ranked AS (
    SELECT *, RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rnk
    FROM products
)
SELECT * FROM ranked WHERE rnk = 1;