이번 포스팅에서는 psql 과 SQL Query 를 이용해서 PostgreSQL, Greenplum DB로부터 

 

(1) 모든 Databases 조회하기 (list all databases in PostgreSQL, Greenplum)

(2) 모든 Schemas 조회하기 (list all schemas in PostgreSQL, Greenplum) 

(3) 모든 Tables 조회하기 (list all tables in PostgreSQL, Greenplum)

(4) 모든 Views 조회하기 (list all views in PostgreSQL, Greenplum)

(5) 모든 Columns 조회하기 (list all columns in PostgreSQL, Greenplum)

 

하는 방법을 소개하겠습니다.

Database의 메타 정보를 가지고 있는 information_schemapg_catalog 테이블을 조회해서 원하는 정보를 얻는 방법인데요, 보통 데이터 전처리와 분석을 시작할 때 종종 사용하는 편이어서 알아두면 편리합니다. 

 

 

PpostgreSQL list all database, schema, table, view, columns

 

 

(1) 모든 Databases 조회하기 (list all databases in PostgreSQL, Greenplum)

 

----------------------------------------------------------------------
-- List all DBs, Schemas, Tables, Views, Columns
----------------------------------------------------------------------

-- (1) List all Databases

-- (1-1) using psql
$ \l

-- or alternatively
$ \list


-- (1-2) SQL query
SELECT datname 
FROM pg_database;

 

 

 

(2) 모든 Schemas 조회하기 (list all schemas in PostgreSQL, Greenplum) 

 

-- (2) List all Schemas

-- (2-1) using psql
$ \dn


-- (2-2) using SQL query
SELECT schema_name
FROM information_schema.schemata;

--schema_name       |
--------------------+
--madlib            |
--gp_toolkit        |
--information_schema
--public            |
--pg_catalog        |
--pg_bitmapindex    |
--pg_aoseg          |
--pg_toast          |


-- or alternatively
SELECT nspname
 FROM pg_catalog.pg_namespace;

 

 

 

(3) 모든 Tables 조회하기 (list all tables in PostgreSQL, Greenplum)

 

-- (3) Tables 

-- (3-1) using psql
-- listing all tables
$ \dt

-- listing tables using pattern matching
$ \dt pubic.ab*



-- (3-2) using SQL query
-- listing all tables
SELECT 
    table_schema
    , table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- or alternatively
SELECT
	schemaname
	, tablename
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');


-- listing tables using pattern matching
SELECT 
	schemaname
	, tablename
FROM pg_catalog.pg_tables
WHERE schemaname='public'
	AND tablename LIKE 'ab%'
;

--schemaname|tablename       |
------------+----------------+
--public    |abalone_array   |
--public    |abalone_predict |
--public    |abalone_external|
--public    |abalone         |
--public    |abalone_split   |

 

 

 

(4) 모든 Views 조회하기 (list all views in PostgreSQL, Greenplum)

 

-- (4) List all Views

-- (4-1) using psql
-- $ \dv


-- (4-2) using SQL query
SELECT 
	table_schema
	, table_name AS view_name
FROM information_schema.VIEWS
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

--table_schema|view_name                             |
--------------+--------------------------------------+
--public      |geography_columns                     |
--public      |geometry_columns                      |
--public      |plcontainer_refresh_config            |
--public      |plcontainer_show_config               |
--public      |raster_columns                        |
--public      |raster_overviews                      |


-- or alternatively
SELECT 
	schemaname
	, viewname 
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, viewname;

 

 

 

(5) 모든 Columns 조회하기 (list all columns in PostgreSQL, Greenplum)

 

-- (5) List all Columns

-- (5-1) using psql
$ \d+ table_name


--  (5-2) using SQL query
SELECT 
	table_schema 
	, table_name 
	, column_name
	, data_type
FROM information_schema.columns 
WHERE table_schema = 'public'
	AND table_name = 'abalone'
ORDER BY ordinal_position
;

--table_schema|table_name|column_name   |data_type   
--------------+----------+--------------+----------------+
--public      |abalone   |id            |bigint          |
--public      |abalone   |sex           |text            |
--public      |abalone   |length        |double precision|
--public      |abalone   |diameter      |double precision|
--public      |abalone   |height        |double precision|
--public      |abalone   |whole_weight  |double precision|
--public      |abalone   |shucked_weight|double precision|
--public      |abalone   |viscera_weight|double precision|
--public      |abalone   |shell_weight  |double precision|
--public      |abalone   |rings         |integer         |

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

시계열 데이터를 다루다 보면 년, 분기, 월, 일, 시간, 분, 초 등의 날짜와 시간에 관한 정보를 추출해야 할 일이 있습니다. 

 

이번 포스팅에서는 Greenplum, PostgreSQL 에서 EXTRACT() 함수로 TIMESTAMP, INTERVAL 데이터 유형에서 날짜, 시간 정보를 추출(retrive)하는 방법을 소개하겠습니다. 

 

(1) EXTRACT() 함수로 TIMESTAMP 데이터 유형에서 날짜, 시간 정보 가져오기

(2) EXTRACT() 함수로 INTERVAL 데이터 유형에서 날짜, 시간 정보 가져오기 

 

postgresql, greenplum, extract() function

 

(1) EXTRACT() 함수로 TIMESTAMP 데이터 유형에서 날짜, 시간 정보 가져오기

 

먼저 PostgreSQL TIMESTAMP 데이터 유형에서 년(year), 분기(quarter), 월(month), 주(week), 일(day), 시간(hour), 분(minute), 초(second), 밀리초(millisecond), 마이크로초(microsecond) 정보를 EXTRACT(field from source) 함수를 사용해서 가져와보겠습니다. 

 

------------------------------------------------------
-- PostgreSQL EXTRACT function
-- retriveing a field such as a year, month, and day from a date/time value
-- Syntax: EXTRACT(field FROM source)
-- The field argument specifies which field to extract from the date/time value
-- The source is a value of type 'TIMESTAP' or 'INTERVAL'
-- The 'EXTRACT()' function returns a double precision value. 
-- ref: https://www.postgresqltutorial.com/postgresql-extract/
------------------------------------------------------

SELECT 
	datum AS datetime
	, EXTRACT(YEAR FROM datum)::int AS year 
	, EXTRACT(QUARTER FROM datum)::int AS quarter
	, EXTRACT(MONTH FROM datum)::int AS month 
	, EXTRACT(WEEK FROM datum)::int AS week
	, EXTRACT(DAY FROM datum)::Int AS day 
	, EXTRACT(HOUR FROM datum)::int AS hour 
	, EXTRACT(MINUTE FROM datum)::Int AS minute 
	, EXTRACT(SECOND FROM datum)::Int AS second 
	--, EXTRACT(MILLISECONDS FROM datum)::int AS millisecond -- =45*1000
	--, EXTRACT(MICROSECONDS FROM datum)::Int AS microsecond -- = 45*1000000
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;
    
--datetime               |year|quarter|month|week|day|hour|minute|second|
-------------------------+----+-------+-----+----+---+----+------+------+
--2021-11-28 21:30:45.000|2021|      4|   11|  47| 28|  21|    30|    45|

 

 

PostgreSQL의 TIMESTAMP 데이터 유형에서

  - DOY (Day Of Year) : 1년 중 몇 번째 날인지에 대한 값으로, 1일~365일 범위의 값을 가짐.

  - DOW (Day Of Week): 1주일 중 몇 번째 날인지에 대한 값으로, 일요일이 '0', 토요일이 '6'의 값을 가짐.

  - ISODOW (Day Of Week based on ISO 8601) : ISO 8601 표준을 따라서 1주일 중 몇 번째 날인지에 대한 값으로, 월요일이 '1', 일요일이 '7'의 값을 가짐. 

 

-- extracting the day of year, week from TIMESTAMP type
SELECT 
	datum AS datetime
    -- the DAY OF YEAR that ranges FROM 1 TO 365
	, EXTRACT(DOY FROM datum)::int AS doy 
    
    -- the DAY OF week Sunday(0) TO Saturday(6)
	, EXTRACT(DOW FROM datum)::int AS dow 
    
    -- DAY OF Week based ON ISO 8601 Monday(1) to Sunday(7)
	, EXTRACT(ISODOW FROM datum)::int AS isodow 
FROM (
		SELECT TIMESTAMP '2021-11-28 21:30:45' AS datum
	) ts;


--datetime               |doy|dow|isodow|
-------------------------+---+---+------+
--2021-11-28 21:30:45.000|332|  0|     7|

 

 

 

