데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

PostgreSQL Tutorials 5192 게시물 읽기

No. 5192

재미난 문자열 집계 함수
작성자	김상기(ioseph)	작성일	2004-02-05 23:56	조회수	10,052

아래글은 뉴스그룹에 있었던(2003년도 글 가운데) 내용을

한글로 테스팅 한 것입니다.

사용자 정의 집계 함수를 만드는데, 참고하세요.

재밌지요? (아무래도 정말 plpgsql 용 게시판을 따로 분리를 해야할듯)

mydb=> create function comma_aggregate(text,text) returns text as '
       begin
         if (length($1) > 0 ) then
           return $1 || '', '' || $2;
         else
           return $2;
         end if;
       end;
       ' language 'plpgsql';
CREATE FUNCTION
Time: 1.357 ms
mydb=> create aggregate comma (basetype=text, sfunc=comma_aggregate, stype=text, initcond='' );
CREATE AGGREGATE
Time: 1.397 ms
mydb=> create table t (a date, b text) without oids;
CREATE TABLE
Time: 4.689 ms
mydb=> copy t from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2000-01-01   라면
>> 2000-01-01   국수
>> 2000-01-02   라면
>> 2000-01-02   밥
>> 2000-01-03   밥
>> 2000-01-03   피자
>> \.
Time: 49494.737 ms
mydb=> select a,comma(b) from t group by a;
     a      |   comma
------------+------------
 2000-01-03 | 밥, 피자
 2000-01-02 | 라면, 밥
 2000-01-01 | 라면, 국수
(3 rows)
Time: 1.484 ms
mydb=>

이 글에 대한 댓글이 총 8건 있습니다.

재미있네요.

발상이 기발합니다.

표준 SQL 로 구현하기에는 일이 많은 작업을 저렇게 간단하게 구현하는군요. 한번씩 이런 작업의 필요성을 느끼지요. ^^;

정재익(advance)님이 2004-02-06 09:03에 작성한 댓글입니다.

저도 한번 test해볼려고 하는데요.

language "plpgsql" does not exist

plpgsql이라는 language가 없다고 나오는데요.

어떻게 해야 language를 추가 할수 있나요.

답글 부탇드립니다.

^-^ <-- 초보

박근준님이 2004-02-08 15:20에 작성한 댓글입니다. Edit Ｘ

쉘에서

createlang plpgsql dbname

이런 식으로 pl 랭귀지를 추가합니다.

김상기(ioseph)님이 2004-02-09 12:27에 작성한 댓글입니다.

김상기님 너무나도 잘 작동 됩니다.

감탄! 감탄!

혹시 다른 것도 있으면 소개를 부탁드립니다.

4년 정도 postgres를 사용하면서 정작 기본적인것

밖에는 사용을 못해 본것 같습니다.

postgres를 postgres답게 사용하고 싶습니다.

많은 조언 부탁드립니다.

박근준님이 2004-02-11 16:59에 작성한 댓글입니다. Edit Ｘ

아.. 정말 유!용!한! 내용입니다~~~~~

바로 지금 개발중인 것에 적용을 했습니다 ㅠ_ㅠ

netitup=# \d tb_page
                                                        Table "public.tb_page"
   Column   |         Type          |                                            Modifiers
------------+-----------------------+--------------------------------------------------------------------------------------------------
 pid        | integer               | not null default nextval('tb_page_pid'::text)
 gid        | integer               |
 page       | smallint              | not null default 1::smallint
 name       | character varying(64) |
 columntype | integer[]             |
 opt        | text                  |
 style      | text                  |
 lastmodify | integer               | not null default (date_part('epoch'::text, ('now'::text)::timestamp(6) with time zone))::integer
Indexes:
    "tb_page_pkey" primary key, btree (pid)
    "index_tb_page_gid" btree (gid)
    "index_tb_page_page" hash (page)
