[프로그래머스 / MySQL] 연도별 대장균 크기의 편차 구하기 (Lv2 - 셀프조인, 윈도우 함수)

문제링크: https://school.programmers.co.kr/learn/courses/30/lessons/299310

프로그래머스

 

1. 셀프 조인을 이용한 방법

select 
    year(e1.DIFFERENTIATION_DATE) as YEAR,
    e2.mx_size - e1.SIZE_OF_COLONY as YEAR_DEV,
    e1.ID
from ECOLI_DATA e1
join (
    select year(DIFFERENTIATION_DATE) as year, max(SIZE_OF_COLONY) as mx_size
    from ECOLI_DATA
    group by year(DIFFERENTIATION_DATE)) e2
on year(e1.DIFFERENTIATION_DATE) = e2.year
order by YEAR asc, YEAR_DEV asc

전통적인 방법인 셀프 조인을 이용한 방법이 있다. 문제를 읽어보면 결국 필요한 것은 연도별 최대 대장균 크기가 컬럼으로 하나 더 붙어야한다. 한 테이블에서 연도별 최대 대장균 크기를 뽑아내야하므로 셀프 조인을 하면 된다. 연도별로 group by를 한 후 필요한 정보인 연도와 연도별 최대 대장균 크기 속성을 뽑아낸 테이블을 만든다. 연도를 뽑아내는 이유는 조인을 하기 위해서이다. 원래 테이블에서 연도가 일치하는 것으로 셀프 조인을 하면 된다. 그렇게 되면 연도별 최대 대장균 크기 컬럼까지 붙은 하나의 테이블이 완성된다. 연도별 최대 대장균 크기에서 대장균 크기를 빼기만 해주면 된다.

 

전통적이고 익숙한 사고 흐름을 따라가지만 조인을 하기 시작하면 코드가 복잡해지는 것은 어쩔 수가 없다. 코드로 좀더 짧고 간결하게 표현하는 방법이 있는데 바로 윈도우 함수를 사용하는 방법이다.

 

2. 윈도우 함수를 이용한 방법

select 
    year(e.DIFFERENTIATION_DATE) as YEAR,
    max(e.SIZE_OF_COLONY) over (partition by year(DIFFERENTIATION_DATE)) - e.SIZE_OF_COLONY as YEAR_DEV,
    e.ID
from ECOLI_DATA e
order by YEAR asc, YEAR_DEV asc

셀프조인을 복잡하게 한 이유는 오직 하나 연도별 최대 대장균 크기를 구해야했기 때문이다. 윈도우 함수에서는 select 절에서 바로 이걸 구할 수 있다.

 

윈도우 함수는 함수 부분와 over 절로 나뉜다.

SELECT 
    함수(인자) OVER (
        [PARTITION BY 컬럼명]
        [ORDER BY 컬럼명 [ASC|DESC]]
        [ROWS | RANGE BETWEEN 시작_지점 AND 종료_지점]
    ) AS 별칭
FROM 테이블명;

함수에는 sum, max, min 등 집계함수가 들어가고, `partition by` 이후 컬럼을 기준으로 소그룹으로 나뉜다. `partition by`가 생략되면 전체 행을 하나의 그룹으로 간주한다. `order by`가 윈도우 함수에서는 상당히 중요하다. 파티션 내에서 어떤 순서로 계산을 진행할지를 정의하는데 `sum`, `avg`의 집계함수에서 `order by`를 쓰면 정렬 순서에 따른 누적 계산으로 값이 계산된다.

 

rows와 range는 좀 헷갈리고, 처음보면 이게 뭔소리인지 싶다. 이론과 예시로 살펴보자.

`rows`는 현재 행부터 행 개수를 나타내고, 중복 값을 개별적으로 처리한다.

`range`는 order by의 컬럼 값을 기준으로 계산하고 값이 같으면 하나의 그룹으로 묶어서 처리한다.

• PRECEDING: 앞의 행
• FOLLOWING: 뒤의 행
• CURRENT ROW: 현재 행
• UNBOUNDED PRECEDING: 파티션의 첫 번째 행부터
• UNBOUNDED FOLLOWING: 파티션의 마지막 행까지

위 키워드를 통해 행을 지정해서 아래와 같이 현재 행을 기준으로 특정 행까지 범위를 지정해서 계산하는 것이다.

`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`: 처음부터 현재 행까지 (누적 합계 시 기본값)  `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`: 앞 행, 현재 행, 뒷 행 (총 3행의 평균 등)

`ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`: 현재 행부터 끝까지 

 

구체적인 사례와 코드로 확인해봐야 이해가 빠르다. 아래와 같은 테이블이 있다고 생각해보자.

ID	dt (거래일자)	금액 (amt)
1	2026-03-01	100
2	2026-03-01	200
3	2026-03-02	300

보면 거래일자가 id 1, 2는 같고 3은 다르다. 이때 `order by dt`를 했을 때 rows와 range의 sum 방식의 차이가 생긴다.

SELECT 
    dt, 
    amt,
    -- 물리적인 행의 순서대로 하나씩 더한다.
    SUM(amt) OVER (
        ORDER BY dt 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rows_sum,
    
    -- 동일한 값(날짜)을 가진 행들을 하나의 묶음으로 처리하여 한 번에 더한다.
    SUM(amt) OVER (
        ORDER BY dt 
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS range_sum
FROM transactions;

 

ID	dt (거래일자)	금액 (amt)	rows_sum	range_sum
1	2026-03-01	100	100	300
2	2026-03-01	200	300	300
3	2026-03-02	300	600	600

rows_sum은 그냥 물리적 위치고 바로 계산해버린다.

range_sum은 dt가 같은 것은 같은 걸로 보고 계산한다는 뜻이다. 

 

row와 range는 이런 미묘한 차이가 있다.