배경
새로운 기능의 추가로 기존의 쿼리에 새 테이블을 조인해서 사용해야 하는 경우를 생각해보자.
이 때 새 테이블로부터 집합함수(최댓값, 최소값..) 결과를 조회해야 하는 경우가 있다.
말로 하면 복잡하니 예시로 살펴보자.
기존 쿼리
SELECT A.COLUMN1,
A.COLUMN2,
B.COLUMN1,
B.COLUMN2,
C.COLUMN1,
C.COLUMN2,
FROM TABLE_001 A,
TABLE_002 B,
TABLE_003 C
WHERE ... 기존에 TABLE_001, TABLE_002, TABLE_003을 조인하여 만들어진 쿼리가 있다.
새로운 기능의 추가로 기존 쿼리에 TABLE_004를 추가하여 사용해야 한다.
이 때 조인되는 TABLE_004 컬럼들의 최대값이 필요하다면?
-> group by로 기존의 컬럼을 묶어서(집합으로 만들어) 각 집합의 최대값을 도출해야 한다.
a. GROUP BY 사용
SELECT A.COLUMN1,
A.COLUMN2,
B.COLUMN1,
B.COLUMN2,
C.COLUMN1,
C.COLUMN2,
MAX(D.COLUMN1), -- 추가된 컬럼
MAX(D.COLUMN2), -- 추가된 컬럼
FROM TABLE_001 A,
TABLE_002 B,
TABLE_003 C,
TABLE_004 D -- 새 테이블 추가
WHERE ...
-- group by절로 기존 쿼리의 컬럼을 집합화
GROUP BY A.COLUMN1, A.COLUMN2, B.COLUMN1, B.COLUMN2, C.COLUMN1, COLUMN2
위의 예시와 같이 기존에 작성된 컬럼을 group by에 하나씩 나열해야 한다.
예시에서는 6개 컬럼밖에 존재하지 않지만 실제 운영 환경에서는 몇십개가 될 수 있다.
이렇게 기존 쿼리의 컬럼 수가 너무 많은 경우에 OVER() 함수를 잘 활용하면 GROUP BY 없이 원하는 쿼리를 작성할 수 있다.
b. OVER 사용 - MAX() OVER (PARTITION BY)
SELECT A.COLUMN1,
A.COLUMN2,
B.COLUMN1,
B.COLUMN2,
C.COLUMN1,
C.COLUMN2,
MAX(D.COLUMN1) OVER (PARTITION BY D.ORDER_NO),
MAX(D.COLUMN2) OVER (PARTITION BY D.ORDER_NO)
FROM TABLE_001 A,
TABLE_002 B,
TABLE_003 C,
TABLE_004 D -- 새 테이블 추가
WHERE ... PARTITION BY로 파티셔닝할 기준을 정의한 뒤에 MAX() OVER 혹은 MIN() OVER를 사용하여 해당 집합의 최댓값 최솟값을 구할 수 있다.
위의 예시에서는 오더번호(ORDER_NO)를 기준으로 파티셔닝하여 해당 오더번호를 가진 COLUMN1의 최댓값 조회해온다.
c. OVER 사용 - MAX() KEEP(DENSE_RANK FIRST ORDER BY) OVER(PARTITION BY)
SELECT A.COLUMN1,
A.COLUMN2,
B.COLUMN1,
B.COLUMN2,
C.COLUMN1,
C.COLUMN2,
MAX(D.COLUMN1) KEEP(DENSE_RANK FIRST ORDER BY D.CREATE_DT DESC) OVER (PARTITION BY D.ORDER_NO),
MAX(D.COLUMN1) KEEP(DENSE_RANK FIRST ORDER BY D.CREATE_DT DESC) OVER (PARTITION BY D.ORDER_NO)
FROM TABLE_001 A,
TABLE_002 B,
TABLE_003 C,
TABLE_004 D -- 새 테이블 추가
WHERE ... 동일한 상황에서 최대, 최소 정렬 기준 자체를 변경하고 싶을 때는 KEEP(DENSE_RANK FIRST/LAST ORDER BY 정렬기준)을 함께 사용하면 된다.
위의 예시를 살펴보면
- 오더번호(ORDER_NO)를 기준으로 파티셔닝
- 해당 오더번호를 가진 행을 생성일자(CREATE_DT)를 기준으로 내림차순 정렬했을 때 최댓값. 즉, 생성일자가 가장 나중인 행을 PICK
- 해당 행의 COLUMN1를 조회
