[PostgreSQL, Greenplum] 연속형 데이터의 집계/ 요약 통계량 구하기

이번 포스팅에서는 PostgreSQL, Greenplum database에서 SQL, MADlib 함수, PL/R, PL/Python을 사용해서 연속형 데이터에 대한 요약통계량을 구하는 방법을 소개하겠습니다.  무척 쉬운 내용이므로 쉬어가는 코너 정도로 가볍게 생각해주시면 좋겠습니다. ^^

PostgreSQL, Greenplum database 에서 연속형 데이터에 대해 그룹별로, 

(1) SQL 로 요약통계량 구하기

(2) Apache MADlib 으로 요약통계량 구하기

참고로, 이번 포스팅에서는 PostgreSQL 9.4, Greenplum 6.10.1 버전을 사용하였으며, PostgreSQL 9.4 버전보다 낮은 버전을 사용하면 최빈값(mode), 사분위부(percentile) 구하는 함수를 사용할 수 없습니다. 

먼저, 예제로 사용하기 위해 '나이'의 연속형 데이터와 '성별'의 범주형 데이터 (그룹)를 가진 간단한 테이블을 만들어보겠습니다. 결측값(missing value)도 성별 그룹별로 몇 개 넣어봤습니다. 

DROP TABLE IF EXISTS cust; CREATE TABLE cust (id INT, age INT, gender TEXT); INSERT INTO cust VALUES (1,NULL,'M'), (2,NULL,'M'), (3,25,'M'), (4,28,'M'), (5,27,'M'), (6,25,'M'), (7,26,'M'), (8,29,'M'), (9,25,'M'), (10,27,'M'), (11,NULL,'F'), (12,23,'F'), (13,25,'F'), (14,23,'F'), (15,24,'F'), (16,26,'F'), (17,23,'F'), (18,24,'F'), (19,22,'F'), (20,23,'F');

(1) SQL로 연속형 데이터의 그룹별 요약통계량 구하기

함수가 굳이 설명을 안해도 될 정도로 간단하므로 길게 설명하지는 않겠습니다. 

표준편차 STDDEV() 와 분산 VARIANCE() 함수는 표본표준편차(sample standard deviation), 표본분산(sample variance) 를 계산해줍니다. 만약 모표준편차(population standard deviation), 모분산(population variance)를 구하고 싶으면 STDDEV_POP(), VAR_POP() 함수를 사용하면 됩니다. 

PostgreSQL 9.4 버전 이상부터 최빈값(MODE), 백분위수(Percentile) 함수가 생겨서 정렬한 후에 집계하는 기능이 매우 편리해졌습니다. (MODE(), PERCENTILE_DISC() 함수를 사용하지 않고 pure SQL로 최빈값과 백분위수를 구하려면 query 가 꽤 길어집니다.)

SELECT     gender AS group_by_value     , 'age' AS target_column     , COUNT(*) AS row_count     , COUNT(DISTINCT age) AS distinct_values     , AVG(age)     , VARIANCE(age)     , STDDEV(age)     , MIN(age)     , PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY age) AS first_quartile     , MEDIAN(age)     , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY age) AS third_quartile     , MAX(age)     , MODE() WITHIN GROUP (ORDER BY age) -- over PostgreSQL 9.4 FROM cust WHERE age IS NOT NULL GROUP BY gender ORDER BY gender;

성별 그룹별로 연령(age) 칼럼의 결측값 개수를 구해보겠습니다. 

결측값 개수는 WHERE age IS NULL 로 조건절을 주고 COUNT(*)로 행의 개수를 세어주면 됩니다. 

SELECT      gender     , COUNT(*) AS missing_count FROM cust WHERE age IS NULL GROUP BY gender ORDER BY gender; Out[5]: gender missing_count F 1 M 2

위의 집계/ 요약통계량과 결측값 개수를 하나의 조회 결과로 보려면 아래처럼 Join 을 해주면 됩니다.

WITH summary_tbl AS (     SELECT         gender AS group_by_value         , 'age' AS target_column         , COUNT(*) AS row_count         , COUNT(DISTINCT age) AS distinct_values         , AVG(age)         , VARIANCE(age)         , STDDEV(age)         , MIN(age)         , PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY age) AS first_quartile         , MEDIAN(age)         , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY age) AS third_quartile         , MAX(age)         , MODE() WITHIN GROUP (ORDER BY age)     FROM cust     WHERE age IS NOT NULL     GROUP BY gender     ORDER BY gender ), missing_tbl AS (     SELECT         gender AS group_by_value         , COUNT(*) AS missing_count     FROM cust     WHERE age IS NULL     GROUP BY gender ) SELECT a.*, b.missing_count FROM summary_tbl a LEFT JOIN missing_tbl b USING(group_by_value) ;

(2) Apache MADlib으로 연속형 데이터의 그룹별 요약통계량 구하기

Apache MADlib의 madlib.summary() 함수를 사용하면 단 몇 줄의 코드만으로 위의 (1)번에서 SQL 집계 함수를 사용해서 길게 짠 코드를 대신해서 매우 깔끔하고 간단하게 구할 수 있습니다. 

아래는 (1)번의 결과를 얻기위해 성별(gender) 연령(age) 칼럼의 집계/요약데이터를 구하는 madlib.summary() 함수 예시입니다. 

Target columns 위치에는 1 개 이상의 분석을 원하는 연속형 데이터 칼럼을 추가로 넣어주기만 하면 되므로 (1) 번의 pure SQL 대비 훨씬 편리한 측면이 있습니다! 

그리고 그룹별로 구분해서 집계/요약하고 싶으면 Grouping columns 위치에 기준 칼럼 이름을 넣어주기만 하면 되므로 역시 (1)번의 pure SQL 대비 훨씬 편리합니다!

DROP TABLE IF EXISTS cust_summary; SELECT madlib.summary('cust'     -- Source table                       ,'cust_summary'   -- Output table                       , 'age'                -- Target columns                       , 'gender'            -- Grouping columns );

madlib.summary() 함수의 결과 테이블에서 조회할 수 있는 집계/요약통계량 칼럼 리스트는 아래와 같습니다. 

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS     WHERE TABLE_SCHEMA = 'public'         AND TABLE_NAME    = 'cust_summary'     ORDER BY ORDINAL_POSITION; Out[7]: column_name group_by group_by_value target_column column_number data_type row_count distinct_values missing_values blank_values fraction_missing fraction_blank positive_values negative_values zero_values mean variance confidence_interval min max first_quartile median third_quartile most_frequent_values mfv_frequencies

[Reference]

* PostgreSQL aggregate functions: https://www.postgresql.org/docs/9.4/functions-aggregate.html

* Apache MADlib summary function: https://madlib.apache.org/docs/v1.11/group__grp__summary.html

다음번 포스팅에서는 PostgreSQL, Greenplum에서 SQL과 Apache MADlib을 이용하여 상관계수, 상관계수 행렬을 구하는 방법(https://rfriend.tistory.com/581)을 소개하겠습니다.

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

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