(2) EXTRACT() 함수로 INTERVAL 데이터 유형에서 날짜, 시간 정보 가져오기

 

위의 (1)번에서는 TIMESTAMP 데이터 유형에 대해서 날짜, 시간 정보를 가져왔다면, 이번에는 INTERVAL 데이터 유형에 대해서 날짜, 시간 정보를 추출해보겠습니다.

 

INTERVAL 데이터 유형이 익숙하지 않은 분들을 위해서, 아래에 INTERVAL 데이터 유형을 사용해서 기준일로부터 INTERVAL 기간 만큼을 뺀 날짜/시간을 계산해 보았습니다.  ('2021-11-28 21:30:45' 에서 '2년 3개월 5일 2시간 10분 5초' 이전은 날짜/시간은?)

 

-- The INTERVAL data type allows you to store and manipulate a period of time 
-- in years, months, days, hours, minutes, seconds, etc. 
-- (Syntax) @ INTERVAL [ fields ] [ (p) ]
SELECT 
	TIMESTAMP '2021-11-28 21:30:45' AS datetme
	, TIMESTAMP '2021-11-28 21:30:45'  
	  - INTERVAL '2 years 3 months 5 days 2 hours 10 minutes 5 seconds' 
    AS intv_dt;


--datetme                |intv_dt                |
-------------------------+-----------------------+
--2021-11-28 21:30:45.000|2019-08-23 19:20:40.000|

 

 

 

그럼, INTERVAL 데이터 유형에서 EXTRACT(field FROM source) 함수를 사용해서 년(year), 분기(quarter), 월(month), 일(day), 시간(hour), 분(minute), 초(second) 정보를 가져와 보겠습니다. 

 

단, 위의 (1)번에서 TIMESTAMP 데이터 유형에 대해서는 가능했던 DOW, DOY, ISODOW, ISOYEAR, WEEK 등의 정보에 대해서는 INTERVAL 데이터 유형에 대해서는 EXTRACT() 함수가 지원하지 않습니다. 

 

-- extracting the month, day, hour  from an interval. 
-- DOW, DOY, ISODOW, ISOYEAR, WEEK are not supported for INTERVAL
	
SELECT 
	intv AS INTERVAL
	, EXTRACT(YEAR FROM intv)::int AS intv_year
	, EXTRACT(QUARTER FROM intv)::int AS intv_quarter
	, EXTRACT(MONTH FROM intv)::int AS intv_month
	, EXTRACT(DAY FROM intv)::int AS intv_day 
	, EXTRACT (HOUR FROM intv)::int AS intv_hour 
	, EXTRACT(MINUTES FROM intv)::int AS intv_min
	, extract(SECONDS FROM intv)::int AS intv_sec
FROM (
	SELECT INTERVAL '2 years 3 months 5 days 2 hours 10 minutes 5 seconds' 
    	AS intv
	) ts;


--interval                      |intv_year|intv_quarter|intv_month|intv_day|intv_hour|intv_min|intv_sec|
--------------------------------+---------+------------+----------+--------+---------+--------+--------+
--2 years 3 mons 5 days 02:10:05|        2|           2|         3|       5|        2|      10|       5|

 

[ Reference ]

* PostgreSQL EXTRACT() function
  : https://www.postgresqltutorial.com/postgresql-extract/

 

 

다음번 포스팅에서는 PostgreSQL TO_CHAR() 함수를 사용해서 TIMESTAMP 데이터 유형을 포맷을 가진 문자열로 변환 (converting TIMESTAMP data type to a formatted string) 하는 방법을 소개하겠습니다. (https://rfriend.tistory.com/709)

 

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

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

 

728x90
반응형
Posted by Rfriend
,

이동평균(Moving Average) 는 시계열 데이터를 분석할 때 이상치(Outlier), 특이값, 잡음(Noise) 의 영향을 줄이거나 제거하는 Smoothing 의 목적이나, 또는 미래 예측에 자주 사용됩니다.  개념이 이해하기 쉽고 직관적이기 때문에 실무에서 많이 사용됩니다. 주식 투자를 하는 분이라면 아마도 이동평균에 대해서 익숙할 것입니다. 

 

이동평균에는 가중치를 어떻게 부여하느냐에 따라서 단순이동평균(Simple Moving Average), 가중이동평균(Weighted Moving Average), 지수이동평균(Exponential Moving Average) 등이 있습니다. 

 

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 Window Function 을 사용하여 가중치를 사용하지 않는 (혹은, 모든 값에 동일한 가중치 1을 부여한다고 볼 수도 있는) 

 

(1) 단순이동평균 계산하기 (Calculating a Simple Moving Average) 

(2) 처음 이동평균 날짜 모자라는 부분은 NULL 처리하고 단순이동평균 계산하기

(3) 누적 단순이동평균 계산하기 (Calculating a Cumulative Simple Moving Average)

 

하는 방법을 소개하겠습니다. 

 

 

PostgreSQL, Greenplum, Simple Moving Average using Window Function

 

 

먼저, 세일즈 날짜와 판매금액의 두 개 칼럼으로 구성된, 예제로 사용할 간단한 시계열 데이터(Time Series Data) 테이블을 만들어보겠습니다.  

 

-- creating a sample table
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
	sale_dt date
	, sale_amt int
) DISTRIBUTED RANDOMLY;

INSERT INTO sales VALUES 
('2021-06-01', 230)
, ('2021-06-02', 235)
, ('2021-06-03', 231)
, ('2021-06-04', 244)
, ('2021-06-05', 202)
, ('2021-06-06', 260)
, ('2021-06-07', 240)
, ('2021-06-08', 235)
, ('2021-06-09', 239)
, ('2021-06-10', 242)
, ('2021-06-11', 244)
, ('2021-06-12', 241)
, ('2021-06-13', 246)
, ('2021-06-14', 247)
, ('2021-06-15', 249)
, ('2021-06-16', 245)
, ('2021-06-17', 242)
, ('2021-06-18', 246)
, ('2021-06-19', 245)
, ('2021-06-20', 190)
, ('2021-06-21', 230)
, ('2021-06-22', 235)
, ('2021-06-23', 231)
, ('2021-06-24', 238)
, ('2021-06-25', 241)
, ('2021-06-26', 245)
, ('2021-06-27', 242)
, ('2021-06-28', 243)
, ('2021-06-29', 240)
, ('2021-06-30', 238);

SELECT * FROM sales ORDER BY sale_dt LIMIT 5;

--sale_dt        sale_amt
--2021-06-01	230
--2021-06-02	235
--2021-06-03	231
--2021-06-04	244
--2021-06-05	202

 

 

(1) 단순이동평균 계산하기 (Calculating a Simple Moving Average) 

 

현재 날짜를 기준으로 2일전~현재날짜 까지 총 3일 기간 동안의 값을 사용하여 단순 이동평균을 구해보겠습니다. 

 

moving average for last 3 days = (Xt + Xt-1 + Xt-2) / 3

 

PostgreSQL 의 9.0 이상의 버전에서는 AVG()와  OVER() 의 Window Function을 사용하여 매우 편리하게 단순이동평균 (Simple Moving Average)을 계산할 수 있습니다. 

 

시계열 데이터는 시간의 순서가 중요하므로 OVER(ORDER BY sale_dt)  에서 먼저 날짜를 기준으로 정렬을 해주어야 합니다.

 

OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 로 2일전~현재날짜 까지 총 3일 간의 Window 를 대상으로 평균을 계산하는 것을 지정해줍니다. 

 

가령, 아래의 '2021-06-03' 일의 3일 단순이동평균값은 아래와 같이 '2021-06-01', '2021-06-02', '2021-06-03' 일의 3일치 세일즈 판매금액의 평균이 되겠습니다. 

 

* 3일 단순이동평균('2021-06-03') = (230 + 235 + 231) / 3 = 232.0

 

ROUND(avg(), 1) 함수를 사용해서 단순이동평균값에 대해 소수점 첫째자리 반올림을 할 수 있습니다. 그리고 필요 시 단순이동평균 계산할 대상을 조회할 때 WHERE 조건절을 추가할 수도 있습니다. 

 

-- Calculating a Moving Average for last 3 days using Window Function

