[SQL/세션] 3회차 - WINDOW 함수

세션2회차 문제2번

프로그래머스 문제 링크 https://school.programmers.co.kr/learn/courses/30/lessons/164668

> 내 풀이 : join 과 having 으로 필터링햔 풀이

SELECT u.user_id
        , u.nickname
        , SUM(price) AS TOTAL_PRICE
FROM used_goods_board AS b
LEFT JOIN used_goods_user AS u
ON b.writer_id=u.user_id
WHERE b.status = 'DONE'
GROUP BY u.user_id, u.nickname
HAVING SUM(price) >= '700000'
ORDER BY TOTAL_PRICE ASC
;

> from절 서브쿼리 풀이

SELECT 
    U.USER_ID,
    U.NICKNAME,
    S.TOTAL_SALES
FROM USED_GOODS_USER U
JOIN (
    SELECT WRITER_ID, SUM(PRICE) AS TOTAL_SALES
    FROM USED_GOODS_BOARD
    WHERE STATUS = 'DONE'
    GROUP BY WRITER_ID
    HAVING SUM(PRICE) >= 700000
) S
ON U.USER_ID = S.WRITER_ID
ORDER BY S.TOTAL_SALES ASC;

> CTE 절 사용 : 재사용 가능

WITH SALES AS (
    SELECT WRITER_ID, SUM(PRICE) AS TOTAL_SALES
    FROM USED_GOODS_BOARD
    WHERE STATUS = 'DONE'
    GROUP BY WRITER_ID
)
SELECT 
    U.USER_ID,
    U.NICKNAME,
    S.TOTAL_SALES
FROM SALES S
JOIN USED_GOODS_USER U
  ON U.USER_ID = S.WRITER_ID
WHERE S.TOTAL_SALES >= 700000
ORDER BY S.TOTAL_SALES ASC;

💡 여기서는 WHERE 절에서 필터링을 한 이유?!

집계는 이미 CTE 안에서 끝났기 때문에 WHERE로 필터링 가능 !!

⭐️ CTE 쿼리 구조의 장점 : 단계적으로 나눌 수 있음 → 데이터 흐름을 직관적으로 이해 가능!

집계단계 : 회원별 거래금액 합계 계산
출력단계 : 회원 정보와 조인 후 필터링 및 정렬

2회차 문제3

프로그래머스 문제 링크 https://school.programmers.co.kr/learn/courses/30/lessons/59045

> 내 풀이 : JOIN을 사용한 풀이

🚨 주의 : where절에 연산자(and, or)는 잘 묶어줘야 함! "그리고" 인지 "또는" 인지 구분!

SELECT i.animal_id
        , i.animal_type
        , i.name
FROM animal_ins AS i
INNER JOIN animal_outs AS o
ON i.animal_id=o.animal_id
WHERE i.sex_upon_intake LIKE 'Intact%' 
  AND (o.sex_upon_outcome LIKE 'Spayed%'
   OR o.sex_upon_outcome LIKE 'Neutered%')
ORDER BY o.animal_id ASC 
;

> CASE WHEN 을 사용한 풀이

SELECT i.ANIMAL_ID,
       i.ANIMAL_TYPE,
       i.NAME
FROM ANIMAL_INS i
JOIN ANIMAL_OUTS o
  ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE CASE 
        WHEN i.SEX_UPON_INTAKE LIKE '%Intact%' THEN 'N'
        ELSE 'Y'
      END = 'N'
  AND CASE 
        WHEN o.SEX_UPON_OUTCOME LIKE '%Intact%' THEN 'N'
        ELSE 'Y'
      END = 'Y'
ORDER BY i.ANIMAL_ID;

✅ WHERE절의 풀이가 특이하다! 짚고 넘어가보자~

입소 시 상태 확인

i.SEX_UPON_INTAKE가 "Intact..." → 'N' (Not neutered, 중성화 X)
그 외(Spayed Female, Neutered Male 등) → 'Y' (중성화 O)
조건: = 'N' → 입소할 때 중성화되지 않은 동물만 남김.

