지난번 포스팅에서는 PostgreSQL, Greenplum database에서 SQL과 Apache MADlib을 활용해서 대용량의 연속형 데이터에 대한 In-DB 상관관계 분석(Correlation Analysis in Database)에 대해서 알아보았습니다.

이번 포스팅에서는 상관관계 분석에서 한발 더 나아가서, 설명변수(독립변수) X와 목표변수(종속변수) Y 간의 선형/인과관계를 모델링하는 선형 회귀모형(Linear Regression)을 훈련(train)하고 예측(predict)하는 방법을 소개하겠습니다.

PostgreSQL, Greenplum database에서 대용량 데이터에 대해
(1) SQL로 선형 회귀모형 적합하고 모수 확인하기
(2) SQL로 그룹별로 선형 회귀모형 적합하고 예측하기
(3) Apache MADlib으로 다중 선형 회귀모형 적합하기
(4) Apache MADlib으로 그룹별로 다중 선형 회귀모형 적합하고 예측하기




먼저, 예제로 사용할 데이터로 4개의 연속형 데이터('sepal_length', 'sepal_width', 'petal_length', 'petal_width')와 1개의 범주형 데이터('class_name')를 가진 iris 데이터셋으로 테이블을 만들어보겠습니다.



-- Iris data table
DROP TABLE IF EXISTS iris;
CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,
                    petal_length FLOAT, petal_width FLOAT,
                   class_name text);
INSERT INTO iris VALUES
(1,5.1,3.5,1.4,0.2,'Iris-setosa'),
(2,4.9,3.0,1.4,0.2,'Iris-setosa'),
(3,4.7,3.2,1.3,0.2,'Iris-setosa'),
(4,4.6,3.1,1.5,0.2,'Iris-setosa'),
(5,5.0,3.6,1.4,0.2,'Iris-setosa'),
(6,5.4,3.9,1.7,0.4,'Iris-setosa'),
(7,4.6,3.4,1.4,0.3,'Iris-setosa'),
(8,5.0,3.4,1.5,0.2,'Iris-setosa'),
(9,4.4,2.9,1.4,0.2,'Iris-setosa'),
(10,4.9,3.1,1.5,0.1,'Iris-setosa'),
(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),
(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),
(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),
(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),
(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),
(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),
(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),
(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),
(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),
(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),
(21,6.3,3.3,6.0,2.5,'Iris-virginica'),
(22,5.8,2.7,5.1,1.9,'Iris-virginica'),
(23,7.1,3.0,5.9,2.1,'Iris-virginica'),
(24,6.3,2.9,5.6,1.8,'Iris-virginica'),
(25,6.5,3.0,5.8,2.2,'Iris-virginica'),
(26,7.6,3.0,6.6,2.1,'Iris-virginica'),
(27,4.9,2.5,4.5,1.7,'Iris-virginica'),
(28,7.3,2.9,6.3,1.8,'Iris-virginica'),
(29,6.7,2.5,5.8,1.8,'Iris-virginica'),
(30,7.2,3.6,6.1,2.5,'Iris-virginica');

SELECT * FROM iris ORDER BY id LIMIT 5;





  (1) SQL로 선형 회귀모형 적합하고 모수 확인하기


PostgreSQL 에서 설명변수(독립변수) X 1개와 목표변수(종속변수) Y 와의 선형 회귀모형을 적합할 수 있습니다. 대신에 하나의 함수로 한번에 선형 회귀모형을 적합하는 것은 아니구요, REGR_SLOPE(Y, X) 함수로 기울기(slope)를 구하고, REGR_INTERCEPT(Y, X)로 Y절편을 구할 수 있습니다.



-- Python으로 산점도와 선형회귀선을 겹쳐서 그래보면 아래와 같습니다.



## Scatter Plot using Python seaborn package


import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
plt.rcParams['figure.figsize'] = [12, 8]


iris = sns.load_dataset('iris')


sns.regplot(x=iris['petal_length'],
            y=iris['petal_width'],
            fit_reg=True)

plt.title('Scatter Plot with Regression Line', fontsize=16)
plt.show()

 




REGR_COUNT(Y, X)는 관측치의 개수, REGR_AVGY(Y, X) 는 Y의 평균 값, REGR_AVGX(Y, X) 는 X의 평균 값을 구해줍니다.


그리고 REGR_R2(Y, X)는 적합된 선형회귀모형의 설명력을 보여주는 결정계수(coefficient of determination)를 구해줍니다.



----------------------------
-- (1) PostgreSQL functions
----------------------------
-- Training a Regression using PostgreSQL regr_slope(Y, X), regr_intercept(Y, X) function
DROP TABLE IF EXISTS iris_regr_postgres;
CREATE TABLE iris_regr_postgres AS (
SELECT
    'petal_width' AS y_var_nm
    , 'petal_length' AS x_var_nm
    , REGR_SLOPE(petal_width, petal_length)
    , REGR_INTERCEPT(petal_width, petal_length)
    , REGR_R2(petal_width, petal_length)
    , REGR_AVGY(petal_width, petal_length)
    , REGR_AVGX(petal_width, petal_length)
    , REGR_COUNT(petal_width, petal_length)
FROM  iris
);



SELECT * FROM iris_regr_postgres;







  (2) SQL로 그룹별로 선형 회귀모형 적합하고 예측하기


다음으로 'class_name' 범주('iris_setosa', 'iris_versicolor', 'iris_virginica') 그룹별로 1개 설명변수 'petal_length'와 종속변수 'petal_width'의 관계를 모형화하는 선형 회귀모형을 적합해보겠습니다.


위의 (1)번 SQL query에 SELECT 문에 그룹 칼럼('class_name')을 넣어주고, FROM 절 다음에 GROUP BY 그룹 칼럼('class_name') 을 넣어주면 됩니다. 모델 3개가 잘 적합되었습니다.



-- Regression by Groups
DROP TABLE IF EXISTS iris_regr_grp_postgres;
CREATE TABLE iris_regr_grp_postgres AS (
SELECT
    class_name AS group_nm
    , 'petal_width' AS y_var_nm
    , 'petal_length' AS x_var_nm
    , REGR_SLOPE(petal_width, petal_length)
    , REGR_INTERCEPT(petal_width, petal_length)
    , REGR_R2(petal_width, petal_length)
    , REGR_AVGY(petal_width, petal_length)
    , REGR_AVGX(petal_width, petal_length)
    , REGR_COUNT(petal_width, petal_length)
FROM  iris
GROUP BY class_name
ORDER BY class_name
);



SELECT * FROM iris_regr_grp_postgres ORDER BY group_nm;





이제 위에서 적합한 class_name별 3개 모델(기울기 slope, Y절편 intercept)의 모수를 활용해서 아래의 수식을 사용해서 예측을 해보겠습니다.



-- Prediction
SELECT
    iris.class_name
    , iris.id
    , iris.petal_width AS y_petal_width
    , (iris.petal_length * m.regr_slope + m.regr_intercept) AS pred_petal_width
FROM iris, iris_regr_grp_postgres m
WHERE iris.class_name = m.group_nm
ORDER BY id;







  (3) Apache MADlib으로 다중 선형 회귀모형(Multiple Linear Regression) 적합하기


SQL 기반의 오픈소스 Apache MADlib의 madlib.linregr_train() 함수를 사용하여 PostgreSQL, Greenplum database에서 여러개의 설명변수를 사용하는 다중 선형회귀모형 (multiple linear regression)을 적합할 수 있습니다.


위의 (1)번 PostgreSQL 의 기울기, Y절편 함수에서는 설명변수 X로 1개의 칼럼만을 사용하는 한계가 있었습니다. 그리고 기울기와 Y절편, R^2 등을 구하기 위해 개별 함수를 사용해야 하는 불편함이 있었습니다.


반면에, MADlib의 madlib.linregr_train() 함수는 source table, output table, dependent variable, ARRAY[1, independent variables] 의 순서대로 칼럼 이름을 넣어주면 됩니다. 그러면 회귀계수(coef), 결정계수(r2), 설명변수별 표준화오차(std_err)와 t통계량(t_stats), p값(p_values), condition_no, 관측치 개수(num_rows_processed), 결측치 개수(num_missing_rows_skipped), 분산공분산(variance_covariance) 의 결과를 반환합니다.



----------------
-- (2) MADlib
----------------
-- Multivariate Regression using MADlib
DROP TABLE IF EXISTS iris_regr, iris_regr_summary;
SELECT madlib.linregr_train(
    'iris'              -- source table
    , 'iris_regr'      -- output table
    , 'petal_width' -- dependent variable
    , 'ARRAY[1, petal_length, sepal_length]' -- independent variables
);

SELECT * FROM iris_regr;





위의 선형 회귀모형 적합 결과를 좀더 보기 좋도록 UNNEST() 를 사용해서 설명변수별로 구분해서 풀어서 제시해보겠습니다.



SELECT
    UNNEST(ARRAY['intercept', 'petal_length', 'sepal_length']) AS var_nm
    , UNNEST(coef) AS coef
    , UNNEST(std_err) AS std_err
    , UNNEST(t_stats) AS t_stats
    , UNNEST(p_values) AS p_values
FROM iris_regr;






  (4) Apache MADlib으로 그룹별로 다중 선형 회귀모형 적합하고 예측하기


이번에는 class_name 범주의 그룹별로 다중 선형회귀모형을 적합해 보겠습니다.


madlib.linregr_tarin() 함수의 5번째 인자에 Grouping Column으로서 'class_name' 을 넣어주면 됩니다.



