질문) TIME 중복 .제거 질문입니다 0 9 217

by 구지또 [Oracle 기초] [2021.10.13 15:28:05]


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

여기서 같이 중복되는 시간은 뺀후 더하고 싶습니다.

 

by 마농 [2021.10.14 09:22:43]

원하는 결과표를 보여주세요.


by 구지또 [2021.10.15 07:49:47]


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로 변경될때는 중복제거말고 그대로 보여야하는데 .. 어떻게해야할까요

잘못짠거같은데.ㅠ 도움부탁드려요


by 구지또 [2021.10.14 17:23:56]
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분만 더해야합니다.

(즉 시간의 텀은 제외하고 더해야함)


by 마농 [2021.10.15 08:09:59]

APPLE 에서 GRAPE 로 바뀔 때는 제외하거나 합쳐지거나 하면 안되는거 아닌가요?
기준이 뭔가요? A 가 아니라 B 가 기준인가요?
이 표 형태 그대로 출력되기를 원하는 건가요? 아니면 원하는 값 부분만 출력하면 되는 건가요?
원하는 값이 많이 비어 있는데? 8개 행이 모두 나와야 하는 건가요? 값이 있는 행만 나오면 되는 건가요?


by 구지또 [2021.10.15 08:13:13]

기준은 B입니다.

APPLE에서 GRAPE로 바뀔때는 중복된 시간을 제거하지않고 그대로 GRAPE OUT - IN 값을 보여줘야하며,

그다음 로우 GRAPE에서부터 또 GRAPE가 같으니 중복 시간을 제외헤야합니다.

최종 원하는값에서는 B컬럼으로 그룹바이 SUM할거라 원하는값이 비어보이는것 입니다.

마지막 보여줘야할 컬럼은

B,원하는값 입니다.


by 마농 [2021.10.15 08:31:49]
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
;

 


by 구지또 [2021.10.15 09:35:36]

감사합니다 ㅠ

혹시 맨 상위 TM컬럼에서

하루가 넘어가버리는 경우는 어떻게 표현을 해야하나요?

그냥 시간으로 나눈후 문자로 바꿔줘야할까요?


by 마농 [2021.10.15 09:49:37]
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
;

 


by 구지또 [2021.10.15 10:41:12]

감사합니다 

덕분에 많이 공부되었습니다!

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