출소 시 상태 확인

o.SEX_UPON_OUTCOME가 "Intact..." → 'N' (중성화 X)
그 외(Spayed..., Neutered...) → 'Y' (중성화 O)
조건: = 'Y' → 출소할 때 중성화된 동물만 남김.

따라서, 입소 당시엔 중성화 X(N), 출소 당시엔 중성화 O(Y)인 동물만 필터링!

윈도우 함수

모든 컬럼을 잃고 싶지 않을 때 사용

행과 행의 관계를 알기 쉽게 해줌

하나의 컬럼이 생성되기 때문에 SELECT 절에서 사용!!

⭐️윈도우 함수의 종류

분류	대표 함수	설명	GROUP BY 병행 여부
집계 함수	SUM(), MAX(), MIN(), AVG(), COUNT()	윈도우 범위 내 합계, 최대/최소, 평균, 개수 계산	가능
순위 함수	RANK(), DENSE_RANK(), ROW_NUMBER()	행에 순위를 매기거나 행 번호 부여	불가능
순서 함수	FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()	파티션 내 첫 값/마지막 값, 이전/다음 행 값 참조	불가능
비율/ 분석 함수	RATIO_TO_REPORT(), PERCENT_RANK(), CUME_DIST(), NTILE(n)	비율, 누적 백분율, 분위수 계산	불가능

-- 윈도우 함수 기본 문법

SELECT 
    WINDOW_FUNCTION(컬럼명) OVER ( -- OVER는 윈도우 함수가 계산될 범위를 정하는 키워드
        PARTITION BY 컬럼명   -- 파티션 기준 그룹화
        ORDER BY 컬럼명       -- 파티션 내 정렬 기준
    ) AS 별칭
FROM 
    테이블명;
    
    
SELECT WINDOW_FUNCTION(컬럼명) OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) AS 별칭
FROM 테이블명;

예시) ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
-> ROW_NUMBER 라는 윈도우 함수를 사용하고, 그 기준을 컬럼1로 , 정렬은 컬럼2로 지정

구성요소	설명
OVER	윈도우 함수 범위를 지정하는 핵심 키워드
PARTITION BY	그룹핑 기준 (마치 GROUP BY처럼 작동하지만, 행은 그대로 유지)
ORDER BY	정렬 기준 (순위, 누적 계산 등에 필요)

▼ 윈도우 함수 괄호 안에 컬럼 필요 여부 정리

함수 이름	괄호 안에 컬럼 필요 여부	설명	쿼리 예시
RANK()	❌ 없음	순위 (중복 순위 있음, 건너뜀)	RANK() OVER (ORDER BY salary DESC) AS rank_no
DENSE_RANK()	❌ 없음	순위 (중복 순위 있음, 건너뜀 없음)	DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_no
ROW_NUMBER()	❌ 없음	고유 순번 부여	ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no
PERCENT_RANK()	❌ 없음	백분율 순위 (0~1 사이)	PERCENT_RANK() OVER (ORDER BY salary DESC) AS pct_rank
CUME_DIST()	❌ 없음	누적 백분율 (현재 행 이하 비율)	CUME_DIST() OVER (ORDER BY salary DESC) AS cume_dist
NTILE(N)	✅ 있음 (N은 숫자)	N등분으로 나눠 등급 부여	NTILE(4) OVER (ORDER BY salary DESC) AS quartile
LAG(컬럼)	✅ 있음	이전 행의 값	LAG(salary) OVER (ORDER BY salary) AS prev_salary
LAG(컬럼, 숫자)	✅ 있음	N번째 이전 행의 값	LAG(salary, 2) OVER (ORDER BY salary) AS prev2_salary
LEAD(컬럼)	✅ 있음	이후 행의 값	LEAD(salary) OVER (ORDER BY salary) AS next_salary
LEAD(컬럼, 숫자)	✅ 있음	N번째 이후 행의 값	LEAD(salary, 3) OVER (ORDER BY salary) AS next3_salary
FIRST_VALUE(컬럼)	✅ 있음	파티션 내 첫 번째 값	FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS top_salary
LAST_VALUE(컬럼)	✅ 있음	파티션 내 마지막 값	LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_salary
RATIO_TO_REPORT(컬럼)	✅ 있음	전체합 대비 현재값 비율 (Oracle)	RATIO_TO_REPORT(salary) OVER () AS salary_ratio
SUM(컬럼)	✅ 있음	누적 합계 (윈도우 함수일 경우)	SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_total
AVG(컬럼)	✅ 있음	누적 평균 등	AVG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_avg

