주단위(주차별) 사용률 증감 구하기 0 2 7,481

by 신이만든짝퉁 [SQL Query] 주차 주별 증감률 [2013.03.25 15:45:24]


안녕하세요.
아래와 같은 테이블이 있습니다.

table_a (
  reg_date(pk)      varchar2   -- 년월일
, used_rate     number    -- 사용률
)

이 테이블은 날짜별로 디스크 사용량을 표시한 것입니다.
이 테이블을 이용해 주단위(1~ 약52주)로 사용률 증감을 구하려 합니다.

검색조건으로 reg_date 가 시작일과 종료일이 주어지며
이 기간 동안의 주차별 사용률 증감을 구하고자 합니다.

예컨데 시작일('20130301')과 종료일('20130325')이 주어지면
select TO_CHAR(to_date('20130301', 'yyyymmdd'), 'IW') from dual;  -- 09
select TO_CHAR(to_date('20130325', 'yyyymmdd'), 'IW') from dual;  -- 13


9주차 부터 13주차 사이의 데이터 증감률을 구하고자합니다.
데이터는 해당 주의  첫 월요일을 기준으로 합니다.
단, 조회되는 날짜가 시작일과, 종료일이 주어진 검색조건을 벗어나서는 안됩니다.
그러므로 여기서는 10주 ~ 13주 사이의 데이터만 조회해야 합니다.
(9주차의 첫 월요일은 2월 25일이므로)


-- 데이터 예시 --
WITH table_a AS (
SELECT '20130301' reg_date, 100 used_rate FROM dual UNION ALL
SELECT '20130302',    102 FROM dual UNION ALL
SELECT '20130303',    105 FROM dual UNION ALL
SELECT '20130304',    111 FROM dual UNION ALL
SELECT '20130305',    107 FROM dual UNION ALL
SELECT '20130306',    120 FROM dual UNION ALL
SELECT '20130311',    110 FROM dual UNION ALL
SELECT '20130312',    101 FROM dual UNION ALL
SELECT '20130313',    150 FROM dual UNION ALL
SELECT '20130318',    142 FROM dual UNION ALL
SELECT '20130319',    111 FROM dual UNION ALL
SELECT '20130320',    120 FROM dual UNION ALL
SELECT '20130325',    130 FROM dual UNION ALL
SELECT '20130326',    122 FROM dual
)
SELECT * FROM table_a;





-- 원하는 결과 --
reg_date,        used_rate,  used_rate_diff
20130304(10주차)    111           
20130311(11주차)    110           -1
20310318(12주차)    142           32
20310325(13주차)    130          -12



고수님들의 명쾌한(?) 쿼리 부탁드립니다.
by 시퀄 [2013.03.25 16:04:13]

SELECT reg_date||'('|| TO_CHAR(to_date(reg_date, 'yyyymmdd'), 'IW')||'주차)'
, used_rate-LAG(used_rate) OVER (ORDER BY reg_date)
, used_rate
  FROM table_a
 WHERE reg_date BETWEEN '20130301' AND '20130325'
   AND reg_date = TO_CHAR(trunc(to_date(reg_date, 'yyyymmdd'), 'IW'), 'YYYYMMDD')
 ;

by 신이만든짝퉁 [2013.03.25 16:11:18]
헉~~ 감사합니다.
저는 삽질하다가 아래와 같이 겨우 구했는데 너무 어렵게 생각했네요 ㅠ_ㅠ
다시한번 명쾌한 쿼리 감사합니다. ^^

SELECT REG_DATE
     , USED_RATE
     , USED_RATE - LAG(USED_RATE) OVER(ORDER BY REG_DATE) USED_RATE_DIFF
  FROM table_a
 WHERE REG_DATE IN (
                    SELECT REG_DATE
                      FROM (
                             SELECT TO_CHAR(TRUNC(TO_DATE('20130301', 'YYYYMMDD'), 'IW')+7+(LEVEL-1)*7, 'YYYYMMDD') REG_DATE        -- 선택 년월일(20130301 : 시작일)
                               FROM DUAL
                            CONNECT BY LEVEL <= CEIL((TO_DATE('20130325', 'YYYYMMDD') - TO_DATE('20130301', 'YYYYMMDD') + 1)/7)     -- 선택 년월일(20130301 : 시작일, 20130325 : 종료일)
                           )           
                     WHERE REG_DATE <= TO_DATE('20130325', 'YYYYMMDD')
                   )
 
;
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입