프로그래머스 SQL 풀이 오답노트(13): WINDOW, PARTITION BY

SeongGyun Hong·2025년 2월 7일

MS SQL Server oracle partition by sql window

SQL

목록 보기

37/51

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

그룹별 1위를 뽑으려면 어떻게 해야 하는가?

1. GROUP BY와 FETCH FIRST ROW

GROUP BY의 경우 그룹별 요약 정보를 제공한다.
굳이 요약 정보라고 하는 이유는 SELECT 절에 GROUP의 대상이 아닌 이상, 집계정보만이 올 수 있기 때문이다.

GROUP BY를 사용할 때 FETCH FIRST ROW ONLY 구문을 함께 사용하면 그룹화된 결과 중 첫 번째 행만을 가져올 수 있다.

SELECT department_id, AVG(salary) 
FROM employees
GROUP BY department_id
FETCH FIRST 1 ROW ONLY;

다만, 주의할 점은 있습니다. ORDER BY를 명시하지 않으면 어떤 그룹이 선택될지 예측할 수 없다는 것이다.

따라서 특정 조건의 그룹을 선택하고 싶다면 반드시 ORDER BY와 함께 사용해야 한다.

SELECT department_id, AVG(salary) 
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC  -- 평균 급여가 가장 높은 부서가 선택됨
FETCH FIRST 1 ROW ONLY;

2. WINDOW 함수

그런데, GROUP BY로는 그룹별 1위를 뽑을 수는 없다. 단순히 모든 그룹 통틀어 1위는 가능할 지라도...
그러면 어떻게 해야하는가?

WINDOW 함수를 활용하자.

WINDOW 함수는 GROUP BY와 달리 행 데이터를 유지하면서 집계나 순위 계산이 가능하다.

단, WINDOW 함수는 SELECT 절과 ORDER BY 절, 그리고 서브쿼리에서만 사용할 수 있다.
WHERE절이나 GROUP BY 절에서는 직접 사용이 불가능!

사용 가능한 경우

-- SELECT 절에서 사용
SELECT 
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary) as rank
FROM employees;

-- ORDER BY 절에서 사용
SELECT employee_name, salary
FROM employees
ORDER BY ROW_NUMBER() OVER (ORDER BY salary);

-- 서브쿼리에서 사용
SELECT *
FROM (
    SELECT 
        employee_name,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) as rank
    FROM employees
) ranked
WHERE rank <= 3;

사용 불가능한 경우

-- WHERE 절에서 직접 사용 (에러 발생)
SELECT employee_name
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary) <= 3;

-- GROUP BY 절에서 사용 (에러 발생)
SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*))
FROM employees
GROUP BY department_id;

3. WINDOW 함수 활용

WINDOW는 간단한 데이터 분석에서도 많이 사용한다.
물론 집계 함수 자체가 분석에 자주 사용되니까 당연한 말이다 ㅋㅋ

1. 부서별 급여 순위 매기기

SELECT 
    employee_name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM employees;

2. 일별 매출 누적 합계 구하기

SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) as running_total
FROM sales;

3. 주식 가격 이동평균 계산

SELECT 
    date,
    price,
    AVG(price) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as moving_avg
FROM stock_prices;

4. RANK vs ROW_NUMBER

비슷하긴 한데, 동일값 처리에서 차이를 보인다.

-- 예시 데이터
CREATE TABLE scores (
    name VARCHAR2(10),
    score NUMBER
);

INSERT INTO scores VALUES ('Alice', 95);
INSERT INTO scores VALUES ('Bob', 95);
INSERT INTO scores VALUES ('Carol', 90);
INSERT INTO scores VALUES ('David', 90);
INSERT INTO scores VALUES ('Eve', 85);

-- RANK() 사용
SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank
FROM scores;

-- 결과:
-- name  | score | rank
-- Alice | 95    | 1
-- Bob   | 95    | 1
-- Carol | 90    | 3
-- David | 90    | 3
-- Eve   | 85    | 5

-- ROW_NUMBER() 사용
SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_num
FROM scores;

-- 결과:
-- name  | score | row_num
-- Alice | 95    | 1
-- Bob   | 95    | 2
-- Carol | 90    | 3
-- David | 90    | 4
-- Eve   | 85    | 5

주요 차이점

RANK()는 동일한 값에 같은 순위를 부여하고, 다음 순위는 건너뜀 (1,1,3,3,5)

ROW_NUMBER()는 동일한 값이어도 각 행에 고유한 순번을 부여 (1,2,3,4,5)

DENSE_RANK()는 RANK()와 비슷하지만 순위를 건너뛰지 않음 (1,1,2,2,3)

5. 정답쿼리(프로그래머스 131123번 문제)

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (
    SELECT 
        REST_ID,
        REST_NAME,
        FOOD_TYPE,
        FAVORITES,
        RANK() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS ROTY
    FROM REST_INFO
    )
WHERE ROTY = 1
ORDER BY FOOD_TYPE DESC

MS SQL SERVER에서는 FROM절의 서브쿼리에서 반드시 alias 지정해줘야 해서 아래처럼만 하면 호환 가능

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (
    SELECT 
        REST_ID,
        REST_NAME,
        FOOD_TYPE,
        FAVORITES,
        RANK() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS ROTY
    FROM REST_INFO
) AS sub  -- MS SQL SERVER에서는 FROM 절 서브쿼리에서 반드시 별칭 지정해야 함.
WHERE ROTY = 1
ORDER BY FOOD_TYPE DESC;

6. 정리

GROUP BY와 WINDOW 함수는 각각의 특성이 뚜렷해서 용도에 맞게 사용하는 것이 중요.
본 프로그래머스 문제는 GROUP BY로 분류 되어있으나, 그 실질은 WINDOW 함수의 활용에 있었기에 본 게시글을 통해 정리하였음.

요약하자면,
GROUP BY는 데이터를 요약할 때 좋고
WINDOW 함수는 원본 데이터를 유지하면서 추가 분석이 필요할 때 사용하면 좋음.

실무에서는 이 두 가지를 적절히 조합해서 사용하는 경우도 많음. 예를 들어, GROUP BY로 먼저 데이터를 집계한 후 그 CTE 결과에 WINDOW 함수를 적용하여 순위를 매기는 식!

아래 쿼리를 참고하자-!

WITH FoodTypeStats AS (
    -- 음식 유형별 평균 별점과 리뷰 개수 계산 (GROUP BY 사용)
    SELECT 
        FOOD_TYPE,
        AVG(RATING) AS AVG_RATING,
        COUNT(*) AS REVIEW_COUNT
    FROM REST_REVIEWS
    GROUP BY FOOD_TYPE
)
SELECT 
    r.FOOD_TYPE,
    r.REST_ID,
    r.REST_NAME,
    r.RATING,
    r.REVIEWS,
    f.AVG_RATING, 
    f.REVIEW_COUNT,
    
    -- 음식 유형별로 레스토랑을 별점 기준으로 순위 매기기 (RANK 사용)
    RANK() OVER (PARTITION BY r.FOOD_TYPE ORDER BY r.RATING DESC) AS RANK_IN_FOOD_TYPE,

    -- 음식 유형별 누적 리뷰 비율 계산 (CUME_DIST 사용)
    CUME_DIST() OVER (PARTITION BY r.FOOD_TYPE ORDER BY r.REVIEWS DESC) AS CUMULATIVE_REVIEW_RATIO

FROM REST_REVIEWS r
JOIN FoodTypeStats f ON r.FOOD_TYPE = f.FOOD_TYPE
ORDER BY r.FOOD_TYPE, RANK_IN_FOOD_TYPE;