⭐️ 은 가장 많이 쓰이는 함수!!

함수	정의	특징
RANK()	ORDER BY 기준 순위 부여	동순위 → 같은 값, 다음 순위 건너뜀 (예: 1,2,2,4)
DENSE_RANK()	RANK와 동일하나 순위 건너뛰지 않음	동순위 → 같은 값, 다음 순위 연속 (예: 1,2,2,3)
ROW_NUMBER() ⭐️	각 행에 고유 번호 부여	중복값 있어도 고유 번호 (예: 1,2,3,4)
FIRST_VALUE()	파티션(그룹) 내에서 정렬 기준상 첫 번째 값을 반환	파티션별 정렬 후 첫 번째 값 가져옴
LAST_VALUE()	파티션(그룹) 내에서 정렬 기준상 마지막 값을 반환	기본은 현재 행까지의 마지막 값 → 옵션 필요
LAG() ⭐️	이전 행 값 가져옴	기본 N=1, 없으면 NULL
LEAD() ⭐️	다음 행 값 가져옴	기본 N=1, 없으면 NULL
PERCENT_RANK()	백분위 순위(0~1)	(순위-1) / (전체행수-1)
CUME_DIST()	누적 비율(0~1)	현재 값 이하 비율 → 항상 마지막 값은 1
NTILE(N)	데이터를 N등분해 구간 번호 부여	1~N 등분, 분위수 계산에 활용
RATIO_TO_REPORT()	전체합 대비 비율	Oracle 전용, 합계 1.0 (100%)

📌 윈도우 함수를 쓰는 대표적인 상황 (드래그 해서 확인)

1. 순위 매기기(ranking) → 예: 부서별 급여 순위를 매겨라 (부서별로 높은 급여 순위를 계산하면서, 모든 직원 행을 그대로 유지)

✅ RANK( ), DENSE_RANK( ), ROW_NUMBER( )

2. 누적값, 이동평균 → 예: 월별 매출 누적 합계를 구하라 (각 달 매출 행은 그대로 두고, 누적 합계 컬럼 추가)

✅ SUM( ) OVER, AVG( ) OVER

3. 비교 (이전/다음 행 값 참조) → 직원의 급여가 직전 직원보다 얼마나 증가했는가? (같은 테이블 내 행 간 비교 가능)

✅ LAG( ), LEAD( )

4. 비율 계산 → 부서별 급여에서 각 직원 급여가 차지하는 비율 (각 행의 급여가 부서 전체 합계에서 몇 %인지 계산)

✅ RATIO_TO_REPORT( ), PERCENT_RANK( ), CUME_DIST( )

5. 특정 위치 값 가져오기 → 부서별 최고 급여자, 최저 급여자(파티션(그룹) 내에서 정렬 기준으로 맨 앞/맨 뒤 행의 값을 가져옴)

✅ FIRST_VALUE( ), LAST_VALUE( )

정리

💡 윈도우 함수는 데이터를 줄이지 않고 행 그대로 유지하면서 추가적인 통계·순위·누적값·비교값을 컬럼으로 붙이고 싶을 때 씁니다. 집계 함수(SUM, AVG)는 데이터를 줄이고 요약 윈도우 함수는 데이터를 그대로 두고 확장

