[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot)

데이터 크기가 작다면 Python이나 R로 데이터를 로딩해서 직접 동일 간격 구간별 집계를 한다든지, 변수별 히스토그램이나 박스 그래프, 막대그래프를 그릴 수 있을 것입니다. 하지만 데이터 크기가 로컬 컴퓨터의 메모리 한계를 벗어날 정도로 크다면 그때는 DB에서 SQL로 집계를 한 후, 집계 결과만을 Python이나 R로 가져와서 시각화를 하는 방법을 생각해볼 수 있습니다. 

이번 포스팅에서는 

(1) Greenplum DB, PostgreSQL DB의 width_bucket() SQL 함수를 사용하여 동일 간격 범위별로 관측치 개수를 세어보고, 

(2) Python에서 width_bucket() SQL Query를 재사용하기 쉽게 사용자 정의 함수(user defined function)을 정의하여, 

(3) Python으로 DB connect하여 GPDB에서 집계한 결과로 막대 그래프 그리는 방법

을 소개하겠습니다. 

먼저, 예제로 사용할 간단한 houses (세금, 화장실 개수, 욕실 개수, 가격, 크기) 데이터를 DBeaver나 PGadmin IV 등의 DB tool을 사용해서 테이블을 생성하고 insert into 해보겠습니다. 

DROP TABLE IF EXISTS houses; CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,             size INT, lot INT); INSERT INTO houses VALUES   (1 ,  590 ,       2 ,    1 ,  50000 ,  770 , 22100),   (2 , 1050 ,       3 ,    2 ,  85000 , 1410 , 12000),   (3 ,   20 ,       3 ,    1 ,  22500 , 1060 ,  3500),   (4 ,  870 ,       2 ,    2 ,  90000 , 1300 , 17500),   (5 , 1320 ,       3 ,    2 , 133000 , 1500 , 30000),   (6 , 1350 ,       2 ,    1 ,  90500 ,  820 , 25700),   (7 , 2790 ,       3 ,  2.5 , 260000 , 2130 , 25000),   (8 ,  680 ,       2 ,    1 , 142500 , 1170 , 22000),   (9 , 1840 ,       3 ,    2 , 160000 , 1500 , 19000),  (10 , 3680 ,       4 ,    2 , 240000 , 2790 , 20000),  (11 , 1660 ,       3 ,    1 ,  87000 , 1030 , 17500),  (12 , 1620 ,       3 ,    2 , 118600 , 1250 , 20000),  (13 , 3100 ,       3 ,    2 , 140000 , 1760 , 38000),  (14 , 2070 ,       2 ,    3 , 148000 , 1550 , 14000),  (15 ,  650 ,       3 ,  1.5 ,  65000 , 1450 , 12000); SELECT * FROM houses;

1. Greenplum DB, PostgreSQL DB의 width_bucket() SQL 함수를 사용하여      동일 간격 범위별로 관측치 개수를 세기

houses 테이블에서 가격(price) 변수의 최소값은 $22,500, 평균은 $122,140, 최대값은 $260,000 이군요. 

SELECT  min(price) AS min_val,   avg(price) AS mean, max(price) AS max_val FROM houses

위에서는 SQL문의 min(), avg(), max() 함수를 써서 이들 통계량을 구했는데요, 경우에 따라서는 동일 간격의 범위/ 구간별로 관측치가 몇 개 있는지를 구해보고 싶은 경우가 있습니다. (<- 히스토그램으로 시각화를 하죠)  SQL 문으로 하려면 width_bucket() 함수를 사용하면 case when 문을 길게 쓰지 않고도 간편하게 '동일 간격 범위의 bucket 별로 관측치를 집계'할 수 있습니다. 

houses 테이블의 price 칼럼에 대해 5개의 동일 간격(즉, 4개의 cutting line을 사용)의 bucket별로 관측치를 세어(count) 보겠습니다. width_bucket(변수, 시작 값, 끝 값, cutting line 개수) 의 순서로 arguments를 넣어주면 되며, 이렇게 만든 bucket 별로 count(*) 하고, GROUP BY bucket 으로 그룹별 묶어주면 됩니다. 

