데이터베이스 질문과 답변
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')
쿼리 실력이 너무 부족하여.. 위의 쿼리에서 도출된 기간의 월별 대여일 수 조차.. 어떻게 결과값으로 출력해야할지도 감을 못잡고 있습니다...
방향이라도 제시해주시면 정말 감사하겠습니다!!
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
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
;
오호~ 월별로 자를 필요가 없었네요ㅎㅎ
달력테이블 하나 미리 만들어 놓고 사용하시면 편리합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.