3회차 문제1 (코드카타48번)

https://school.programmers.co.kr/learn/courses/30/lessons/131123

🚨 어려워했던 문제!!

> 질문해서 푼 풀이

# 튜플 사용
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
    SELECT FOOD_TYPE, MAX(FAVORITES)
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)
ORDER BY FOOD_TYPE DESC;

💡 음식종류별로 즐겨찾기수가 가장 많은 식당 ?

그룹 바이?
where절 서브쿼리?
가장 많은? max() 함수?

> 다른풀이1 : where 절 서브쿼리(중첩(일반) 서브쿼리)

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO R
WHERE FAVORITES = (
    SELECT MAX(FAVORITES)
    FROM REST_INFO
    WHERE FOOD_TYPE = R.FOOD_TYPE
)
ORDER BY FOOD_TYPE DESC;

💡 서브쿼리 : 음식 종류별로 즐겨찾기 수가 가장 많은 것들 조건으로 필터링 한다!! 동적 조건(조건의 유동성)

최대인데, 최대가 뭔지를 모르겠어... 음식 종류별로 유동적으로 최대가 바뀌겠네?

➡️ 그럴땐 where절 서브쿼리!!!

> 다른풀이2 : 서브쿼리로 음식 종류별 즐겨찾기 최대값을 구한 뒤, 원본 테이블과 조인해서 해당 식당의 정보를 가져오는 방식

SELECT R.FOOD_TYPE, R.REST_ID, R.REST_NAME, R.FAVORITES
FROM REST_INFO R
JOIN (
    SELECT FOOD_TYPE, MAX(FAVORITES) AS MAX_FAV
    FROM REST_INFO
    GROUP BY FOOD_TYPE
) M
ON R.FOOD_TYPE = M.FOOD_TYPE
AND R.FAVORITES = M.MAX_FAV
ORDER BY R.FOOD_TYPE DESC;

💡 내부 서브쿼리 (M) → 외부 쿼리와 JOIN : 음식종류가 같은 것으로 즐찾수 많은

> 다른풀이3 : CTE + JOIN 방식

WITH MAX_FAV AS (
    SELECT FOOD_TYPE, MAX(FAVORITES) AS MAX_FAV
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)
SELECT R.FOOD_TYPE, R.REST_ID, R.REST_NAME, R.FAVORITES
FROM REST_INFO R
JOIN MAX_FAV M
  ON R.FOOD_TYPE = M.FOOD_TYPE
 AND R.FAVORITES = M.MAX_FAV
ORDER BY R.FOOD_TYPE DESC;

💡 2번째 풀이와 비슷하다.

> 다른풀이4 : 윈도우 함수 사용

최대? 음식종류 별로 즐찾수가 가장 많아? 오 윈도우 함수 가능할지도!!? 그룹내에서 순위를 매기고 1등만 뽑기 때문!

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (
    SELECT 
        FOOD_TYPE,
        REST_ID,
        REST_NAME,
        FAVORITES,
        ROW_NUMBER() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RN
    FROM REST_INFO
) A
WHERE RN = 1
ORDER BY FOOD_TYPE DESC;

▼ 왜 rank()나 dense_rank()가 아니고 row_number()?

RANK() → 동점이 있으면 같은 순위(1, 1, 3…)
DENSE_RANK() → 동점이 있으면 같은 순위지만 다음 순위는 바로 이어짐(1, 1, 2…)
ROW_NUMBER() → 무조건 1개만 뽑힘 (동점도 강제로 순서가 매겨짐 → 1, 2, 3…)

▼ 최종 정리 (wher절 서브쿼리 / join 방식 / 윈도우 함수 방식)

