[PostgreSQL] 집계함수 vs 분석함수 (GROUP BY, PARTITION BY)

집계함수

- 집계함수는 여러행 또는 테이블 전체 행으로부터 그룹별로 집계하여 결과를 반환한다.

select   category_id
,        AVG(price) 
from     products 
group by category_id 
order by category_id

category_id	avg
1	18.5
2	23.67
6	97
7	30
8	31

 

 

분석함수

- 테이블에 있는 데이터를 특정 용도로 분석하여 결과를 반환하는 함수이다.

- 분석함수는 집계 결과를 각 행마다 보여준다.

select supplier_id
,      category_id
,      AVG(price) over (partition by category_id) 
from   products

supplier_id	category_id	avg
1	1	18.5
1	1	18.5
2	2	23.67
2	2	23.67
3	2	23.67
1	2	23.67
3	2	23.67
4	6	97
3	7	30
4	8	31

 

 

Sample Data

create table products (
	product_id varchar(2)
,	product_name varchar(100)
,	supplier_id varchar(2)
,	category_id varchar(2)	
,	unit varchar(255)
,	price float
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'1'
,	'Chais'
,	'1'
,	'1'
,	'10 boxes x 20 bags'
,	18
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'2'
,	'Chang'
,	'1'
,	'1'
,	'24 - 12 oz bottles'
,	19
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'3'
,	'Aniseed Syrup'
,	'1'
,	'2'
,	'12 - 550 ml bottles'
,	10
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'4'
,	'Chef Anton Cajun Seasoning'
,	'2'
,	'2'
,	'48 - 6 oz jars'
,	22
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'5'
,	'Chef Anton Gumbo Mix'
,	'2'
,	'2'
,	'36 boxes'
,	21.35
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'6'
,	'Grandma Boysenberry Spread'
,	'3'
,	'2'
,	'12 - 8 oz jars'
,	25
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'7'
,	'Uncle Bob Organic Dried Pears'
,	'3'
,	'7'
,	'12 - 1 lb pkgs'
,	30
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'8'
,	'Northwoods Cranberry Sauce'
,	'3'
,	'2'
,	'12 - 12 oz jars'
,	40
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'9'
,	'Mishi Kobe Niku'
,	'4'
,	'6'
,	'18 - 500 g pkgs'
,	97
);

insert into products (
	product_id
,	product_name
,	supplier_id
,	category_id
,   unit
,	price
) values (
	'10'
,	'Ikura'
,	'4'
,	'8'
,	'12 - 200 ml jars'
,	31
);

 

Reference

http://www.gurubee.net/lecture/2671

분석함수란?