PostgreSQL 분석함수 AVG 함수

실습 테이블 만들기 https://dog-developers.tistory.com/154

AVG 란?

분석함수 AVG() 부터 본격적으로 분석함수를 알아 볼 수 있습니다.

 

기본문법

 

- 사용하고자 하는 분석함수를 쓰고 대상 컬럼을 기재 후 PARTITION BY에서 값을 구하는 기준 컬럼을 작성 후

ORDER BY에서 정렬 컬럼을 기재합니다.

SELECT
	C1,
    분석함수(C2,C3,...) OVER(PARTITION BY C4 ORDER BY C5)
FROM TABLE_NAME;

 

실습

 

- PRODUCT 테이블의 전체 PRICE 평균 구하기.

select 
	AVG (PRICE)
from
	PRODUCT;

실습1

 

- PRODUCT_GROUP 테이블과 PRODUCT 테이블을 GROUP_ID컬럼 기준으로 INNER JOIN합니다.

그 후 PRODUCT_GROUP테이블의 ->GROUP_NAME 컬럼을 기준으로 PRICE컬럼의 평균값을 구합니다.

 

SELECT
	B.GROUP_NAME,
	AVG(PRICE)
FROM
	PRODUCT A
INNER JOIN PRODUCT_GROUP B ON
	(A.GROUP_ID = B.GROUP_ID)
GROUP BY
	B.GROUP_NAME

PRODUCT_GROUP 테이블의 GROUP_NAME 기준의 평균값이 조회 됩니다.

실습2

- 위의 실습처럼 GROUP_NAME을 기준으로 평균값을 구하지만 위에처럼 정확한 결과 값만을 조회 하는것이 아니라

GROUP_NAME과 평균도 나오고 PRODUCT 테이블의 정보도 같이 나오고 싶을때 조회 방법

SELECT
	A.PRODUCT_NAME,
	A.PRICE,
	B.GROUP_NAME,
	AVG(PRICE) OVER (PARTITION BY B.GROUP_NAME)
FROM
	PRODUCT A
INNER JOIN PRODUCT_GROUP B ON
	(A.GROUP_ID = B.GROUP_ID)

PRODUCT 의 정보와 PRODUCT_GROUP의 정보+평균 값이 같이 조회됩니다.

실습3

ORDER BY절이 추가되었을때 알아보기

 

- 위의 문법과 아래의 문법의 정렬의 값은 같습니다.

SELECT
	A.PRODUCT_NAME,
	A.PRICE,
	B.GROUP_NAME,
	AVG(PRICE) OVER (PARTITION BY B.GROUP_NAME ORDER BY B.GROUP_NAME)
FROM
	PRODUCT A
INNER JOIN PRODUCT_GROUP B ON
	(A.GROUP_ID = B.GROUP_ID)

정렬 하는 기준값이 PARTITION BY와 같을경우 ORDER BY를 작성하나 하지 않으나 같은 결과를 보여줍니다.

하지만 ORDER BY 정렬 기준값이 A.PRICE를 기준으로 정렬하면 값은 달라집니다.

실습3

 

- 같은 조건에서 ORDER BY 기준값만 A.PRICE 로 변경하여 조회합니다.

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

결과의 값은 누적집계로 변경 됩니다.

1번 행의 LABTOP의 가격은 700이므로 1번행의 평균은 700/1 = 700 입니다.

2번 행의 LAPTOP의 가격도 700이므로 (700+700) / 2 = 700 입니다.

3번 행의 LAPTOP의 가격은 800이므로 (700 + 700 + 800) / 3 = 733.333... 입니다.

4번행의 LAPTOP 가격은 1,200이므로 (700 + 700 + 800 + 1200) = 850입니다.

이렇듯 GROUP_NAME을 기준으로 행들의 누적값의 평균을 계산하여 조회합니다.

실습3

 

PARTITION BY와 ORDER BY를 조합하여 여러가지의 평균값을 분석할 수 있습니다.