Foreign-key constraints:
    "$1" FOREIGN KEY (gid) REFERENCES tb_global(gid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE

netitup=# \d tb_column
                       Table "public.tb_column"
 Column |   Type    |                    Modifiers
--------+-----------+-------------------------------------------------
 cid    | integer   | not null default nextval('tb_column_cid'::text)
 pid    | integer   |
 seq    | smallint  |
 eid    | integer[] |
 style  | text      |
Indexes:
    "tb_column_pkey" primary key, btree (cid)
    "index_tb_column_eid" gist (eid)
    "index_tb_column_pid" hash (pid)
Foreign-key constraints:
    "$1" FOREIGN KEY (pid) REFERENCES tb_page(pid) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE

netitup=# select p.pid, uniq(sort(string_to_array(comma(array_to_string(c.eid,',')),',')::int[])) as eids from tb_page p, tb_column c
netitup-# where p.pid=c.pid and ( c.eid @@ '61' or c.eid @@ '64' ) group by 1;
 pid |  eids
-----+---------
  43 | {61}
  42 | {61,64}
(2 rows)

netitup=# EXPLAIN ANALYZE select p.pid, uniq(sort(string_to_array(comma(array_to_string(c.eid,',')),',')::int[])) as eids from tb_page p, tb_column c
netitup-# where p.pid=c.pid and ( c.eid @@ '61' or c.eid @@ '64' ) group by 1;
                                                                            QUERY PLAN                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=19.33..19.36 rows=2 width=36) (actual time=0.911..0.952 rows=2 loops=1)
   ->  Nested Loop  (cost=0.00..19.32 rows=2 width=36) (actual time=0.255..0.387 rows=3 loops=1)
         ->  Index Scan using index_tb_column_eid, index_tb_column_eid on tb_column c  (cost=0.00..9.65 rows=2 width=36) (actual time=0.087..0.165 rows=3 loops=1)
               Index Cond: ((eid @@ '61'::query_int) OR (eid @@ '64'::query_int))
         ->  Index Scan using tb_page_pkey on tb_page p  (cost=0.00..4.82 rows=1 width=4) (actual time=0.053..0.058 rows=1 loops=3)
               Index Cond: (p.pid = "outer".pid)
 Total runtime: 2.083 ms
(7 rows)

쿼리를 한번 날리고 어플단에서 아닌 데이터를 골라줘야 했던 걸 바로 한방에~~ ㅠ_ㅠ;

감사합니다 :)

신기배(nonun)님이 2004-02-16 23:18에 작성한 댓글입니다.
이 댓글은 2004-02-16 23:20에 마지막으로 수정되었습니다.

제가 완벽하게 원하는 쿼리는 이렇게 됩니다...

netitup=# EXPLAIN ANALYZE select p.pid, p.name, uniq(sort(string_to_array(comma(e.eid),',')::int[]))
netitup-# from tb_page p join tb_column c using(pid) join tb_element e on(e.type=6 and e.eid=any(c.eid))
netitup-# where p.gid=3 group by 1,2;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=65.85..65.93 rows=5 width=122) (actual time=0.950..0.966 rows=2 loops=1)
   ->  Nested Loop  (cost=42.13..65.75 rows=13 width=122) (actual time=0.259..0.346 rows=4 loops=1)
         Join Filter: ("outer".eid = ANY ("inner".eid))
         ->  Seq Scan on tb_element e  (cost=0.00..22.50 rows=5 width=4) (actual time=0.053..0.059 rows=2 loops=1)
               Filter: ("type" = 6)
         ->  Materialize  (cost=42.13..42.18 rows=5 width=150) (actual time=0.088..0.118 rows=8 loops=2)
               ->  Hash Join  (cost=17.08..42.13 rows=5 width=150) (actual time=0.169..0.219 rows=8 loops=1)
                     Hash Cond: ("outer".pid = "inner".pid)
                     ->  Seq Scan on tb_column c  (cost=0.00..20.00 rows=1000 width=36) (actual time=0.005..0.020 rows=8 loops=1)
                     ->  Hash  (cost=17.07..17.07 rows=5 width=118) (actual time=0.094..0.094 rows=0 loops=1)
                           ->  Index Scan using index_tb_page_gid on tb_page p  (cost=0.00..17.07 rows=5 width=118) (actual time=0.065..0.081 rows=3 loops=1)
                                 Index Cond: (gid = 3)
 Total runtime: 2.000 ms
