[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
29. 조인과 집계 데이터 - 13. 분석함수란
30. 조인과 집계 데이터 - 14. AVG함수
31. 조인과 집계 데이터 - 15. ROW_NUMBER, RANK, DENSE_RANK 함수
32. 조인과 집계 데이터 - 16. FIRST_VALUE, LAST_VALUE 함수
[강의내용]
29. 조인과 집계 데이터 - 13. 분석함수란
[분석함수는?]
특정 집합내에서 결과 건수의 변화없이 합계 및 카운트 등을 계산할 수 있는 함수이다.
집계함수는 집계 결과만을 출력한다.
COUNT()와 OVER()를 결합하면 분석함수로써 집계의 결과 및 테이블 내용을 함께 출력할 수 있다.
집계함수는 GROUP BY를 동반하는 특성이 있고,
분석함수는 GROUP BY대신 SELECT절에서 바로 나타내기 위해 OVER()를 동반하는 특성이 있다.
[샘플데이터 확인]
PRODUCT_GROUP, PRODUCT 2개의 테이블을 사용한다.
30. 조인과 집계 데이터 - 14. AVG함수
먼저 분석함수의 문법에 대해 살펴보면
[문법]
SELECT C1, 분석함수(C2, C3, ...) OVER(PARTITION BY C4 ORDER BY C5)
FROM TABLE_NAME;
사용하고자 하는 분석함수를 쓰고 대상 컬럼을 기재후
PARTITION BY에서 값을 구하는 기준 컬럼을 쓰고
ORDER BY에서 정렬 컬럼을 기재한다.
[실습1]
함수값과 테이블 내용을 함께 보기 위해 AVG()로 분석함수를 사용해본다.
SELECT A.PRODUCT_NAME, A.PRICE, B.GROUP_NAME,
AVG(A.PRICE) OVER (PARTITION BY B.GROUP_NAME)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
분석함수를 사용하여 결과집합을 그대로 출력하면서 GROUP_NAME 기준의 평균을 출력했다.
[실습2]
OVER절에 분석함수 컬럼과 동일한 컬럼을 ORDER BY 절에 넣어주면 누적 평균도 구할 수 있다.
SELECT A.PRODUCT_NAME, A.PRICE, B.GROUP_NAME,
AVG(A.PRICE) OVER (PARTITION BY B.GROUP_NAME ORDER BY PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
31. 조인과 집계 데이터 - 15. ROW_NUMBER, RANK, DENSE_RANK 함수
3종의 함수는 해당 집합내에서 특정 컬럼의 순위를 구하는 함수이다.
순위를 구할 컬럼을 분석함수() 괄호안에 지정하는 것이 아니라 OVER()절의 ORDER BY 뒤에 넣어준다.
[실습1] ROW_NUMBER함수
순위를 무조건 1, 2, 3, 4, 5, ... 순으로 설정한다.
SELECT A.PRODUCT_NAME,
B.GROUP_NAME,
A.PRICE,
ROW_NUMBER() OVER
(PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
[실습2] RANK함수
공동순위가 발생하면 다음 순위는 건너뛴다. 1, 1, 3, 4, ...
SELECT A.PRODUCT_NAME,
B.GROUP_NAME,
A.PRICE,
RANK() OVER
(PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
[실습3] DENSE_RANK함수
RANK와 다르게 순위를 건너뛰지 않는다. 1, 1, 2, 3, ...
SELECT A.PRODUCT_NAME,
B.GROUP_NAME,
A.PRICE,
DENSE_RANK() OVER
(PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
DENSE_RANK의 효과를 보기위해 오름차순으로 정렬했다.
32. 조인과 집계 데이터 - 16. FIRST_VALUE, LAST_VALUE 함수
FIRST_VALUE()와 LAST_VALUE()는 해당 집합안에서 특정 컬럼의 첫번째 값 혹은 마지막 값을 구한다.
[실습1] FIRST_VALUE함수
SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE,
FIRST_VALUE(A.PRICE) OVER
(PARTITION BY B.GROUP_NAME ORDER BY A.PRICE
) AS LOWEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
순위 함수들과는 다르게 FIRST_VALUE() 컬럼 안에 값을 구하고자 하는 컬럼을 명시해준다.
GROUP_NAME 기준 PRICE가 가장 작은 값이 출력되었다.
ASC, DESC를 섞어서 쓰면 FIRST_VALUE를 LAST_VALUE처럼 사용할 수 있다.
하지만 LAST_VALUE를 쓸 때는 주의할 점이 있다.
[실습2] LAST_VALUE함수
SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE,
LAST_VALUE(A.PRICE) OVER
(PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS LOWEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
OVER()절 맨 마지막에 아래와 같은 문구가 추가되었다.
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
한국어로 해석하면 "제한없는 이전부터 제한없는 다음까지의 범위" 라는 뜻이다.
즉 PARTITION인 GROUP_NAME 전체가 RANGE가 됨을 뜻한다.
이 문구가 추가되는 이유는 LAST_VALUE()의 디폴트값이 아래의 문구이기 때문이다.
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
"제한없는 이전부터 현재행까지" 라는 뜻이다.
즉 RANGE 말미에 UNBOUNDED FOLLOWING 조건을 써주지 않으면 현재행까지만 PARTITION이 지정된다.
디폴트값 상태로 쿼리를 실행해보면
SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE,
LAST_VALUE(A.PRICE) OVER
(PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC
) AS LOWEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID);
PARTITION이 GROUP_NAME 전체가 아닌 현재행까지로 제한되어 버린다.