안녕하세요.
일별자료(Daily)를 7일간 합산하여 주별자료 (Weekly)료 변환하려 하는데 도움부탁 드립니다.
과정 1. (함수설정)
강성일님의 함수이용 (http://database.sarang.net/?inc=read&aid=6619&criteria=pgsql&subcrit=&id=&limit=20&keyword=for+loop&page=1)
-- source:http://database.sarang.net/?inc=read&aid=6619&criteria=pgsql&subcrit=&id=&limit=20&keyword=for+loop&page=1
CREATE OR REPLACE FUNCTION "public"."cdate" (date, date,interval) RETURNS SETOF "pg_catalog"."record" AS
$body$
DECLARE
node1 record;
subnode record;
BEGIN
FOR node1 IN SELECT to_date($1 + $3,'YYYY-MM-DD') as cdate where to_date($1 + $3,'YYYY-MM-DD')<= $2 LOOP
RETURN NEXT node1;
FOR subnode IN SELECT * FROM cdate(node1.cdate, $2,$3) as t(cdate date) LOOP
RETURN NEXT subnode;
END LOOP;
END LOOP;
RETURN;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
-- end of function --
과정 2: (날짜 테이블 생성)
DROP TABLE mydate;
CREATE TABLE mydate AS SELECT * FROM cdate('1982-12-31'::date,'1984-12-31',interval'1 day') as t(cdate date);
SELECT * FROM mydate;
+++++++++++++++++++
cdate
+++++++++++++++++++
1983-01-01
1983-01-02
1983-01-03
....
...
1984-12-31
과정3: (테스트 테이블 생성)
-- create test table --
DROP TABLE test;
CREATE TABLE test
(
staid character varying(12),
date date,
stidx character varying(7),
val double precision
)
WITHOUT OIDS;
SELECT * FROM test;
++++++++++++++++++++++
staid date val
++++++++++++++++++++++
과정 4: (테스트 테이블 자료입력)
DROP FUNCTION create_test_table();
CREATE FUNCTION "create_test_table" () RETURNS text AS '
DECLARE
cdate_this date;
text_out date;
row_data mydate%ROWTYPE;
BEGIN
-- FOR i IN 1..(SELECT count(*) FROM mydate) LOOP
FOR row_data IN SELECT * FROM mydate LOOP
SELECT INTO cdate_this cdate FROM mydate;
text_out := row_data.cdate;
INSERT INTO test(staid,date,val) VALUES (0100105,text_out, trunc(1000*random()));
-- IF i < (select count(*)/2 from mydate) THEN
-- INSERT INTO test(staid,date,val) VALUES (0100105,text_out, trunc(1000*random()));
-- ELSE
-- INSERT INTO test(staid,date,val) VALUES (0100777,text_out, trunc(1000*random()));
-- END IF;
--END LOOP;
END LOOP;
RETURN text_out;
END;
' LANGUAGE 'plpgsql';
select create_test_table();
select * from test;
++++++++++++++++++++
staid date val
100105 1983-01-01 251
100105 1983-01-02 664
100105 1983-01-03 762
.....
.....
100105 1984-12-31 603
과정 5: (주별자료로 전환)
SELECT staid, date_trunc('week', date) as weekly, sum(val) as weeklyflow FROM test group by staid, date_trunc('week',date) order by weekly;
+++++++++++++++++++++
staid weekly weeklyflow
100105 1982-12-27 00:00:00-06 915
100105 1983-01-03 00:00:00-06 4153
100105 1983-01-10 00:00:00-06 3116
.....
.....
100105 1984-12-24 00:00:00-06 1606
100105 1984-12-31 00:00:00-06 603
질문:
1. 왜, 날짜를 1983-01-01 에서 1984-12-31 일간 주별 (7일 합산)을 요청했는데, 왜 결과가 1982-12-27 (과정 5) 부터 출력이 되는지요?
2. 보통 평년이 365일이라 1년을 정확하게 52주로 계산하고 싶은데 1월1일부터 시작을 하면 7일을 합산하여 1 주로 계산하여 매해 마지막일인 12월 31일을 제거하여 총 52주 (364/7 = 52주)로 만들고 싶습니다.
즉, 결과값이
++++++++++++++++++++++++++++++
staid weekly weeklyflow
100105 1983-01-07 00:00:00-06 915
100105 1983-01-14 00:00:00-06 4153
....
....
100105 1984-12-30 00:00:00-06 586
3. 그런데, 1984년 같은 경우는 윤달이 (2월달이 29일까지 있음)있어, 총일수가 366일이 되어, 이 같은 경우는 마지막 12월 30일과 31일을 제거하여 총 52주로 만들고 싶습니다.
도움부탁드립니다.
|