[SQL/DB] 분석함수

ack·2021년 6월 18일

SQL/DB

목록 보기

4/8

ch03 JOIN과 집계데이터 - 분석함수

특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 합계 및 카운트 등을 계산할 수 있는 함수
집계의 결과와 테이블의 내용도 함께 보여줌

SELECT C1, 분석함수(c2, c3, ..) OVER (PARTITION BY C4 ORDER BY C5)
FROM TABLE_NAME

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)

-- 누적평균을 구할 수 있다.
SELECT A.PRODUCT_NAME,
	A.PRICE,
	B.GROUP_NAME,
	AVG(A.PRICE) 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)

ROW_NUMBER

특정 집합 내에서 결과 건수의 변화 없이 해당 집합 안에서 특정 컬럼의 순위를 구하기
동일한 순위 없음

SELECT A.PRODUCT_NAME,
	A.PRICE,
	B.GROUP_NAME,
	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)

RANK

같은 순위면 다음 순위 건너 뜀 - (1, 1, 3, 4)

SELECT A.PRODUCT_NAME,
	A.PRICE,
	B.GROUP_NAME,
	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)

DENSE_RANK

같은 순위면 다음 순위 건너 뛰지 않음 (1, 1, 2, 3)

SELECT A.PRODUCT_NAME,
	A.PRICE,
	B.GROUP_NAME,
	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)

FIRST_VALUE

특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 특정 컬럼의 첫번째 값

SELECT A.PRODUCT_NAME,
	A.PRICE,
	B.GROUP_NAME,
	FIRST_VALUE(A.PRICE) 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)

LAST_VALUE

특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 특정 컬럼의 마지막 값

SELECT A.PRODUCT_NAME,
	A.PRICE,
	B.GROUP_NAME,
	LAST_VALUE(A.PRICE) 
	OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GROUP_ID)
-- GROUP_NAME 기준으로 PRICE컬럼으로 정렬한 값 중에서 파티션의 첫번째 로우부터 파티션의 마지막 로우까지