A | B | IN_TIME | OUT_TIME |
APPLE | 과일 | 20211013 16:15:54 | 20211013 16:34:35 |
APPLE | 과일 | 20211013 16:28:36 | 20211013 16:47:18 |
GRAPE | 과일 | 20211013 16:41:27 | 20211013 17:00:10 |
GRAPE | 과일 | 20211013 16:54:17 | 20211013 17:13:48 |
GRAPE | 과일 | 20211013 17:30:00 | 20211013 17:35:00 |
GRAPE | 과일 | 20211013 17:32:00 | 20211013 17:37:00 |
CAKE | 과자 | 20211013 17:07:50 | 20211013 17:26:31 |
CAKE | 과자 | 20211013 17:20:41 | 20211013 17:39:21 |
fruit_table 테이블에 위와 같은 데이터가 있습니다.
이전 같은 A가 있고, 이전 OUT_TIME이 다음 IN_TIME보다 이전이면
중복시간만큼 뺴고 싶은데 어떻게해야할까요?
이때 A컬럼과 B컬럼은 다른 것들도 존재합니다.
예) APPLE의 21:00:54 ~ 23:10:02
두번째 APPLE의 21:09:22 ~ 23:15:23
여기서 같이 중복되는 시간은 뺀후 더하고 싶습니다.
WITH tt AS
(
SELECT 'APPLE' AS A, '과일' AS b, '20211013 16:15:54' AS in_time ,'20211013 16:34:35' AS out_time FROM dual UNION ALL
SELECT 'APPLE', '과일', '20211013 16:28:36', '20211013 16:47:18' FROM dual UNION ALL
SELECT 'GRAPE', '과일', '20211013 16:41:27', '20211013 17:00:10' FROM dual UNION ALL
SELECT 'GRAPE', '과일', '20211013 16:54:17', '20211013 17:13:48' FROM dual UNION ALL
SELECT 'GRAPE', '과일', '20211013 17:30:00', '20211013 17:35:00' FROM dual UNION ALL
SELECT 'GRAPE', '과일', '20211013 17:32:00', '20211013 17:37:00' FROM dual UNION ALL
SELECT 'CAKE', '과자', '20211013 17:07:50', '20211013 17:26:31' FROM dual UNION ALL
SELECT 'CAKE', '과자', '20211013 17:20:41', '20211013 17:39:21' FROM dual
)
SELECT
A,b, in_time, out_time, gap_min, next_time,
round((out_time-next_time)*24*60,2) next_gap_time,
CASE WHEN round((out_time-next_time)*24*60,2) > 0 THEN gap_min - round((out_time-next_time)*24*60,2)
ELSE gap_min
END gap_final
FROM
(
SELECT
A, b, in_time, out_time,
round((out_time-in_time)*24*60,2) gap_min,
LEAD(in_time) OVER(PARTITION BY b ORDER BY in_time) AS next_time
FROM
(
SELECT
A, b, TO_DATE(in_time,'yyyymmdd hh24:mi:ss') in_time, TO_DATE(out_time,'yyyymmdd hh24:mi:ss') out_time
FROM tt
)
)
A컬럼 APPLE에서 GRAPE로 변경될때는 중복제거말고 그대로 보여야하는데 .. 어떻게해야할까요
잘못짠거같은데.ㅠ 도움부탁드려요
A | B | IN_TIME | OUT_TIME | 갭시간 | 다음시간 | 다음시간 갭 | 마지막 값 | 원하는 값 |
APPLE | 과일 | 20211013 16:15:54 | 20211013 16:34:35 | 18.68 | 20211013 16:28:36 | 6.00 | 12.68 | |
APPLE | 과일 | 20211013 16:28:36 | 20211013 16:47:18 | 18.7 | 20211013 16:41:27 | 5.90 | 12.8 | |
GRAPE | 과일 | 20211013 16:41:27 | 20211013 17:00:10 | 18.72 | 20211013 16:54:17 | 5.90 | 12.82 | |
GRAPE | 과일 | 20211013 16:54:17 | 20211013 17:13:48 | 19.52 | 20211013 17:30:00 | -16.2 | 19.52 | 57.9 |
GRAPE | 과일 | 20211013 17:30:00 | 20211013 17:35:00 | 5 | 20211013 17:32:00 | 3 | 2 | |
GRAPE | 과일 | 20211013 17:32:00 | 20211013 17:37:00 | 5 | 5 | 64.82 | ||
CAKE | 과자 | 20211013 17:07:50 | 20211013 17:26:31 | 18.68 | 20211013 17:20:41 | 5.8 | 12.88 | |
CAKE | 과자 | 20211013 17:20:41 | 20211013 17:39:21 | 18.67 | 18.67 | 31.55 |
1.A컬럼의 값이 바뀌지 않는다면 시작 IN_TIME부터 같은 A컬럼의 마지막 OUT_TIME이 몇분 걸렸는지만 나오면되는데
2.이때, A컬럼의 값이 바뀌면 중복시간을 제외하지않고 더합니다.
3. 마지막 GRAPE 이전 OUT_TIME이 13분이고 마지막 IN_TIME은17시 30분입니다.
이때 텀이 17분 가량있는데 이 해당 텀은 제외하고 5분만 더해야합니다.
(즉 시간의 텀은 제외하고 더해야함)
WITH tt AS ( SELECT 'APPLE' a, '과일' b, '20211013 16:15:54' in_time ,'20211013 16:34:35' out_time FROM dual UNION ALL SELECT 'APPLE', '과일', '20211013 16:28:36', '20211013 16:47:18' FROM dual UNION ALL SELECT 'GRAPE', '과일', '20211013 16:41:27', '20211013 17:00:10' FROM dual UNION ALL SELECT 'GRAPE', '과일', '20211013 16:54:17', '20211013 17:13:48' FROM dual UNION ALL SELECT 'GRAPE', '과일', '20211013 17:30:00', '20211013 17:35:00' FROM dual UNION ALL SELECT 'GRAPE', '과일', '20211013 17:32:00', '20211013 17:37:00' FROM dual UNION ALL SELECT 'CAKE' , '과자', '20211013 17:07:50', '20211013 17:26:31' FROM dual UNION ALL SELECT 'CAKE' , '과자', '20211013 17:20:41', '20211013 17:39:21' FROM dual ) SELECT b , TO_CHAR(TO_DATE(SUM(sec), 'sssss'), 'hh24:mi:ss') tm , ROUND(SUM(sec) / 60, 2) mi FROM (SELECT a, b , ROUND((MAX(out_time) - MIN(in_time)) *24*60*60) sec FROM (SELECT a, b , in_time, out_time , SUM(flag) OVER(PARTITION BY a ORDER BY in_time, out_time) grp FROM (SELECT a, b , in_time, out_time , CASE WHEN MAX(out_time) OVER(PARTITION BY a ORDER BY in_time, out_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= in_time THEN 0 ELSE 1 END flag FROM (SELECT a, b , TO_DATE( in_time, 'yyyymmdd hh24:mi:ss') in_time , TO_DATE(out_time, 'yyyymmdd hh24:mi:ss') out_time FROM tt ) ) ) GROUP BY a, b, grp ) GROUP BY b ORDER BY b ;
WITH tt AS ( SELECT 'APPLE' a, '과일' b, '20211013 16:15:54' in_time ,'20211013 16:34:35' out_time FROM dual UNION ALL SELECT 'APPLE', '과일', '20211013 16:28:36', '20211013 16:47:18' FROM dual UNION ALL SELECT 'GRAPE', '과일', '20211013 16:41:27', '20211013 17:00:10' FROM dual UNION ALL SELECT 'GRAPE', '과일', '20211013 16:54:17', '20211013 17:13:48' FROM dual UNION ALL SELECT 'GRAPE', '과일', '20211013 17:30:00', '20211013 17:35:00' FROM dual UNION ALL SELECT 'GRAPE', '과일', '20211013 17:32:00', '20211013 17:37:00' FROM dual UNION ALL SELECT 'CAKE' , '과자', '20211013 17:07:50', '20211013 17:26:31' FROM dual UNION ALL SELECT 'CAKE' , '과자', '20211013 17:20:41', '20211013 17:39:21' FROM dual UNION ALL SELECT 'SOJU' , '음료', '20211013 17:20:41', '20211014 17:39:21' FROM dual ) SELECT b , FLOOR(SUM(sec) / (24*60*60)) ||' '|| TO_CHAR(TO_DATE(MOD(SUM(sec), 24*60*60), 'sssss'), 'hh24:mi:ss') tm , ROUND(SUM(sec) / 60, 2) mi FROM (SELECT a, b , ROUND((MAX(out_time) - MIN(in_time)) *24*60*60) sec FROM (SELECT a, b , in_time, out_time , SUM(flag) OVER(PARTITION BY a ORDER BY in_time, out_time) grp FROM (SELECT a, b , in_time, out_time , CASE WHEN MAX(out_time) OVER(PARTITION BY a ORDER BY in_time, out_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= in_time THEN 0 ELSE 1 END flag FROM (SELECT a, b , TO_DATE( in_time, 'yyyymmdd hh24:mi:ss') in_time , TO_DATE(out_time, 'yyyymmdd hh24:mi:ss') out_time FROM tt ) ) ) GROUP BY a, b, grp ) GROUP BY b ORDER BY b ;