데이터 크기가 작다면 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

columnbucketrange_lowrange_highcnt
0price122500450003
1price245000675007
2price367500900003
3price4900001125001
4price51125001350001




이제 위의 구슬들을 잘 꿰어서 진주 목걸이를 만들 차례가 되었습니다. 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)

columnbucketrange_lowrange_highcnt
0price122500450003
1price245000675007
2price367500900003
3price4900001125001
4price51125001350001

 

rng_bucket('houses', 'price', 10)

columnbucketrange_lowrange_highcnt
0price122500450001
1price245000675002
2price367500900004
3price4900001125001
4price51125001350004
5price61350001575001
6price92025002250001
7price102250002475001


rng_bucket('houses', 'tax', 5)

columnbucketrange_lowrange_highcnt
0tax120405
1tax240606
2tax360801
3tax4801002
4tax51001201


잘 동작하는군요! ^^


 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로 시각화해서 보세요. 자주 쓰는 코드라면 사용자 정의 함수를 만들어놓고 재사용하시구요. 


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

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


Posted by R Friend R_Friend