반응형
/********************************************************************************************
-- Title : [PGS9.2] 임의의 Sequence, Series, Identity를 row로 생성하기
-- Reference : http://www.postgresql.org/docs/9.2/static/functions-srf.html
-- Key word : generate_series generate series sequence identity level 더미 dummy
                    임의 데이터 생성 샘플 데이터 추가 sample data
********************************************************************************************/

-- 테이블 생성
-- drop table ttt_srl;
create table ttt_srl
( a int, b int);

insert into ttt_srl values (1,1);
insert into ttt_srl values (2,2);
insert into ttt_srl values (3,3);

select * from ttt_srl;

-- generate_series함수
select generate_series(1,5);
select generate_series(0,500,100);
select generate_series(500,0,-100);
select * from generate_series(1,1000, 100);

-- 활용 
select * 
from ttt_srl
cross join 
(select generate_series(1,10) "pivot") b

select current_date + "series"."index" as "dates", "series"."index"
from generate_series(0,365,7) as "series"("index");

select date('2013-01-01') + (to_char(a,'99')||' month')::interval as date
     , to_char(a,'99')||' month', a
from generate_series(0,19) as a;

select current_date + s.a as dates 
from generate_series(0,14,7) as s(a);

select * 
from generate_series('2008-03-01 00:00'::timestamp
                    ,'2008-03-04 12:00', '10 hours');



반응형

+ Recent posts