SELECT  width_bucket(price, 22500, 260000, 4) AS bucket,  count(*) AS cnt FROM houses GROUP BY bucket ORDER BY bucket;

그런데, 위의 집계 결과를 보면 각 bucket의 구간이 어떻게 되는지 표만 봐서는 알 수 없어서 좀 갑갑한 면이 있습니다. 그래서 이번에는 각 bucket의 구간의 시작(from_val)과 끝(to_val)의 값을 같이 볼 수 있도록 해보겠습니다. 그리고 width_bucket(변수, 시작 값, 끝 값, cutting line 개수) 에서 '시작 값(starting point)'과 '끝 값(ending point)'을 수작업으로 직접 입력해주는 것이 아니라 해당 변수의 최소값과 최대값을 min(), max() 함수로 구해서 자동으로 입력해줄 수 있도록 with clause SQL문을 사용해서 짜보겠습니다. 

WITH stats AS ( SELECT  min(price) AS min_val,  max(price) AS max_val FROM houses ), bucket_tbl AS ( SELECT  width_bucket(price, min_val, max_val, 4) AS bucket,  count(*) AS cnt FROM houses, stats GROUP BY bucket ORDER BY bucket ) SELECT  bucket,  min_val + (bucket-1)*min_val AS from_val,  min_val + bucket*min_val AS to_val,  cnt FROM stats, bucket_tbl;

처음보다 훨씬 보기에 좋아졌습니다. 그렇지요?! 

여기까지 짜보았으니 이제 슬슬 더 욕심이 나기 시작합니다. DB에서 SQL로 bucket 별 관측치 개수를 집계할 결과를 Python이나 R로 가져와서 시각화를 해보면 더 가독성이 좋아질텐데....., 위의 SQL문을 사용자 정의 함수로 만들어서 table이름, 변수이름, bucket 개수 입력값만 바꾸어주면 알아서 집계를 해주면 더 편할텐데.... 하는 욕심 말이지요. 

그래서, Python으로 Greenplum 이나 PoesgreSQL DB에 connect하여 위의 width_bucket() SQL문을 사용자 정의 함수로 만들고, DB에서 집계한 결과를 pandas의 DataFrame.plot.bar() 함수로 시각화해보겠습니다. 

2. Python에서 width_bucket() SQL Query를 재사용하기 쉽게     사용자 정의 함수(user defined function)을 정의

여기서부터는 Jupyter Notebook이나 Spyder 와 같은 Python IDE에서 진행하면 됩니다. (저는 Jupyter Notebook을 사용하였습니다)

아래 Python 코드는 psycopg2 라이브러리를 사용해서 Greenplum DB 혹은 PostgreSQL DB에 접속하고, Query문을 받아서 실행시킨 후에, 그 결과를 pandas DataFrame으로 반환하는 사용자 정의 함수 read_sql() 함수를 정의한 것입니다. 

import pandas as pd import matplotlib as plt %matplotlib inline # UDF for GPDB connection and Querying, Save to pandas DataFrame def read_sql(query):      import pandas as pd      import psycopg2 as pg     # DB Connection (put it with yours)      conn = pg.connect(host='localhost',                         port='5432',                         dbname='postgres',                         user='postgres',                         password='postgres')      # Get a DataFrame      result = pd.read_sql(query, conn)      conn.close()      return result

위에서 정의한 read_sql() 사용자 정의함수를 사용해서 5개 bucket 별로 관측치 개수를 집계한 SQL query문을 날려보겠습니다. SQL query문은 따옴표 3개 (""" sql query syntax """) 안에 써주면 됩니다. read_sql() 사용자 정의 함수가 잘 작동하는군요. 

 

