3. 윈도우 함수 문제

🟢 연습문제 3-1: 기본 순위

• RANK() : 동일한 점수에 대해서 같은 순위 매김
  => 상품별 총 판매량 순위에 적절
• ROW_NUMBER() : 동일한 점수여도 하나의 순위만 매김
  => 카테고리 내 고객별 순위에 적절 

[문제]
상품별 총 판매량을 구하고, 판매량 순위를 매기시오.

[테이블 구조]
products: product_id, product_name, category
order_items: order_item_id, order_id, product_id, quantity

-- 답
SELECT A.product_id, A.product_name
    , SUM(B.quantity) TOTAL_QUNAT
    , RANK() OVER(ORDER BY SUM(B.quantity) DESC) RN
FROM PRODUCTS A
INNER JOIN ORDER_ITEMS B
    ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY A.product_id, A.product_name
ORDER BY RN DESC

 

실전문제 3-1: 월별 매출 성장률과 이동평균

• GROUPBY할 때,  (YEAR, MONTH)가 인덱스로 사용되어 DATE_FORMAT보다는 연산량이 적어 효율적임
• 전월 대비 : LAG함수를 써서 직전달 정보 가져오기 ORDER BY 는 월별 오름차순 설정
• 3개월 이동 평균 : AVG함수 사용 ORDER BY 월별 오름차순 설정 후 2개 직전달 합계 가져오기
  • 직전달 합계 정보 가져오기 : ROWS 2 PRECEDING(앞선 이전의)
• 연초 대비 누적 증감률 : 전월 대비 증감율과 방식은 동일하나, 연초 값을 FIRST_VALUE 함수 사용

[문제]
2023년 월별 매출 데이터를 분석하여 다음을 구하시오:
1. 각 월의 총 매출
2. 전월 대비 증감률 (%) : 전월 대비 증감률 = (현재월 매출 - 전월 매출) / 전월 매출 × 100
3. 3개월 이동평균
4. 연초 대비 누적 증감률
결과는 월 순서대로 정렬하시오.

[테이블 구조]
orders: order_id, customer_id, order_date, amount, status

-- 답
SELECT  YEAR(ORDER_DATE) ORDER_YEAR
	, MONTH(ORDER_DATE) ORDER_MONTH
    -- 각 월의 총 매출
    , SUM(AMOUNT) SALE_SUM
    -- 전월 대비 증감률
    , ROUND(
    	(SUM(AMOUNT) - LAG(SUM(AMOUNT)) OVER(ORDER BY YEAR(ORDER_DATE), MONTH(ORDER_DATE)) )
        /LAG(SUM(AMOUNT)) OVER(ORDER BY YEAR(ORDER_DATE), MONTH(ORDER_DATE)) *100
    	,2) SALE_RATE
    -- 3개월 이동평균
    , ROUND(
    	AVG(SUM(AMOUNT))
        	OVER(ORDER BY YEAR(ORDER_DATE), MONTH(ORDER_DATE))
            ROWS 2 PRECEDING)
    	,2) THREE_SALE_AVG
	-- 연초 대비 누적 증감률
    , ROUND(
    	(SUM(AMOUNT) - FIRST_VALUE(SUM(AMOUNT)) OVER(ORDER BY YEAR(ORDER_DATE), MONTH(ORDER_DATE)) )
        /FIRST_VALUE(SUM(AMOUNT)) OVER(ORDER BY YEAR(ORDER_DATE), MONTH(ORDER_DATE))*100
    	,2) SALE_RATE2
FROM ORDERS
WHERE ORDER_DATE >= '2023-01-01'
	AND ORDER_DATE < '2024-01-01'
    AND STATUS = 'COMPLETED'
GROUP BY YEAR(ORDER_DATE)
	, MONTH(ORDER_DATE)
ORDER BY ORDER_YEAR, ORDER_MONTH
;

 

 

🎯 상황별 최적 선택

📋 출력 형태가 중요한 경우

 

sql

-- 요구사항: "2023-01", "2023-02" 형태로 출력
DATE_FORMAT(ORDER_DATE, '%Y-%m') ✅
-- vs
CONCAT(YEAR(ORDER_DATE), '-', LPAD(MONTH(ORDER_DATE), 2, '0')) ❌ (복잡함)

🚀 성능이 중요한 경우

 

sql

-- 대용량 데이터 + 인덱스 활용
YEAR(ORDER_DATE), MONTH(ORDER_DATE) ✅
-- vs  
DATE_FORMAT(ORDER_DATE, '%Y-%m') ❌ (문자열 연산 부하)

---

📊 성능 차이 이유

DATE_FORMAT이 느린 이유:

• 문자열 변환 연산
• 인덱스 활용도 낮음
• 메모리 사용량 증가

YEAR, MONTH가 빠른 이유:

• 정수 연산
• 날짜 인덱스 직접 활용
• 메모리 효율적

---

💡 실무 권장사항

 

sql

-- 화면 출력용: DATE_FORMAT 사용
SELECT DATE_FORMAT(ORDER_DATE, '%Y-%m') AS SALES_MONTH

-- 집계/분석용: YEAR, MONTH 사용  
GROUP BY YEAR(ORDER_DATE), MONTH(ORDER_DATE)

결론: 문제에서 특별한 출력 형태를 요구하지 않으면 YEAR, MONTH가 더 효율적이에요!