SELECT 
	sale_dt
	, sale_amt
	, ROUND(
		AVG(sale_amt) 
		OVER(
			ORDER BY sale_dt 
			ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1
		) AS avg_sale_amt
FROM sales
ORDER BY sale_dt;

--sale_dt   sale_amt   avg_sale_amt
--2021-06-01	230	230.0
--2021-06-02	235	232.5
--2021-06-03	231	232.0
--2021-06-04	244	236.7
--2021-06-05	202	225.7
--2021-06-06	260	235.3
--2021-06-07	240	234.0
--2021-06-08	235	245.0
--2021-06-09	239	238.0
--2021-06-10	242	238.7
--2021-06-11	244	241.7
--2021-06-12	241	242.3
--2021-06-13	246	243.7
--2021-06-14	247	244.7
--2021-06-15	249	247.3
--2021-06-16	245	247.0
--2021-06-17	242	245.3
--2021-06-18	246	244.3
--2021-06-19	245	244.3
--2021-06-20	190	227.0
--2021-06-21	230	221.7
--2021-06-22	235	218.3
--2021-06-23	231	232.0
--2021-06-24	238	234.7
--2021-06-25	241	236.7
--2021-06-26	245	241.3
--2021-06-27	242	242.7
--2021-06-28	243	243.3
--2021-06-29	240	241.7
--2021-06-30	238	240.3

 

 

날짜를 X 축으로 놓고, Y 축에는 날짜별 (a) 세일즈 금액, (b) 3일 단순이동평균 세일즈 금액 을 시계열 그래프로 나타내서 비교해보면 아래와 같습니다. 예상했던대로 '3일 단순이동평균' 세일즈 금액이 스파이크(spike) 없이 smoothing 되어있음을 확인할 수 있습니다. 

 

아래 코드는 Jupyter Notebook에서 Python 으로 Greenplum DB에 연결(connect)하여, SQL query 를 해온 결과를 Python pandas의 DataFrame으로 만들어서, matplotlib 으로 시계열 그래프를 그려본 것입니다.

(* 참고: Jupyter Notebook에서 PostgreSQL, Greenplum DB connect 하여 데이터 가져오는 방법은 https://rfriend.tistory.com/577, https://rfriend.tistory.com/579 참조)

 

## --- Jupyter Notebook ---

import pandas as pd
import matplotlib.pyplot as plt

## loading ipython, sqlalchemy, spycopg2
%load_ext sql

## Greenplum DB connection
%sql postgresql://dsuser:changeme@localhost:5432/demo
#'Connected: dsuser@demo'


## getting data from Greenplum by DB connection from jupyter notebook
%%sql sam << SELECT 
sale_dt
, sale_amt
, ROUND(
AVG(sale_amt) 
OVER(
ORDER BY sale_dt 
ROWS BETWEEN 2 PRECEDING 
AND CURRENT ROW)
  , 1
  ) AS avg_sale_amt
FROM sales
ORDER BY sale_dt;

# * postgresql://dsuser:***@localhost:5432/demo
#30 rows affected.
#Returning data to local variable sam


## converting to pandas DataFrame
sam_df = sam.DataFrame()


sam_df.head()
#sale_dt	sale_amt	avg_sale_amt
#0	2021-06-01	230	230.0
#1	2021-06-02	235	232.5
#2	2021-06-03	231	232.0
#3	2021-06-04	244	236.7
#4	2021-06-05	202	225.7

## plotting time-series plot
import matplotlib.pyplot as plt

plt.rcParams['figure.figsize'] = [14, 10]
plt.plot(sam_df.sale_dt, sam_df.sale_amt, marker='s', color='r', label='original')
plt.plot(sam_df.sale_dt, sam_df.avg_sale_amt, marker='o', color='b', label='moving average')
plt.title('Simple Moving Average', fontsize=18)
plt.xlabel('Sale Date', fontsize=14)
plt.ylabel('Sale Amount', fontsize=14)
plt.legend(fontsize=12, loc='best')
plt.show()

 

original data vs. simple moving average

 

 

 

 

(2) 처음 이동평균 날짜 모자라는 부분은 NULL 처리하고 단순이동평균 계산하기

 

위의 (1)번에서 '3일 단순이동평균' 값을 계산할 때 시계열 데이터가 시작하는 첫번째와 두번째 날짜에 대해서는 이전 데이터가 존재하지 않기 때문에 '3일치' 데이터가 부족하게 됩니다. (만약 '10일 단순이동평균'을 계산한다고 하면 처음 시작하는 9일치 데이터의 경우 '10일치' 데이터에는 모자라게 되겠지요.) 

 

위의 (1)번에서는 이처럼 '3일치' 데이터가 모자라는 '2021-06-01', '2021-06-02' 일의 경우 '3일치'가 아니라 '1일치', '2일치' 단순이동평균으로 대체 계산해서 값을 채워넣었습니다. 

 

하지만, 필요에 따라서는 '3일치 단순이동평균'이라고 했을 때 이전 데이터가 '3일치'가 안되는 경우에는 단순이동평균을 계산하지 말고 그냥 'NULL' 값으로 처리하고 싶은 경우도 있을 것입니다. 이때 (2-1) CASE WHEH 과 AVG(), OVER() window function을 사용하는 방법, (2-2) LAG(), OVER() window function 을 이용하는 방법의 두 가지를 소개하겠습니다. 

 

 

(2-1) CASE WHEH 과 AVG(), OVER() window function을 사용하여 단순이동평균 계산하고, 이동평균계산 날짜 모자라면 NULL 처리하는 방법

 

SELECT 
	sale_dt
	, sale_amt
	, CASE WHEN 
		row_number() OVER(ORDER BY sale_dt) >= 3 
		THEN 
			ROUND(
				AVG(sale_amt) 
				OVER(
					ORDER BY sale_dt 
					ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
			, 1)
		ELSE NULL END 
		AS avg_sale_amt
FROM sales 
ORDER BY sale_dt;

--sale_dt  sale_amt  avg_sale_amt
--2021-06-01	230	NULL
--2021-06-02	235	NULL
--2021-06-03	231	232.0
--2021-06-04	244	236.7
--2021-06-05	202	225.7
--2021-06-06	260	235.3
--2021-06-07	240	234.0
--2021-06-08	235	245.0
--2021-06-09	239	238.0
--2021-06-10	242	238.7
--2021-06-11	244	241.7
--2021-06-12	241	242.3
--2021-06-13	246	243.7
--2021-06-14	247	244.7
--2021-06-15	249	247.3
--2021-06-16	245	247.0
--2021-06-17	242	245.3
--2021-06-18	246	244.3
--2021-06-19	245	244.3
--2021-06-20	190	227.0
--2021-06-21	230	221.7
--2021-06-22	235	218.3
--2021-06-23	231	232.0
--2021-06-24	238	234.7
--2021-06-25	241	236.7
--2021-06-26	245	241.3
--2021-06-27	242	242.7
--2021-06-28	243	243.3
--2021-06-29	240	241.7
--2021-06-30	238	240.3

 

 

 

(2-2) LAG(), OVER() window function을 사용하여 단순이동평균 계산하고, 이동평균계산 날짜 모자라면 NULL 처리하는 방법

 

아래 LAG() 함수를 사용한 방법은 이렇게도 가능하다는 예시를 보여준 것이구요, 위의 (2-1) 과 비교했을 때 'x일 단순이동평균'에서 'x일'이 숫자가 커질 경우 수작업으로 LAG() 함수를 'x일' 날짜만큼 모두 써줘야 하는 수고를 해줘야 하고, 그 와중에 휴먼 에러가 개입될 여지도 있어서 아무래도 위의 (2-1) 방법이 더 나아보입니다. 

 

-- Calculating a Simple Moving Average using LAG() Window Function

SELECT 
	sale_dt
	, sale_amt
	, ROUND(
			(sale_amt::NUMERIC 
				+ LAG(sale_amt::NUMERIC, 1) OVER(ORDER BY sale_dt) 
				+ LAG(sale_amt::NUMERIC, 2) OVER(ORDER BY sale_dt)
			)/3
			, 1) AS avg_sale_amt
FROM sales 
ORDER BY sale_dt;

--sale_dt  sale_amt  avg_sale_amt
--2021-06-01	230	NULL
--2021-06-02	235	NULL
--2021-06-03	231	232.0
--2021-06-04	244	236.7
--2021-06-05	202	225.7
--2021-06-06	260	235.3
--2021-06-07	240	234.0
--2021-06-08	235	245.0
--2021-06-09	239	238.0
--2021-06-10	242	238.7
--2021-06-11	244	241.7
--2021-06-12	241	242.3
--2021-06-13	246	243.7
--2021-06-14	247	244.7
--2021-06-15	249	247.3
--2021-06-16	245	247.0
--2021-06-17	242	245.3
--2021-06-18	246	244.3
--2021-06-19	245	244.3
--2021-06-20	190	227.0
--2021-06-21	230	221.7
--2021-06-22	235	218.3
--2021-06-23	231	232.0
--2021-06-24	238	234.7
--2021-06-25	241	236.7
--2021-06-26	245	241.3
--2021-06-27	242	242.7
--2021-06-28	243	243.3
--2021-06-29	240	241.7
--2021-06-30	238	240.3

 

 

 

(3) 누적 단순이동평균 계산하기 (Calculating a Cumulative Simpe Moving Average)

 

처음 시작하는 날짜부터 해서 누적으로 단순이동 평균 (Cumulative Moving Average) 을 계산하고 싶을 때는 아래처럼 AVG(sale_amt) OVER(ORDER BY sale_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 처럼 window 범위를 처음부터 현재까지로 설정해주면 됩니다. 

 

아래 예에서 '2021-06-05'일까지의 누적 단순이동평균 값은 아래와 같이 계산되었습니다. 

 

Cumulative simple moving average('2021-06-05') = (230 + 235 + 231 + 244 + 202) / 5 = 228.4

 

-- Calculating a Cumulative Moving Average
SELECT 
	sale_dt
	, sale_amt
	, ROUND(
		AVG(sale_amt) 
		OVER(
			ORDER BY sale_dt 
			ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
			, 1) AS avg_cum_sale_amt
FROM sales 
ORDER BY sale_dt;

--sale_dt  sale_amt  avg_cum_sale_amt
--2021-06-01	230	230.0
--2021-06-02	235	232.5
--2021-06-03	231	232.0
--2021-06-04	244	235.0
--2021-06-05	202	228.4
--2021-06-06	260	233.7
--2021-06-07	240	234.6
--2021-06-08	235	234.6
--2021-06-09	239	235.1
--2021-06-10	242	235.8
--2021-06-11	244	236.5
--2021-06-12	241	236.9
--2021-06-13	246	237.6
--2021-06-14	247	238.3
--2021-06-15	249	239.0
--2021-06-16	245	239.4
--2021-06-17	242	239.5
--2021-06-18	246	239.9
--2021-06-19	245	240.2
--2021-06-20	190	237.7
--2021-06-21	230	237.3
--2021-06-22	235	237.2
--2021-06-23	231	236.9
--2021-06-24	238	237.0
--2021-06-25	241	237.1
--2021-06-26	245	237.4
--2021-06-27	242	237.6
--2021-06-28	243	237.8
--2021-06-29	240	237.9
--2021-06-30	238	237.9

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

지난번 포스팅에서는 PostgreSQL, Greenplum에서 두 개의 SELECT 문 결과에 대한 합집합(UNION, UNION ALL), 교집합(INTERSECT), 차집합(EXCEPT) 에 대해서 알아보았습니다. (참고 ==> https://rfriend.tistory.com/659 )

 

이번 포스팅에서는 Sub-Query의 결과를 WHERE 문에서 비교 조건으로 하여 사용할 수 있는 한정 술어 연산자로서 ANY, SOME, ALL, EXISTS 연산자(operator)에 대해서 알아보겠습니다. 

 

 

1. ANY, SOME 연산자

2. ALL 연산자

3. EXISTS, NOT EXISTS 연산자

 

 

 

[ Sub-Query 결과를 비교 조건으로 사용하는 한정 술어 ANY/ SOME vs. ALL 비교 ]

PostgreSQL, Greenplum, 한정술어, any, some, all

 

 

먼저, 예제로 사용할 간단한 테이블 두 개를 만들어보겠습니다. 'cust_master' 테이블은 id, age, gender 의 세 개 칼럼으로 구성되어 있고, cust_amt  테이블은 id, amt 의 두 개 칼럼으로 구성되어 있으며, 두 테이블은 id 를 공통으로 가지고 있어 서로 연결이 가능합니다. (** 이번 포스팅에서는 JOIN 은 사용하지 않고, 대신 한정 술어를 사용해서 JOIN 결과와 유사한 결과를 얻어보겠습니다.)

 

-----------------------------------------------------------------------------------
-- 한정술어: ANY/ SOME, ALL, EXISTS, NOT EXISTS operators
-----------------------------------------------------------------------------------

-- creating sample tables 
DROP TABLE IF EXISTS cust_master;
CREATE TABLE cust_master (
	id int
	, age int 
	, gender text
) DISTRIBUTED RANDOMLY;

INSERT INTO cust_master VALUES 
(1, 45, 'M')
, (2, 34, 'F')
, (3, 30, 'F')
, (4, 28, 'M')
, (5, 59, 'M')
;

DROP TABLE IF EXISTS cust_amt;
CREATE TABLE cust_amt (
	id int
	, amt int
) DISTRIBUTED RANDOMLY;

INSERT INTO cust_amt VALUES 
(1, 500)
, (2, 200)
, (3, 750)
, (8, 900)
, (9, 350)
;

 

 

 

(1) ANY, SOME 연산자

 

ANY 한정술어 연산자는 Sub-Query 의 결과 값들 중에서 어떤 값이라도 =, <>, !=, <, <=, >, >= 등의 비교 연산자의 조건을 만족하면 TRUE 를 반환하며, 그렇지 않은 경우 FALSE 를 반환합니다.  SOME 한정 술어 연산자는 ANY 연산자와 동일한 기능을 수행합니다. 

Sub-Query 는 반드시 1개의 칼럼만 반환해야 합니다.  

 

아래의 예에서는 cust_amt 테이블에서 amt > 300 인 조건을 만족하는 id 와 동일한('=') id 를 가진 값을 cust_master 테이블에서 SELECT 해본 것입니다. (JOIN 문을 사용해도 됩니다만, ANY 연산자를 사용해서 아래처럼도 가능합니다. PostgreSQL이 내부적으로 query optimization을 해서 JOIN 문을 쓰던 ANY/ SOME 연산자를 쓰던 성능은 비슷합니다.)

 

WHERE 조건문에 IN 연산자를 사용할 경우에는 ANY/SOME 연산자에서 같이 사용했던 비교 연산자 ('=') 가 없는 차이점이 있습니다. 

 

WHERE 조건절에서 ANY, SOME 연산자에 비교연산자(=, <>, !=, <, <=, >, >=) 가 같이 사용되었을 경우의 의미는 포스팅 초반의 표를 참고하세요. 

 

--------------------------------
-- ANY, SOME operator
--------------------------------

-- ANY operator compares a value to a set of values returned by a subquery.
--The ANY operator must be preceded by one of the following comparison operator =, <=, >, <, > and <>
--The ANY operator returns true if any value of the subquery meets the condition, otherwise, it returns false.

SELECT id
FROM cust_amt
WHERE amt > 300
ORDER BY id;

--id
--1
--3
--8
--9


-- ANY OPERATOR

SELECT * 
FROM cust_master 
WHERE id = ANY (
	SELECT id
	FROM cust_amt
	WHERE amt > 300
);

--id  age  gender
--1	45	M
--3	30	F


-- SOME OPERATOR

SELECT * 
FROM cust_master 
WHERE id = SOME (
	SELECT id
	FROM cust_amt
	WHERE amt > 300
);


-- IN

SELECT * 
FROM cust_master 
WHERE id IN (
	SELECT id
	FROM cust_amt
	WHERE amt > 300
);

 

 

 

(2) ALL 연산자

 

ALL 한정술어 연산자는 Sub-Query의 결과의 '모든 값과 비교' 하여 '모든 값이 조건을 만족하면 TRUE, 그렇지 않으면 FALSE'를 반환합니다. 

 

WHERE 조건절에서 ALL 연산자에 비교연산자(=, <>, !=, <, <=, >, >=) 가 같이 사용되었을 경우의 의미는 포스팅 초반의 표를 참고하세요.  가령  아래의 예에서 "WHERE age > ALL (sub-query)" 는 "WHERE age > sub-query의 MAX" 값과 같은 의미입니다.  아래의 예에서는 Sub-Query의 avg_age 가 32, 44 이므로 이중에서 MAX 값인 44보다 age가 큰 값을 cust_master 테이블에서 조회를 하겠군요. 

 

---------------------------
-- the ALL operator
---------------------------

-- the PostgreSQL ALL operator compares a value with a list of values returned by a subquery.

SELECT gender, avg(age) AS avg_age 
FROM cust_master 
GROUP BY gender;

--gender  avg_age
--F	32.0000000000000000
--M	44.0000000000000000


SELECT * 
FROM cust_master 
WHERE age > ALL (
	SELECT avg(age) AS avg_age 
	FROM cust_master 
	GROUP BY gender
);

--id  age  gender
--1	45	M
--5	59	M

 

 

 

(3) EXISTS, NOT EXISTS 연산자

 

EXISTS 연산자는 Sub-Query의 결과에서 값이 존재하는지를 평가하는 블리언 연산자입니다. 만약 Sub-Query의 결과에 단 1개의 행이라도 값이 존재하다면 EXISTS 연산자의 결과는 TRUE 가 되며, Sub-Query의 결과가 한 개의 행도 존재하지 않는다면 FALSE 가 됩니다. 

 

아래의 예에서는 cust_master 테이블과 cust_amt 의 두 개 테이블을 같이 사용해서, cust_amt 테이블의 amt > 400 인 조건을 만족하고 cust_master 와 cust_amt 테이블에서 id 가 서로 같은 값이 존재(EXISTS) 하는 cust_master 의 모든 칼럼 값을 가져온 것입니다. (JOIN 문을 사용하지 않고도 EXISTS 문을 사용해서 아래처럼 쓸 수도 있답니다. 성능은 비슷.)

 

NOT EXISTS 연산자는 EXISTS 연산자를 사용했을 때와 정반대의 값을 반환합니다.(TRUE, FALSE 가 서로 정반대임). 

 

---------------------------
-- EXISTS operator
---------------------------
-- The EXISTS operator is a boolean operator that tests for existence of rows in a subquery.
-- If the subquery returns at least one row, the result of EXISTS is TRUE. 
-- In case the subquery returns no row, the result is of EXISTS is FALSE.

SELECT *
FROM cust_master AS m 
WHERE EXISTS (
	SELECT 1
	FROM cust_amt AS a 
	WHERE m.id = a.id 
		AND a.amt > 400
	) 
ORDER BY id;

--id  age  gender
--1	45	M
--3	30	F


----------------------------------
-- NOT EXISTS operator
----------------------------------

-- in case the subquery returns no row, the result is of NOT EXISTS is TRUE
SELECT *
FROM cust_master AS m 
WHERE NOT EXISTS (
	SELECT 1
	FROM cust_amt AS a 
	WHERE m.id = a.id 
		AND a.amt > 400
	) 
ORDER BY id;

--id  age  gender
--2	34	F
--4	28	M
--5	59	M

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

지난번 포스팅에서는 여러개 테이블에서 SELECT 문으로 가져온 결과들의 합집합을 구할 때 UNION 은 중복 확인 및 처리를 하고 UNION ALL 은 중복확인 없이 여러 테이블의 모든 값을 합친다는 차이점을 소개하였습니다. (참고 => https://rfriend.tistory.com/658 )

 

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 SELECT 문의 결과끼리 합치고 빼는 집합 연산자로서 

 

(1) 합집합 UNION

(2) 교집합 INTERSECT 

(3) 차집합 EXCEPT 

(4) 필요조건: 칼럼의 개수가 같아야 하고, 모든 칼럼의 데이터 유형이 동일해야 함. 

 

에 대해서 알아보겠습니다. 

 

벤다이어 그램으로 PostgreSQL, Greenplum DB에서 SELECT 문 결과에 대한 합집합 UNION, 교집합 INTERSECT, 차집합 EXCEPT 했을 때의 결과를 도식화하면 아래와 같습니다. 

 

PostgreSQL, Greenplum DB, UNION, INTERSECT, EXCEPT

 

예제로 사용할 간단한 테이블 두 개를 만들어보겠습니다.  'x1', 'x2' 의 두 개 칼럼이 있고, 두 개 모두 동일하게 'integer' 데이터 유형이어서 테이블 집합연산자인 합집합, 교집합, 차집합의 예제로 사용할 수 있습니다. 

 

-- creating sample tables 

-- Sample Table 1
DROP TABLE IF EXISTS sample_1;
CREATE TABLE sample_1 (x1 int, x2 int) 
DISTRIBUTED randomly;

INSERT INTO sample_1 VALUES (1, 11), (2, 12), (3, 13), (4, 14), (5, 15);
SELECT * FROM sample_1 ORDER BY x1;
--x1    x2
--1	11
--2	12
--3	13
--4	14
--5	15


-- Sample Table 2
DROP TABLE IF EXISTS sample_2;
CREATE TABLE sample_2 (x1 int, x2 int) 
DISTRIBUTED randomly;

INSERT INTO sample_2 VALUES (4, 14), (5, 15), (6, 16), (7, 17), (8, 18);
SELECT * FROM sample_2 ORDER BY x1;

--x1    x2
--4	14
--5	15
--6	16
--7	17
--8	18

 

 

아래의SELECT 문 결과에 대한 UNION, INTERSECT, EXCEPT query 구문은 별도의 추가 설명이 필요 없을 정도로 쉬운 내용이므로 예제 집합연산자의 결과만 제시하는 것으로 설명을 갈음하겠습니다. 

 

 

(1) 합집합 UNION

-- UNION
SELECT * FROM sample_1 
UNION 
SELECT * FROM sample_2 
ORDER BY x1;

--x1    x2
--1	11
--2	12
--3	13
--4	14
--5	15
--6	16
--7	17
--8	18

 

 

 

(2) 교집합 INTERSECT

-- INTERSECT
SELECT * FROM sample_1 
INTERSECT 
SELECT * FROM sample_2
ORDER BY x1;

--x1    x2
--4	14
--5	15

 

 

 

(3) 차집합  EXCEPT

 

두 테이블의 차집합 EXCEPT 는 먼저 SELECT 한 결과에서 나중에 SELECT 한 결과 중 중복되는 부분을 제외한 후의 나머지 결과를 반환합니다. 

참고로, Oracle, MySQL DB에서는 SELECT 문 결과에 대한 차집합은 MINUS 함수를 사용해서 구할 수 있습니다. 

 

-- EXCEPT
SELECT * FROM sample_1 
EXCEPT 
SELECT * FROM sample_2 
ORDER BY x1;

--x1    x2
--1	11
--2	12
--3	13

 

 

 

(4) 필요조건: 칼럼의 개수가 같아야 하고, 모든 칼럼의 데이터 유형이 동일해야 함. 

 

UNION, UNION ALL, INTERSECT, EXCEPT 의 집합연산자를 사용하려면 SELECT 문으로 불러온 두 테이블의 결과에서 칼럼의 개수가 서로 같아야 하고 또 모든 칼럼의 데이터 유형(Data Type)이 서로 동일해야만 합니다. 만약 칼럼의 데이터 유형이 서로 다르다면 아래와 같은 에러가 발생합니다. (아래 예에서는 'x2' 칼럼이 하나는 'integer', 또 하나는 'text' 로서 서로 다르기때문에 에러가 발생한 경우임)/ 

 

SQL Error [42804]: ERROR: UNION types integer and text cannot be matched

 

-- The data types of all corresponding columns must be compatible.

-- Sample Table 3
DROP TABLE IF EXISTS sample_3;
CREATE TABLE sample_3 (x1 int, x2 text) 
DISTRIBUTED randomly;

INSERT INTO sample_3 VALUES (10, 'a'), (20, 'b'), (30, 'c'), (40, 'd'), (50, 'f');
SELECT * FROM sample_3 ORDER BY x1;

--x1		y
--10	a
--20	b
--30	c
--40	d
--50	f

-- ERROR
SELECT * FROM sample_1 
INTERSECT 
SELECT * FROM sample_3;

--SQL Error [42804]: ERROR: UNION types integer and text cannot be matched

 

 

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

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

 

 

728x90
반응형
Posted by Rfriend
,

지난번 포스팅에서는 PostgreSQL, Greenplum DB에서 JOIN 문을 사용하여 여러개의 테이블을 Key 값을 기준으로 왼쪽+오른쪽으로 연결하는 다양한 방법(INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN)을 소개하였습니다. (참고 ==> https://rfriend.tistory.com/657)

 

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 UNION, UNION ALL 함수를 사용해서 여러개의 테이블을 위+아래로 합치는 방법을 소개하겠습니다.  이전의 JOIN 이 Key값 기준 연결/매칭의 개념이었다면 이번 포스팅의 UNION, UNION ALL은 합집합(union of sets) 의 개념이라고 볼 수 있습니다. 

 

(1) UNION : 중복값 제거 후 테이블을 위+아래로 합치기

(2) UNION ALL : 중복값 제거하지 않은 채 테이블을 위+아래로 합치기

(3) 전제조건: 합치려는 테이블 칼럼의 데이터 유형(data type)이 서로 같아야 함. 

 

 

UNION 은 두 테이블의 중복값을 제거한 후에 두 테이블을 위+아래로 합친 결과를 반환하는 반면에, UNION ALL 은 중복값 여부를 확인하지 않고 두 테이블의 모든 값을 위+아래로 합친 결과를 반환합니다. 

 

POSTGRESQL, GREENPLUM UNION, UNION ALL

 

 

UNION 의 경우 두 테이블의 값을 스캔 해서 두 테이블 간의 중복값 여부를 확인하는 중간 단계가 존재하기 때문에 두 테이블의 각 크기가 매우 큰 경우  UNION ALL 대비 상대적으로 연산 시간이 오래 걸립니다. 만약 위+아래로 합치려는 두 테이블의 값 간에 중복값이 없다거나 혹은 중복값 여부를 확인할 필요 없이 모두 합치기만 필요한 요건의 경우에는 UNION ALL 을 사용하는 것이 속도 면에서 유리합니다. 

 

 

간단한 예제 테이블들을 만들어서 예를 들어보겠습니다. 

 

-------------------------------------
-- UNION vs. UNION ALL
-------------------------------------

-- Sample Table 1
DROP TABLE IF EXISTS sample_1;
CREATE TABLE sample_1 (x1 int, x2 int) 
DISTRIBUTED randomly;

INSERT INTO sample_1 VALUES (1, 11), (2, 12), (3, 13), (4, 14), (5, 15);
SELECT * FROM sample_1 ORDER BY x1;
--x1 x2
--1	11
--2	12
--3	13
--4	14
--5	15


-- Sample Table 2
DROP TABLE IF EXISTS sample_2;
CREATE TABLE sample_2 (x1 int, x2 int) 
DISTRIBUTED randomly;

INSERT INTO sample_2 VALUES (4, 14), (5, 15), (6, 16), (7, 17), (8, 18);
SELECT * FROM sample_2 ORDER BY x1;

--x1 x2
--4	14
--5	15
--6	16
--7	17
--8	18

 

 

 

(1) UNION : 중복값 제거 후 테이블을 위+아래로 합치기

 

SELECT column1, column2, ... FROM table1 

UNION

SELECT column1, column2, ... FROM table2

와 같은 형식의 구문으로 두 테이블에서 중복값을 제거한 후에 위+아래로 합칠 수 있습니다. 

 

--------------------
-- (1) UNION
--------------------
SELECT * FROM sample_1 
UNION 
SELECT * FROM sample_2
ORDER BY x1;

--x1 x2
--1	11
--2	12
--3	13
--4	14
--5	15
--6	16
--7	17
--8	18

 

 

 

(2) UNION ALL : 중복값 제거하지 않은 채 테이블을 위+아래로 합치기

 

SELECT column1, column2, ... FROM table1 

UNION ALL

SELECT column1, column2, ... FROM table2

와 같은 형식의 구문으로 두 테이블에서 중복값을 제거하지 않은 상태에서 (중복값 체크 없음) 위+아래로 합칠 수 있습니다. 

 

-------------------------
-- (2) UNION ALL 
-------------------------
SELECT * FROM sample_1 
UNION  ALL 
SELECT * FROM sample_2
ORDER BY x1;

--x1 x2
--1	11
--2	12
--3	13
--4	14
--4	14 -- 중복
--5	15
--5	15 -- 중복
--6	16
--7	17
--8	18

 

 

 

(3) 전제조건: 합치려는 테이블 칼럼의 데이터 유형(data type)이 서로 같아야 함. 

 

만약 UNION, UNION ALL 로 합치려는 두 테이블의 칼럼의 데이터 유형(data type)이 서로 같지 않다면, (아래의 예에서는 sample_1 테이블의 x2 칼럼은 integer, sample_3 테이블의 x2 칼럼은 text로서 서로 다름), "ERROR: UNION types integer and text cannot be matched" 라는 에러 메시지가 발생합니다. 

 

-- (3) The data types of all corresponding columns must be compatible.

-- Sample Table 3
DROP TABLE IF EXISTS sample_3;
CREATE TABLE sample_3 (x1 int, x2 text) 
DISTRIBUTED randomly;

INSERT INTO sample_3 VALUES (10, 'a'), (20, 'b'), (30, 'c'), (40, 'd'), (50, 'f');
SELECT * FROM sample_3 ORDER BY x1;

--x1		y
--10	a
--20	b
--30	c
--40	d
--50	f

-- ERROR
SELECT * FROM sample_1 
UNION
SELECT * FROM sample_3;

--SQL Error [42804]: ERROR: UNION types integer and text cannot be matched
--  Position: 38

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

이번 포스팅에서는 PostgreSQL, Greenplum Database에서 여러개의 테이블을 Key 값을 기준으로 JOIN 구문을 사용하여 연결하는 다양한 방법을 소개하겠습니다. 그리고 두 테이블 내 관측치 간의 모든 가능한 조합을 반환해주는 CROSS JOIN 에 대해서도 마지막에 소개하겠습니다. (DB 종류에 상관없이 join SQL query는 거의 비슷합니다.)

 

(1) INNER JOIN

(2) LEFT JOIN

(3) RIGHT JOIN

(4) FULL JOIN

(5) 3개 이상 복수개의 테이블을 JOIN 으로 연결하기

(6) CROSS JOIN

 

 

joining two tables in postgresql

 

 

먼저 예제로 사용한 간단한 2개의 테이블을 만들어보겠습니다. 두 테이블을 연결할 수 있는 공통의 Key값으로서 'id'라는 이름의 칼럼을 두 테이블이 모두 가지고 있습니다. 

 

'tbl1' 과 'tbl2'는 Key 'id'를 기준으로 id = [2, 3, 4] 가 서로 동일하게 존재하며, 'tbl1'의 id = [1]은 'tbl1'에만 존재하고, 'tbl2'의 id = [5] 는 'tbl2'에만 존재합니다. 각 JOIN 방법 별로 결과가 어떻게 달라지는지 유심히 살펴보시기 바랍니다. 

 

-- Creating two sample tables

-- sample table 1
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1 (
	id int
	, x text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

SELECT * FROM tbl1 ORDER BY id;
--id  x
--1	a
--2	b
--3	c
--4   d


-- sample table 2
DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl2 (
	id int
	, y text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl2 VALUES (2, 'e'), (3, 'f'), (4, 'g'), (5, 'h');

SELECT * FROM tbl2 ORDER BY id;
--id  y
--2	e
--3	f
--4	g
--5	h

 

 

(1) INNER JOIN 

INNER JOIN 은 두 테이블의 Key 값을 기준으로 교집합에 해당하는 값들만 반환합니다. 두 테이블에서 Key 값이 겹치지 않는 값들은 제거되었습니다. 

 

--------------
-- INNER JOIN
--------------

SELECT a.id, a.x, b.y
FROM tbl1 AS a 
INNER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--2	b	e
--3	c	f
--4	d	g

 

 

 

(2) LEFT OUTER JOIN

LEFT OUTER JOIN 은 왼쪽 테이블을 기준으로 Key값이 서로 같은 오른쪽 테이블의 값들을 왼쪽 테이블에 연결해줍니다. 아래의 예에서는 왼쪽의 'tbl1'의 값들은 100% 모두 있고, LEFT OUTER JOIN 으로 연결해준 오른쪽 'tbl2' 테이블의 경우 id = [5] 의 값이 제거된 채 id = [2, 3, 4] 에 해당하는 값들만 'tbl1'과 연결이 되었습니다. 그리고 왼쪽 'tbl1'에는 있지만 오른쪽 'tbl2'에는 없는 id = [1] 에 해당하는 값의 경우 y = [NULL] 값을 반환하였습니다. 

 

-------------------
-- LEFT OUTER JOIN
-------------------

SELECT a.id, x, y
FROM tbl1 AS a 
LEFT OUTER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--1	a	[NULL]
--2	b	e
--3	c	f
--4	d	g

 

 

 

(3) RIGHT OUTER JOIN

RIGHT OUTER JOIN 은 LEFT OUTER JOIN 과 정반대라고 생각하면 이해하기 쉽습니다. 이번에는 오른쪽 테이블을 기준으로 Key 값이 같은 왼쪽 테이블의 값을 오른쪽 테이블에 연결해줍니다. 

아래 RIGHT OUTER JOIN 예에서는 오른쪽 테이블은 'tbl2'는 100% 모두 있고, 왼쪽 테이블 'tbl1'의 경우 'tbl2'와 Key 값이 동일한 id = [2, 3, 4] 에 해당하는 값들만 'tbl2'에 연결이 되었습니다. 'tbl2'에만 존재하고 'tbl1'에는 없는 id = [5] 의 경우 'tbl1'의 'x' 칼럼 값은 [NULL] 값이 됩니다. 

 

--------------------
-- RIGHT OUTER JOIN
--------------------

SELECT a.id, x, y
FROM tbl1 AS a 
RIGHT OUTER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--2	b	e
--3	c	f
--4	d	g
--5 [NULL]	h

 

 

 

(4) FULL JOIN

FULL JOIN은 양쪽 테이블 모두를 기준으로 Key 값이 같은 값들을 연결시켜 줍니다. 이때 한쪽 테이블에만 Key 값이 존재할 경우 다른쪽 테이블의 칼럼 값에는 [NULL] 값을 반환합니다. 

제일 위에 있는 도식화 그림을 참고하시면 위의 INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN에 대해서 좀더 이해하기 쉬울 거예요. 

 

---------------
-- FULL JOIN
---------------

SELECT a.id, x, y
FROM tbl1 AS a 
FULL JOIN tbl2 AS b
ON a.id = b.id;
--id x y
--1	a	[NULL]
--2	b	e
--3	c	f
--4	d	g
--5 [NULL] h

 

 

 

(5) 3개 이상의 복수개의 테이블을 JOIN 으로 연결하기

 

위의 (1)~(4) 는 2개의 테이블을 Key 값을 기준으로 JOIN 문으로 연결한 것이었습니다. 만약 3개 이상의 복수개의 테이블을 JOIN 으로 연결하고자 한다면 아래의 예처럼 JOIN 문과 연결 Key 값을 ON 으로 이어서 써주면 됩니다.

아래의 예는 'tbl1' 테이블을 기준으로 'tbl2', 'tbl3'를 'id' Key를 기준으로 LEFT OUTER JOIN 한 것입니다. 

 

--------------------------------------------------------
-- LEFT OUTER JOIN with Multiple Tables
--------------------------------------------------------

-- creating the 3rd table
DROP TABLE IF EXISTS tbl3;
CREATE TABLE tbl3 (
	id int
	, z text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl2 VALUES (2, 'i'), (4, 'j'), (6, 'k'), (8, 'l');
SELECT * FROM tbl3 ORDER BY id;
--id  z
--2	i
--4	j
--6	k
--7	l


-- LEFT OUTER JOIN with 3 tables
SELECT a.id, x, y
FROM tbl1 AS a 
LEFT OUTER JOIN tbl2 AS b 
	ON a.id = b.id
LEFT OUTER JOIN tbl3 AS c	
	ON a.id = c.id
ORDER BY a.id;
--id x y z
--1	a	[NULL] [NULL]
--2	b	e i
--3	c	f [NULL]
--4	d	g j

 

 

 

(6) CROSS JOIN

위의 (1)~(5)까지의 JOIN은 두 테이블에 동일하게 존재하는 Key값을 기준으로 두 테이블을 연결하여 주었다면, 이제 CROSS JOIN 은 두 테이블의 모든 값들 간의 조합을 반환하며, 이때 Key 값은 필요없습니다. 가령 왼쪽 테이블에 m 개의 행이 있고, 오른쪽 테이블에 n 개의 행이 있다면 두 테이블의 CROSS JOIN은 m * n 개의 조합(combination)을 반환합니다. 

 

실수로 행의 개수가 엄청나게 많은 두 테이블을 CROSS JOIN 하게 될 경우 시간도 오래 걸리고 자칫 memory full 나서 DB가 다운되는 경우도 있습니다.  따라서 CROSS JOIN 을 할 때는 지금 하려는 작업이 CROSS JOIN 요건이 맞는 것인지 꼭 한번 더 확인이 필요하며, 소요 시간이나 메모리가 여력이 되는지에 대해서도 먼저 가늠해볼 필요가 있습니다. 

 

----------------
-- CROSS JOIN
----------------

SELECT a.id AS id_a, a.x, b.id AS id_b, b.y
FROM tbl1 AS a 
CROSS JOIN tbl2 AS b
ORDER BY a.id, b.id;
--id_a x id_b y
--1	a	2	e
--1	a	3	f
--1	a	4	g
--1	a	5	h
--2	b	2	e
--2	b	3	f
--2	b	4	g
--2	b	5	h
--3	c	2	e
--3	c	3	f
--3	c	4	g
--3	c	5	h
--4	d	2	e
--4	d	3	f
--4	d	4	g
--4	d	5	h

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

지난번 포스팅 (rfriend.tistory.com/631) 에서는 Greenplum DB에서 PostGIS 를 사용하여 좌표 인식, 변환, 계산을 하기 위해서 필요한 좌표계 참조 테이블인 PostGIS의 spatial_ref_sys 테이블을 모든 segment nodes 에 복제하는 방법을 소개하였습니다. 

 

이번 포스팅에서는 PostGIS의 원래의 spatial_ref_sys 테이블에는 없는 좌표계 (Coordinate Reference System) 를 사용해서 두 개의 좌표계간 변환을 하는 방법을 소개하겠습니다. 

 

(방법 1) spatial_ref_sys 테이블에 새로운 좌표계를 등록하고 (즉, insert into spatial_ref_sys 테이블),

            --> PostGIS의 ST_Transform() 함수를 사용해서 좌표계 간 변환하기

(방법 2) PostGIS_Transform_Geometry() 함수를 사용해서 직접 새로운 좌표계로 변환하기

 

 

transforming Coordinate Reference System in PostGIS, PostgreSQL, Greenplum

 

예로서 KATECH 좌표계와  WGS84 좌표계 간 변환하는 방법을 소개하겠습니다.

참고로 KATECH 좌표계는 국내의 포털 사이트의 지도 API 서비스나 국내 네비게이션에서 사용하고 있는 비공식 좌표계로서, 원점으로 위도 38도, 경도 128도의 단일원점을 사용하기 때문에 TM128 좌표계라고도 합니다. KATECH 좌표계는 국내 CNS(자동차 항법장치)를 위해서 만들어진 것으로 3개(서부,중부,동부)의 투영원점을 위도38도, 경도127도 30분의 단일원점으로 통합한 것입니다. (* 출처: hmjkor.tistory.com/377).

 

 

(방법 1)  (step 1) spatial_ref_sys 테이블에 새로운 좌표계를 등록하고 (즉, insert into spatial_ref_sys 테이블),

            --> (step 2) PostGIS의 ST_Transform() 함수를 사용해서 좌표계 간 변환하기

 

(step 1) spatial_ref_sys 테이블에  미등록되어 있는 KATECH 좌표계를 등록하기 

 

* 사전에 Greenplum DB 의 모든 segment nodes에 spatial_ref_sys 테이블이 복제(replication)되어 있어야 합니다.

  (참고: rfriend.tistory.com/631)  

* 아래처럼 insert into 쿼리 구문으로 좌표계 정보를 spatial_ref_sys 테이블에 추가해줍니다. 이때, SRID 번호는 사용자가 알아서 지정해주면 됩니다. 

* 아래 KATECH 좌표계의 정보는 다시 한번 각 사용처에서 사용하고 있는 것과 동일한 것인지 다시 한번 확인해보시기 바랍니다.(critical 한 정보이므로 반드시 아래 숫자 맞는지 다시 한번 double check 해보세요!) 

 

-- inserting KATECH CRS into spatial_ref_sys table
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) 
values (
    10000, 
    'sr-org', 
    8030, 
    '+proj=tmerc +lat_0=38 +lon_0=128 +k=0.9999 +x_0=400000 +y_0=600000 +ellps=bessel +towgs84=-145.907,505.034,685.756,-1.162,2.347,1.592,6.342 +units=m +no_defs', 
    'PROJCS["Katech",GEOGCS["Bessel 1841",
    DATUM["unknown",SPHEROID["bessel",6377397.155,299.1528128],
    TOWGS84[-145.907,505.034,685.756,-1.162,2.347,1.592,6.342]],PRIMEM["Greenwich",0],
    UNIT["degree",0.0174532925199433]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",38],
    PARAMETER["central_meridian",128],
    PARAMETER["scale_factor",0.9999],
    PARAMETER["false_easting",400000],
    PARAMETER["false_northing",600000],
    UNIT["Meter",1]]'
);   

 

 

(step 2)  PostGIS 의 ST_Transform() 함수를 사용해서  WGS 84 (SRID 4326) 좌표계를 KATECH (SRID 10000, 위에서 지정한 번호로서, SRID 번호는 지정하기 나름임) 좌표계로 변환. 

 

-- (1st method) using ST_Transform() and CRS information from spatial_ref_sys table
SELECT ST_Transform(ST_SetSRID(ST_Point(-123.365556, 48.428611), 4326), 10000) AS katech_geom;

 

 

 

(방법 2) PostGIS_Transform_Geometry() 함수를 사용해서 직접 새로운 좌표계로 변환하기

 

두번째는 spatial_ref_sys 테이블에 새로운 좌표계를 등록할 필요없이, PostGIS_Transform_Geometry() 함수안에 (a) 기존의 변환하려는 대상 좌표계 : proj_from, (b) 앞으로 변환하려고 하는 기준 좌표계 : proj_to 를 직접 넣어주는 방식입니다. 

위의 (방법 1)에서 사용한 PostGIS의 ST_Transform() 함수의 소스 코드를 까서 살펴보면 그 안에 PostGIS_Transform_Geometry() 함수를 사용하고 있습니다. 

 

아래의 예는 table_with_geometry 라는 from 절의 테이블에서 x_axis, y_axis 의 경도, 위도 좌표를 가져와서 25를 더하고 뺀 값을  ST_MakeEnvelope() 함수로 만든 사각형 Polygon 의 좌표를  WGS 84 에서 KATECH 좌표계로 PostGIS_Transform_Geometry() 함수를 써서 변환해본 것입니다. 

 

이렇게 수작업으로 하면 Greenplum DB의 각 segment nodes에 복제되어 있는 spatial_ref_sys 테이블에 (방법 1 - a) 처럼 KATECH 좌표계를 미리 등록 (insert into) 하지 않고 바로 사용할 수 있는 장점이 있습니다. 하지만, 이런 좌표계 변환을 여러 사용처에서, 반복적으로 해야 하는 경우라면 매번 이렇게 복잡하게 좌표계 정보를 직접 입력하는 것은 번거로고 어려운 뿐만 아니라, human error 를 유발할 위험도 다분히 있기 때문에 추천하지는 않습니다. 

 

-- (2nd Method) using PostGIS_Transform_Geometry() function directly

SELECT  
    PostGIS_Transform_Geometry(
        -- geom
        ST_Union(ST_MakeEnvelope(x_axis::integer -25, y_axis::integer - 25, x_axis::integer + 25, y_axis::integer + 25, 6645))
        -- proj_from
        , ‘+proj=tmerc +lat_0=38 +lon_0=128 +k=0.9999 +x_0=400000 +y_0=600000 _ellps=bessel +units=m +no_defs +towgs84=-115.80,474.99,674.11,1.16,-2.31,-1.63,6.43’
        -- proj_to
        , ‘+proj=tmerc +lat_0=38 +lon_0=128 +k=0.9999 +x_0=400000 +y_0=600000 +ellps=bessel +towgs84=-145.907,505.034,685.756,-1.162,2.347,1.592,6.342 +units=m +no_defs’
        -- SRID
        , 5179)
FROM table_with_geometry …;

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

PostgreSQL database에서 오픈소스 PostGIS 를 사용하여 지리공간 데이터 변환, 연산 및 분석을 하는데 있어 첫번째로 챙겨야 하는 것이 있다면 지리공간의 기준이 되는 좌표계(Coordinate Reference System)인  SRID (Spatial Reference IDentifier) 를 spatial_ref_sys 테이블의 값으로 설정해주는 것입니다.  

 

SRID 별로 지리공간 좌표 참조 정보가 들어있는 spatial_ref_sys 테이블은 아래와 같이 srid, auth_name, auth_srid, srtext, proj4text 의 칼럼으로 구성되어 있습니다. 

 

예를 들어서, WGS(World Geodetic System) 84 좌표계는 SRID = 4326 으로 조회를 하면 됩니다. 

 

 

 

SRID 설정은 PostGIS의  ST_SetSRID() 함수를 사용합니다. 

SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611), 4326) AS wgs84long_lat; 
wgs84long_lat
-------------------------
POINT(-123.365556 48.428611)

 

 

PostgreSQL DB 에서 PostGIS 를 사용해서 두 개의 좌표계 간에 좌표 변환을 하려면  ST_Transform() 함수를 사용하면 간단하게 좌표 변환을 할 수 있습니다. 그런데 만약 Greenplum DB 에서 PostGIS 의 좌표변환 함수인 ST_Transform() 함수를 사용한다면 아래와 같은 에러가 발생할 것입니다. (몇 년 전에 POC를 하는데 아래 에러 해결하려고 workaround 만드느라 반나절 고생했던게 생각나네요. ^^;;;)

 

"ERROR: function cannot execute on QE slice because it accesses relation spatial_ref_sys" 

 

이런 에러가 발생하는 이유는 Greenplum database의 경우 PostgreSQL 엔진을 기반으로 하고 있지만,  Shared nothing 아키텍쳐의 MPP (Massively Parallel Processing) database 로서, ST_Transform() 함수 실행 시 각 segment nodes 가 spatial_ref_sys 좌표계 테이블을 참조하지 못하기 때문입니다. Greenplum DB 에서 이 에러를 해결하기 위해서는 spatial_ref_sys 좌표계 정보가 들어있는 테이블을 여러개의 segment nodes에 복제를 해주면 됩니다.  

 

Greenplum 6.x 버전 부터는 'DISTRIBUTED REPLICATED' 를 사용해서 쉽게 테이블을 각 segment nodes 에 복제할 수 있습니다.  (Greenplum 5.x 버전에서는  CROSS JOIN 을 사용해서 각 segment nodes 에 복제해주면 됩니다.)

 

 

[ Greenplum 6.x 버전에서 spatial_ref_sys 테이블을 각 segment nodes 에 복제하여 생성하는 절차 ]

 

(1) 기존의 spatial_ref_sys 테이블을 spatial_ref_sys_old 로 테이블 이름을 바꿔줍니다. 

(2) spatial_ref_sys 라는 이름의 테이블을 'DISTRIBUTED REPLICATED' 모드로 해서 각 segment nodes에 복제해서 생성해줍니다. 이때 칼럼 이름과 속성, 제약조건은 아래의 SQL query 를 그래도 복사해서 사용하시면 됩니다. 

(3) 위의 (1)번에서 이름을 바꿔놓았던 기존의 테이블인  spatial_ref_sys_old 테이블에서 SELECT 문으로 데이터를 조회해와서 새로 각 테이블에 복제 모드로 생성해놓은  spatial_ref_sys 테이블에 데이터를 삽입해줍니다. 

 

-- (1) changing the spatial_ref_sys table's name
ALTER TABLE spatial_ref_sys RENAME TO spatial_ref_sys_old;


-- (2) creating spatial_ref_sys table using DISTRIBUTED REPLICATED
CREATE TABLE spatial_ref_sys(
    srid int4 NOT NULL
    , auth_name VARCHAR(256) NULL
    , auth_srid INT4 NULL
    , srtext VARCHAR(2048) NULL
    , proj4text TEXT NOT NULL
    , CONSTRAINT spatial_ref_sys_pkey_1 PRIMARY KEY (srid)
    , CONSTRAINT spatial_ref_sys_srid_check_1 CHECK (((srid > 0) AND (srid <= 998999)))
)
DISTRIBUTED REPLICATED;


-- (3) inserting spatial_ref_sys_old data inot replicated segments' tables
INSERT INTO spatial_ref_sys SELECT * FROM spatial_ref_sys_old;

 

 

 

이제 spatial_ref_sys 테이블이 모든 segment nodes에 복제가 되었으니 Greenplum DB에서 PostGIS의 ST_Transform() 함수를 사용해서 좌표계 SRID 4326 (WGS 84) 인 데이터를 좌표계 SRID 3785 로 변환해보겠습니다. 

 

-- Mark a point as WGS 84 long lat and then transform to web mercator (Spherical Mercator) --
SELECT ST_Transform(ST_SetSRID(ST_Point(-123.365556, 48.428611), 4326), 3785) AS spere_merc; 

spere_merc
---------------------------------
SRID=3785;POINT(-13732990.8753491 6178458.96425423)

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,
지난번 포스팅에서는 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


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



728x90
반응형
Posted by Rfriend
,