'PostgreSQL' 태그의 글 목록 (4 Page)

'PostgreSQL'에 해당되는 글 38건

2020.07.04 [PostgreSQL, Greenplum] 문자열 처리 연산자 및 함수 (character type operators and functions)
2020.03.29 [Greenplum] PL/R을 이용한 그룹별 선형회귀모형 분산 병렬 학습 및 예측
2019.12.15 [PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum)
2019.12.09 [Python, R, PostgreSQL] 그룹별로 행을 내리기, 올리기 (Lag, Lead a row by Group) 6
2019.11.28 [Postgres, Greenplum] 현재 수행중인 query 조회하고 강제 종료 (kill) 하는 방법
2019.04.27 [Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore)
2019.03.27 [Greenplum DB] GPDB docker에 PostGIS 설치하기
2019.03.16 [Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜

[PostgreSQL, Greenplum] 문자열 처리 연산자 및 함수 (character type operators and functions)

Greenplum and PostgreSQL Database 2020. 7. 4. 22:33

이번 포스팅에서는 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

SELECT LENGTH('abc123');

----------

result

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

----------

result

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

----------

result

(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

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

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

FROM char_type_test;

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

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] ipython-sql, pgspecial로 Jupyter Notebook에서 DB접속하고 SQL query 하기 (0)	2020.10.20
[PostgreSQL, Greenplum] 문자열 패턴 매칭 (Pattern Matching) (0)	2020.07.12
[PostgreSQL, Greenplum] 산술연산자, 비교연산자, 논리연산자, 기타연산자 (Operators) (0)	2020.06.28
[PostgreSQL, Greenplum DB] 수정 Update, 삭제 Delete, 테이블 비우기 Truncate (0)	2020.06.20
[PostgreSQL, Greenplum DB] DATABASE, SCHEMA, TABLE, VIEW 만들고 데이터 등록(INSERT)하기 (0)	2020.06.14

Posted by Rfriend

[Greenplum] PL/R을 이용한 그룹별 선형회귀모형 분산 병렬 학습 및 예측

Greenplum and PostgreSQL Database 2020. 3. 29. 19:39

만약 한개 당 1분 걸리는 동일한 프로세스의 100개의 일을 한 명이서 한다면 100분이 걸릴텐데요, 이것을 100명에게 일을 1개씩 나누어서 동시에 분산해서 시킨다면 1분(+취합하는 시간 약간) 밖에 안걸릴 것입니다. 1명이 100명을 이길 수는 없기 때문입니다.

대용량 데이터에 대해서 빠른 성능으로 통계나 기계학습의 고급 분석을 처리해야 하는 경우라면 Greenplum 과 같은 MPP (Massively Parallel Processing) 아키텍처 기반의 DB에서 R 이나 Python 언어로 작성한 알고리즘을 In-DB에서 PL/R, PL/Python을 사용해서 분산 병렬 처리할 수 있습니다.

이번 포스팅에서는 Greenplum DB에서 PL/R (Procedural Language R) 을 사용해서 분산 병렬처리(distributed parallel processing하여 그룹별로 선형회귀모형을 각각 적합하고 예측하는 방법을 소개하겠습니다. 모든 연산이 In-DB 에서 일어나기 때문에 데이터 I/O 가 없으므로 I/O 시간을 절약하고 architecture 와 workflow를 간단하게 가져갈 수 있는 장점도 있습니다. (vs. DB 에서 local R 로 데이터 말아서 내리고, local R로 모형 적합 / 예측 후, 이 결과를 다시 DB에 insert 하고 하는 복잡한 절차가 필요 없음)

이번에 소개할 간단한 예제로 사용할 데이터셋은 abalone 공개 데이터셋으로서, 성 (sex) 별로 구분하여 무게(shucked_weight)와 지름(diameter) 설명변수를 input으로 하여 껍질의 고리 개수(rings)를 추정하는 선형회귀모형을 적합하고, 예측하는 문제입니다.

이러한 일을 성별 F, M, I 별로 순차적으로 하는 것이 아니라, Greenplum DB 에서 성별 F, M, I 별로 PL/R로 분산 병렬처리하여 동시에 수행하는 방법입니다.

(1) abalone 데이터셋으로 테이블 만들기

먼저, abalone 데이터셋을 공개 데이터셋 웹사이트에서 가져와서 External table을 만들고, 이로부터 abalone table 을 생성해보겠습니다.

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

-- Linear Regression in Parallel

-- using PL/R

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

-- Dataset for example: abalone dataset from the UC Irvine Machine Learning Repository

-- url: http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data

-- Create an external web table

DROP EXTERNAL TABLE IF EXISTS abalone_external;

CREATE EXTERNAL WEB TABLE abalone_external(

sex text

, length float8

, diameter float8

, height float8

, whole_weight float8

, shucked_weight float8

, viscera_weight float8

, shell_weight float8

, rings integer -- target variable to predict

) LOCATION('http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data')

FORMAT 'CSV'

(null as '?');

-- Create a table of abalone

DROP TABLE IF EXISTS abalone;

CREATE TABLE abalone AS

SELECT * FROM abalone_external

DISTRIBUTED BY (sex);

-- Viewing data distribution

SELECT gp_segment_id, COUNT(*) AS row_cnt

FROM abalone

GROUP BY gp_segment_id;

-- Check data

SELECT * FROM abalone LIMIT 5;

(2) Train, Test set 테이블 분할 (train, test set split)

다음으로 MADlib(https://madlib.apache.org/) 의 madlib.train_test_split() 함수를 사용해서 abalone 원 테이블을 train set, test set 테이블로 분할(split into train and test set) 해보겠습니다.

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

-- Train, Test set split

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

-- Check the number of observations per sex group(F, I, M)
SELECT sex, COUNT(*) FROM abalone GROUP BY sex;

-- Train, Test set split

DROP TABLE IF EXISTS out_train, out_test;

SELECT madlib.train_test_split(

'abalone', -- Source table

'out', -- Output table

0.8, -- train_proportion

NULL, -- Default = 1 - train_proportion = 0.5

'sex', -- Strata definition

'rings, shucked_weight, diameter', -- Columns to output

FALSE, -- Sample with replacement

TRUE); -- Separate output tables

SELECT * FROM out_train LIMIT 5;

SELECT sex, count(*) FROM out_train GROUP BY sex;

SELECT sex, count(*) FROM out_test GROUP BY sex;

(3) array aggregation 하여 PL/R에서 사용할 데이터셋 준비하기

좀 낯설을 수도 있는데요, PL/R 에서는 array 를 input으로 받으므로 array_agg() 함수를 사용해서 설명변수를 칼럼별로 array aggregation 해줍니다. 이때 성별(sex) 로 모형을 각각 병렬로 적합할 것이므로 group by sex 로 해서 성별로 따로 따로 array aggregation 을 해줍니다. 이렇게 해주면 long format으로 여러개의 열(row)에 들어있던 값들이 성별로 구분이 되어서 하나의 array( { } )에 모두 들어가게 됩니다. (아래 이미지 참조)

-- Data Preparation

-- : array aggregation using array_agg()

DROP TABLE IF EXISTS abalone_array;

CREATE TABLE abalone_array AS

SELECT

sex::text -- group

, array_agg(rings::float8) as rings -- y

, array_agg(shucked_weight::float8) as s_weight -- x1

, array_agg(diameter::float8) as diameter -- x2

FROM out_train

GROUP BY sex

DISTRIBUTED BY (sex);

SELECT * FROM abalone_array;

(4) 선형회귀모형 적합하는 PL/R 사용자 정의 함수 정의하기 (Define PL/R UDF)

선형회귀모형 PL/R 의 반환받는 값을 두가지 유형으로 나누어서 각각 소개하겠습니다.

(4-1) 적합된 회귀모형의 회귀계수 (coefficients) 를 반환하기

(4-2) 적합된 회귀모형 자체(fitted model itself)를 반환하기

먼저, (4-1) 적합된 회귀모형의 회귀계수를 반환하는 PL/R 함수를 정의하는 방법을 소개하겠습니다.

R의 lm() 함수를 사용하여 다중 선형회귀모형을 적합(fit a model)하면, summary(fitted_model)$coef 는 추정된 회귀계수(coef_est), 표준오차(std_error), T통계량(t_stat), P-값 (p_value) 를 반환합니다.

CREATE OR REPLAE FUNCTION pl_r_funtion_name(column_name data_type[], ...) 으로 PL/R 함수 이름과 인자로 받는 칼럼 이름, 데이터 유형을 정의해주고,

이들 모형 적합 후의 추정된 회귀계수와 통계량을 Greenplum DB에 반환하려면 데이터 유형이 여러개이므로 composit type 을 별도로 정의('lm_abalone_type')해주어고, PL/R 사용자 정의함수에서 returns setof lm_abalone_type 처럼 써주면 됩니다.

그 다음에, $$ pure R codes block $$ LANGUAGE 'plr' 형식으로 R codes 를 통째로 $$ ~~~~ $$ 안에 넣어주면 됩니다.

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

-- (4-1) PL/R : Linear Regression Model's Coefficients --> Predict

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

-- Return Types

DROP TYPE IF EXISTS lm_abalone_type CASCADE;

CREATE TYPE lm_abalone_type AS (

variable text

, coef_est float

, std_error float

, t_stat float

, p_value float

);

-- PL/R User Defined Function

DROP FUNCTION IF EXISTS plr_lm_train(float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_train(

rings float8[]

, s_weight float8[]

, diameter float8[]

) RETURNS SETOF lm_abalone_type AS

m1 <- lm(rings ~ s_weight + diameter)

m1_s <- summary(m1)$coef

temp_m1 <- data.frame(rownames(m1_s), m1_s)

return(temp_m1)

LANGUAGE 'plr';

(5) PL/R 실행하기 (execute PL/R UDF in Greenplum DB)

위의 (4)에서 정의한 성별(per sex) 선형회귀모형을 적합하여 회귀계수와 통계량을 반환하는 PL/R 사용자 정의함수를 Greenplum DB 안에서 병렬처리하여 실행해보겠습니다.

select sex, (plr_lm_train(rings, s_weight, diameter)).* from abalone_array 처럼 위의 (4)번에서 정의한 PL/R 함수에 (3)번에서 준비한 array 가 들어있는 테이블의 칼럼을 써주고, from 절에 array 테이블 이름을 써주면 됩니다.

이때 테이블에 return 받는 값들이 composit type의 여러개 칼럼들이므로 (plr_udf(column, ...)).* 처럼 PL/R 함수를 괄호 ( ) 로 싸주고 끝에 '*' (asterisk) 를 붙여줍니다. ( * 를 빼먹으면 여러개의 칼럼별로 나누어지지 않고 한개의 칼럼에 튜플로 모든 칼럼이 뭉쳐서 들어갑니다)

-- Execution of Linear Regression PL/R

DROP TABLE IF EXISTS lm_abalone_model_coef;

CREATE TABLE lm_abalone_model_coef AS (

SELECT sex, (plr_lm_train(rings, s_weight, diameter)).*

FROM abalone_array

) DISTRIBUTED BY (sex);

SELECT * FROM lm_abalone_model_coef;

(6) 적합한 선형회귀모형을 사용해 test set에 대해 예측하기 (prediction on test set)

위의 (5)번에서 적합한 성별 선형회귀모형들의 회귀계수 (coefficients per sex groups) 를 사용해서 test set 의 데이터셋에 대해 PL/R 함수로 분산 병렬처리하여 rings 값을 예측해보겠습니다. (training 도 분산병렬처리, prediction/ scoring 도 역시 분산병렬처리!)

먼저, 예측하는 PL/R 함수에 넣어줄 test set을 array aggregation 해줍니다.

다음으로, ID별로 실제값(actual rings)과 예측한 값(predicted rings)을 반환받을 수 있도록 composite type 을 정의해줍니다.

그 다음엔 추정된 회귀계수를 사용해서 예측할 수 있도록 행렬 곱 연산을 하는 PL/R 함수(plr_lm_coef_predict())를 정의해줍니다.

마지막으로 예측하는 PL/R 함수를 실행해줍니다.

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

-- Prediction and Model Evaluation for Test set

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

-- Preparation of test set in aggregated array

DROP TABLE IF EXISTS test_array;

CREATE TABLE test_array AS

SELECT

sex::text

, array_agg(rings::float8) as rings -- y

, array_agg(shucked_weight::float8) as s_weight -- x1

, array_agg(diameter::float8) as diameter --x2

FROM out_test

GROUP BY sex

DISTRIBUTED BY (sex);

SELECT * FROM test_array;

-- Define composite data type for predicted return values

DROP TYPE IF EXISTS lm_predicted_type CASCADE;

CREATE TYPE lm_predicted_type AS (

id int

, actual float

, pred float

);

-- Define PL/R UDF of prediction per groups using linear regression coefficients

DROP FUNCTION IF EXISTS plr_lm_coef_predict(float8[], float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_coef_predict(

rings float8[]

, s_weight float8[]

, diameter float8[]

, coef_est float8[]

) RETURNS SETOF lm_predicted_type AS

actual <- rings # y

intercept <- 1

X <- cbind(intercept, s_weight, diameter) # X matrix

coef_est <- matrix(coef_est) # coefficients matrix

predicted <- X %*% coef_est # matrix multiplication

df_actual_pred <- data.frame(actual, predicted)

id <- as.numeric(rownames(df_actual_pred))

return(data.frame(id, df_actual_pred))

LANGUAGE 'plr';

-- Execute PL/R Prediction UDF

DROP TABLE IF EXISTS out_coef_predict;

CREATE TABLE out_coef_predict AS (

SELECT sex, (plr_lm_coef_predict(c.rings, c.s_weight, c.diameter, c.coef_est)).*

FROM (

SELECT a.*, b.coef_est

FROM test_array a,

(SELECT sex, array_agg(coef_est) AS coef_est FROM lm_abalone_model_coef GROUP BY sex) b

WHERE a.sex = b.sex

) c

) DISTRIBUTED BY (sex);

-- Compare 'actual' vs. 'predicted'

SELECT * FROM out_coef_predict WHERE sex = 'F' ORDER BY sex, id LIMIT 10;

(7) 회귀모형 자체를 Serialize 해서 DB에 저장하고, Unserialize 해서 예측하기

위의 (4)번~(6번) 까지는 적합된 회귀모형의 회귀계수와 통계량을 반환하고, 이를 이용해 예측을 해보았다면, 이번에는

- (4-2) 적합된 회귀모형 자체(model itself)를 Serialize 하여 DB에 저장하고 (인코딩),

- 이를 DB에서 읽어와서 Unserialize 한 후 (디코딩), 예측하기

- 단, 이때 예측값의 95% 신뢰구간 (95% confidence interval) 도 같이 반환하기

를 해보겠습니다.

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

-- (2) PL/R : Linear Model --> Serialize --> Deserialize --> Predict

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

-- PL/R User Defined Function Definition

DROP FUNCTION IF EXISTS plr_lm_model(float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_model(

rings float8[]

, s_weight float8[]

, diameter float8[]

) RETURNS bytea -- serialized model as a byte array

lr_model <- lm(rings ~ s_weight + diameter)

return (serialize(lr_model, NULL))

LANGUAGE 'plr';

-- Execution of Linear Regression PL/R

DROP TABLE IF EXISTS lm_abalone_model;

CREATE TABLE lm_abalone_model AS (

SELECT sex, plr_lm_model(rings, s_weight, diameter) AS serialized_model

FROM abalone_array

) DISTRIBUTED BY (sex);

-- We can not read serialized model

SELECT * FROM lm_abalone_model;

DROP TYPE IF EXISTS lm_predicted_interval_type CASCADE;

CREATE TYPE lm_predicted_interval_type AS (

id int

, actual float

, pred float

, lwr float

, upr float

);

-- PL/R function to read a serialized PL/R model

DROP FUNCTION IF EXISTS plr_lm_model_predict(float8[], float8[], float8[], bytea);

CREATE OR REPLACE FUNCTION plr_lm_model_predict(

rings float8[]

, s_weight float8[]

, diameter float8[]

, serialized_model bytea

) RETURNS SETOF lm_predicted_interval_type

model <- unserialize(serialized_model)

actual <- rings # y

X <- data.frame(s_weight, diameter) # new data X

predicted <- predict(model, newdata = X, interval = "confidence")

df_actual_pred <- data.frame(actual, predicted)

id <- as.numeric(rownames(df_actual_pred))

return (data.frame(id, df_actual_pred))

LANGUAGE 'plr';

-- Predict

DROP TABLE IF EXISTS out_model_predict;

CREATE TABLE out_model_predict AS (

SELECT sex, (plr_lm_model_predict(c.rings, c.s_weight, c.diameter, c.serialized_model)).*

FROM (

SELECT a.*, b.serialized_model

FROM test_array a, lm_abalone_model b

WHERE a.sex = b.sex

) c

) DISTRIBUTED BY (sex);

SELECT * FROM out_model_predict WHERE sex = 'F' ORDER BY sex, id LIMIT 10;

[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측은 https://rfriend.tistory.com/533 를 참고하세요.

[References]

Reference of PL/R on Greenplum: https://pivotalsoftware.github.io/gp-r/
PL/R - R PROCEDURAL LANGUAGE FOR POSTGRESQL by Joseph E Conway : http://joeconway.com/plr.html
Pivotal Greenplum PL/R Language Extension 설치 : https://gpdb.docs.pivotal.io/530/ref_guide/extensions/pl_r.html
Greenplum 5에 PL/R 설치 및 R 패키지 수동 설치
: https://rfriend.tistory.com/442
Greenplum 6에 PL/R 설치 및 R 버전 업그레이트 : http://gpdbkr.blogspot.com/2019/12/greenplum-6-plr-r.html
Apache MADlib 설치 및 사용 매뉴얼: https://madlib.apache.org/

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum, PostgreSQL DB] PL/R 을 위한 데이터셋 array aggregation 준비하는 3가지 방법 (4)	2020.04.05
[Greenplum] PL/R Error ERROR: Error on receive from seg0 slice1: server closed the connection unexpectedly 확인사항과 대처 방안 (0)	2020.04.01
[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum) (0)	2019.12.15
[Postgres, Greenplum] 현재 수행중인 query 조회하고 강제 종료 (kill) 하는 방법 (0)	2019.11.28
[Greenplum] 오픈소스 Greenplum DB와 Apache MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib) (0)	2019.08.25

Posted by Rfriend

[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum)

Greenplum and PostgreSQL Database 2019. 12. 15. 23:22

이번 포스팅에서는 PostgreSQL, Greenplum Database를 이용해서 시계열 데이터를 특정 시간 단위 구간(예: 10분 단위, 1시간 단위, 1일 단위, 1달 단위, 1년 단위 등) 별로 집계/ 요약하는 방법을 소개하겠습니다.

(* python pandas로 특정 시간 단위 구간별로 시계열 데이터를 집계하는 방법은 https://rfriend.tistory.com/495 참조)

-- 사전 준비 작업 --

(1) '년-월-일' date dimension table 만들기

(2) '시간:분' time dimension table 만들기

(3) '년-월-일 시간:분' cross-join 한 date & time dimension table 만들기

(4) 예제 시계열 데이터(time series) table 만들기

==> '날짜-시간' 기준정보를 '시계열 거래 데이터'에 merge 하기

-- 10분 단위 구간별로 시계열 데이터 집계/ 요약하기 --

(5) 10분 단위 구간별로 첫번째 행 값(first row value), 마지막 행 값(last row value) 구하기

(6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기

(7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중위수(median), 3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기

(8) 10분 단위 구간별로 평균(mean), 표본 분산(sample variance), 표본 표준편차(sample standard deviation) 구하기

(9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)

(1) '년-월-일' date dimension table 만들기

날짜(년-월-일)를 기준으로 년(year), 분기(quarter), 월(month), 주(week), 일(day), 공휴일(holiday) 등의 다양한 관점의 group by 할 수 있는 구분자를 생성해놓은 테이블을 만들어보겠습니다. 날짜(년-월-일) 관련 기준 정보, 매핑 테이블이라고 보면 되겠습니다.

아래 예제는 2019년 12월1일 ~ 2019년 12월 31일 까지의 31일 기간의 날짜 데이터 기준정보를 만들었습니다.

만약 2019년 1월1일~2019년 12월 31일 까지의 365일 전체의 날짜 데이터 기준정보를 만들고 싶다면 from 절을 아래와 같이 수정해주면 됩니다.

[ 2019년 1월 1일~2019년 12월 31일 (365일) 기간 일 별 기준정보 테이블 만드는 FROM 절 generate query ]

FROM (

SELECT '2019-01-01'::DATE + SEQUENCE.DAY AS datum

FROM generate_series(0, 364) AS SEQUENCE(DAY)

GROUP BY SEQUENCE.DAY

) DQ

아래의 postgresql wiki 페이지의 sql query를 참고하였습니다.

* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions

------------------------
-- date dimension table
------------------------
-- create 'dim_date' table
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date (
date DATE NOT NULL,
year INT NOT NULL,
month INT NOT NULL,
month_nm VARCHAR(9) NOT NULL,
day INT NOT NULL,
day_of_year INT NOT NULL,
weekday_nm VARCHAR(9) NOT NULL,
calender_week INT NOT NULL,
formatted_date CHAR(12) NOT NULL,
quartal CHAR(2) NOT NULL,
year_quartal CHAR(7) NOT NULL,
year_month CHAR(7) NOT NULL,
year_calendar_week CHAR(7) NOT NULL,
weekend VARCHAR(7) NOT NULL,
holiday_korea VARCHAR(20) NOT NULL,
period VARCHAR(20) NOT NULL,
cwstart DATE NOT NULL,
cwend DATE NOT NULL,
month_start DATE NOT NULL,
month_end DATE NOT NULL
);

-- create index on 'date'
CREATE INDEX dim_date_date_idx ON dim_date(date);

-- insert generate date dimension data (from 2019-01-01 to 2019-12031)
-- into 'dim_date' table
INSERT INTO dim_date (
SELECT
datum AS date,
EXTRACT(YEAR FROM datum)::INT AS year,
EXTRACT(MONTH FROM datum)::INT AS month,
-- Localized month name
to_char(datum, 'TMMonth') AS month_nm,
EXTRACT(DAY FROM datum)::INT AS day,
EXTRACT(doy FROM datum)::INT AS day_of_year,
-- Localized weekday
to_char(datum, 'TMDay') AS weekday_nm,
-- ISO calendar week
EXTRACT(week FROM datum)::INT AS calendar_week,
to_char(datum, 'dd. mm. yyyy') AS formatted_date,
'Q' || to_char(datum, 'Q') AS quartal,
to_char(datum, 'yyyy/"Q"Q') AS year_quartal,
to_char(datum, 'yyyy/mm') AS year_month,
-- ISO calendar year and week
to_char(datum, 'iyyy/IW') AS year_calendar_week,
-- Weekend
CASE WHEN EXTRACT(isodow FROM datum) IN (6, 7) THEN 'Weekend'
ELSE 'Weekday' END
AS weekend,
-- holidays for Korea in year 2019
CASE WHEN to_char(datum, 'MMDD')
IN ('0101', '0205', '0206', '0207', '0301', '0505', '0512',
'0606', '0815', '0912', '0913', '0914', '1003', '1009', '1225')
THEN 'Holiday' ELSE 'No holiday' END
AS holiday_korea,
-- Some periods of the year, adjust for your organisation and country
CASE WHEN to_char(datum, 'MMDD') BETWEEN '0701' AND '0831'
THEN 'Summer break'
WHEN to_char(datum, 'MMDD') BETWEEN '1115' AND '1225'
THEN 'Christmas season'
WHEN to_char(datum, 'MMDD') > '1225' OR to_char(datum, 'MMDD') <= '0106'
THEN 'Winter break'
ELSE 'Normal' END
AS period,
-- ISO start and end of the week of this date : from monday to sunday
datum + (1 - EXTRACT(isodow FROM datum))::INTEGER AS CWStart, -- monday
datum + (7 - EXTRACT(isodow FROM datum))::INTEGER AS CWEnd, -- sunday
-- Start and end of the month of this date
datum + (1 - EXTRACT(DAY FROM datum))::INTEGER AS month_start,
((datum + (1 - EXTRACT(DAY FROM datum))::INTEGER + '1 month'::INTERVAL)::DATE
- '1 day'::INTERVAL)::DATE AS month_end
FROM (
SELECT '2019-12-01'::DATE + SEQUENCE.DAY AS datum
FROM generate_series(0, 30) AS SEQUENCE(DAY)
GROUP BY SEQUENCE.DAY
) DQ
ORDER BY 1);

SELECT * FROM dim_date ORDER BY date;

(2) '시간:분' time dimension table 만들기

시간('시간:분')을 기준으로 시간(time), 분(minute), 10분 단위 구간(time_span_min_10), 1시간 단위 구간(time_span_hour_1), 일 구분 범주(day_category) 등으로 구분해서 집계/요약할 수 있는 구분자, 기준 정보, 매핑 테이블을 만들어보겠습니다.

* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions

------------------------
-- time dimension table
------------------------
-- create dimension table 'dim_time'
DROP TABLE IF EXISTS dim_time;
CREATE TABLE dim_time (
time_of_day CHAR(6) NOT NULL,
hour INT NOT NULL,
minute INT NOT NULL,
time_span_min_10 CHAR(15) NOT NULL,
time_span_hour_1 CHAR(15) NOT NULL,
day_category VARCHAR(15) NOT NULL
);

-- create index on 'time_of_day'
CREATE INDEX dim_time_time_of_day_idx ON dim_time(time_of_day);

-- insert generated time_of_day data into 'dim_time' table
INSERT INTO dim_time (
SELECT
TO_CHAR(MINUTE, 'hh24:mi') AS time_of_day,
-- Hour of the day (0 - 23)
EXTRACT(HOUR FROM MINUTE)::INTEGER AS hour,
-- Minute of the day (0 - 1439)
(EXTRACT(HOUR FROM MINUTE)*60 +
EXTRACT(MINUTE FROM MINUTE))::INT AS minute,
-- Extract and format 10 minutes time span
TO_CHAR(
MINUTE -
(EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10
|| 'minutes')::INTERVAL, 'hh24:mi') || ' – ' ||
TO_CHAR(
MINUTE -
(EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10
|| 'minutes')::INTERVAL + '10 minutes'::INTERVAL, 'hh24:mi')
AS time_span_min_10,
-- Extract and format 1 hour time span
TO_CHAR(
MINUTE -
(EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60
|| 'minutes')::INTERVAL, 'hh24:mi') || ' – ' ||
TO_CHAR(
MINUTE -
(EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60
|| 'minutes')::INTERVAL + '60 minutes'::INTERVAL, 'hh24:mi')
AS time_span_hour_1,
-- Names of day periods
CASE WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '06:00' AND '08:59'
THEN 'morning'
WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '09:00' AND '11:59'
THEN 'AM'
WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '12:00' AND '17:59'
THEN 'PM'
WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '18:00' AND '21:59'
THEN 'evening'
ELSE 'night'
END AS day_category
FROM (SELECT '00:00'::TIME + (SEQUENCE.MINUTE || ' minutes')::INTERVAL
AS MINUTE
FROM generate_series(0,1439) AS SEQUENCE(MINUTE)
GROUP BY SEQUENCE.MINUTE
) DQ
ORDER BY 1
);

SELECT * FROM dim_time ORDER BY time_of_day LIMIT 60;

(3) '년-월-일 시간:분' cross-join 한 date & time dimension table 만들기

(1)번에서 만든 dim_date 테이블과 (2)번에서 만든 dim_time 테이블을 cross join 하여 'dim_date_time' 테이블을 만들어보겠습니다.

31일 (dim_date) * 24시간 * 60분 (dim_time) = 44,640 개의 전체 행 (total number of rows) 을 가지는 '년-월-일 시간:분' (dim_date_time) 기준정보 매핑 테이블을 가지고 이제 년 ~ 분 단위를 넘나들면서 원하는 시간 단위 구간별로 시계열 데이터를 집계/ 요약할 수 있습니다.

앞으로 join 의 기준이 될 '년-월-일 시간:분'(dt) 칼럼에 대해서는 빠른 join을 위해서 index 를 생성해주었습니다.

---------------------------------------
-- cross join b/w dim_date and dim_time
---------------------------------------
DROP TABLE IF EXISTS dim_date_time;
CREATE TABLE dim_date_time AS (
SELECT
to_char((dim_date.date || ' ' || dim_time.time_of_day)::timestamp,
'yyyy-mm-dd hh24:mi') AS dt
, dim_date.*
, dim_time.*
FROM dim_date
CROSS JOIN dim_time
);

-- create index on 'dt'
CREATE INDEX dim_date_time_dt_idx ON dim_date_time(dt);

SELECT * FROM dim_date_time ORDER BY dt LIMIT 5;

SELECT COUNT(*) FROM dim_date_time;
44640

(4) 예제 시계열 데이터(time series) table 만들기 ==> '날짜-시간' 기준 정보 merge 하기

간단한 예제로 '년-월-일 시간:분'(dt), 가격(price), 수량(amt) 의 3개 칼럼을 가지는 거래 시계열 데이터 테이블을 만들어보겠습니다.

---------------------
-- Transaction table
---------------------
-- create ts table
DROP TABLE IF EXISTS ts;
CREATE TABLE ts (
dt TEXT NOT NULL
, price NUMERIC NOT NULL
, amt INT NOT NULL
);

-- insert transaction data into ts table
INSERT INTO ts VALUES
('2019-12-19 00:00', 12, 4),
('2019-12-19 00:02', 21, 2),
('2019-12-19 00:04', 41, 1),
('2019-12-19 00:06', 79, 4),
('2019-12-19 00:08', 61, 2),
('2019-12-19 00:10', 81, 1),
('2019-12-19 00:12', 24, 3),
('2019-12-19 00:14', 62, 1),
('2019-12-19 00:16', 76, 3),
('2019-12-19 00:18', 63, 1),
('2019-12-19 00:20', 95, 2),
('2019-12-19 00:22', 82, 1),
('2019-12-19 00:24', 82, 3),
('2019-12-19 00:26', 70, 1),
('2019-12-19 00:28', 30, 4),
('2019-12-19 00:30', 33, 1),
('2019-12-19 00:32', 22, 2),
('2019-12-19 00:34', 77, 3),
('2019-12-19 00:36', 58, 3),
('2019-12-19 00:38', 96, 3),
('2019-12-19 09:02', 10, 2),
('2019-12-19 09:08', 50, 3),
('2019-12-19 14:04', 20, 1),
('2019-12-19 16:00', 10, 5),
('2019-12-19 21:00', 30, 4)
;

SELECT * FROM ts ORDER BY dt;

다음으로, '시계열 거래 데이터'(ts table)에 '날짜-시간 기준정보'(dim_date_time table) 를 '년-월-일 시간:분'(dt) 칼럼을 기준으로 병합(left outer join)해보겠습니다. 이제 특정 시간 단위 구간별로 집계, 요약할 수 있는 데이터 준비가 되었습니다.

※ 만약 '시계열 거래 데이터'에 특정 날짜-시간대에 값이 존재하던지 말던지 간에 동일한 특정 시간 단위 구간별로 값을 집계, 요약하고자 한다면, (즉, '시계열 거래 데이터'가 없으면 NULL이나 '0'으로 집계) LEFT OUTER JOIN 할 때 왼쪽에 '날짜-시간 기준정보'(dim_date_time) 테이블을 놓고, 오른쪽에 '시계열 거래 데이터'(ts) 테이블을 놓고 left outer join 을 해주어야 합니다.

------------------------------------
-- merge 'date-time' dimension table
------------------------------------
DROP TABLE IF EXISTS ts_dt;
CREATE TABLE ts_dt AS (
SELECT
a.*
, b.date
, b.time_span_min_10
, b.time_span_hour_1
, b.day_category
FROM ts a
LEFT OUTER JOIN dim_date_time b ON a.dt = b.dt
);

SELECT * FROM ts_dt ORDER BY dt LIMIT 5;

(5) 10분 단위 구간별로 첫번째 행 값(first-row value), 마지막 행 값(last row value) 구하기

시계열 데이터를 시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별로 첫번째 행의 값(first row's value)을 구하는 방법에는 두가지가 있습니다.

첫번째 방법은 ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt) 의 window function을 사용하는 것입니다. (오름차순 정렬 order by dt)

두번째 방법은 DISTINCT ON (date, time_span_min_10) 함수를 사용하는 것입니다.

-- (1) first, last by time_span_min_109
-- (1-1-1) way 1: first using ROW_NUMBER()
WITH time_span_min_10_tmp AS (
SELECT
date
, time_span_min_10
, price
, ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt)
AS row_num
FROM ts_dt
)
SELECT
date
, time_span_min_10
, price AS price_10m_first
FROM time_span_min_10_tmp
WHERE row_num = 1
;

-- (1-1-2) way 2: first using DISTINCT ON
SELECT DISTINCT ON (date, time_span_min_10)
date
, time_span_min_10
, price AS price_10m_first
FROM ts_dt
ORDER BY date, time_span_min_10, dt, price;

시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별 마지막 행의 값(last row's value)을 구하는 두가지 방법은 위에서 소개한 첫번째 행의 값을 구하는 방법에서 정렬 기준을 정반대로 해서, 내림차순 정렬(ORDER BY dt DESC) 을 해주면 됩니다.

-- (1-2-1) way 1: last using ROW_NUMBER()
WITH time_span_min_10_tmp AS (
SELECT
date
, time_span_min_10
, price
, ROW_NUMBER()
OVER (PARTITION BY date, time_span_min_10 ORDER BY dt DESC)
AS row_num
FROM ts_dt
)
SELECT
date
, time_span_min_10
, price AS price_10m_last
FROM time_span_min_10_tmp
WHERE row_num = 1
;

-- (1-2-2) way 2: last using DISTINCT ON
SELECT
DISTINCT ON (date, time_span_min_10)
date
, time_span_min_10
, price AS price_10m_last
FROM ts_dt
ORDER BY date, time_span_min_10, dt DESC, price;

(6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기

10분 단위 구간(10 minutes time span) 별로 가격(price) 칼럼의 합(sum)을 먼저 구하고, ==> 시간 순서대로 정렬된 상태에서 10분 단위 구간별로 SUM() OVER () window function을 사용해서 누적 합(cumulative sum)을 구해보겠습니다.

-- (2) sum, cumulative sum
-- sum, cumulative sum by time_span_min_10
WITH price_10m_sum_tmp AS (
SELECT
date
, time_span_min_10
, sum(price) AS price_10m_sum
FROM ts_dt
GROUP BY 1, 2
)
SELECT
date
, time_span_min_10
, price_10m_sum
, SUM(price_10m_sum)
OVER ( ORDER BY time_span_min_10
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS price_10m_cumsum
FROM price_10m_sum_tmp
;

집계/ 요약을 하는 시간 단위 구간을 위의 10분에서 이번에는 1 시간 (1 hour time span) 단위 구간별로 변경해서 가격 합(sum)과 가격 누적합(cumulative sum)을 구해보겠습니다. 위의 (3)번에서 '날짜-시간 기준정보 테이블'(dim_date_time table)에서 '1시간 단위 구간' 칼럼 (time_span_hour_1) 을 만들어놓았었기 때문에 group by 집계/ 요약하는 기준이 되는 칼럼을 'time_span_min_10'을 'time_span_hour_1'으로 변경해주기만 하면 됩니다.

-- sum, cumulative sum by time_span_hour_1
WITH price_1h_sum_tmp AS (
SELECT
date
, time_span_hour_1
, sum(price) AS price_1h_sum
FROM ts_dt
GROUP BY 1, 2
)
SELECT
date
, time_span_hour_1
, price_1h_sum
, SUM(price_1h_sum)
OVER ( ORDER BY time_span_hour_1
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS price_1h_cumsum
FROM price_1h_sum_tmp;

(7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중앙값(median),
3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기

최소값은 min(), 최대값은 max(), 1사분위수/중앙값/3사분위수는 percentile_disc(), 범위는 max() - min() 함수를 이용해서 time_span_min_10 으로 group by 집계, 요약하였습니다.

1/2/3 사분위수(quantile) 구하는 percentile_disc(0.25 / 0.5 / 0.75) within group (order by price) 함수를 사용해서 IQR ( = q3 - q1) 구할 수도 있습니다.

-- (3) min, q1, median, q3, max, range by time_span_min_10
SELECT
date
, time_span_min_10
, MIN(price) AS price_10m_min
, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY price) AS price_10m_q1
, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) AS price_10m_median
, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY price) AS price_10m_q3
, MAX(price) AS price_10m_max
, MAX(price) - MIN(price) AS price_10m_range
FROM ts_dt
GROUP BY 1, 2
ORDER BY 1, 2;

(8) 10분 단위 구간별로 평균(mean), 분산(variance), 표준편차(standard deviation) 구하기

10분 단위 구간(10 minutes time span) 별로 표본 분산(sample variance)은 variance() 함수, 표본 표준편차(sample standard deviation)은 stddev() 함수를 이용하여 구하였습니다.

10분 단위 구간별로 만약 행의 개수가 1개만 있는 경우 분산, 표준편차를 구할 수 없으므로 NULL 값을 반환하게 되는데요, 이런 경우 coalesce() 를 사용해서 '0'으로 채워주었습니다.

-- (4) mean, variance, standard deviation by time_span_min_10
SELECT
date
, time_span_min_10
, AVG(price) AS price_10m_mean
, COALESCE(VARIANCE(price), 0) AS price_10m_var
, COALESCE(STDDEV(price), 0) AS price_10m_stddev
FROM ts_dt
GROUP BY 1, 2
ORDER BY 1, 2;

(9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)

-- (5) amount-weighted average price
WITH price_mult_amt_tmp AS (
SELECT
dt
, date
, time_span_min_10
, amt
, (price * amt) AS price_mult_amt
FROM ts_dt
)
SELECT
date
, time_span_min_10
, ROUND((sum(price_mult_amt) / sum(amt)), 2) AS price_10m_amount_weighted_avg
FROM price_mult_amt_tmp
GROUP BY 1, 2
ORDER BY 1, 2;

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

이번 포스팅이 도움이 되었다면 아래의 '공감~

'를 꾹 눌러주세요. :-)

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum] PL/R Error ERROR: Error on receive from seg0 slice1: server closed the connection unexpectedly 확인사항과 대처 방안 (0)	2020.04.01
[Greenplum] PL/R을 이용한 그룹별 선형회귀모형 분산 병렬 학습 및 예측 (0)	2020.03.29
[Postgres, Greenplum] 현재 수행중인 query 조회하고 강제 종료 (kill) 하는 방법 (0)	2019.11.28
[Greenplum] 오픈소스 Greenplum DB와 Apache MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib) (0)	2019.08.25
[GPDB, PostgreSQL] Python DataFrame을 Sqlalchemy engine을 이용해 DB Table에 직접 쓰기 : df.to_sql() (0)	2019.07.28

Posted by Rfriend

[Python, R, PostgreSQL] 그룹별로 행을 내리기, 올리기 (Lag, Lead a row by Group)

Python 분석과 프로그래밍/Python 데이터 전처리 2019. 12. 9. 00:55

Lag, Lead window function은 시계열 데이터를 처리할 때 많이 사용하는 매우 유용한 함수입니다.

이번 포스팅에서는 PostgreSQL, Python (pandas), R (dplyr) 을 이용해서 그룹별로 행을 하나씩 내리기, 올리기 (lag or lead a row by group using PostgreSQL, Python, R) 하는 방법을 소개하겠습니다.

1. PostgreSQL로 그룹별로 특정 칼럼의 행을 하나씩 내리기, 올리기

(lag, lead a row by group using PostgreSQL lag(), lead() window function)

연월일(dt), 그룹ID(id), 측정값(val) 의 세 개 칼럼을 가진 시계열 데이터의 테이블을 PostgreSQL DB에 만들어보겠습니다.

DROP TABLE IF EXISTS ts;

CREATE TABLE ts (

dt date not null

, id text not null

, val numeric not null

);

INSERT INTO ts VALUES

('2019-12-01', 'a', 5)

, ('2019-12-02', 'a', 6)

, ('2019-12-03', 'a', 7)

, ('2019-12-04', 'a', 8)

, ('2019-12-01', 'b', 13)

, ('2019-12-02', 'b', 14)

, ('2019-12-03', 'b', 15)

, ('2019-12-04', 'b', 16);

SELECT * FROM ts ORDER BY id, dt;

PostgreSQL 의 LAG(value, offset, default), LEAD(value, offset, default) Window function을 이용해서 그룹ID('id') 별로 측정값('val')의 행을 하나씩 내리기(lag), 올리기(lead) 해보겠습니다. 행을 내리거나 올린 후에 빈 셀의 값은 'NULL'로 지정해주었습니다.

LAG(), LEAD() 함수를 사용할 때 그룹ID('id')별로 년월일('dt') 을 기준으로 내림차순 정렬(OVER(PARTITIO BY id ORDER BY dt)) 을 해줍니다.

-- lead() windows function

SELECT

, LAG(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lag_1

, LEAD(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lead_2

FROM ts;

lag(), lead() 함수를 사용해서 lag_1, lead_2 라는 새로운 칼럼을 추가한 'ts_lag_lead' 라는 이름의 테이블을 만들어보겠습니다.

DROP TABLE IF EXISTS ts_lag_lead;

CREATE TABLE ts_lag_lead AS (

SELECT

, LAG(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lag_1

, LEAD(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lead_2

FROM ts

);

SELECT * FROM ts_lag_lead ORDER BY id, dt;

2. Python pandas 로 DataFrame 내 그룹별 특정 칼럼의 행을 하나씩 내리기, 올리기

(shift a row by group using Python pandas library)

위에서 PostgreSQL의 lag(), lead() window function과 똑같은 작업을 Python pandas 를 가지고 수행해보겠습니다.

먼저 dt, id, val의 칼럼을 가진 pandas DataFrame 시계열 데이터를 만들어보겠습니다.

import pandas as pd

ts = pd.DataFrame({'dt': ['2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04',

'2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04'],

'id': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],

'val': [5, 6, 7, 8, 13, 14, 15, 16]})

	dt	id	val
0	2019-12-01	a	5
1	2019-12-02	a	6
2	2019-12-03	a	7
3	2019-12-04	a	8
4	2019-12-01	b	13
5	2019-12-02	b	14
6	2019-12-03	b	15
7	2019-12-04	b	16

shift() 함수를 쓰기 전에 sort_values() 함수로 정렬을 해주는데요, lag 는 내림차순 정렬, lead는 오름차순 정렬임에 주의해야 합니다. (PostgreSQL, R 대비 Python이 좀 불편하긴 하네요 -,-;)

(a) lag: sort_values() 함수를 이용해서 년월일('dt')를 기준으로 내림차순 정렬 (ascending=True) 한 후, 'id' 그룹별로 'val' 값을 하나씩 내려기 groupby('id')['val'].shift(1)

(b) lead: sort_values() 함수를 이용해서 년월일('dt')를 기준으로 오름차순 정렬 (ascending=False) 한 후, 'id' 그룹별로 'val' 값을 하나씩 올리기 groupby('id')['val].shift(1)

# lag a row by group 'id'

ts['val_lag_1'] = ts.sort_values(by='dt', ascending=True).groupby('id')['val'].shift(1)

# lead a row by group 'id'

ts['val_lead_1'] = ts.sort_values(by='dt', ascending=False).groupby('id')['val'].shift(1)

ts.sort_values(by=['id', 'dt'])

	dt	id	val	val_lag_1	val_lead_1
0	2019-12-01	a	5	NaN	6.0
1	2019-12-02	a	6	5.0	7.0
2	2019-12-03	a	7	6.0	8.0
3	2019-12-04	a	8	7.0	NaN
4	2019-12-01	b	13	NaN	14.0
5	2019-12-02	b	14	13.0	15.0
6	2019-12-03	b	15	14.0	16.0
7	2019-12-04	b	16	15.0	NaN

3. R dplyr 로 dataframe 내 그룹별 특정 칼럼의 행을 하나씩 내리기, 올리기

(lag, lead a row by group using R dplyr library)

위에서 PostgreSQL의 lag(), lead() window function과 똑같은 작업을 R dplyr library를 가지고 수행해보겠습니다.

먼저 dt, id, val의 칼럼을 가진 R DataFrame 시계열 데이터를 만들어보겠습니다.

#install.packages("dplyr")

library(dplyr)

dt <- c(rep(c('2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04'), 2))

id <- c(rep('a', 4), rep('b', 4))

val <- c(5, 6, 7, 8, 13, 14, 15, 16)

ts <- data.frame(dt, id, val)

A data.frame: 8 × 3
dt	id	val
<fct>	<fct>	<dbl>
2019-12-01	a	5
2019-12-02	a	6
2019-12-03	a	7
2019-12-04	a	8
2019-12-01	b	13
2019-12-02	b	14
2019-12-03	b	15
2019-12-04	b	16

R은 Postgresql 처럼 lag(), lead() window function을 가지고 있고 dplyr library의 chain operator를 써서 arrange() 함수로 'dt' 기준 내림차순 정렬하고, group_by(id)를 써서 그룹ID('id')별로 lag(), lead()를 무척 편리하게 적용해서 새로운 변수를 생성(mutate)할 수 있습니다.

ts <- ts %>%

arrange(dt) %>%

group_by(id) %>%

mutate(val_lag_1 = lag(val, 1),

val_lead_1 = lead(val, 1))

arrange(ts, id, dt)

A grouped_df: 8 × 5
dt	id	val	val_lag_1	val_lead_1
<fct>	<fct>	<dbl>	<dbl>	<dbl>
2019-12-01	a	5	NA	6
2019-12-02	a	6	5	7
2019-12-03	a	7	6	8
2019-12-04	a	8	7	NA
2019-12-01	b	13	NA	14
2019-12-02	b	14	13	15
2019-12-03	b	15	14	16
2019-12-04	b	16	15	NA

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

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

728x90

저작자표시 비영리 변경금지

'Python 분석과 프로그래밍 > Python 데이터 전처리' 카테고리의 다른 글

[Python datetime] datetime 모듈의 데이터 유형 (data types in pandas datetime module) (0)	2019.12.21
[Python pandas] resample() 메소드로 시계열 데이터를 10분 단위 구간별로 집계/요약 하기 (8)	2019.12.15
[Python] pandas DataFrame: ValueError: If using all scalar values, you must pass an index 에러 해결 방법 (0)	2019.09.15
[Python] 웹에서 XML 포맷 데이터를 Python으로 읽어와서 DataFrame으로 만들기 (18)	2019.09.04
[Python] 웹으로 부터 JSON 포맷 데이터 읽어와서 pandas DataFrame으로 만들기 (0)	2019.08.31

Posted by Rfriend

[Postgres, Greenplum] 현재 수행중인 query 조회하고 강제 종료 (kill) 하는 방법

Greenplum and PostgreSQL Database 2019. 11. 28. 00:17

DB에서 데이터처리나 분석을 하다보면 실수를 한다든지, 데이터 크기가 너무 크다든지, index가 안걸려있는 상태에서 join을 하려고 했다든지, cross join이 되어 연산량이 기하급수적으로 늘었다든지, 여러가지 이유로 수행시간이 너무 오래걸리는 경우가 생길 수 있습니다. 이럴 경우 강제로 돌아가고 있는 쿼리를 강제 종료 (kill active query) 시켜야 하는 상황이 생길 수 있습니다.

이번 포스팅에서는 PostgreSQL DB, Greenplum DB 이용 시에 터미널에서 psql 로

(1) 터미널 창에서 psql 실행하여

(2) 현재 수행되고 있는 쿼리(current active query)를 조회하고,

(3) 특정 쿼리를 강제 종료 (kill a specific query) 시키는 방법

을 소개하겠습니다.

(1) 먼저, 터미널을 열고 $ psql postgres 을 입력해서 psql 을 사용할 수 있는 환경으로 들어갑니다.

ihongdon-ui-MacBook-Pro:~ ihongdon$ psql postgres

psql (11.5)

Type "help" for help.

postgres=#

호스트 이름, DB 이름, 사용자 ID, Password 입력은 아래 psql을 참고하세요.

$ psql -h host_name -d db_name -u user_id

$ Password ********

(2) 터미널에서 psql 로 pg_stat_activity 테이블에서 아래의 조회 쿼리를 사용하여 현재 Postgres에서 수행 중인 쿼리(active query)를 조회해보겠습니다.

select pid, now() - query_start as "runtime", usename, datname, state, query

from pg_stat_activity

where now() - query_start > '2 minutes'::interval and state = 'active'

order by runtime desc;

위의 쿼리로 조회를 해서 강제 종료시키고자 하는 쿼리의 'pid' 를 확인합니다. 아래의 예의 경우 pid = 4941 이네요.

postgres=#

postgres=# select pid, now() - query_start as "runtime", usename, datname, state, query from pg_stat_activity where now() - query_start > '2 minutes'::interval and state = 'active' order by runtime desc;

------+-----------------+----------+----------+--------+----------------------------------------

4941 | 00:34:00.595933 | ihongdon | postgres | active | +

| | | | DROP TABLE IF EXISTS my_table; +

| | | | CREATE TABLE my_table AS ( +

| | | | SELECT +

| | | | a.* +

| | | | , b.age +

| | | | , b.pch_amt +

| | | | , b.pch_cnt +

| | | | , b.age +

| | | | FROM +

| | | | cust_master AS a +

| | | | LEFT OUTER JOIN cust_pch AS b +

| | | | ON a.cust_id = b.cust_id +

| | | | ); +

| | | |

(1 row)

postgres=#

(3) SELECT pg_terminate_backend(put your pid) 로 강제 종료 시키고자 하는 쿼리의 pid 를 지정해주어서 kill 시킵니다.

postgres=# SELECT pg_terminate_backend(4941);

pg_terminate_backend

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

(1 row)

postgres=#

-----------------[ Greenplum DB (GPDB) 알아두면 요긴한 팁 ] -------------------------

Greenplum DB(GPDB)도 현재 수행 중이 쿼리 조회하고 강제 종료하는 방법 (Postgres와 동일)

-- ssh로 GPDB 접속

> ssh gpadmin@xxx.xxx.xxx.xxx
> password: xxxxxxxxx

-- 현재 수행 중인 쿼리 조회
> select datname, username, proceed, sess_id, current_query from pg_stat_activity;

-- 현재 수행중인 쿼리 강제 종료
> select pg_cancel_backend(**put pid here**)

GPDB에서 Segment 확인하는 방법

> select gp_segment_id, count(*) from mytable group by gp_segment_id;

GPDB master, Segment 확인

> select * from pg_catalog.gp_segment_configration;

GPDB, Postgres DB 칼럼 이름 및 데이터 유형 확인 (column name and data type)

select column_name, data_type

from information_schema.columns
where table_name ='table_name'

gpperfrom db 에 접속하여 system history 테이블 조회하는 방법 (병렬처리 확인)

select

ctime,

hostname,

avg(cpu_user + cpu_sys) cpu,

avg(mem_actual_used/1024/1024/1024) memogy_gigabyte,

avg(dist_rb_rate/1024/1024) dist_rb_megabyte,

avg(disk_wb_rate/1024/1024) disk_wb_megabyte,

avg(net_rb_rate/1024/1024) net_rb_megabyte,

avg(net_wb_rate/1024/1024) net_wb_megabyte

from system_hitory

where ctime between '2019-01-01 12:00:00' and '2019-01-01 23:59:59'

and hostname not in ('dev1.gphd.local', 'dev2.gphd.local')

group by ctime, hostname

order by 1, 2 asc

PostgreSQL DB 서버 시작, 종료 (How to Start and Stop PostgreSQL Database Server)

- Mac OS (명령 프롬프트 창에서 Command 실행)

-- Mac OS 에서 Homebrew 사용하여 PostgreSQL 시작 (Start PostgreSQL using Homebrew on Mac OS)

$ brew services start postgresql

-- Mac OS 에서 Homebrew 사용하여 PostgreSQL 종료 (Stop PostgreSQL using Homebrew on Mac OS)

$ brew services stop postgresql

- Windows OS (명령 프롬프트 창에서 command 실행)

-- Windows OS 에서 PostgreSQL 시작 (Start PostgreSQL on Windows OS)

$ pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" start

-- Windows OS 에서 PostgreSQL 종료 (Stop PostgreSQL on Windows OS)

$ pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" stop

웹브라우저에서 GPDB Command Center 접속하는 방법

http://localhost:28080/main/querymonitor/hostmetrics

GPDB를 UI 상에서 편리하게 모니터링 하고 관리할 수 있게 해주는 GPDB Command Center 에 대한 자세한 설명은 gpcc.docs.pivotal.io 를 참고하세요.

[ Pivotal Greenplum Command Center Images ((https://gpcc.docs.pivotal.io/600/welcome.html) ]

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum] PL/R을 이용한 그룹별 선형회귀모형 분산 병렬 학습 및 예측 (0)	2020.03.29
[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum) (0)	2019.12.15
[Greenplum] 오픈소스 Greenplum DB와 Apache MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib) (0)	2019.08.25
[GPDB, PostgreSQL] Python DataFrame을 Sqlalchemy engine을 이용해 DB Table에 직접 쓰기 : df.to_sql() (0)	2019.07.28
[GPDB, Postgres] Python DataFrame을 Postgresql, Greenplum DB에 Copy 해서 넣는 방법 (0)	2019.07.17

Posted by Rfriend

[Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore)

Greenplum and PostgreSQL Database 2019. 4. 27. 18:59

앞의 포스팅에서는 공간지리 형태의 데이터셋을 import 하는 방법들을 소개하였습니다.

이번 포스팅에서는 PostgreSQL, Greenplum DB의 PostGIS 에서 테이블(Table) 형태로 있는 공간지리 데이터에 대해서 (1) pg_dump로 공간지리 테이블을 백업하기(Backup), (2) pg_restore 로백업한 공간지리 테이블을 다시 불러오기 (Restore) 를 해보겠습니다.

(* Reference: https://github.com/PacktPublishing/Mastering-PostGIS)

(1) pg_dump로 공간지리 데이터 테이블 백업하기 (Create a Backup table)

명령 프롬프트 창에서 docker로 Greenplum DB를 실행한 후에, gpadmin 계정으로 들어가서 이미 geometry 포맷으로 만들어두었던 data_import.earthquakes_subset_with_geom 테이블을 pg_dump 를 사용하여 백업해보았습니다. (host, port, user 부분은 각자의 database 설정을 입력하면 됨)

[gpadmin@mdw tmp]$ pg_dump -h localhost -p 5432 -U gpadmin -t data_import.earthquakes_subset_with_geom -c -F c -v -b -f earthquakes_subset_with_geom.backup gpadmin

pg_dump: reading extensions

pg_dump: identifying extension members

20190417:04:24:25|pg_dump-[INFO]:-reading schemas

pg_dump: reading user-defined tables

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined functions

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined types

20190417:04:24:25|pg_dump-[INFO]:-reading type storage options

20190417:04:24:25|pg_dump-[INFO]:-reading procedural languages

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined aggregate functions

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operators

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined external protocols

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operator classes

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operator families

pg_dump: reading user-defined text search parsers

pg_dump: reading user-defined text search templates

pg_dump: reading user-defined text search dictionaries

pg_dump: reading user-defined text search configurations

20190417:04:24:26|pg_dump-[INFO]:-reading user-defined conversions

20190417:04:24:26|pg_dump-[INFO]:-reading type casts

20190417:04:24:26|pg_dump-[INFO]:-reading table inheritance information

pg_dump: finding extension tables

20190417:04:24:26|pg_dump-[INFO]:-reading rewrite rules

20190417:04:24:26|pg_dump-[INFO]:-finding inheritance relationships

20190417:04:24:26|pg_dump-[INFO]:-reading column info for interesting tables

pg_dump: finding the columns and types of table "earthquakes_subset_with_geom"

20190417:04:24:26|pg_dump-[INFO]:-flagging inherited columns in subtables

20190417:04:24:26|pg_dump-[INFO]:-reading indexes

20190417:04:24:26|pg_dump-[INFO]:-reading constraints

20190417:04:24:26|pg_dump-[INFO]:-reading triggers

pg_dump: reading dependency data

pg_dump: saving encoding = UTF8

pg_dump: saving standard_conforming_strings = on

pg_dump: dumping contents of table earthquakes_subset_with_geom

[gpadmin@mdw tmp]$

(2) pg_restore 로 백업 테이블 다시 불러오기

이미 테이블로 만들어져 있는 data_import.earthquakes_subset_with_geom 테이블을 삭제한 후에, (1)번에서 백업해둔 데이터를 불러오겠습니다.

테이블을 먼저 삭제해볼께요.

-- (2) (DBeaver db tool 에서) drop table

DROP TABLE data_import.earthquakes_subset_with_geom;

테이블을 삭제하였으니, 이제 다시 (1)번에서 백업해두었던 데이터를 다시 불러와서 테이블을 생성(Restore a Backup table)해보겠습니다.

-- (3) (명령 프롬프트 창에서) Restore using pg_restore

[gpadmin@mdw tmp]$ pg_restore -h localhost -p 5432 -U gpadmin -v -d gpadmin earthquakes_subset_with_geom.backup

pg_restore: connecting to database for restore

pg_restore: creating TABLE earthquakes_subset_with_geom

pg_restore: restoring data for table "earthquakes_subset_with_geom"

pg_restore: setting owner and privileges for TABLE earthquakes_subset_with_geom

[gpadmin@mdw tmp]$

DBeaver db tool에서 백업 테이블을 잘 불어와서 테이블이 생성이 되었는지 확인해보겠습니다.

-- (4) (DBeaver db tool 에서) 백업 되었는지 조회 확인

SELECT * FROM data_import.earthquakes_subset_with_geom LIMIT 10;

백업 테이블 불어오기(restore)가 잘 되었네요.

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[GPDB, Postgres] Python DataFrame을 Postgresql, Greenplum DB에 Copy 해서 넣는 방법 (0)	2019.07.17
[Greenplum DB] GPDB에 PL/R Language Extension, R 패키지 수동 설치 방법 (0)	2019.05.16
[Greenplum DB] PostGIS에 raster2pgsql 을 사용하여 raster data import 하기 (0)	2019.04.17
[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷) (0)	2019.04.11
[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법 (0)	2019.04.11

Posted by Rfriend

[Greenplum DB] GPDB docker에 PostGIS 설치하기

Greenplum and PostgreSQL Database 2019. 3. 27. 07:45

이번 포스팅에서는 PostgreSQL, Greenplum database에서 지리공간 데이터 분석 (Geo-Spatial data analysis) 을 할 수 있도록 해주는 외장 확장 오픈 소스 소프트웨어 프로그램인 PostGIS 를 Greenplum docker 위에 설치하는 방법을 소개하겠습니다.

Greenplum DB에 PostGIS 를 설치하는 가이드는 https://gpdb.docs.pivotal.io/5100/ref_guide/extensions/postGIS.html 를 참고하였습니다.

0. (사전 준비 사항) Docker를 이용하여 Greenplum DB + MADlib + PL/x 설치

CentOS + Greenplum + MADlib + PL/R + PL/Python 이 설치된 Docker Image를 이용하여 분석환경을 구성하는 자세한 내용은 https://rfriend.tistory.com/379 포스팅을 참고하기 바랍니다.

명령 프롬프트 창을 띄우고 아래 docker 명령어로 greenplum을 간편하게 설치해보세요.

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

-- GPDB w/MADlib, PL/x on Docker : https://hub.docker.com/r/hdlee2u/gpdb-analytics

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

-- (1) Docker Image Pull

$ docker pull hdlee2u/gpdb-analytics

$ docker images

-- (2) Docker Image Run(port 5432) -> Docker Container Creation

$ docker run -i -d -p 5432:5432 -p 28080:28080 --name gpdb-ds --hostname mdw hdlee2u/gpdb-analytics /usr/sbin/sshd -D

$ docker ps -a

-- (3) To Start Greenplum Database and Use psql

$ docker exec -it gpdb-ds /bin/bash

[root@mdw /]# su - gpadmin

[gpadmin@mdw ~]$ gpstart -a

.... GPDB start

....

CnetOS와 GPDB 버전에 맞는 PostGIS 버전을 다운로드해서 설치를 해야 합니다. IP 확인, CentOS version 확인, MADlib, PL/R 버전 확인, R & Python Data Science Package version 확인하는 방법은 아래를 참고하세요.

- CentOS : release 7.4

- Greenplum Database : ver 5.10.2

- MADlib : ver 1.15

- PL/R : 2.3.2

- DataScienceR : 1.0.1

- DataSciencePython : 1.1.1

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

-- IP check

[gpadmin@mdw ~]$

[root@mdw ~]# cd /home/gpadmin

[root@mdw gpadmin]# ifconfig -a

eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500

inet 172.17.0.2 netmask 255.255.0.0 broadcast 172.17.255.255

ether 02:42:ac:11:00:02 txqueuelen 0 (Ethernet)

RX packets 25395 bytes 10372326 (9.8 MiB)

RX errors 0 dropped 0 overruns 0 frame 0

TX packets 25074 bytes 79368842 (75.6 MiB)

TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

... (이하 생략)

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

-- MADlib, PL/R, Python Data Science Package, GP Command Center version check

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

[root@mdw gpadmin]# cd /setup

[root@mdw setup]# ls -al

total 810088

drwxr-xr-x 4 gpadmin gpadmin 4096 Aug 13 2018 .

drwxr-xr-x 1 root root 4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13 2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13 2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin 4096 Jul 23 2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin 29040039 Aug 13 2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10 2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13 2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin 4 Aug 13 2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin 4096 Aug 11 2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin 3023537 Aug 13 2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin 39279994 Aug 13 2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg

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

-- CentOS version check

[gpadmin@mdw setup]$ cat /etc/os-release

NAME="CentOS Linux"

VERSION="7 (Core)"

ID="centos"

ID_LIKE="rhel fedora"

VERSION_ID="7"

PRETTY_NAME="CentOS Linux 7 (Core)"

ANSI_COLOR="0;31"

CPE_NAME="cpe:/o:centos:centos:7"

HOME_URL="https://www.centos.org/"

BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"

CENTOS_MANTISBT_PROJECT_VERSION="7"

REDHAT_SUPPORT_PRODUCT="centos"

REDHAT_SUPPORT_PRODUCT_VERSION="7"

1. Pivotal Network에서 PostGIS 다운로드

(1) https://network.pivotal.io/ 접속 (다운로드를 위해서는 회원가입 필요)

> (2) 'Pivotal Greenplum Releases: 5.10.2' : https://network.pivotal.io/products/pivotal-gpdb#/releases/158026

> (3) 'Greenplum Adnvanced Analytics' : https://network.pivotal.io/products/pivotal-gpdb#/releases/158026/file_groups/1084

> (4) 'PostGIS 2.1.5+pivotal.1 for RHEL 7' file download

의 순서대로 경로를 찾아가서 PostGIS 2.1.5+pivotal.1 for RHEL 7 파일을 다운로드 합니다.

2. 다운로드한 PostGIS 압축파일을 Greenplum Docker 컨테이너 안으로 복사(copy)하기

다른 명령 프롬프트 창을 띄우고, 아래처럼 Downloads 폴더로 경로 변경 후에 docker cp 명령문으로 1번에서 다운로드한 PostGIS 2.1.5 압축 파일을 Greenplum 도커 컨테이너 안의 'gpdb-ds:/setup' 경로로 복사해주세요.

-- [At another terminal window] Copy PostGIS 2.1.5 to GPDB-DS Docker Container

ihongdon-ui-MacBook-Pro:~ ihongdon$ pwd

/Users/ihongdon

ihongdon-ui-MacBook-Pro:~ ihongdon$ cd Downloads/

ihongdon-ui-MacBook-Pro:Downloads ihongdon$ ls -al

-rw-r--r--@ 1 ihongdon staff 19839907 3 22 16:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

ihongdon-ui-MacBook-Pro:Downloads ihongdon$ docker cp postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg gpdb-ds:/setup

ihongdon-ui-MacBook-Pro:Downloads ihongdon$

3. gpadmin 계정에게 postgis-2.1.5 파일에 대한 권한 부여 (chown)

(1) gpadmin 으로 들어와 있는 명령 프롬프트 창으로 와서 root 계정으로 로그인 후에 => (2) chown 명령어를 이용하여 gpadmin 에 PostGIS 파일에 대한 권한을 부여해줍니다.

-- 파일 소유자나 소유 그룹 변경 : chown

[gpadmin@mdw setup]$ su -

Password:

Last login: Fri Mar 22 07:01:35 UTC 2019 on pts/0

[root@mdw ~]# cd /setup

[root@mdw setup]# ls -al

total 829464

drwxr-xr-x 1 gpadmin gpadmin 4096 Mar 22 07:33 .

drwxr-xr-x 1 root root 4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13 2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13 2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin 4096 Jul 23 2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin 29040039 Aug 13 2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10 2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13 2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin 4 Aug 13 2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin 4096 Aug 11 2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin 3023537 Aug 13 2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin 39279994 Aug 13 2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 501 games 19839907 Mar 22 07:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

[root@mdw setup]# chown gpadmin:gpadmin postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

4. gppkg로 각 Segment 노드에 PostGIS 설치하기

(1) 명령 프롬프트 창에서 root 계정에서 exit 후 => gpadmin 계정에서 gppkg -i 로 PostGIS 2.1.5를 설치합니다.

(2) 그러면 로그 메시지에 'gppkg:mdw:gpadmin-[INFO]:-Please run the following commands to enable the PostGIS package: $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh mydatabase install'라는 메시지가 나옵니다. 이 메시지를 추가로 실행시킵니다.

-- PostGIS 2.1.5 install

[root@mdw setup]# exit

logout

[gpadmin@mdw setup]$ ls -al

total 829464

drwxr-xr-x 1 gpadmin gpadmin 4096 Mar 22 07:33 .

drwxr-xr-x 1 root root 4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13 2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13 2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin 4096 Jul 23 2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin 29040039 Aug 13 2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10 2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13 2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin 4 Aug 13 2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin 4096 Aug 11 2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin 3023537 Aug 13 2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin 39279994 Aug 13 2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 19839907 Mar 22 07:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

[gpadmin@mdw setup]$ gppkg -i postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

20190322:07:36:54:011243 gppkg:mdw:gpadmin-[INFO]:-Starting gppkg with args: -i postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Installing package postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix /usr/local/greenplum-db-5.10.2'

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Installing postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg locally

20190322:07:36:56:011243 gppkg:mdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix /usr/local/greenplum-db-5.10.2'

20190322:07:36:56:011243 gppkg:mdw:gpadmin-[INFO]:-Installing rpms cmdStr='rpm -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix=/usr/local/greenplum-db-5.10.2'

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-Completed local installation of postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg.

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-Please run the following commands to enable the PostGIS package: $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh mydatabase install

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg successfully installed.

[gpadmin@mdw setup]$ cd $GPHOME

[gpadmin@mdw greenplum-db]$ cd share

[gpadmin@mdw share]$ ls

gdal greenplum packages postgresql proj

[gpadmin@mdw share]$ cd postgresql/

[gpadmin@mdw postgresql]$ cd contrib/

[gpadmin@mdw contrib]$ ls

citext.sql gp_distribution_policy.sql gp_svec_test.sql oid2name.txt postgis-2.1 uninstall_fuzzystrmatch.sql uninstall_hstore.sql

dblink.sql gp_session_state.sql hstore.sql orafunc.sql uninstall_citext.sql uninstall_gp_distribution_policy.sql uninstall_orafunc.sql

fuzzystrmatch.sql gp_sfv_test.sql indexscan.sql pgcrypto.sql uninstall_dblink.sql uninstall_gp_session_state.sql uninstall_pgcrypto.sql

[gpadmin@mdw contrib]$ cd postgis-2.1/

[gpadmin@mdw postgis-2.1]$ ls

install postgis_manager.sh uninstall upgrade

[gpadmin@mdw postgis-2.1]$ $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh gpadmin install

SET

BEGIN

CREATE FUNCTION

CREATE TYPE

CREATE FUNCTION

INSERT 0 1

COMMIT

ANALYZE

[gpadmin@mdw postgis-2.1]$

자, 이제 PostGIS가 Greenplum docker 컨테이너 안에 설치가 되었습니다.

5. PostGIS 샘플 Query 실행해서 테스트해보기

DBeaver DB tool로 아래의 PostGIS 테이블 생성해보고 select query 를 날려보겠습니다.

-- Create PostGIS extension

CREATE EXTENSION postgis;

-- PostGIS version check

SELECT PostGIS_Version();

-- PostGIS sample query

CREATE TABLE geom_test ( gid int4, geom geometry,

name varchar(25) );

INSERT INTO geom_test ( gid, geom, name )

VALUES ( 1, 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square');

INSERT INTO geom_test ( gid, geom, name )

VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' );

INSERT INTO geom_test ( gid, geom, name )

VALUES ( 3, 'MULTIPOINT(3 4,8 9)', '2D Aggregate Point' );

SELECT * from geom_test WHERE geom &&

Box3D(ST_GeomFromEWKT('LINESTRING(2 2 0, 3 3 0)'));

잘 작동하는군요. ^^

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법 (0)	2019.04.11
[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기 (0)	2019.04.10
[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot) (0)	2019.03.21
[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜 (0)	2019.03.16
[Greenplum DB] 외부 데이터를 Table에 업로드하는 5가지 방법 : CREATE EXTERNAL TABLE, COPY, INSERT INTO VALUES(), pd.DataFrame.to_sql(), DBeaver import (0)	2019.03.10

Posted by Rfriend

[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜

Greenplum and PostgreSQL Database 2019. 3. 16. 22:30

이번 포스팅에서는 Greenplum DB, Postgresql 에서 테이블을 생성한 후에 SQL로 데이터 전처리하는 몇 가지 방법을 소개하겠습니다.

예제로 사용할 간단한 고객 정보 테이블을 생성하고, 행 번호, 고객번호, 이름, 나이, 성별, 지역, 등록일 값을 입력해보겠습니다.

DROP TABLE IF EXISTS public.cust;

CREATE TABLE public.cust (

seq_num integer

, cust_id text not null

, name text not null

, age integer

, gender text

, region text

, regist_date date

);

INSERT INTO public.cust VALUES

(1, 'A001', 'choi', 25, 'M', 'seoul', '2018-01-25'),

(2, 'A002', 'kang', 30, 'F', 'Busan', '2019-02-08'),

(3, 'A003', 'lee', 29, NULL, 'seoul', '2018-05-30'),

(4, 'B001', 'kim', 35, 'F', 'seoul', '2018-12-22'),

(5, 'B002', 'sung', 34, 'M', 'busan', '2019-02-19'),

(6, 'B003', 'park', NULL, NULL, 'SEOUL', '2019-03-15');

SELECT * FROM public.cust ORDER BY seq_num;

위의 테이블에서

(1) 고객ID(cust_id) 문자열의 첫 번째 문자열을 가져다가 group 칼럼 만들기
: SUBSTRING(cust_id, 1, 1) AS group

(2) group별로 seq_num 순서에 따라 행 번호 부여하기
: ROW_NUMBER() OVER(PARTITION BY SUBSTRING(cust_id, 1, 1) ORDER BY seq_num) AS grp_num

(3) 나이(age) 결측값을 전체 평균 값으로 채우기
: COALESCE(age, AVG(age) OVER())::INTEGER AS age

(4) 성별(gender) 결측값을 "Unknown" 값으로 채우기
: COALESCE(gender, 'Unknown') AS gender

(5) 지역(region) 대문자를 소문자로 바꾸기
: LOWER(region) AS region

(6) 이름(name)이 'choi', 'park', 'lee', 'kim'은 그대로 두고, 그 외는 'others'로 바꾸어서 name_2 칼럼 만들기
: CASE WHEN name IN ('choi', 'park', 'lee', 'kim') THEN name ELSE 'others' END AS name_2

-- substring of id's first character
-- insert row number
-- fill missing value of 'age' with average
-- fill missing value of 'gender' with 'Unknown'
-- convert upper letter into lower letter
-- if name IN ('choi', 'park', 'lee', 'kim') then name, else 'other'

DROP TABLE IF EXISTS public.cust_preprocessed CASCADE;

CREATE TABLE public.cust_preprocessed AS

(

SELECT

seq_num,

cust_id,

SUBSTRING(cust_id, 1, 1) AS group,

ROW_NUMBER() OVER(PARTITION BY SUBSTRING(cust_id, 1, 1) ORDER BY seq_num) AS grp_num,

COALESCE("age", AVG(age) OVER())::INTEGER AS "age",

COALESCE(gender, 'Unknown') AS gender,

LOWER(region) AS region,

name,

CASE WHEN name IN ('choi', 'park', 'lee', 'kim') THEN name

ELSE 'others' END AS name_2,

regist_date

FROM public.cust

ORDER BY cust_id

) DISTRIBUTED RANDOMLY;

SELECT * FROM public.cust_preprocessed ORDER BY seq_num;

다음으로 날짜 형식의 데이터에서 년(year), 월(month), 일(day), 현재 날짜(now), 입력 날짜로 부터 현재 날짜까지의 소요 일(day until now)을 계산해보겠습니다.

(7) 등록 날짜에서 년(year) 정보 추출
: EXTRACT (YEAR FROM regist_date)::int AS year

(8) 등록 날짜에서 월(month) 정보 추출
: EXTRACT (MONTH FROM regist_date)::int AS month

(9) 등록 날짜에서 일(day) 정보 추출
: EXTRACT (DAY FROM regist_date)::int AS day

(10) 현재 날짜 자동 입력
: now()::DATE

(11) 이전 등록 날짜에서 현재까지의 소요 일 계산
: AGE(regist_date) AS time_from_regist

-- extract year, month, day from regist_date

DROP TABLE IF EXISTS public.cust_date CASCADE;

CREATE TABLE public.cust_date AS

(

SELECT *,

EXTRACT (YEAR FROM regist_date)::int AS year,

EXTRACT (MONTH FROM regist_date)::int AS month,

EXTRACT (DAY FROM regist_date)::int AS day,

now()::DATE,

AGE(regist_date) AS time_from_regist

FROM public.cust_preprocessed

ORDER BY cust_id

) DISTRIBUTED RANDOMLY;

SELECT

seq_num, regist_date, year, month, day,

now, time_from_regist

FROM public.cust_date

ORDER BY seq_num;

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] GPDB docker에 PostGIS 설치하기 (0)	2019.03.27
[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot) (0)	2019.03.21
[Greenplum DB] 외부 데이터를 Table에 업로드하는 5가지 방법 : CREATE EXTERNAL TABLE, COPY, INSERT INTO VALUES(), pd.DataFrame.to_sql(), DBeaver import (0)	2019.03.10
Greenplum DB, Postgresql DB에 사용할 수 있는 오픈소스 database tool DBeaver 설치 및 사용 방법 (0)	2019.03.04
[Docker] Error response from daemon: driver failed programming external connectivity on endpoint, port is already allocated 대처 방법 (port 강제 종료) (11)	2018.09.24

Posted by Rfriend

이전 1 2 3 4 다음

R, Python 분석과 프로그래밍의 친구 (by R Friend)

'PostgreSQL'에 해당되는 글 38건

[PostgreSQL, Greenplum] 문자열 처리 연산자 및 함수 (character type operators and functions)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum] PL/R을 이용한 그룹별 선형회귀모형 분산 병렬 학습 및 예측

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Python, R, PostgreSQL] 그룹별로 행을 내리기, 올리기 (Lag, Lead a row by Group)

'Python 분석과 프로그래밍 > Python 데이터 전처리' 카테고리의 다른 글

[Postgres, Greenplum] 현재 수행중인 query 조회하고 강제 종료 (kill) 하는 방법

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] GPDB docker에 PostGIS 설치하기

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

카테고리

태그목록

티스토리툴바