데이터베이스 질문과 답변
with t as (
--입실시간, 퇴실시간
select '00100' cust_no, to_date('2019-07-03 180000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-07-03 190000', 'YYYY-MM-DD HH24MISS') end_dt from dual union all --[CASE1]_0원
select '00100' cust_no, to_date('2019-07-06 180000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-07-06 190001', 'YYYY-MM-DD HH24MISS') end_dt from dual union all --[CASE2]_1000원
select '00100' cust_no, to_date('2019-07-10 180000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-07-11 020000', 'YYYY-MM-DD HH24MISS') end_dt from dual union all --[CASE3]_4000원
select '00100' cust_no, to_date('2019-07-19 050003', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-07-19 080010', 'YYYY-MM-DD HH24MISS') end_dt from dual union all --[CASE4]_1000원
select '00100' cust_no, to_date('2019-07-22 140000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-07-22 190010', 'YYYY-MM-DD HH24MISS') end_dt from dual union all --[CASE5]-1
select '00100' cust_no, to_date('2019-07-22 193009', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-07-22 220030', 'YYYY-MM-DD HH24MISS') end_dt from dual union all --[CASE5]-2
)
select * from t;
안녕하세요 회원여러분~~~ 무더운 여름 고생하십니다 ㅠㅠ
다름이 아니라 조건에 따른 시간 계산 로직을 짜다가 턱 막혀버려서..
열심히 공부하려 하는데 ㅠㅠ매번 쓰는 것만 익숙해지고 시간계산 같은건 아직까지도 많이 생소하네요 ㅠㅠ
여러분들은 이런거 어떻게 계산하시나요?ㅠㅠㅠ
<<조건>>
월~금 : 19시부터~ 08시이전만 계산
토,일 : 24시간 계산
[CASE1]
입실 : 7/3(수) 18:00:00
퇴실 : 7/3(수) 19:00:00
부과금액 : 0원 (평일 금액계산 시간 아님)
[CASE2]
입실 : 7/6(토) 18:00:00
퇴실 : 7/6(토) 19:00:01
부과금액 : 1000원 (주말 금액계산 시간)
[CASE3]
입실 : 7/10(수) 18:00:00
퇴실 : 7/11(목) 02:00:00
부과금액 : 4000원
사유 : 7/10 18:00:00 ~ 1일 23:59:00 (3시간 초과로 3000원)
7/11 00:00:00 ~ 2일 02:00:00 (3시간 이하로 1000원)
날짜가 변경되면 별개의 건으로 본다.
[CASE4]
입실 : 7/19(금) 05:00:03
퇴실 : 7/19(금) 08:00:10
부과금액 : 1000원 (평일계산시간 19시부터~ 08시이전만 계산이므로 3시간 미만건)
[CASE5]-1
입실 : 7/22(월) 14:00:00
퇴실 : 7/22(월) 19:00:10
[CASE5]-2
입실 : 7/22(월) 19:30:09
퇴실 : 7/22(월) 22:00:30
부과금액 : [CASE5]-1만 봤을때는 1000원이지만 (19:00~19:10 시간이 포함되므로 3시간 미만건)
[CASE5]-2를 보면 1시간 미만의 텀으로 다시 입실하으므로 연장으로 보고 3000원 부과 (19:00:00 ~ 22:00:30)
정리하면 각각을 보면 1000+1000이지만 공백의 시간이 한시간미만일 경우 연장건으로 본다.
[쿼리결과]
고객번호 | 3시간이하건수 | 3시간초과건수 |
------------------------------------------------------
00100 | 3 | 2 |
- 3시간이하건수 : [CASE2], [CASE3], [CASE4]
- 3시간초과건수 : [CASE3], [CASE5]
19:00:01 이후에 입실/퇴실 하는 경우에 금액을 지불해야한다.
--> 19:00:01 이전 입실한 시간은 금액처리 안 한다는 거 같은데
데이터를 보면 날짜가 넘어가는 구간도 보이는데요. 이럴 땐 어떻게 처리해요?
00시 이후는 금액 계산 안 하나요?
2019-04-23 191200 2019-04-23 204400
2019-04-23 230800 2019-04-24 012900
23일 05시에 입실해서 27일 20시에 퇴실할 수도 있겠네요?
일단 이어진 시간 묶는 것만..
with t as (
--입실시간, 퇴실시간
select '00100' cust_no, to_date('2019-04-23 230800', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-24 012900', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-18 174700', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-18 192900', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-17 231000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-18 005900', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-23 191200', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-23 204400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-30 195500', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-30 214400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-02 175000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-02 192900', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-05 193000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-05 201400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-30 170800', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-30 190400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-30 221500', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-30 230400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-27 221500', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-27 233900', 'YYYY-MM-DD HH24MISS') end_dt from dual
)
select cust_no, min(str_dt) str_dt, max(end_dt) end_dt
from
(
select cust_no, str_dt, end_dt, sum(fg) over(partition by cust_no order by str_dt, end_dt) fg
from
(
select cust_no, str_dt, end_dt, case when max(end_dt) over(partition by cust_no order by str_dt, end_dt rows between unbounded preceding and 1 preceding) + 1/24 > str_dt then 0 else 1 end fg
from t
)
)
group by cust_no, fg
order by str_dt, end_dt
jkson님 ㅠㅠㅠ답변감사합니다.
제가 누락한 조건들이 있더라구요 ㅠㅠ샘플데이터와 추가조건 업데이트 했습니다 ㅠㅠ조건누락 죄송합니다.
(원본예시, 설명예시, 결과예시) 이 3가지가 연결이 안되고 따로 노네요?
인과관계에 맞게 자료를 일치시켜주시는게 좋습니다.
그렇네요..마음만 너무 앞섰던 것 같습니다 ㅠ바쁘실텐데 죄송합니다ㅠㅠ
조건과 테스트데이터 케이스별로 업데이트했습니다!!
WITH t AS
(
SELECT '00100' cust_no, TO_DATE('20190423 2308', 'yyyymmdd hh24mi') str_dt, TO_DATE('20190424 0129', 'yyyymmdd hh24mi') end_dt FROM dual
UNION ALL SELECT '00100', TO_DATE('20190418 1747', 'yyyymmdd hh24mi'), TO_DATE('20190418 1929', 'yyyymmdd hh24mi') FROM dual
UNION ALL SELECT '00100', TO_DATE('20190417 2310', 'yyyymmdd hh24mi'), TO_DATE('20190418 0059', 'yyyymmdd hh24mi') FROM dual
UNION ALL SELECT '00100', TO_DATE('20190423 1912', 'yyyymmdd hh24mi'), TO_DATE('20190423 2044', 'yyyymmdd hh24mi') FROM dual
UNION ALL SELECT '00100', TO_DATE('20190430 1955', 'yyyymmdd hh24mi'), TO_DATE('20190430 2144', 'yyyymmdd hh24mi') FROM dual
UNION ALL SELECT '00100', TO_DATE('20190402 1750', 'yyyymmdd hh24mi'), TO_DATE('20190402 1929', 'yyyymmdd hh24mi') FROM dual
UNION ALL SELECT '00100', TO_DATE('20190405 1930', 'yyyymmdd hh24mi'), TO_DATE('20190405 2014', 'yyyymmdd hh24mi') FROM dual
UNION ALL SELECT '00100', TO_DATE('20190430 1708', 'yyyymmdd hh24mi'), TO_DATE('20190430 1904', 'yyyymmdd hh24mi') FROM dual
UNION ALL SELECT '00100', TO_DATE('20190430 2215', 'yyyymmdd hh24mi'), TO_DATE('20190430 2304', 'yyyymmdd hh24mi') FROM dual
UNION ALL SELECT '00100', TO_DATE('20190427 2215', 'yyyymmdd hh24mi'), TO_DATE('20190427 2339', 'yyyymmdd hh24mi') FROM dual
)
SELECT cust_no
, COUNT(CASE WHEN h <= 3 THEN 1 END) cnt_or_less_3
, COUNT(CASE WHEN h > 3 THEN 1 END) cnt_over_3
, COUNT(CASE WHEN h <= 3 THEN 1 END) * 1000 amt_or_less_3
, COUNT(CASE WHEN h > 3 THEN 1 END) * 3000 amt_over_3
, COUNT(CASE WHEN h <= 3 THEN 1 END) * 1000
+ COUNT(CASE WHEN h > 3 THEN 1 END) * 3000 amt_tot
FROM (SELECT cust_no
, GREATEST(0, MAX(end_dt) - GREATEST(MIN(str_dt), TRUNC(MIN(str_dt)) + 19/24)) * 24 h
FROM (SELECT cust_no, str_dt, end_dt
, SUM(flag) OVER(PARTITION BY cust_no ORDER BY str_dt) grp
FROM (SELECT cust_no, str_dt, end_dt
, CASE WHEN LAG(end_dt) OVER(PARTITION BY cust_no ORDER BY str_dt)
>= str_dt - 1/24
THEN 0 ELSE 1 END flag
FROM t
)
)
GROUP BY cust_no, grp
)
GROUP BY cust_no
;
주말과 평일의 경계에 걸친 샘플 만들어 보세요.(평일과 주말의 제한이 다른데 이게 애매합니다.)
(금~토) 토요일 9시에 끝난 경우 8시로 자를 것인지? 9시까지 계산할 것인지?
(일-월) 월요일 9시에 끝난 경우 8시로 자를 것인지? 9시까지 계산할 것인지?
마농님 관심 감사합니다.
[CASE1]
20일이 주말이기 때문에 9시까지 계산해야 합니다.
20190719(금) 21시 ~ 20190719(금) 24시 3000원
20190720(토) 00시 ~ 20190720(토) 09시 3000원 총 6000
[CASE2]
22일이 평일이기 때문에 8시까지 계산해야 합니다.
20190721(일) 21시 ~ 20190721(일) 24시 3000원
20190722(월) 00시 ~ 20190722(월) 08시 3000원 총 6000
select '00100' cust_no , to_date('2019-07-19 210000', 'YYYY-MM-DD HH24MISS') str_dt , to_date('2019-07-20 090000', 'YYYY-MM-DD HH24MISS') end_dt from dual union all --[CASE1]
select '00100' cust_no , to_date('2019-07-21 210000', 'YYYY-MM-DD HH24MISS') str_dt , to_date('2019-07-22 090000', 'YYYY-MM-DD HH24MISS') end_dt from dual --[CASE2]
엥? 일별로 나누어 계산하나요?
목요일 23시부터 금요일 01시까지 2시간 이용해도
일별로 1시간씩 1000원 계산해서 2000원이 나와야 하나요?
금요일 23시부터 토요일 01시까지 2시간 이용해도
일별로 1시간씩 1000원 계산해서 2000원이 나와야 하나요?
넵 목요일 23:59:59 부터 금요일 00:01:01 해도 2000원입니다ㅠㅠ
뭔가 불합리해 보이네요?
목요일 21시부터 목요일 23시까지 2시간 이용 --> 1,000 원
목요일 23시부터 금요일 01시까지 2시간 이용 --> 2,000 원 <-- 억울합니다.
금요일 01시부터 금요일 03시까지 2시간 이용 --> 1,000 원
저도 억울합니다. 좀만 깍아주세요ㅠㅠ
위에 말씀드린 것처럼 며칠간 사용하는 경우는 전혀 없나요?
월09시~목21시 처럼.
마농님, 행정에서 정해준 정책이 이러하여.. 저도 여러가지 불합리한 케이스가 떠오릅니다만 정책대로 가야 할 것 같습니다.
jkson님, 넵 그런 케이스는 존재하지 않습니다. 길어봤자 24시간에 3일 정도가 끼일 수는 있습니다.
또 다른 억울한 예
1. 토요일 00~24 이용 24시간 --> 3000원 <-- 개이득
2. 토요일 4시간씩 끊어서 5번 이용 총 20시간 이용
00-04
05-09
10-14
15-19
10-24
--> 3000 * 5 = 15000원 <- 억을해요.
네 그 부분도 알고 있습니다..ㅠㅠ
월요일 06시~22시 이용시, 중간에 불인정 시간(08~19)을 연속으로 볼지? 불연속으로 볼지?
- 1. 연속 이용이므로 (06-08) (19-21) 총 4시간 --> 3시간 이상 3천원
- 2. 두개의 시간으로 나누어 불연속으로 보면 --> 2시간 짜리 2건 2천원
1번이 맞습니다.
월요일이므로 06~22시까지 사용하였지만 실제계산은 19~22시로 3시간입니다. (3000원)
예를들어 06:00~ 19:01 일 경우는 1000원입니다.
6시부터 22시 사이에 퇴장 후 재 입장사이의 시간이 1시간이 넘지 않으면 무조건 연속건입니다.
이상한데요?
제 질문과 전혀 다른 답변이 왔네요?
06~22 가 왜? 19~22 가 돼죠? 06~08 은 어디로?
아 제가 잘못 이해를 했습니다.
해당 건은 06~08 두시간 1000원
19~21 두시간 1000원 입니다.
2번 기준이 맞습니다.
아 종일 사용하면 오전오후가 따로군요~ 이참에 이상한 정책을 바꾸자고 건의해보심이..ㅋㅋ
with t as (
--입실시간, 퇴실시간
select '00100' cust_no, to_date('2019-04-23 230800', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-24 012900', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-18 174700', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-18 192900', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-17 231000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-18 005900', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-23 191200', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-23 204400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-30 195500', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-30 214400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-02 175000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-02 192900', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-05 193000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-05 201400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-30 170800', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-30 190400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-30 221500', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-30 230400', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-02 120000', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-02 130000', 'YYYY-MM-DD HH24MISS') end_dt from dual union all
select '00100' cust_no, to_date('2019-04-27 221500', 'YYYY-MM-DD HH24MISS') str_dt, to_date('2019-04-27 233900', 'YYYY-MM-DD HH24MISS') end_dt from dual
)
select cust_no, str_dt, end_dt
, case when hr >= 3 then 3000
when hr > 0 then 1000
else 0
end amt
from
(
select cust_no, str_dt, end_dt
, case when to_char(str_dt,'dy') in ('sat','sun') then (end_dt - str_dt) * 24 else (end_dt2 - str_dt2) * 24 end hr
from
(
select cust_no, str_dt, end_dt
, case when str_dt between trunc(str_dt) + 8/24 and trunc(str_dt) + 19/24 then trunc(str_dt) + 19/24 else str_dt end str_dt2
, case when end_dt between trunc(str_dt) + 8/24 and trunc(str_dt) + 19/24 then trunc(str_dt) + 8/24 else end_dt end end_dt2
from
(
select cust_no, min(str_dt) str_dt, max(end_dt) end_dt
from
(
select cust_no, str_dt, end_dt, sum(fg) over(partition by cust_no order by str_dt, end_dt) fg
from
(
select cust_no, str_dt, end_dt, case when max(end_dt) over(partition by cust_no order by str_dt, end_dt rows between unbounded preceding and 1 preceding) + 1/24 > str_dt
and trunc(max(end_dt) over(partition by cust_no order by str_dt, end_dt rows between unbounded preceding and 1 preceding)) = trunc(str_dt) then 0 else 1 end fg
from (select cust_no, decode(lv,0,str_dt,trunc(str_dt)+lv) str_dt, decode(lv,max(lv) over (partition by cust_no, str_dt, end_dt),end_dt,trunc(str_dt)+lv+0.99999) end_dt
from t, (select level -1 lv from dual connect by level <= 3)
where trunc(end_dt) - trunc(str_dt) >= lv
order by cust_no, str_dt
)
)
)
group by cust_no, fg
)
)
)
맞을까나요? 저는 다시 업무하러 갑니다.
JKSON님 ㅠㅠㅠ바쁘신 와중에 시간내서 봐주셔서 너무 감사합니다 ㅠㅠ
관심 가져주셨으나 채택드리지 못해 죄송한 마음입니다..
처음보는 기능들을 많이 쓰셔서 많은 도움이 되었습니다.
많이 배우고 갑니다!! 감사합니다!!!
WITH t AS
(
SELECT '00100' cust_no, TO_DATE('2019-07-03 180000', 'yyyy-mm-dd hh24miss') str_dt, TO_DATE('2019-07-03 190000', 'yyyy-mm-dd hh24miss') end_dt FROM dual
UNION ALL SELECT '00100', TO_DATE('2019-07-06 180000', 'yyyy-mm-dd hh24miss'), TO_DATE('2019-07-06 190001', 'yyyy-mm-dd hh24miss') FROM dual
UNION ALL SELECT '00100', TO_DATE('2019-07-10 180000', 'yyyy-mm-dd hh24miss'), TO_DATE('2019-07-11 020000', 'yyyy-mm-dd hh24miss') FROM dual
UNION ALL SELECT '00100', TO_DATE('2019-07-19 050003', 'yyyy-mm-dd hh24miss'), TO_DATE('2019-07-19 080010', 'yyyy-mm-dd hh24miss') FROM dual
UNION ALL SELECT '00100', TO_DATE('2019-07-22 140000', 'yyyy-mm-dd hh24miss'), TO_DATE('2019-07-22 190010', 'yyyy-mm-dd hh24miss') FROM dual
UNION ALL SELECT '00100', TO_DATE('2019-07-22 193009', 'yyyy-mm-dd hh24miss'), TO_DATE('2019-07-22 220030', 'yyyy-mm-dd hh24miss') FROM dual
)
, t1 AS
(
-- Step 1. 기간 쪼개기
SELECT cust_no, str_dt, end_dt
, GREATEST(str_dt, TRUNC(str_dt + lv - 1), TRUNC(str_dt + lv - 1) + s_h/24) s
, LEAST (end_dt, TRUNC(str_dt + lv - 0), TRUNC(str_dt + lv - 1) + e_h/24) e
FROM t
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)
, (SELECT 1 gb, 0 s_h, 24 e_h FROM dual -- 주말(1) 종일
UNION ALL SELECT 2, 0, 8 FROM dual -- 평일(2) 오전
UNION ALL SELECT 2, 19, 24 FROM dual -- 평일(2) 오후
)
WHERE lv <= TRUNC(end_dt) - TRUNC(str_dt) + 1
AND gb = CASE WHEN TO_CHAR(str_dt + lv - 1, 'd') IN ('1','7') THEN 1 ELSE 2 END
AND str_dt < TRUNC(str_dt + lv - 1) + e_h/24
AND end_dt > TRUNC(str_dt + lv - 1) + s_h/24
)
, t2 AS
(
-- Step 2. 1시간 이내 합치기
SELECT cust_no
, MIN(s) s
, MAX(e) e
, (MAX(e) - MIN(s)) * 24 h
FROM (SELECT cust_no, s, e
, SUM(flag) OVER(PARTITION BY cust_no, TRUNC(s) ORDER BY s) grp
FROM (SELECT cust_no, s, e
, CASE WHEN LAG(e) OVER(PARTITION BY cust_no, TRUNC(s) ORDER BY s)
> s - 1/24 THEN 0 ELSE 1 END flag
FROM t1
)
)
GROUP BY cust_no, TRUNC(s), grp
)
-- Step 3. 결과 집계
SELECT cust_no
, COUNT(CASE WHEN h <= 3 THEN 1 END) cnt_or_less_3
, COUNT(CASE WHEN h > 3 THEN 1 END) cnt_over_3
, COUNT(CASE WHEN h <= 3 THEN 1 END) * 1000 amt_or_less_3
, COUNT(CASE WHEN h > 3 THEN 1 END) * 3000 amt_over_3
, COUNT(CASE WHEN h <= 3 THEN 1 END) * 1000
+ COUNT(CASE WHEN h > 3 THEN 1 END) * 3000 amt_tot
FROM t2
GROUP BY cust_no
;
와...와....마농님...........감동입니다...한줄 한줄 다 신기합니다..감사합니다....
공부 열심히 해야겠네요..
) 버튼을 클릭하여 작성 하시면 됩니다.