구분	WHERE 절 서브쿼리 방식	JOIN 방식	윈도우 함수 방식
동작 원리	바깥 테이블의 각 행마다 서브쿼리를 실행해 해당 FOOD_TYPE의 MAX(FAVORITES)와 비교	서브쿼리에서 음식 종류별 최댓값 테이블(M)을 만들고, 원본 테이블과 매칭	ROW_NUMBER() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC)로 그룹별 순위를 매기고, 1등만 선택
가독성	간단하고 직관적 (짧은 코드)	최댓값 테이블 → 조인 구조가 명확	“그룹별 순위 매기고 1등 뽑는다”는 개념이 직관적
성능	바깥 행마다 서브쿼리 실행 → 대용량에서 비효율적	그룹별 최댓값을 한 번만 계산 후 조인 → 효율적	순위 계산을 한 번만 하고 바로 필터링 → 대규모 데이터에도 효율적
확장성	단순 최대값 비교까지만 적합	평균, 최소값 등 다른 집계값을 함께 활용하기 쉽다	다양한 순위 로직(RANK, DENSE_RANK) 적용 가능 → 동점 처리 유연
동점 처리	최대값과 같은 여러 행 모두 출력됨	최대값과 같은 여러 행 모두 출력됨	ROW_NUMBER()는 동점 중 1개만, RANK/DENSE_RANK는 동점 모두 출력 가능
권장 상황	데이터가 적거나 학습용으로 간단히 쓸 때	실무에서 집계값을 다른 조건과 함께 활용할 때	실무에서 직관적이고 확장성·성능까지 모두 고려할 때

3회차 문제2 (코드카타49번)

https://school.programmers.co.kr/learn/courses/30/lessons/131116

> 내 풀이

SELECT category
        , MAX(price) AS max_price
        , product_name
FROM food_product
WHERE (category, price) IN (SELECT category, max(price) as max_price
                           FROM food_product
                           WHERE category in ('과자', '국', '김치', '식용유')
                            GROUP BY category
                           )
GROUP BY category
ORDER BY price DESC;

분류별(CATEGORY)로 묶어야 함.
각 분류에서 가장 비싼 가격(최댓값)을 찾아야 함
출력은 분류, 최고가, 상품명
단, 카테고리는 '과자', '국', '김치', '식용유'만
최종적으로 가격 내림차순 정렬
<GROUP BY + MAX> 문제처럼 보이지만, 상품명까지 출력해야 하기 때문에 단순 집계만으로는 안 되겠네!

> 다른풀이1 : 윈도우 함수

SELECT CATEGORY,
       PRICE AS MAX_PRICE,
       PRODUCT_NAME
FROM (
    SELECT CATEGORY,
           PRODUCT_NAME,
           PRICE,
           ROW_NUMBER() OVER (
               PARTITION BY CATEGORY 
               ORDER BY PRICE DESC
           ) AS RN
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
) T
WHERE RN = 1
ORDER BY MAX_PRICE DESC;

> 다른풀이2 : 집계 + JOIN 방식

SELECT FP.CATEGORY,
       FP.PRICE AS MAX_PRICE,
       FP.PRODUCT_NAME
FROM FOOD_PRODUCT FP
JOIN (
    SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY
) TMP
  ON FP.CATEGORY = TMP.CATEGORY
 AND FP.PRICE = TMP.MAX_PRICE
WHERE FP.CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY FP.PRICE DESC;

🎯 정리해보자면,
집계 + JOIN 방식이 결합된 풀이가 많이 나온다.
그리고 윈도우 함수도 종종 나오고 나오면 헷깔릴 수 있으니 복습 철저히 하자! 😎

끝.

'SQL' 카테고리의 다른 글

[SQL/QCC] 3회차 코딩 테스트_해설 (0)	2025.10.24
[SQL/코드카타] 프로그래머스 - 자동차 대여 기록 별 대여 금액 구하기 (0)	2025.10.02
[SQL/코드카타] 프로그래머스 - 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (NOT EXISTS) (1)	2025.09.30

[SQL/세션] 3회차 - WINDOW 함수 | 문제풀이