달력

7

« 2014/7 »

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

'2014/07'에 해당되는 글 3

  1. 2014.07.31 Postgresql Pivot
  2. 2014.07.09 function을 이용한 select
  3. 2014.07.01 pl/r 설치
2014. 7. 31. 10:26

Postgresql Pivot Work/PostgreSQL2014. 7. 31. 10:26

집계 테이블을 표현 할 때 흔히 격자형 형태로 표현하기를 원하는 경우들이 있다.

이런 경우 엑셀에서는 pivot 이라는 기능을 이용한다.

데이터베이스에서도 비슷한 상황이 존재할 수 있는데, 이를 위해서 DBMS마다 약간씩 다른 방법을 제공한다. Postgresql 의 경우는 아래은 방법을 제공한다.


SELECT a::date AS cdate,

       b.desc AS TYPE,

       (random() * 10000 + 1)::int AS val

FROM generate_series((now() - '10 days'::interval)::date, now()::date, '1 day'::interval) a,

  (SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b;

위의 쿼리 결과는 아래와 같다.

cdate type val
2014-07-21 OSX 3128
2014-07-21 Windows 8118
2014-07-21 Linux 6699
2014-07-22 OSX 6364
2014-07-22 Windows 4439
2014-07-22 Linux 3727
2014-07-23 OSX 6010
2014-07-23 Windows 5006
2014-07-23 Linux 2249
2014-07-24 OSX 3664
2014-07-24 Windows 2377
2014-07-24 Linux 6907
2014-07-25 OSX 6145
2014-07-25 Windows 728
2014-07-25 Linux 5920
2014-07-26 OSX 6704
2014-07-26 Windows 1331
2014-07-26 Linux 1472
2014-07-27 OSX 5668
2014-07-27 Windows 1408
2014-07-27 Linux 6162
2014-07-28 OSX 6805
2014-07-28 Windows 4762
2014-07-28 Linux 5977
2014-07-29 OSX 8917
2014-07-29 Windows 9468
2014-07-29 Linux 7261
2014-07-30 OSX 1913
2014-07-30 Windows 1495
2014-07-30 Linux 5639
2014-07-31 OSX 5195
2014-07-31 Windows 4623
2014-07-31 Linux 3756

이런 결과의 격자형 형태를 위해 아래처럼 case를 이용한 쿼리를 작성하는 방법이 일반적이다.

select cdate

, sum(case when type = 'OSX' then val else 0 end) OSX

, sum(case when type = 'Windows' then val else 0 end) Windows

, sum(case when type = 'Linux' then val else 0 end) Linux

from (

SELECT a::date AS cdate,

  b.desc AS TYPE,

  (random() * 10000 + 1)::int AS val

FROM generate_series((now() - '10 days'::interval)::date, now()::date, '1 day'::interval) a,

 (SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b

) a

group by cdate

order by 1;

결과는 아래와 같다.

cdate osx windows linux
2014-07-21 7095 287 1101
2014-07-22 9741 3417 3237
2014-07-23 6321 7028 9647
2014-07-24 569 8523 7655
2014-07-25 7371 4659 2482
2014-07-26 8352 9015 559
2014-07-27 604 5552 8965
2014-07-28 79 4691 1138
2014-07-29 3355 9816 2113
2014-07-30 4707 1285 2997
2014-07-31 2029 8379 3283


Postgresql 에서 제공하는 또다른 방법은 crosstab을 이용하는 벙법이다. 쿼리는 아래와 같다.

-- crosstab 을 이용하기 위한 extension 설치

CREATE EXTENSION tablefunc;


-- Query

SELECT *

FROM crosstab(

'SELECT

a cdate,

b.desc AS type,

(random() * 10000 + 1)::int AS val

FROM generate_series((now() - ''10 days''::interval)::date, now()::date, ''1 day''::interval) a,

(SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux'']) AS DESC) b ORDER BY 1,2'

,'SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux''])'

AS ct(cdate date, OSX int, Windows int, Linux int);

결과는 위의 case를 이용한 Query와 동일하다.


출처 : http://www.craigkerstiens.com/2013/06/27/Pivoting-in-Postgres/

참고 : http://www.postgresql.org/docs/9.1/static/tablefunc.html


:
Posted by Elick
2014. 7. 9. 14:34

function을 이용한 select Work/PostgreSQL2014. 7. 9. 14:34

Description & Report

PostgreSql에서는 Stored Procedure 라는 것을 제공하지 않고, Function 만 제공하는 것으로 파악된다.
Return Value를 적당히 결정해 주면 되므로 어찌되었든 종합적인 Query 저장 기능 및 pre-compile 을 할 수 있으므로 이정도면 충분하다.
Oracle에서는 PL/SQL, SQL Server에서는 TSQL, Postgresql에서는 plpgsql 로 부르는 것으로 판단된다.

생성 문법은 Oracle과 같이 CREATE OR REPLACE 를 지원하며 변수선언도 유사하고, SQL Server와 같이 function body에 DDL(Date Define Language)을 사용해도 Compile이 Invalid 되지 않는 것으로 Test 되었다. *(*여기까지는 Oracle의 장점과 SQL Server의 장점을 더한 것으로 보여 매우 만족스럽다)
어느 정도까지 사용할 수 있을지는 다양한 형태로 사용해 보야하겠지만, 지금 수준에서는 사용할만 하다.

Create plpgsql
pgAdmin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE OR REPLACE FUNCTION agt.usp_jttr_jtpr_YEAR_CNT() RETURNS int AS $$
DECLARE
--  v_Return int;
BEGIN
    truncate table agt.tb_jttr_01_YEAR_CNT;
     
    insert into agt.tb_jttr_01_YEAR_CNT
    select case when a.cyear is null then b.cyear else a.cyear end cyear
        , coalesce(a.app_cnt, 0) app_cnt
        , coalesce(b.pub_cnt, 0) pub_cnt
    from (select substr(app_date,1,4) CYEAR, COUNT(*) app_cnt FROM tb_jttr_01 GROUP BY substr(app_date,1,4)) A
        full outer join (select substr(pub_date,1,4) CYEAR, COUNT(*) pub_cnt FROM tb_jttr_01 GROUP BY substr(pub_date,1,4)) B
            on A.cyear = B.cyear;
 
    truncate table agt.tb_jptr_01_YEAR_CNT;
 
    insert into agt.tb_jptr_01_YEAR_CNT
    select case when a.cyear is null then b.cyear else a.cyear end cyear
        , coalesce(a.app_cnt, 0) app_cnt
        , coalesce(b.pub_cnt, 0) pub_cnt
    from (select substr(app_date,1,4) CYEAR, COUNT(*) app_cnt FROM tb_jptr_01 GROUP BY substr(app_date,1,4)) A
        full outer join (select substr(pub_date,1,4) CYEAR, COUNT(*) pub_cnt FROM tb_jptr_01 GROUP BY substr(pub_date,1,4)) B
            on A.cyear = B.cyear;
    RETURN 0;
END;
$$ LANGUAGE plpgsql;
Execute plpgsql
pgAdmin
select agt.usp_jttr_jtpr_YEAR_CNT();

 

Return RecordSet

SQL Server와 같이 Recordset을 return 하게 하는 방법.

pgAdmin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Definition
CREATE OR REPLACE FUNCTION db_xml_kr.usp_test_rs(v_seq_xml bigint)
  RETURNS TABLE (
    seq_xml bigint,
  filepath_converted character varying(256)
  )
LANGUAGE SQL AS $$
    SELECT seq_xml
        , filepath_converted
    FROM db_xml_kr.x_filepath
    WHERE seq_xml = $1
    limit 10;
$$;
-- Execute
SELECT * FROM db_xml_kr.usp_test_rs(13);

위와 같이 간단히 Parameter 만 받아서 처리하는 경우는 비교적 쉽게 작성할 수 있지만, 조건문 변수 선언 및 할당 등 plpgsql에서 제공하는 기능을 모두 사용하려면 조금 다르게 작성해야 한다.

Return 받을 recordset 이 Table의 Column을 다 받는다면 역시 비교적 쉽게 작성할 수 있다. (예:http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html)

만일 생성되어 있는 Table과 Return 받을 Recordset 의 형태가 다르다면 아래처럼 Type 을 생성한 후 받는 방법을 사용한다.

pgAdmin (203.242.170.203 db_patent)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- create type
create type rs_info_table as (ordinal_position integer, column_name character varying, data_type character varying, character_maximum_length  integer);
 
-- create function
CREATE OR REPLACE FUNCTION asp_info_table(IN v_tname character varying)
  RETURNS SETOF rs_info_table AS
$BODY$
DECLARE
    rs rs_info_table%rowtype;
BEGIN
    IF position('.' in $1 ) > 0 THEN
        for rs in
            SELECT A.ordinal_position, A.column_name, A.data_type, A.character_maximum_length
            FROM information_schema.columns A
            WHERE table_schema = substring($1, 1, position('.' in $1) - 1)
                AND table_name = substring($1, position('.' in $1) + 1, character_length($1))
            ORDER BY ordinal_position      
        loop
            return next rs;
        end loop;
    ELSE
        for rs in
            SELECT A.ordinal_position, A.column_name, A.data_type, A.character_maximum_length
            FROM information_schema.columns A
            WHERE table_schema = 'public'
                AND table_name = $1
            ORDER BY ordinal_position
        loop
            return next rs;
        end loop;
    END IF;
    return;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION asp_info_table(IN v_tname character varying) OWNER TO nips;
 
-- Execute
select * from asp_info_table('rdb_jt.t_publish');

위 방법만 가능한 줄 알았다..

정말 다른 방법이 없는 것으로 확인되었는데... 최근 입수한 PostgreSQL Server Programming 이라는 책에서 아래와 같은 아주 간단한 방법을 제공하는 것을 확인했다.

CREATE OR REPLACE FUNCTION db_pas.usp_inven_select_with_proj_id_tech_id_stream(p_proj_id character varying, p_tech_id_stream character varying)
  RETURNS TABLE(id_kipi character varying, dt_sys   character varying, query_info   character varying) AS
$BODY$
DECLARE
    rs db_pas.t_inven%rowtype;
BEGIN
    IF length(p_tech_id_stream) = 0 THEN
        return query
        select distinct b.id_kipi, b.dt_sys, coalesce(b.query_info, '')
        from db_pas.t_inven_tech a inner join db_pas.t_inven b on a.id_kipi = b.id_kipi and a.proj_id = b.proj_id
        where a.proj_id = '{0}';
    ELSE
        return query
        select distinct b.id_kipi, b.dt_sys, coalesce(b.query_info, '')
        from db_pas.t_inven_tech a inner join db_pas.t_inven b on a.id_kipi = b.id_kipi and a.proj_id = b.proj_id
        where a.proj_id = p_proj_id
            and a.tech_id in (select unnest(string_to_array(p_tech_id_stream, ',')));
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

함수를 생성할 때마다 타입을 같이 생성해 주어야 하는 번거로움과 loop을 해야 하는 두려움을 모두 해결할 수 있는 좋은 방법을 찾았다.

postgresql-9.2 에서 test 되었다.

:
Posted by Elick
2014. 7. 1. 10:35

pl/r 설치 Work/PostgreSQL2014. 7. 1. 10:35

R 이라고... SAS나 SPSS 같은... 통계쪽에서 사용하는 패키지가 있다. 

PL/R은 R 통계 컴퓨팅 언어로 PostgreSQL의 함수들과 집계 함수를 작성 할 수있는 PostgreSQL의 언어 확장이다.


CentOS에 설치된 Postgresql-9.2 에서는 기본적으로 제공하지 않지만... yum을 이용하여 pl/r을 설치할 수 있다.


[root@syslog ~]# yum search plr

.

.

.

[root@syslog ~]# yum install plr92.x86_64

.

.

.

[root@syslog ~]# su - postgres

[postgres@syslog ~]$ cd /usr/pgsql-9.2/share/extension/

[postgres@syslog extension]$ ls

.

.

.

plr--8.3.0.15.sql

plr.control

plr.sql

plr--unpackaged--8.3.0.15.sql

.

.

.

extension을 설치할 준비는 끝났다.


설치한다.

[postgres@syslog extension]$ psql -d mgtdb 

psql (8.4.20, server 9.2.8)

WARNING: psql version 8.4, server version 9.2.

         Some psql features might not work.

Type "help" for help.


mgtdb=# 

mgtdb=# create extension plr;

CREATE EXTENSION


설치가 잘 되었는지 간단한 테스트!

mgtdb=# SELECT * FROM plr_environ();

        name        |                                                 value                                                 

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

 HOSTNAME           | syslog.nips.local

 SHELL              | /bin/bash

 TERM               | linux

 HISTSIZE           | 1000

 QTDIR              | /usr/lib64/qt-3.3

 QTINC              | /usr/lib64/qt-3.3/include

 USER               | postgres

 TMOUT              | 900

 MAIL               | /var/spool/mail/postgres

 PATH               | /usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/postgres/bin

 PWD                | /home/postgres

 LANG               | en_US.UTF-8

 HISTCONTROL        | ignoredups

 SHLVL              | 1

 HOME               | /home/postgres

 LOGNAME            | postgres

 QTLIB              | /usr/lib64/qt-3.3/lib

 CVS_RSH            | ssh

 PGDATA             | /var/lib/pgsql/9.2/data

 LESSOPEN           | |/usr/bin/lesspipe.sh %s

 HISTTIMEFORMAT     | %F %T IP: ID:postgres  

 G_BROKEN_FILENAMES | 1

 _                  | /usr/pgsql-9.2/bin/postmaster

 PGLOCALEDIR        | /usr/pgsql-9.2/share/locale

 PGSYSCONFDIR       | /etc/sysconfig/pgsql

 LC_COLLATE         | en_US.UTF-8

 LC_CTYPE           | en_US.UTF-8

 LC_MESSAGES        | en_US.UTF-8

 LC_MONETARY        | C

 LC_NUMERIC         | C

 LC_TIME            | C

(31 rows)


mgtdb=# 


reference : http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01

:
Posted by Elick