[PostgreSQL, Greenplum] Window 함수를 사용해서 그룹별로 요약통계량 구하기

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 Window 함수를 사용해서 그룹별로 요약 통계량을 구하고, 이 통계량을 이용해서 새로운 변수를 만드는 방법을 소개하겠습니다. 

 

(1) 전체 평균: MAX(value) OVER(PARTITION BY NULL) 을 계산해서 새로운 변수 만들기

(2) 그룹별 평균: MAX(value) OVER(PARTITION BY group_col) 을 계산해서 새로운 변수 만들기

 

 

먼저, 예제로 사용할 테이블을 만들어보겠습니다. 

 

-------------------------------------------------------------------
-- Aggregation by groupby using Window Function
-------------------------------------------------------------------

-- creating a sample table
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
	grp TEXT 
	, id INT
	, val INT
);

INSERT INTO tbl VALUES 
  ('a', 1, 4)
, ('a', 2, 1)
, ('a', 3, 3)
, ('a', 4, 5)
, ('a', 5, 2)
, ('b', 6, 7)
, ('b', 7, 5)
, ('b', 8, 8)
, ('b', 9, 10)
, ('b', 10, 9)
;

SELECT * FROM tbl ORDER BY 1, 2;
--grp|id|val|
-----+--+---+
--a  | 1|  4|
--a  | 2|  1|
--a  | 3|  3|
--a  | 4|  5|
--a  | 5|  2|
--b  | 6|  7|
--b  | 7|  5|
--b  | 8|  8|
--b  | 9| 10|
--b  |10|  9|

 

 

 

(1) 전체 평균: MAX(value) OVER (PARTITION BY NULL) 을 계산해서 새로운 변수 만들기

 

아래 예에서는 그룹별 구분없이 전체 최대값을 계산해서 MAX(val) OVER(PARTITION BY NULL) 해서 원래 값을 나누어주어서 새로운 변수 val_max_ration 를 만들어주었습니다. 

 

-- MAX value for all values using Window Function OVER(PARTITION BY NULL)
SELECT 
	a.*
	, val::NUMERIC / MAX(val) OVER(PARTITION BY NULL) 
		AS val_max_ratio 
FROM tbl AS a
ORDER BY 1, 2
;

--grp|id|val|val_max_ratio         |
-----+--+---+----------------------+
--a  | 1|  4|0.40000000000000000000|
--a  | 2|  1|0.10000000000000000000|
--a  | 3|  3|0.30000000000000000000|
--a  | 4|  5|0.50000000000000000000|
--a  | 5|  2|0.20000000000000000000|
--b  | 6|  7|0.70000000000000000000|
--b  | 7|  5|0.50000000000000000000|
--b  | 8|  8|0.80000000000000000000|
--b  | 9| 10|1.00000000000000000000|
--b  |10|  9|0.90000000000000000000|

 

 

 

(2) 그룹별 평균: MAX(value) OVER(PARTITION BY group_col) 을 계산해서 새로운 변수 만들기

 

아래의 예에서는 MAX(val) OVER(PARTITION BY grp) 로 그룹별 최대값을 구해서 원래 값을 나누어줌으로써 그룹별 최대값 대비 값의 비율(val_grp_max_ration) 이라는 새로운 변수를 만들었습니다. 

 

-- MAX value by Group using Window Function OVER(PARTITION BY grp)
SELECT 
	a.*
	, val::NUMERIC / MAX(val) OVER(PARTITION BY grp) 
		AS val_grp_max_ratio 
FROM tbl AS a
ORDER BY 1, 2
;

--grp|id|val|val_grp_max_ratio         |
-----+--+---+----------------------+
--a  | 1|  4|0.80000000000000000000|
--a  | 2|  1|0.20000000000000000000|
--a  | 3|  3|0.60000000000000000000|
--a  | 4|  5|1.00000000000000000000|
--a  | 5|  2|0.40000000000000000000|
--b  | 6|  7|0.70000000000000000000|
--b  | 7|  5|0.50000000000000000000|
--b  | 8|  8|0.80000000000000000000|
--b  | 9| 10|1.00000000000000000000|
--b  |10|  9|0.90000000000000000000|

 

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요!  :-)