-- Multiple Regression by Group using MADlib
DROP TABLE IF EXISTS iris_regr_grp, iris_regr_grp_summary;
SELECT madlib.linregr_train(
    'iris'          -- source table
    , 'iris_regr_grp'   -- output table
    , 'petal_width' -- dependent variable
    , 'ARRAY[1, petal_length, sepal_length]' -- indepent variables
    , 'class_name'  -- grouping column
);

--SELECT * FROM iris_regr_grp;

SELECT
    class_name
    , UNNEST(ARRAY['intercept', 'petal_length', 'sepal_length']) AS var_nm
    , UNNEST(coef) AS coef
    , UNNEST(std_err) AS std_err
    , UNNEST(t_stats) AS t_stats
    , UNNEST(p_values) AS p_values
FROM iris_regr_grp;





위에서 3개 범주 그룹별로 적합한 모델을 사용해서 madlib.linregr_predict() 함수로 예측을 해보겠습니다. 이때 WHERE 조건절에 input dataset의 class_name 범주와 모델 테이블의 class_name 이 같아야 한다는 조건을 추가해줍니다.



-- Prediction
SELECT iris.*,
       madlib.linregr_predict( m.coef,
                               ARRAY[1,petal_length,sepal_length]
                             ) AS predict
FROM iris, iris_regr_grp m
WHERE  iris.class_name = m.class_name
ORDER BY id
LIMIT 10;





[Reference]
* PostgreSQL 9.4: https://www.postgresql.org/docs/9.4/functions-aggregate.html
* Apache MADlib : https://madlib.apache.org/docs/latest/group__grp__linreg.html


이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)



Posted by R Friend Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 PostgreSQL, Greenplum database에서 SQL, MADlib 을 사용하여 연수형 데이터의 집계/ 요약통계량(aggregation/ summary statistics)을 구하는 방법(https://rfriend.tistory.com/580)을 소개하였습니다.

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 SQL, Apache MADlib 을 사용하여 여러개의 연속형 변수들 간의 상관관계를 구하는 방법을 소개하겠습니다.

(1) SQL 로 두개 연속형 변수간 상관계수 구하기
(2) SQL 로 다수의 연속형 변수간 상관계수 행렬(Correlation Matrix) 구하기
(3) MADlib으로 다수의 연속형 변수간 상관계수 행렬 구하기
(4) MADlib으로 다수의 연속형 변수간 그룹별로 상관계수 행렬(Correlation Matrix by Group) 구하기



예제로 사용하도록 연속형 데이터 4개 칼럼과 범주형 데이터 1개를 가지고 iris 샘플 데이터를 테이블로 만들어보겠습니다.



-- Iris data table
DROP TABLE IF EXISTS iris;
CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,
                    petal_length FLOAT, petal_width FLOAT,
                   class_name TEXT);
INSERT INTO iris VALUES
(1,5.1,3.5,1.4,0.2,'Iris-setosa'),
(2,4.9,3.0,1.4,0.2,'Iris-setosa'),
(3,4.7,3.2,1.3,0.2,'Iris-setosa'),
(4,4.6,3.1,1.5,0.2,'Iris-setosa'),
(5,5.0,3.6,1.4,0.2,'Iris-setosa'),
(6,5.4,3.9,1.7,0.4,'Iris-setosa'),
(7,4.6,3.4,1.4,0.3,'Iris-setosa'),
(8,5.0,3.4,1.5,0.2,'Iris-setosa'),
(9,4.4,2.9,1.4,0.2,'Iris-setosa'),
(10,4.9,3.1,1.5,0.1,'Iris-setosa'),
(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),
(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),
(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),
(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),
(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),
(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),
(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),
(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),
(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),
(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),
(21,6.3,3.3,6.0,2.5,'Iris-virginica'),
(22,5.8,2.7,5.1,1.9,'Iris-virginica'),
(23,7.1,3.0,5.9,2.1,'Iris-virginica'),
(24,6.3,2.9,5.6,1.8,'Iris-virginica'),
(25,6.5,3.0,5.8,2.2,'Iris-virginica'),
(26,7.6,3.0,6.6,2.1,'Iris-virginica'),
(27,4.9,2.5,4.5,1.7,'Iris-virginica'),
(28,7.3,2.9,6.3,1.8,'Iris-virginica'),
(29,6.7,2.5,5.8,1.8,'Iris-virginica'),
(30,7.2,3.6,6.1,2.5,'Iris-virginica');



SELECT * FROM iris ORDER BY id LIMIT 5;






  (1) SQL 로 두개 연속형 변수간 상관계수 구하기


공분산(Covariance)은 두 변량이 각각의 평균으로부터 변화하는 방향 및 양에 대한 기대값입니다. PostgreSQL, Greenplum DB에서 모집단의 공분산(Population Covariance)는 COVAR_POP(Y, X) 함수를 사용하며, 샘플 공분산(Sample Covariance)은 COVAR_SAMP(Y, X) 함수를 사용합니다.

(* 공분산, 상관계수 참고: https://rfriend.tistory.com/126)


피어슨 상관계수(Pearson's Correlation Coefficient)는 공분산을 표준편차로 나누어서 표준화해준 값으로서, -1~1 사이의 값을 가집니다. 1에 가까울수록 두 변수간에 양의 선형관계가 있고, -1에 가까울수록 음의 선형관계가 있으며, 0이면 선형관계가 없다고 해석합니다. PostgreSQL, Greenplum DB에서는 CORR(Y, X) 함수를 사용합니다.



----- b/w Y and X
-- Population Covariance using SQL: covar_pop(Y, X)
SELECT COVAR_POP(sepal_length, sepal_width) FROM iris;


-- Sample Covariance using SQL: covar_samp(Y, X)
SELECT COVAR_SAMP(sepal_width, sepal_length) FROM iris;


-- Pearson's Correlation Coefficients using SQL: corr(Y, X)
SELECT CORR(sepal_width, sepal_length) FROM iris;





  (2) SQL 로 다수의 연속형 변수간 상관계수 행렬(Correlation Matrix) 구하기


먼저, 이해를 돕기 위해서 2단계로 나누어서 과정을 설명하자면요, 긴 형태(long format)로 두 연속형 변수 간의 상관계수를 구해보겠습니다. (두번째 단계에서 Pivoting 하는데 사용하는, sub query 의 내용임)


------ Correlation Coefficients among multiple variables
-- Correlation Matrix

-- (a) Calculating pairwise correlation using corr(Y, X) function

SELECT
    1 AS column_position
    , 'sepal_length' AS row
    , 'sepal_width' AS col
    , CORR(sepal_length, sepal_width) AS corr_coef
FROM iris
UNION
SELECT

    1 AS column_position
    , 'sepal_length' AS row
    , 'petal_length' AS col
    , CORR(sepal_length, petal_length) AS corr_coef
FROM iris
UNION
SELECT

    1 AS column_position
    , 'sepal_length' AS row
    , 'petal_width' AS col
    , CORR(sepal_length, petal_width) AS corr_coef
FROM iris
UNION
SELECT

    2 AS column_position
    , 'sepal_width' AS row
    , 'petal_length' AS col
    , CORR(sepal_width, petal_length) AS corr_coef
FROM iris
UNION
SELECT

    2 AS column_position
    , 'sepal_width' AS row
    , 'petal_width' AS col
    , CORR(sepal_width, petal_width) AS corr_coef
FROM iris
UNION
SELECT

    3 AS column_position
    , 'petal_length' AS row
    , 'petal_width' AS col
    , CORR(petal_length, petal_width) AS corr_coef
FROM iris;




위의 SQL query를 sub query 로 넣고, manual 하게 테이블을 Pivot 하여 우리가 원하는 상관계수 행렬 (correlation matrix)를 만들어보겠습니다. 대각행렬에서 우상단에만 상관계수를 나타내고 좌하단에는 0으로 채워넣기를 하였습니다.



-- (b) Pivoting the table to get a correlation matrix

SELECT
    column_position
    , row
    , SUM(CASE WHEN col='sepal_length' THEN corr_coef ELSE 0 END) AS sepal_length
    , SUM(CASE WHEN col='sepal_width' THEN corr_coef ELSE 0 END) AS sepal_width
    , SUM(CASE WHEN col='petal_length' THEN corr_coef ELSE 0 END) AS petal_length
    , SUM(CASE WHEN col='petal_width' THEN corr_coef ELSE 0 END) AS petal_width
FROM (
    SELECT
        1 AS column_position
        , 'sepal_length' AS row
        , 'sepal_width' AS col
        , CORR(sepal_length, sepal_width) AS corr_coef
    FROM iris
    UNION
    SELECT

        1 AS column_position
        , 'sepal_length' AS row
        , 'petal_length' AS col
        , CORR(sepal_length, petal_length) AS corr_coef
    FROM iris
    UNION
    SELECT

        1 AS column_position
        , 'sepal_length' AS row
        , 'petal_width' AS col
        , CORR(sepal_length, petal_width) AS corr_coef
    FROM iris
    UNION
    SELECT

        2 AS column_position
        , 'sepal_width' AS row
        , 'petal_length' AS col
        , CORR(sepal_width, petal_length) AS corr_coef
    FROM iris
    UNION
    SELECT

        2 AS column_position
        , 'sepal_width' AS row
        , 'petal_width' AS col
        , CORR(sepal_width, petal_width) AS corr_coef
    FROM iris
    UNION
    SELECT
        3 AS column_position
        , 'petal_length' AS row
        , 'petal_width' AS col
        , CORR(petal_length, petal_width) AS corr_coef
    FROM iris
) a
GROUP BY column_position, row
ORDER BY column_position;




원하는 상관계수행렬을 구하기는 했는데요, SQL query가 엄청 길어지고 또 복잡해졌습니다. 분석의 대상이 되는 연속형 데이터 칼럼 수가 늘어날수록 query가 더 길어지고 복잡해지는 구조여서 더 좋은 방법이 없을까 고민하게 됩니다. 짜잔~! 이럴때 PostgreSQL, Greenplum DB에서 쉽고 편하고 또 빠르게 분석하는데 쓸 수 있는 SQL 기반의 오픈소스 분석툴인 Apache MADlib이 있습니다!




  (3) MADlib으로 다수의 연속형 변수간 상관계수 행렬 구하기


위의 (2)번에서 pure SQL로 했던 상관계수 행렬 구하기를 Apache MADlib의 madlib.correlation() 함수를 사용하면 아래처럼 단 3줄이면 끝납니다! 비교할 수 없을 정도로 SQL query가 간소해졌습니다!



----- Correlation Matrix using MADlib
DROP TABLE IF EXISTS iris_corr_output, iris_corr_output_summary;
SELECT madlib.correlation( 'iris',             -- source table
                           'iris_corr_output',       -- output table
                           'sepal_length, sepal_width, petal_length, petal_width' -- target columns
                         );


/*                         
SELECT madlib.correlation( 'iris',        -- source table
                           'iris_corr_output',  -- output table
                           '*'                     -- for all columns
                         );                        
*/
                       


SELECT * FROM iris_corr_output ORDER BY column_position;






  (4) MADlib으로 다수의 연속형 변수간 그룹별로 상관계수 행렬구하기

       (Correlation Matrix by Group using Apache MADlib)


PostgreSQL, Greenplum DB에서 Apache MADlib을 사용했을 때 코드가 간소해지는점 말고 또 좋은 점 중의 하나는 그룹별 연산을 하기에도 굉장히 편리하다는 점입니다.


아래 예제는 class_name ('iris-setosa', 'iris-versicolor', 'iris-virginica') 의 그룹별로 상관계수 행렬을 구하기(correlation matrix by groups using MADlib) 입니다. 위의 (3)번 예제어서 'grouping column' 위치에 "class_name" 칼럼 이름만 추가하면 끝입니다. MADlib이 이처럼 "simple & powerful" 합니다. (만약 (2)번 방법으로 SQL로 일일이 group by 넣어서 query 짠다고 상상을 해보면 끔찍하지 않나요? ^^;)



-- Correlation Matrix by Groups using MADlib
DROP TABLE IF EXISTS iris_corr_grp_output, iris_corr_grp_output_summary;
SELECT madlib.correlation( 'iris'             -- source table
                           , 'iris_corr_grp_output' -- output table
                           , '*' -- target columns
                           , False -- verbose
                           , 'class_name' -- grouping column
                         );
                         


SELECT * FROM iris_corr_grp_output ORDER BY class_name, column_position;




[Reference]
* PostgreSQL 9.4: https://www.postgresql.org/docs/9.4/functions-aggregate.html
* Apache MADlib: https://madlib.apache.org/docs/v1.0/group__grp__correlation.html

이번 포스팅이 많은 도움이 되었기를 바랍니다 .
행복한 데이터 과학자 되세요!  :-)




Posted by R Friend Rfriend

댓글을 달아 주세요

이번 포스팅에서는 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]:
gendermissing_count
F1
M2