query = """     WITH stats AS (         SELECT              min(price) AS min_val,              max(price) AS max_val         FROM houses     ), bucket_tbl AS (         SELECT          width_bucket(price, min_val, max_val, 4) AS bucket,          count(*) AS cnt     FROM houses, stats     GROUP BY bucket     ORDER BY bucket     )     SELECT         'price' AS column,         bucket,          min_val + (bucket-1)*min_val AS range_low,          min_val + bucket*min_val AS range_high,          cnt     FROM stats, bucket_tbl; """ rng_bucket = read_sql(query) rng_bucket column bucket range_low range_high cnt 0 price 1 22500 45000 3 1 price 2 45000 67500 7 2 price 3 67500 90000 3 3 price 4 90000 112500 1 4 price 5 112500 135000 1

이제 위의 구슬들을 잘 꿰어서 진주 목걸이를 만들 차례가 되었습니다. Python의 format() 문을 접목하여 동일 간격 bucket 별로 관측치 개수를 집계하는 SQL query 문의 테이블 이름(tbl_nm), 변수 이름(var_nm), bucket 개수(bucket_num) 의 3개 인자를 받는 Python 사용자 정의 함수 rng_bucket(tbl_nm, var_nm, bucket_num)를 만들어보겠습니다. 

def rng_bucket(tbl_nm, var_nm, bucket_num=10):          query = """     WITH stats AS (         SELECT              min({var_nm}) AS min_val,              max({var_nm}) AS max_val         FROM {tbl_nm}     ), bucket_tbl AS (         SELECT          width_bucket({var_nm}, min_val, max_val, ({bucket_num}-1)) AS bucket,          count(*) AS cnt     FROM {tbl_nm}, stats     GROUP BY bucket     ORDER BY bucket     )     SELECT          '{var_nm}' AS column,         bucket,          min_val + (bucket-1)*min_val AS range_low,          min_val + bucket*min_val AS range_high,          cnt     FROM stats, bucket_tbl     ORDER BY bucket     """.format(tbl_nm = tbl_nm,                var_nm = var_nm,                bucket_num = bucket_num)          rng_bucket = read_sql(query)          return rng_bucket;

목걸이로 다 꿰었으니 이제 rng_bucket() 사용자 정의 함수에 'price' 변수에 대해 bucket 개수를 5개, 10개로 넣어서 실행시켜 보고, 변수 이름을 'price' 대신 'tax'를 넣어서도 실행시켜 보겠습니다. 

rng_bucket('houses', 'price', 5) column bucket range_low range_high cnt 0 price 1 22500 45000 3 1 price 2 45000 67500 7 2 price 3 67500 90000 3 3 price 4 90000 112500 1 4 price 5 112500 135000 1   rng_bucket('houses', 'price', 10) column bucket range_low range_high cnt 0 price 1 22500 45000 1 1 price 2 45000 67500 2 2 price 3 67500 90000 4 3 price 4 90000 112500 1 4 price 5 112500 135000 4 5 price 6 135000 157500 1 6 price 9 202500 225000 1 7 price 10 225000 247500 1 rng_bucket('houses', 'tax', 5) column bucket range_low range_high cnt 0 tax 1 20 40 5 1 tax 2 40 60 6 2 tax 3 60 80 1 3 tax 4 80 100 2 4 tax 5 100 120 1

잘 동작하는군요! ^^

3. Python으로 DB connect하여 GPDB에서 집계한 결과로 막대 그래프 그리기

마지막으로, pandas DataFrame으로 반환받은 세금(tax) 변수의 5개 bucket 별 집계 결과를 pandas 막대그래프(bar graph)로 그려보겠습니다. 

tax_bucket = rng_bucket('houses', 'price', 5) ax = tax_bucket.plot.bar(x='bucket', y='cnt', rot=0)

요약하자면, 데이터 사이즈가 수백 테라바이트, 수 페타바이트급이면 Greenplum, PostgreSQL DB에서 집계하시구요, 결과는 Python이나 R로 시각화해서 보세요. 자주 쓰는 코드라면 사용자 정의 함수를 만들어놓고 재사용하시구요. 

많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요. :-)