(13 rows)

이 쿼리가 더 빠르게 될순 없는지요 -.-;

intarray 는 되어 있고 잘만 하면 gist 인덱스를 추가로 할 수 있을거 같은데 아무리 해도 1.8~2.0ms 의 담을 넘지 못하는 것 같습니다.. (ibook G3에서 하는거니까 느릴지도.. -.-;)

서브쿼리고 뭐고 여러가지 해봤는데 더 빠른것(0.8~1.4)도 있었지만.. 인덱스를 타거나 하는 부분에서 애로사항이.. -.-

신기배(nonun)님이 2004-02-17 00:50에 작성한 댓글입니다.

기배님이 뭔가 잘못 생각하고 있는 것 같습니다.

comma 함수는 집계함수입니다.

집계함수의 특성상 이 함수의 리턴 결과를 가지고 또 어떻게 하겠다면, 그것은 당연히 인덱스를 사용할 수 없습니다.

생각을 조금 바꾸어 보셔야할 듯싶습니다.

쿼리를 보아하니, 쪽지 시스템 비슷한 놈 같은데,

사용자 id, 게시물 id 모두 정수형으로 하고, intarray gist 인덱스를 사용해서 풀어간다면, 속도 문제는 해결 하실 수 있을 것같습니다.

윗 예제는 날짜별로 식단이 어떻게되는가를 보여주는 보고서일 뿐이거든요. :)

저놈이 아마 실업무에서 쓰여진다면,

발신인: 홍길동

수인인: 김철수, 박영희, .....

이런 해더정보를 사람이 쉽게 볼 수 있도록 나열 하는 정도일듯싶습니다.

김상기(ioseph)님이 2004-02-17 07:38에 작성한 댓글입니다.

위의 세 테이블의 구조가..

하나는 페이지, 하나는 컬럼, 하나는 엘리먼트 입니다..

페이지 안에 컬럼이, 컬럼 안에 배열로 엘리먼트의 id가 들어가구요~

특정 타입의 엘리먼트가 들어간 페이지이름과 특정 타입의 엘리먼트의 id를 배열로 리턴받아야 하는 것이라 -.-

집계함수를 사용해도 될거 같아서 사용해봤습니다 ^^;;

위와 비슷한 기능의 쿼리를 만들어서 사용 할때는 속도도 빠르고 인덱스도 btree+gist 두가지 다 사용했는데 단점이 가져온 데이터를 다시 가공해야 한다는 것이었구요 ㅠ_ㅠ;

DB에서 할 수 있는것은 DB에서 해라.. 라는 말이 기억나서 -_-;;;

(사실은 테이블 구조 전체를 바꾸면 해결되긴 하는데.. 엄두가 -.-)

신기배(nonun)님이 2004-02-17 16:29에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
5233	익숙하지 않은 자료형 - Array [4]	정재익	2004-03-02	10055
5232	로또로 배우는 인덱스 :) [2]	김상기	2004-02-27	7180
5230	Pseudo-Types in PostgreSQL	정재익	2004-02-27	6474
5192	재미난 문자열 집계 함수 [8]	김상기	2004-02-05	10052
5186	PostgreSQL FAQ 한글 번역판	정재익	2004-02-04	17710
5183	Recent PostgreSQL Optimizer Improvements - Tom Lane [1]	김상기	2004-02-03	5746
5043	유저 가이드 schema 전체번역한 것입니다. [1]	이현희	2003-11-09	9725