위의 집계/ 요약통계량과 결측값 개수를 하나의 조회 결과로 보려면 아래처럼 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)을 소개하겠습니다.


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

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



Posted by R Friend Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 Python의 ipython-sql 패키지를 이용하여 Jupyter Notebook 의 로컬변수(variable in local namespace)로 PostgreSQL, Greenplum database에 동적으로 SQL query를 하는 3가지 방법을 소개하였습니다.(https://rfriend.tistory.com/578)


이번 포스팅에서는 Python의 ipython-sql 패키지를 이용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database에 SQL query한 결과를 pandas DataFrame으로 가져오는 (즉, 객체로 할당하는) 3가지 방법을 소개하겠습니다.


(1) ResultSet = %sql SELECT ... (single-line query)

(2) %%sql ResultSet << SELECT ... (multi-line queries)

(3) %%sql SELECT ... 를 먼저 실행한 후에,

    ResultSet = _  (select 결과를 사후에 할당)

==> (공통) sql.run.ResultSet을 pandas DataFrame으로 변환: df = ResultSet.DataFrame()






  (0) Python 패키지 사전 설치 및 PostgreSQL, Greenplum database 접속


명령 프롬프트 창에서 pip로 PostgreSQL, Greenplum database에 접속하여 SQL query를 할 수 있는 Python 패키지를 설치해주세요.



-- (명령 프롬프트 창에서)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 



ipython-sql 패키지를 이용하여 Jupyter Notebook에서 Greenplum database에 접속해보겠습니다.



%load_ext sql


# %sql postgresql://username:passowrd@host:port/database

%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 1 rows affected.




예제로 사용할 간단한 houses 테이블을 만들고 데이터를 넣어보겠습니다.



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






  (1) ResultSet = %sql SELECT ... (single-line query)


SQL query가 1줄 짜리인 경우에 일반적인 IPython assignment 방식(result = %sql SELECT ...)으로 간단하게 사용할 수 있습니다.


SQL query 결과를 할당받으면 'sql.run.ResultSet' 데이터 형태입니다. 이를 pandas DataFrame으로 변환하려면 pandas의 DataFrame() 메소드를 사용하면 됩니다. ResultSet.DataFrame() 처럼 해서 일단 pandas DataFrame으로 변환을 해주고 나면, pandas의 데이터전처리 및 시각화 함수를 사용할 수 있습니다.



# for 1 line SQL query
house_sub_1 = %sql SELECT * FROM houses WHERE price <= 80000;

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
3 rows affected.

 

house_sub_1

[Out]

idtaxbedroombathpricesizelotregion
32031.02250010603500busan
159021.05000077022100seoul
1565031.565000145012000inchon


type(house_sub_1)

[Out] sql.run.ResultSet


# converting 'sql.run.ResultSet into pandas DataFrame

df_house_sub_1 = house_sub_1.DataFrame()
type(df_house_sub_1)

[Out] pandas.core.frame.DataFrame


# plot using pandas function

import matplotlib.pyplot as plt
df_house_sub_1['price'].plot(kind='bar')
plt.show()






  (2) %%sql ResultSet << SELECT ... (multi-line queries)


2줄 이상의 SQL quries 결과를 할당하려면 %%sql result_name 그리고 이어서 '<<' 연산자(<< operator)를 사용합니다.

(참고로, %sql 은 1줄 짜리 SQL query를 쓸 때, %%sql 은 2줄 이상의 SQL query를 쓸 때 사용함)


'sql.run.ResultSet' 데이터 형태로 할당받은 결과를 DataFrame() 메소드를 사용해서 pandas DataFrame으로 변환하는 것은 위의 (1)번과 동일합니다.



%%sql house_sub_2 << SELECT *
FROM houses
WHERE price <= 80000;

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected. Returning data to local variable house_sub_2

 

house_sub_2

[Out]

idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
32031.02250010603500busan
1565031.565000145012000inchon


type(house_sub_2)

[Out] sql.run.ResultSet


# Converting 'sql.run.ResultSet' into pandas DataFrame

df_house_sub_2 = house_sub_2.DataFrame()
type(df_house_sub_2)

[Out] pandas.core.frame.DataFrame





  (3) %%sql SELECT ... 를 먼저 실행한 후에,

    ResultSet = _  (select 결과를 사후에 할당)


경우에 따라서는 먼저 %sql 로 1줄 짜리 SQL query를 먼저 하거나, 혹은 %%sql 로 2줄 이상의 SQL query 를 Jupyter Notebook에서 먼저 실행한 후에, 이의 결과를 확인하고 나서 나중에 그 결과를 Python의 pandas DataFrame으로 가져오고 싶을 때도 있을 것입니다.


이럴 경우에 직전에 실행한 %sql SELECT ... 또는 %%sql SELECT ... 결과를 result = _ 를 사용해서 'sql.run.ResultSet'으로 할당받을 수 있습니다.


'sql.run.ResultSet' 데이터 형태로 할당받은 결과를 DataFrame() 메소드를 사용해서 pandas DataFrame으로 변환하는 것은 위의 (1)번과 동일합니다.



%sql SELECT * FROM houses WHERE price <= 80000;

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected.

Out[22]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
32031.02250010603500busan
1565031.565000145012000inchon


# Capturing the quries results and assigning to ResultSet

house_sub_3 = _


house_sub_3

[Out]

idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
32031.02250010603500busan
1565031.565000145012000inchon


type(house_sub_3)

[Out] sql.run.ResultSet

 

df_house_sub_3 = house_sub_3.DataFrame()
type(df_house_sub_3)

[Out] pandas.core.frame.DataFrame



* Reference: https://pypi.org/project/ipython-sql/


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

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


Posted by R Friend Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 Python의 ipython-sql, psycopg2 패키지를 사용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database에 접속할 수 있는 4가지 방법(https://rfriend.tistory.com/577)을 소개하였습니다.


이번 포스팅에서는 Python의 ipython-sql, psycopg2 패키지를 사용하여 PostgreSQL, Greenplum database에 SQL query를 할 때 Jupyter Notebook의 로컬 변수를 SQL query에 대입하여 변수값을 동적으로 대체해가면서 query 할 수 있는 3가지 방법을 소개하겠습니다.


Python의 로컬 변수를 SQL query 문에 사용할 수 있으므로 Python과 PostgreSQL, Greenplum DB를 서로 연동해서 데이터분석과 프로그래밍을 하는 경우 매우 강력하고 유용하게 사용할 수 있습니다.


(방법 1) Variable Substitution:  %sql SELECT :variable_name

(방법 2) Variable Substitution:  %sql SELECT {variable_name}

(방법 3) Variable Substitution:  %sql SELECT $variable_name





  (0) 필요 Python 패키지 사전 설치


아래의 SQLAlchemy, psycopg2, ipython-sql, pgspecial, sql_magic 중에서 아직 설치가 안된 패키지가 있다면 아래처럼 명령 프롬프트 창에서 Python의 패키지를 설치해줍니다.



-- (명령 프롬프트 창에서 pip 로 설치)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

 



ipython-sql 패키지로 Jupyter Notebook에서 Greenplum database에 접속한 후에, 예제로 사용할 간단한 houses 테이블을 만들어보겠습니다.



%load_ext sql


%sql postgresql://gpadmin:changeme@localhost/demo

[Out] 'Connected: gpadmin@demo'


%sql select version();

 * postgresql://gpadmin:***@localhost/demo
1 rows affected.
Out[3]:
version
PostgreSQL 9.4.24 (Greenplum Database 6.10.1 build commit:efba04ce26ebb29b535a255a5e95d1f5ebfde94e) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Aug 13 2020 02:56:29


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





  (방법 1) Variable Substitution:   %sql SELECT :variable_name


첫번째 방법은 :variable_name 과 같은 named style 을 사용해서 Jupyter Notebook에서 local namespace에 생성한 변수 이름을 SQL query에 넣어서 동적으로 값을 바꾸어 가면서 query 를 해보겠습니다.


(1-1) :variable_name 으로 SQL의 정수(integer) 변수값 대체



id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN :id_var;

[Out]

* postgresql://gpadmin:***@localhost/demo 3 rows affected.

Out[123]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(1-2) :variable_name 으로 SQL의 문자형(character) 변수값 대체



region_var = 'seoul'

%sql SELECT * FROM houses WHERE region = :region_var;

[Out]

* postgresql://gpadmin:***@localhost/demo 4 rows affected.

Out[125]:
idtaxbedroombathpricesizelotregion
10368042.0240000279020000seoul
159021.05000077022100seoul
2105032.085000141012000seoul
487022.090000130017500seoul


 




  (방법 2) Variable Substitution:  %sql SELECT {variable_name}


(2-1) {variable_name} 으로 SQL의 정수(integer) 변수값 대체



id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN {id_var};

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected.

Out[126]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(2-2) '{variable_name}' 으로 SQL의 문자형(character) 변수값 대체



region_var = 'seoul'

%sql SELECT * FROM houses WHERE region = '{region_var}';

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 4 rows affected.

Out[127]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
10368042.0240000279020000seoul
2105032.085000141012000seoul
487022.090000130017500

seoul




  (방법 3) Variable Substitution:  %sql SELECT $variable_name


(3-1) $variable_name 으로 SQL의 정수형(integer) 변수값 대체



id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN $id_var;

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected.

Out[128]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(3-2) $variable_name 으로 SQL의 문자형(character) 변수값 대체


localname space에 문자형의 bind parameter 값 입력해줄 때 큰따옴표(" ")로 감싸주고, 그 안에 작은따옴표(' ')로 값 입력해주도록 하세요. 그냥 작은따옴표(' ')만 했더니 칼럼으로 인식을 해서 에러가 나네요.



region_var = "'seoul'"

%sql SELECT * FROM houses WHERE region = $region_var;

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected.

Out[171]:
idtaxbedroombathpricesizelotregion
2105032.085000141012000seoul
487022.090000130017500seoul
10368042.0240000279020000seoul

 



* Reference: https://pypi.org/project/ipython-sql/



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

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



Posted by R Friend Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 Python의 ipython-sql, pgspecial 패키지를 사용하여 Jupyter Notebook 에서 PostgreSQL, Greenplum database 에 접속(access)하고 SQL query, meta-commands 를 하는 방법(https://rfriend.tistory.com/572)을 간략하게 소개하였습니다.


이번 포스팅에서는 psycopg2 와 ipython-sql 패키지를 사용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database 에 접속하는 4가지 방법을 추가로 소개하겠습니다.


특히, 사용자이름(username), 비밀번호(password), 호스트(host), 포트(port), 데이터베이스(database) 등과 같이 보안이 요구되고 다른 사용자에게 노출이나 공유가 되면 곤란한 정보들(DB Credentials)을 Jupyter Notebook에서 표기/노출하지 않고 별도의 파일로 보관하면서, 이를 불러와서 DB access 할 수 있는 방법들에 주안점을 두고 소개하겠습니다.


-- Jupyter Notebook 의 Cell 안에서 DB credentials 직접 입력 (* 외부 노출되므로 권장하지 않음)

(1) %sql postgresql://Username:Password@Host:Port/Database

(2) %sql $connection_string


-- 별도의 폴더에 별도의 파일로 DB credentials 관리하고, 이를 불러와서 Jupyter Notebook에 입력

    (* 보안유지 되므로 권장함)

(3) config.py 별도 파일 & %sql $connection_string

(4) db_credentions 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path





(0) Python 패키지 사전 설치


먼저, 명령 프롬프트 창에서 아래의 PostgreSQL, Greenplum database에 접속하고 SQL query 를 하기 위해 필요한 Python 패키지들을 pip로 설치해줍니다.



-- (명령 프롬프트 창에서 설치)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 



-- Jupyter Notebook 의 Cell 안에서 DB credentials 직접 입력

   (* 편리하기는 하지만, DB 접속 정보가 외부에 노출되므로 권장하지 않음. )


 (1) %sql postgresql://Username:Password@Host:Port/Database


가장 편리한 방법은 SQLAlchemy 표준 URL (database-driver://Username:Password@Host:Port/Database) 에 따라 PostgreSQL, Greenplum database에 connection engine을 생성하여 접속하는 방법입니다.


하지만, 이 방법은 Jupyter Notebook에 DB 접속정보가 고스란히 노출되기 때문에 만약 다른 조직, 팀원 간에 협업을 하고 notebook 파일을 공유해야할 일이 생길 경우 보안 방침에 위배가 되므로 권장하는 방법은 아닙니다.



%load_ext sql


# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@demo'







 (2) %sql $connection_string


%sql $connection_string 문으로 Jupyter Notebook에서 동적으로 DB credentials 를 Python string format 으로 입력받아서 PostgreSQL, Greenplum database에 접속할 수 있습니다. 


다만, 아래처럼 DB credentials 를 Jupyter notebook 의 Cell 안에서 직접 입력하면 DB 접속 정보가 외부로 노출되는 문제가 있습니다.



%load_ext sql


# DB credentials

username = "gpadmin"
password = "changeme"
host = "localhost"
port = "5432"
database = "demo"


# connection strings using Python string format
connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)


# dynamic access dredentials
%sql $connection_string

 





-- 별도의 폴더에 별도의 파일로 DB credentials 관리하고, 이를 불러와서 Jupyter Notebook에 입력

    (* 보안유지 되므로 권장함)


 (3) config.py 별도 파일 & %sql $connection_string


세번째 방법은 DB Credentials 정보를 별도의 파일에 분리해서 만들어놓고, 이를 불러와서 DB connect 하는 방법입니다. 아래에 예를 들어보면, (폴더, 파일 이름은 각자 알아서 정해주면 됨)


(a) HOME directory 밑에 DB credentials 파일을 넣어둘 'db_cred' 라는 이름의 폴더 만들고,

(b) 'db_cred' 폴더 안에 'gpdb_credentials.py', '__init__.py' 라는 이름의 2개의 Python 파일을 생성함.

     'gpdb_credentials.py' 파일에는 Dictionary (Key : Value 짝) 형태로 Username, Password, Host, Port, Database 정보를 입력해줌. 여러개의 Database 별로 credentials 정보를 각각 다른 이름의 Dictionary 로 하나의 파일 안에 생성해놓을 수 있음.

     '__init__.py' 파일은 내용은 비어있으며, 해당 폴더의 Python 파일을 패키지로 만들기 위해 생성해줌.

(c) Jupyter Notebook 을 작업하는 Directory 에서도 HOME directory 밑의 'db_cred' 폴더에 접근해서 'gpdb_credentials.py' 파일에 접근할 수 있도록 sys.path.append(cred_path) 로 Python 의 Path 에 추가해줌. (sys.path.append(cred_path))

(d) 작업을 하는 Jupyter Notebook 에서 'from gpdb_credentials import demo_db' 문으로 gpdb_credentials.py 파일에서 'demo_db' Dictionary 를 불러옴.

(e) 'demo_db' Dictionary 에서 DB connection에 필요한 정보를 파싱해옴. (dict['key'] 인덱싱)

(f) %sql $ 문 뒤에 (e)에서 파싱해서 만든 connection_string을 입력해서 DB connect 함.



%load_ext sql


# put a folder and DB credential files at HOME directory

import os
homedir = os.getenv('HOME')
cred_path = os.path.join(homedir, 'db_cred')


# add a 'cred_path' for interpreter to search
import sys
sys.path.append(cred_path)


# import DB credentials from 'gpdb_credentials.py' dictionary file.

from gpdb_credentials import demo_db


# parsing DB credentials and connect to Greenplum using %sql $connection_string

username = demo_db['Username']
password = demo_db['Password']
host = demo_db['Host']
port = demo_db['Port']
database = demo_db['Database']

connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)

%sql $connection_string






(4) db_credentials 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path


명령 프롬프트 창에서 아래처럼 0.3.9 버전의 ipython-sql을 설치해줍니다. (최신 버전은 0.4.0 이지만 Python 3.x. 버전의 ipython-sql 0.4.0 버전에 DSN connections 를 하는데 있어 config 를 반환하지 않는 bug가 있습니다. config bug fix 되기 전까지는 0.3.9 버전으로 사용하기 바랍니다.)


-- 명령 프롬프트 창에서 ipython-sql 0.3.9 버전 설치

pip install ipython-sql==0.3.9


(a) PostgreSQL, Greenplum database 접속 정보(connection info.)를 별도의 configuration file 에 저장하여 HOME directory 밑에 보관합니다. 이때 2개 이상의 복수의 DB credentials 정보를 [DB alias] 로 구분해서 하나의 configuration file에 저장해서 사용할 수 있습니다.


파일 이름을 ".odbc.ini", ".dsn.ini" 처럼 "."으로 시작하면 '숨김 파일(hidden file)'이 되어 평상시에는 탐색기, Finder에서는 볼 수가 없으므로 DB 접속정보를 관리하는데 좀더 보안에 유리합니다.

(참고로, Windows OS에서 숨김파일을 보려면, Windows 탐색기에서 [구성] > [폴더 및 검색 옵션] > [폴더 옵션] 대화상자에서 [보기] 탭을 클릭 > [고급 설정]에서 "숨김 파일 밒 폴더 표시"를 선택하면 됩니다.

Mac OS 에서는 Finder에서 "Shift + Command + ." 동시에 눌러주면 숨김 파일이 표시됩니다.)


(b) Jupyter Notebook에서 ipython-sql 로 DSN connections 을 할 수 있습니다.

    %config SqlMagic.dsn_filename = "$homedir/.odbc.ini"


(c) DB connect 된 이후에 제일 처음으로 %sql 로 SQL query 할 때 DB credentions 의 DB alias 를 [ ] 안에 넣어서 명시를 해주고(예: %sql [demo_db] SELECT version();), 그 다음부터 %sql 문으로 SQL query 할 때는 DB alias 를 안써주고 바로 SQL query 를 하면 됩니다.


(d) Jupyter Notebook의 중간 Cell 에서 사용(connect)하려는 DB를 바꾸고 싶으면 %sql [DB_alias2] SELECT .... 처럼 [DB_alias] 부분에 다른 DB alias 이름을 명시해주고 SQL query 를 하면, 그 이후 Cell 부터는 새로운 DB 를 connect 해서 query를 할 수 있습니다.

(예: %sql [dev_db] SELECT COUNT(*) FROM tbl;)



%load_ext sql


import os
homedir = os.getenv('HOME')

# parse and configure gpdb credentials and access to GPDB
%config SqlMagic.dsn_filename = "$homedir/.odbc.ini"

# put [alias_name] after %sql in the first line
%sql [demo_db] SELECT version();

[Out] * postgresql://gpadmin:***@localhost:5432/demo

1 rows affected.



* Reference: https://pypi.org/project/ipython-sql/


다음 포스팅에서는 ipython-sql 로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법(https://rfriend.tistory.com/578)을 소개하겠습니다.


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

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



Posted by R Friend Rfriend

댓글을 달아 주세요

Python의 SQLAlchemy (모든 DB), psycopg2 (PostgreSQL, Greenplum) 패키지를 이용하여 Spyder 나 Pycharm 과 같은 IDE에서 PostgreSQL, Greenplum database에 접속(access) 하고 SQL query 를 할 수 있습니다.




그렇다면 Jupyter Notebook 에서도 DB access, SQL query를 할 수 있으면 편하겠지요?


이번 포스팅에서는 Python의 ipython-sql 과 pgspecial 패키지를 이용하여 PostgreSQL, Greenplum database 에 Jupyter Notebook으로 접속(access DB)하여 SQL query 를 하는 방법을 소개하겠습니다.



(1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기

(2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)

(3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands query 하기 (\l, \dn, \dt)






(0) 사전 설치가 필요한 Python 패키지 리스트


명령 프롬프트 창에서 아래의 5개 패키지에 대해서 pip 로 설치해주시기 바랍니다.


sqlalchemy, psycopg2는 PostgreSQL, Greenplum DB 접속(access, connection)를 위해서 필요한 Python 패키지 입니다.


ipython-sql, sql_magic은 IPython으로 Jupyter Notebook에서 DB access, SQL query 를 하기 위해 필요한 Python 패키지입니다.


pgspecial은 Jupyter Notebook에서 PostgreSQL, Greenplum DB에 meta-commands (역슬래쉬 \ 로 시작하는, psql 에서 사용하는 \l, \dn, \dt 명령문) 를 위해 필요한 Python 패키지입니다.


ipython-sql 의 경우 2020.12월 현재 0.4.0 버전 (python 3.x) 이 최신인데요, %config로 DB access 하는 명령문의 bug가 아직 fix가 안되어 있어서, 아래처럼 ipython-sql==0.3.9 로 한단계 낮은 버전으로 설치해주세요.



-- 명령 프롬프트 창에서 pip 로 python 패키지 설치


$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 




 (1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기


%load_ext sql 로 IPython의 sql 을 로딩하여 %sql 또는 %%sql magic 명령문을 사용할 수 있습니다.

PostgreSQL, Greenplum database에 접속할 때는 SQLAlchemy 의 표준 URL connect strings 를 사용합니다.


[ SQLAlchemy 의 표준 Database URL]


 dialect+driver://username:password@hoat:port/database


아래의 db credentials 로 Greenplum database에 접속할 때의 예입니다.

- driver: postgresql

- username: gpadmin

- password: changeme

- host: localhost

- port: 5432

- database: demo



%load_ext sql


# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@testdb'





* 위의 %sql SQLAlchemy 표준 URL 방법 외에 Jupyter Notebook에서 PostgreSQL, Greenplum DB에 접속하는 다른 3가지 추가 방법은 https://rfriend.tistory.com/577 를 참고하세요.




 (2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)


Jupyter Notebook의 Cell 안에 1줄 SQL query일 경우는 %sql 로 시작하고, 2줄 이상 SQL query 일 경우에는 %%sql 로 시작합니다.


(2-1) %sql : 1줄의 SQL query


1줄짜리 SELECT 문으로 PostgreSQL의 버전을 확인해보겠습니다.



%sql SELECT version();


[Out]

version
PostgreSQL 9.4.24 (Greenplum Database 6.10.1 build commit:efba04ce26ebb29b535a255a5e95d1f5ebfde94e) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Aug 13 2020 02:56:29

 





(2-1) %%sql : 2줄 이상의 SQL query


아래 예제는 pg_catalog.pg_tables 테이블에서 스키마 이름이 pg_catalog, information_schema 가 아닌 테이블을 조회하여 1개만 반환해보는 query 입니다.



%%sql
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
    schemaname != 'information_schema'
LIMIT 1;

 





 (3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands (\l, \dn, \dt)


meta-commands 는 psql 에서 역슬래쉬(\)와 함께 사용해서 데이터베이스, 스키마, 테이블 등을 조회할 때 사용하는 명령어를 말합니다. pgspecial 패키지는 Jupyter Notebook에서 meta-commands 를 사용할 수 있게 해줍니다.


(3-1) database 조회 : %sql \l  (역슬래쉬 + L)



%sql \l


[Out]

NameOwnerEncodingCollateCtypeAccess privileges
demogpadminUTF8en_US.utf8en_US.utf8=Tc/gpadmin
gpadmin=CTc/gpadmin
dsuser=CTc/gpadmin
gpperfmongpadminUTF8en_US.utf8en_US.utf8None
postgresgpadminUTF8en_US.utf8en_US.utf8None
template0gpadminUTF8en_US.utf8en_US.utf8=c/gpadmin
gpadmin=CTc/gpadmin
template1gpadminUTF8en_US.utf8en_US.utf8

=c/gpadmin
gpadmin=CTc/gpadmin




(3-2) Schema 조회 : %sql \dn


%sql \dn


[Out]

NameOwner
gp_toolkitgpadmin
madlibdsuser14
publicgpadmin






(3-3) Table 조회 : %sql \dt

아래 예제는 public 스키마에서 "ab" 로 시작하는 모든 테이블(public.ab*)을 조회한 것입니다.

 

%sql \dt public.ab*


[Out]

SchemaNameTypeOwner
publicabalonetable

gpadmin

publicabalone_corrtable

gpadmin

publicabalone_corr_summarytable

gpadmin

publicabalone_correlationstable

gpadmin




이상으로 ipython-sql, pgspecial 패키지를 사용해서 PostgreSQL, Greenplum database에 접속하고 SQL query, meta-commands 하는 방법에 대한 가장 기본적이고 개략적인 소개를 마치겠습니다.

* 다음번 포스팅에서는 SQLAlchemy, psycopg2, ipython-sql 로 Jupyter Notebook 에서 PostgreSQL, Greenplum database에 접속하는 4가지 방법(https://rfriend.tistory.com/577)에 대한 소소한 팁을 추가로 소개하겠습니다.

* ipython-sql 로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법https://rfriend.tistory.com/578 를 참고하세요.

* ipython-sql로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법https://rfriend.tistory.com/579 를 참고하세요.

이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요!



Posted by R Friend Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 PostgreSQL, Greenplum DB에서 문자열형의 종류, 생성, 처리 연산자(Character Type Operators) 및 함수(Character Functions)에 대해서 소개(https://rfriend.tistory.com/543)하였습니다. 


이번 포스팅에서는 PostgreSQL, Greenplum DB에서 문자열 패턴 매칭(String Pattern Matching)하는 세가지 방법을 소개하겠습니다. 


(1) 전통적인 SQL LIKE 연산자 (LIKE Operator)

(2) SIMILAR TO 연산자 (the SIMILAR TO Operator)

(3) 정규 표현식 (POSIX-Style Regular Expressions)




  (1) SQL LIKE, ~~ 연산자 ('LIKE', or '~~' Operator)


'LIKE' 연산자는 전통적인 SQL에서 문자열 패턴 매칭할 때 가장 보편적으로 사용되며, 아래와 같은 syntax를 사용합니다. 


string LIKE pattern [ESCAPE escape-character]


위의 LIKE 연산자는 LIKE 앞의 문자열이 LIKE 뒤의 패턴과 일치할 경우 TRUE를 반환하며, 일치하지 않을 경우는 FALSE를 반환합니다. 


와일드카드 문자(wildcard character)로서 (1) 퍼센트 부호(%)는 그 위치부터 어떤 문자열(any string)이 와도 상관없으며, (2) 밑줄 부호(_)는 해당 위치에 하나의 문자가 일치하는지를 봐서 TRUE나 FALSE를 반환합니다. LIKE와 '%', '_' 와일드카드 문자를 사용한 패턴 매칭 예 몇개를 살펴보겠습니다. 


 pattern matching

 returns

  SELECT 'abc' LIKE 'abc';

 true

  SELECT 'abc' LIKE 'a%';

 true 

  SELECT 'abc' LIKE '%b'; false 

  SELECT 'abc' LIKE '%b%';

 true
  SELECT 'abc' LIKE '%c';

 true 

  SELECT 'abc' LIKE '_b_';

 true 

  SELECT 'abc' LIKE 'c'; false

  SELECT 'abc' LIKE '_c';

 fals

  SELECT 'abc' LIKE '__c';

 true 



 '~~' 연산자를 'LIKE' 연산자 대신 사용할 수 있습니다. 위에 예 중에서 한개만 'LIKE'를 '~~' 연산자로 바꾸어보면 아래와 같습니다. (Jupyter Notebook에서 sql_magic 라이브러리로 SQL문 사용할 때 LIKE 대신 ~~ 연산자를 사용했었습니다.) 



 SELECT 'abc' ~~ 'a%';  --> returns true

 



패턴 일치여부를 판단하는데 있어 대소문자를 구분합니다. 



 SELECT 'abc' LIKE 'a%';  --> returns true

 SELECT 'abc' LIKE 'A%';  --> returns false

 



일치하지 않는 패턴을 찾고 싶으면 'NOT LIKE' 또는 '!~~' 연산자를 사용합니다. 



 SELECT 'abc' LIKE 'a%';         --> returns true

 SELECT 'abc' NOT LIKE 'a%';  --> retruns false


 SELECT 'abc' ~~ 'a%';          --> returns true

 SELECT 'abc' !~~ 'a%';         --> returns false

 




  (2) SIMILAR TO 연산자 (the SIMILAR TO Operator)




 SELECT 'abc' SIMILAR TO 'abc'; -- true

 SELECT 'abc' SIMILAR TO 'a';    -- false


 SELECT 'abc' SIMILAR TO 'a__'; -- true

 SELECT 'abc' SIMILAR TO '_b_'; -- true

 SELECT 'abc' SIMILAR TO '_bc'; -- true


 SELECT 'abc' SIMILAR TO 'a%';   -- true

 SELECT 'abc' SIMILAR TO '%b';   -- false

 SELECT 'abc' SIMILAR TO '%b%'; -- true

 



-- | denotes alternation (either of two alternatives).

-- Parentheses () can be used to group items into a single logical item.

 SELECT 'abc' SIMILAR TO '%(b|d)%'; -- true

 SELECT 'abc' SIMILAR TO '%(d|e)%'; -- false


-- {m} denotes repetition of the previous item exactly m times.

 SELECT 'abbbc' SIMILAR TO '%b{3}%'; -- true

 SELECT 'abbbc' SIMILAR TO '%b{4}%'; -- false


-- {m,} denotes repetition of the previous item m or more times.

 SELECT 'abbbc' SIMILAR TO '%b{2,}%'; -- true

 SELECT 'abbbc' SIMILAR TO '%b{4,}%'; -- false


-- {m,n} denotes repetition of the previous item at least m and not more than n times.

 SELECT 'abbbc' SIMILAR TO '%b{2,3}%'; -- true

 




 SELECT substring('foobar' from '%#"o_b#"%' for '#'); -- oob


--  It returns null if there is no match.

 SELECT substring('foobar' from '#"o_b#"%' for '#');  -- NULL


 SELECT substring('foobar' from 'o...r'); -- oobar 

 SELECT substring('foobar' from 'o(...)r'); -- oba

 




  (3) 정규 표현식 (POSIX-Style Regular Expressions)


정규표현식을 이용하면 위의 (1) LIKE 연산자, (2) SIMILAR TO 연산자보다 더욱 강력한 패턴 매칭을 할 수 있습니다. 



-- Regular Expressions Matching Operators: ~, ~*, !~, !~*

-- ~ operator: Matches regular expression, case sensitive

 SELECT 'thomas' ~ '.*thomas.*'; -- true

 SELECT 'thomas' ~ '.*Thomas.*'; -- False


-- ~* operator: Matches regular expression, case insensitive

 SELECT 'thomas' ~* '.*Thomas.*'; -- true


-- !~ operator: Does not match regular expression, case sensitive

 SELECT 'thomas' !~ '.*thomas.*'; -- False

 SELECT 'thomas' !~ '.*Thomas.*'; -- True


-- !~* operator: Does not match regular expression, case insensitive

 SELECT 'thomas' !~* '.*Thomas.*'; -- false

 SELECT 'thomas' !~ '.*Thomas.*'; -- true

 SELECT 'thomas' !~* '.*steve.*'; -- true




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

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


* Reference: https://www.postgresql.org/docs/9.5/functions-matching.html


Posted by R Friend Rfriend

댓글을 달아 주세요

이번 포스팅에서는 PostgreSQL, Greenplum Database의 문자열 데이터형 (Character Types)의 종류와, 생성, 운영자, 함수 등에 대해서 알아보겠습니다. 


먼저, PostgreSQL 의 문자열형 종류는 아래처럼 

 - (1) 고정 길이 n 바이트 이내의 문자열 character(n), char(n)

 - (2) 가변 길이 n 바이트 이내의 문자열 varchar(n)

 - (3) 제한 없는 가변 길이 문자열 text, varchar

의 3가지 종류가 있습니다. 

PostgreSQL은 이들 3가지 문자열 종류별로 성능 차이는 없습니다.




  (1) PostgreSQL 문자열형 포함한 테이블 생성 및 문자열 입력


위에서 소개한 3개의 문자열형 종류인 CHAR(2), VARCHAR(10), TEXT 를 포함하고 있는 예제 테이블을 만들어보겠습니다. 괄호안의 숫자는 Bytes 길이를 의미합니다. 


-- Create a table for an example

DROP TABLE IF EXISTS char_type_test;

CREATE TABLE char_type_test (

id SERIAL PRIMARY KEY 

, col_1 CHAR(2)

, col_2 VARCHAR(10)

, col_3 TEXT

);




아래의 두가지 경우는 CHAR(2), VARCHAR(10) 처럼 바이트 길이 제약이 있는데 이를 만족하지 않는 경우 SQL ERROR가 발생한 예입니다. 



(1-1) col_1 의 바이트 길이가 CHAR(2) 와 정확하게 맞지 않아서 에러가 나는 경우



-- PostgreSQL issued an error

-- SQL Error [22001]: ERROR: value too long for type character(2)

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hollo'

, 'PostgreSQL WORLD'

, 'I love PostgreSQL all the time'

);





(1-2) col_2 의 바이트 길이가 VARCHAR(10) 보다 길어서 에러가 나는 경우


-- PostgreSQL issued an error

-- SQL Error [22001]: ERROR: value too long for type character varying(10)

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hi'

, 'PostgreSQL WORLD'

, 'I love PostgreSQL all the time'

);





(1-3) col_1, col_2, col_3 가 모두 문자열형 조건을 만족하여 정상 입력된 경우



-- No error

INSERT INTO char_type_test (col_1, col_2, col_3) 

VALUES (

'Hi'

, 'PostgreSQL'

, 'I love PostgreSQL all the time')

, (

'Hi'

, 'Greenplum'

, 'I love Greenplum too'

);


SELECT * FROM char_type_test ORDER BY id;






  (2) PostgreSQL 문자열 연산자 (Operator)와 함수 (Functions)



(2-1) 문자열 합치기 (String Concatenation)


  • 문자열 합치기: '||' 연산자
  • 문자열 합치기: CONCAT() 함수
  • 구분자를 포함하여 문자열 합치기: CONCAT_WS() 함수


(2-1-1) 문자열 합치기 연산자: '||'


지난번 포스팅에서는 PostgreSQL의 4가지 유형의 연산자로서 산술 연산자, 비교 연산자, 논리 연산자, 비트 연산자에 대해서 소개하였는데요, 문자열에 특화된 연산자 중에서 두 문자열을 합칠 때 사용하는 '||' 연산자를 소개하겠습니다. 


string || string

string || non-string or non-string || string



SELECT

*

, col_1 || ', ' || col_2 AS db_name

, id || ' : ' || col_2 AS db_id

FROM char_type_test;





(2-1-2) 두 문자열 합치기 함수: CONCAT()


위의 (2-1-1)과 동일한 결과를 concat() 함수를 사용하여 얻을 수 있습니다. 



SELECT 

*

, CONCAT(col_1, ', ', col_2) AS db_name_2

, CONCAT(id, ' : ', col_2) AS db_id_2

FROM char_type_test;





문자열을 합치는데 사용하는 '||' 연산자와 CONCAT() 함수 사이에는 NULL 값을 처리하는데 있어 차이가 존재합니다. '||' 연산자는 NULL 값이 있으면 NULL 값을 반환하는 반면에, CONCAT() 함수는 NULL 값을 무시하고 나머지 값을 반환합니다. 



-- Concatenate using || with a NULL value returns NULL

SELECT col_1 || NULL AS result_string FROM char_type_test;




-- Concat function ignores NULL arguments.

SELECT CONCAT(col_1, NULL) AS result_string_2 FROM char_type_test;





(2-1-3) 구분자를 포함하여 문자열을 합치기 (CONCAT With Separator): CONCAT_WS()



-- (3) CONCAT_WS : CONCAT With Separator

-- : CONCAT_WS(separator, str_1, str_2, ...);

SELECT 

*

, CONCAT_WS(', ', col_1, col_2) AS db_name_3

, CONCAT_WS(' : ', id, col_2) AS db_id_3

FROM char_type_test;





합치려는 문자열의 개수가 여러개이고 구분자를 포함시키고 싶을 경우, CATCAT_WS() 함수는 구분자를 한번만 써줘도 되므로, 구분자를 매번 써줘어야 하는 CATCAT() 함수보다 편리합니다. 



-- Useful when there are lots of values to concatenate

SELECT CONCAT('a', ': ', 'b', ': ', 'c', ': ', 'd', ': ', 'e', ': ', 'f') AS concat_result_1;





SELECT CONCAT_WS(': ', 'a', 'b', 'c', 'd', 'e', 'f') AS concat_ws_result_2;






(2-2) 문자열 길이 (String Length) 


  • 문자열 내 비트의 길이: BIT_LENGTH(string)
  • 문자열 내 바이트의 길이: OCTET_LENGTH(string)
  • 문자열 내 문자 길이: LENGTH(string)



SELECT

col_3

, BIT_LENGTH(col_3) AS bit_len_col_3

, OCTET_LENGTH(col_3) AS cotet_len_col_3

, LENGTH(col_3) AS char_len_col_3

FROM char_type_test;




문자열 내 바이트의 길이를 재는 OCTET_LENGTH()와 문자 길이를 재는 LENGTH() 함수가 알파벳과 숫자에서는 차이가 없는데요, 한글처럼 문자열 내 바이트와 문자 길이가 다른 경우도 있습니다. 따라서 한글의 바이트를 재고 싶다면 LENGTH()가 아니라 OCTET_LENGTH() 함수를 사용해야 겠습니다. 


 SELECT OCTET_LENGTH('abc123');

 ---------

 result

 6

 SELECT LENGTH('abc123');

 ----------

 result 

 6


 SELECT OCTET_LENGTH('안녕하세요');

 ----------

 result

 15

 SELECT LENGTH('안녕하세요');

 ----------

 result 

 5




(2-3) 문자열 대/소문자 변환 (String Case Conversion)


  • 문자열 내 문자를 소문자로 바꾸기: LOWER(string)
  • 문자열 내 문자를 대문자로 바꾸기: UPPER(string)
  • 문자열 내 첫번째 문자를 대문자로, 나머지 문자는 소문자로 바꾸기: INITCAP(string)


-- lower(string): convert a string to lower case

-- upper(string): convert a string to upper case

-- initcap(string): convert words in a string to title case

SELECT 

col_2

, LOWER(col_2) AS lower_col_2 

, UPPER(col_2) AS upper_col_2 

, INITCAP(col_2) AS initcap_col_2

FROM char_type_test;






(2-4) 문자열 겹쳐쓰기, 바꾸기, 뒤집기, 반복하기


  • 문자열 겹쳐쓰기: OVERLAY(string PLACING string FROM int [FOR int])
  • 문자열 바꾸기: REPLACE(string, FROM, TO)

아래 예에서 OVERLAY() 함수에 대해 부언설명하자면요, 'col_3' 칼럼에서 8번째 자리부터 시작해서 10개 문자에 해당하는 부분을 'Opensource DB' 라는 새로운 문자열로 덮어쓰기(overlay)를 하라는 뜻입니다. 


-- overlay(string placing string from int [for int])

-- replace(string, from, to)

SELECT 

col_3

, OVERLAY(col_3 PLACING 'Opensource DB ' FROM 8 FOR 10) AS overlayed_col_3

, REPLACE(col_3, 'I', 'You') AS replaced_col_3

FROM char_type_test;


 



  • 문자열 순서 뒤집기: REVERSE(string)
  • 문자열 n번 반복하기: REPEAT(string, int)


-- reverse(string)

-- repeat(string, int)

SELECT 

col_2

, REVERSE(col_2) AS reversed_col_2

, REPEAT(col_2, 3) AS repeated_col_2

FROM char_type_test;


 




(2-5) 문자열의 위치 인덱스: POSITION(substring IN string)


POSITION() 함수이 첫번째 인자로 받는 substring 은 대/소문자를 구분합니다. 



----------------------------------

-- Location of specified substring

----------------------------------

SELECT 

col_3

, POSITION('g' in col_3) AS g_position

, POSITION('G' in col_3) AS "G_position"

, POSITION('love' in col_3) AS love_position

FROM char_type_test;






(2-6) 문자열의 부분 문자열을 잘라오기: SUBSTR(string [from int] [for int])



-- substring(string [from int] [for int])

SELECT 

*

, SUBSTR(col_2, 1, 3) AS substr_col_2_3

, SUBSTR(col_2, 1, 5) AS substr_col_2_5

FROM char_type_test;


 




(2-7) 문자열을 구분자(Delimeter)를 기준으로 분할 후 일부분 가져오기

: SPLIT_PART(string, delimiter, part_position)



--------------

-- Split a string on a specified delimeter

--------------

SELECT 

'2020-07-04' AS yyyymmdd

, SPLIT_PART('2020-07-04', '-', 1) AS year

, SPLIT_PART('2020-07-04', '-', 2) AS month

, SPLIT_PART('2020-07-04', '-', 3) AS day;


 




(2-8) 문자열 내 특정 하위 문자열을 잘라내기: TRIM()


  • 문자열 내 특정 문자로 시작하는 부분 잘라내기: TRIM(leadnig characters from string)
  • 문자열 내 특정 문자로 끝나는 부분 잘라내기: TRIM(trailing characters from string)
  • 문자열 내 특정 문자로 시작하거나 끝나는 부분 잘라내기: TRIM(both characters from string)
  • 문자열의 왼쪽 시작부분에 특정 문자가 있으면 잘라내기: LTRIM(string, characters)
  • 문자열의 오른쪽 끝부분에 특정 문자가 있으면 잘라내기: RTRIM(string, characters)

TRIM()과 LTRIM(), RTRIM() 간에 인자의 위치가 조금 다른 것에 주의하세요. 



--  trim([leading | trailing | both] [character] from string)

SELECT 

'xTomxx' AS original_str

, TRIM(LEADING 'x' FROM 'xTomxx') AS trim_leading

, TRIM(TRAILING 'x' FROM 'xTomxx') AS trim_trailing

, TRIM(BOTH 'x' FROM 'xTomxx') AS trim_both

, LTRIM('xTomxx', 'x') AS l_trim

, RTRIM('xTomxx', 'x') AS r_trim;






(2-9) 문자열의 처음 n개 문자 가져오기


  • 문자열의 왼쪽으로(시작) 부터 처음 n개 문자 가져오기: LEFT(string, int n)
  • 문자열의 오른쪽으로(끝) 부터 처음 n개 문자 가져오기: RIGHT(string, int n)


--------------------------------

-- First n character in a string

--------------------------------

SELECT 

col_2

, LEFT(col_2, 3) AS first_n_from_left

, RIGHT(col_2, 3) AS first_n_from_right

FROM char_type_test;


 




(2-10) 문자열의 길이가 n개보다 모자라는 개수만큼 채우기(Padding)


  • 문자열의 길이가 n 개보다 모자라는 개수만큼 character로 왼쪽부터 채우기
    : LPAD(string, int n, character)
  • 문자열의 길이가 n 개보다 모자라는 개수만큼 character로 오른쪽부터 채우기
    : RPAD(string, int n, character)

아래 예에서는 id가 정수형(int) 이므로 id::char 을 사용하여 문자형으로 변환한 후에, 문자열형 함수인 LPAD(), RPAD() 함수를 적용하였습니다. 


-----------------------------------

-- PAD on the left or rght a string 

-----------------------------------

SELECT 

id

, LPAD(id::char, 5, '0') AS lpad_id

, RPAD(id::char, 5, '0') AS rpad_id

FROM char_type_test;


 



문자열형의 패턴 매칭(pattern matching)까지 다루기에는 포스팅이 너무 길어지므로 다음번에 별도로 소개하겠습니다. 


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

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


Posted by R Friend Rfriend
TAG character type function in postgresql, character type operators in postgresql, charctor(), GreenPlum, PostgreSQL, text, varchar(n), 가변 길이 문자열 varchar(n), 고정 길이 문자열 char(n), 구분자 포함해 문자열 합치기 함수 concat_ws(), 무제한 길이 문자열 text, 문자열 길이 length, 문자열 대문자로 변환 upper(), 문자열 바이트 길이 octet_length, 문자열 비트 길이 bit_length(), 문자열 소문자로 변환 함수 lower(), 문자열 오른쪽의 특정 문자 잘라내기 함수 rtrim(), 문자열 왼쪽 의 특정 문자 잘라내기 함수 ltrim(), 문자열 위치 인덱싱 함수 position(), 문자열 처리 연산자, 문자열 타이틀 형식으로 변환 함수 initcap(), 문자열 특정 위치에 다른 문자열 덮어쓰기 함수 overaly(), 문자열 합치기 연산자 ||, 문자열 합치기 함수 concat(), 문자열을 다른 문자열로 대체 replace(), 문자열을 뒤집기 함수 reverse(), 문자열을 특정 구분자를 기준으로 분리하고 부분 가져오기 함수 split_part(), 문자열을 특정 숫자만큼 반복 함수 repeat(), 문자열의 n개 자리 중 모자라는 부분 채우기 함수 rpad(), 문자열의 오른쪽 왼쪽 양쪽 특정 문자 잘라내기 함수 trim(leading trailing both from), 문자열의 오른쪽부터 처음 n개 문자열 함수 right(), 문자열의 왼쪽부터 처음 몇개 문자열 가져오기 함수 left(), 문자열의 왼쪽의 n개 자리 중 빈 자리 채우기 lpad(), 문자열의 특정 위치 부분 자르기 함수 substr()

댓글을 달아 주세요

이번 포스팅에서는 PostgreSQL, Greenplum DB의 4가지 연산자(Operators)에 대해서 알아보겠습니다. 

  • 산술 연산자 (Arithmetic Operators)
  • 비교 연산자 (Comparison Operators)
  • 논리 연산자 (Logical Operators)
  • 비트 연산자 (Bitwise Operators)




  (1) 산술 연산자 (Arithmetic Operators)



* Reference: https://www.postgresql.org/docs/9.4/functions-math.html


산술연산자는 어려운 것은 없으므로 추가 설명은 생략하겠으며, 다만 나눗셈(/)에 대해서만 조심해야하는 부분이 있어서 추가 설명을 하겠습니다. 


나눗셈의 분자와 분모가 모두 정수(int)인 경우 나눗셈(/)을 하면 정수의 몫을 반환하며, 소수점 부분은 무시가 되므로 유의할 필요가 있습니다. 만약 소수점자리까지의 나눗셈 계산 결과가 모두 필요한 경우 분자나 혹은 분모를 NUMERIC 혹은 FLOAT 로 데이터 형태 변환을 해주어야 합니다. 아래에 간단한 예를 들어보겠습니다. (이걸 신경을 안쓰면 나중에 소수점 부분의 결과가 무시된걸 모르고서 원하는 값이 아니라면서 당황하는 수가 생깁니다.) 



DROP TABLE IF EXISTS test;

CREATE TABLE test (

a int 

, b int

);


INSERT INTO test VALUES (2, 4), (3, 5), (4, 7);

SELECT * FROM test;

-------------

a      b

-------------

2 4

3 5

4 7

 

-- 나눗셈 결과의 소수점 자리 무시

SELECT b / a AS div FROM test;

----------

div

----------

2

1

1


-- 분자를 Numeric으로 형 변환하면 나눗셈 결과 소수점 자리 나옴

SELECT b::numeric / a AS div_1 FROM test;

----------

div_1

----------

2.0000000000000000

1.6666666666666667

1.7500000000000000



-- 분모를 Numeric으로 형 변환하면 나눗셈 결과 소수점 자리 나옴

SELECT b / a::numeric AS div_2 FROM test;

----------

div_2

----------

2.0000000000000000

1.6666666666666667

1.7500000000000000




계승(factorial)의 경우 SELECT 5!, SELECT !!5 처럼 '!'가 한개냐, 두개냐에 따라서 정수를 써주는 위치가 달라집니다. 


절대값(Absolute value)를 구할 때는 '@ col_nm' 혹은 '@ 숫자' 를 해주면 되는데요, 이때 '@' 다음에 스페이브 1칸을 띄워주어야 합니다. (만약 '@' 다음에 한 칸 띄우지 않으면 SQL Error [42883]: ERROR: operator does not exist: @- numeric 와 같은 ERROR가 발생합니다.)


산술 연산을 한 후에 'AS col_nm' 처럼 alias 별명 칼럼 이름을 부여할 수 있습니다. 




  (2) 비교 연산자 (Comparison Operators)


* Reference: https://www.postgresql.org/docs/9.4/functions-comparison.html



비교 연산자(comparison operators)도 어려운 것은 없으므로 길게 설명할 필요는 없어보입니다. 아래의 간단한 예를 살펴보시기 바랍니다. 


비교 연산자는 WHERE 조건절에서 사용되어 두 값을 비교하게 되며, 비교 연산자의 조건을 만족하면 참(TRUE)을, 비교 연산자의 조건을 만족하지 않으면 거짓(FALSE)을 반환합니다. 이를 이용해서 비교 연산자에 대해 참(TRUE)인 조건을 만족하는 값만을 선택(SELECT)해 올 수 있습니다. 



-----------------------

-- Comparison Operators

-----------------------

DROP TABLE IF EXISTS comparison;

CREATE TABLE comparison (

a int 

, b int

);


INSERT INTO comparison VALUES (1, 1), (1, 2), (2, 1), (2, 2);

SELECT * FROM comparison;

----------

a       b

----------

1 1

1 2

2 1

2 2



-- equal to

SELECT * FROM comparison WHERE a = b;

----------

a       b

----------

1 1

2 2



-- not equal

SELECT * FROM comparison WHERE a != b;

SELECT * FROM comparison WHERE a <> b;

----------

a       b

----------

1 2

2 1



-- greater than

SELECT * FROM comparison WHERE a > b;

----------

a       b

----------

2 1



-- less than

SELECT * FROM comparison WHERE a < b;

----------

a       b

----------

1 2



-- greater than or equal to

SELECT * FROM comparison WHERE a >= b;

----------

a       b

----------

1 1

2 1

2 2



-- less than or equal to

SELECT * FROM comparison WHERE a <= b;

----------

a       b

----------

1 1

1 2

2 2




다만 한가지 조심한 것이 있습니다. 비교 연산자 두개를 이어붙여서 사용하는 경우 순서(sequence)가 틀리면 ERROR가 발생합니다. 따라서 꼭 순서에 맞게 (가령, >= greater than or equal to) 비교 연산자를 써주어야 합니다. 



-- SQL Error [42883]: ERROR: operator does not exist: integer =! integer

SELECT * FROM comparison WHERE a =! b;



-- SQL Error [42883]: ERROR: operator does not exist: integer >< integer

SELECT * FROM comparison WHERE a >< b;



-- SQL Error [42601]: ERROR: syntax error at or near "=>"

SELECT * FROM comparison WHERE a => b;



-- SQL Error [42883]: ERROR: operator does not exist: integer =< integer

SELECT * FROM comparison WHERE a =< b;




  (3) 논리 연산자 (Logical Operators)


논리 연산자(Logical Operators)는 조건절에서 여러개의 조건을 AND, OR, NOT 으로 조합하여 사용할 수 있도록 해줍니다. 아래에 우측에 집합 벤다이어그램으로 그림을 그려놓았으니 참고하시기 바랍니다. 




아래의 표는 WHERE 조건절에 a와 b의 두 조건이 참(TRUE), 거짓(FALSE) 여부의 조합별로 AND, OR, NOT 논리 연산자의 결과값이 참(TRUE)인지 또는 거짓(FALSE)인지를 정리한 표입니다. NULL 은 FALSE 로 간주된다는 점 유의하시기 바랍니다. 


[ PostgreSQL Logical Operators Table by TRUE, FALSE combinations ]

* Reference: https://www.postgresql.org/docs/9.4/functions-logical.html



위의 (2)번에서 만들었던 comparison 테이블에 NULL 값을 포함한 행 두개를 추가해서 간단한 논리 연산자 예제를 만들어보겠습니다. 



---------------------

-- Logical Operators

---------------------

INSERT INTO comparison VALUES (NULL, 5), (NULL, NULL);

SELECT * FROM comparison;

----------

a      b

----------

1 1

1 2

2 1

2 2

[NULL] 5

[NULL] [NULL]



-- AND

SELECT * FROM comparison WHERE a = 1 AND b = 2;

----------

a      b

----------

1 2



-- OR

SELECT * FROM comparison WHERE a = 1 OR b = 5;

----------

a      b

----------

1 1

1 2

[NULL] 5



-- NOT

SELECT * FROM comparison WHERE NOT (a = 1);

2 1

2 2



-- NOT IN

SELECT * FROM comparison WHERE a NOT IN (1);

----------

a      b

----------

2 1

2 2



-- IS NOT NULL

SELECT * FROM comparison WHERE b IS NOT NULL;

----------

a      b

----------

1 1

1 2

2 1

2 2

[NULL] 5



-- IS NOT NULL AND IS NOT NULL

SELECT * FROM comparison WHERE a IS NOT NULL AND b IS NOT NULL;

----------

a      b

----------

1 1

1 2

2 1

2 2


-- NOT BETWEEN

SELECT * FROM comparison WHERE b BETWEEN 1 AND 2;

----------

a      b

----------

1 1

1 2

2 1

2 2



SELECT * FROM comparison WHERE b NOT BETWEEN 1 AND 2;

----------

a      b

----------

[NULL] 5





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

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


Posted by R Friend Rfriend

댓글을 달아 주세요