Oracle 월별 대여일 가동율 질문드립니다!. 0 4 203

by 밸런스 [SQL Query] [2019.08.14 04:38:06]


Oracle 해당년도의 월별 책대여일에 대한 가동율을 구현하고 싶은데.. 도저히 제머리로는 어떻게 접근해야할지 잘모르겠어서 질문드립니다!

아래는 테이블 정보입니다.

*가동율 = 해당월 대여일수 / 해당월의 총 일 * 100

<테이블 A>

start_date(대여시작일)     end_date(대여종료일)        book_id

2018-12-17                   2019-03-05                       1

2019-03-09                    2019-04-07                       1

2019-04-30                    2019-05-20                       1

 

<테이블 B>

book_id

1 <-- 타겟

2

3

...

----------------------------------------------------------

<결과>

month        rate

01월            31/31*100 = [100%]

02월            28/28*100 = [100%]

03월            28/31*100 = [90%]

04월            8/30*100  = [26%]

05월            20/31*100 = [64%]

06월            0%

07월            0%

08월            0%

09월            0%

10월            0%

11월            0%

12월            0%

 

위와 같이 책id 1에 대한 해당년도(2019년) 월별 대여일의 가동율을 알고 싶습니다..

SELECT *

FROM  B, A

WHERE B.BOOK_ID = 1

AND    B.BOOK_ID = A.BOOK_ID

AND    TO_DATE('2019','YYYY') BETWEEN TO_DATE(TO_CHAR(A.START_DATE,'YYYYMMDD'),'YYYY')  AND  TO_DATE(TO_CHAR(A.END_DATE,'YYYYMMDD'),'YYYY')

 

쿼리 실력이 너무 부족하여.. 위의 쿼리에서 도출된 기간의 월별 대여일 수 조차.. 어떻게 결과값으로 출력해야할지도 감을 못잡고 있습니다...

방향이라도 제시해주시면 정말 감사하겠습니다!!

 

 

 

by jkson [2019.08.14 08:43:59]
with t as
(
select to_date('2018-12-17','yyyy-mm-dd') as start_date, to_date('2019-03-05','yyyy-mm-dd') as end_date, 1 as book_id from dual union all
select to_date('2019-03-09  ','yyyy-mm-dd') as start_date, to_date('2019-04-07','yyyy-mm-dd') as end_date, 1 as book_id from dual union all
select to_date('2019-04-30','yyyy-mm-dd') as start_date, to_date('2019-05-20','yyyy-mm-dd') as end_date, 1 as book_id from dual
)
select to_char(b.start_yymm, 'mm') mon
     , nvl(trunc(sum(a.end_date - a.start_date + 1) / (b.end_yymm - b.start_yymm + 1) * 100), 0) rate
  from (select greatest(start_date,add_months(trunc(start_date, 'mm'), lv - 1)) start_date
             , least(end_date, add_months(last_day(start_date), lv - 1)) end_date
          from t, 
              (select level lv from dual connect by level <= 60)
         where months_between(trunc(end_date, 'mm'), trunc(start_date, 'mm')) + 1 >= lv
           and t.book_id = 1
       ) a,
       (select add_months(to_date('2019-01-01', 'yyyy-mm-dd'), level - 1) start_yymm,
               last_day(add_months(to_date('2019-01-01', 'yyyy-mm-dd'), level - 1)) end_yymm
          from dual
        connect by level <= 12
       ) b
 where a.end_date(+) >= b.start_yymm
   and a.start_date(+) <= b.end_yymm
 group by b.start_yymm, b.end_yymm
 order by mon

 


by 소주쵝오 [2019.08.15 12:49:30]
with t as
(select to_date('2018-12-17','yyyy-mm-dd') as sdt, to_date('2019-03-05','yyyy-mm-dd') as edt, 1 as book_id from dual union all
 select to_date('2019-03-09  ','yyyy-mm-dd') as sdt, to_date('2019-04-07','yyyy-mm-dd') as edt, 1 as book_id from dual union all
 select to_date('2019-04-30','yyyy-mm-dd') as sdt, to_date('2019-05-20','yyyy-mm-dd') as edt, 1 as book_id from dual
), 
cal as
(select add_months(to_date('2019-01-01', 'yyyy-mm-dd'), level -1) as lsdt 
      , last_day(add_months(to_date('2019-01-01', 'yyyy-mm-dd'), level -1)) as ledt
 from dual connect by level <= 12
)
select yyyymm
     , nvl(floor(rdt / ddt * 100), 0)||'%' as rate
from (select to_char(lsdt, 'yyyymm') yyyymm
           , sum(least(ledt, edt) - greatest(lsdt, sdt) + 1) as rdt
           , max(ledt - lsdt + 1) ddt
      from t
         , cal
      where 1=1
      and lsdt <= edt(+)
      and ledt >= sdt(+)
      and book_id(+) = 1
      group by lsdt
     )
order by yyyymm
;

 


by jkson [2019.08.16 08:10:04]

오호~ 월별로 자를 필요가 없었네요ㅎㅎ


by 마농 [2019.08.16 07:56:39]

달력테이블 하나 미리 만들어 놓고 사용하시면 편리합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입