오라클 시간계산 쿼리 질문드립니다. 0 2 381

by 또니 [SQL Query] 오라클 시간계산 [2018.05.16 15:37:47]


예.JPG (37,635Bytes)

안녕하세요. 고민하다 질문드립니다.

테이블에 대해 설명드리면

AA,BB,CC,DD학생이 시설을 이용한 로그를 나타낸 테이블로

STATE = 'S' -> 시설이용 시작

STATE = 'E' -> 시설이용 종료

이며 현재 학생이름과 시간순서로 정렬해논 상태입니다.

제가 구하고 싶은것은 

학생별로 종료시간에서 시작시간을 빼고 각각 구한 이용시간을 더하여 

학생별 총 시설 이용시간을 구하고싶습니다.

단, 시작시간만있고 종료시간이 없는 경우에는 시간을 합하지 않습니다.

 

하루에 시작,종료시간이 여러번 있는것,

종료시간이 없는것을 처리하는 부분에 어려움을 겪고있습니다.

도움을 주시면 감사하겠습니다.

SELECT 'S' AS STATE,'AA' AS STUDENT,TO_DATE('2018-01-25 20:41:58','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'E' AS STATE,'AA' AS STUDENT,TO_DATE('2018-01-25 20:44:47','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'S' AS STATE,'BB' AS STUDENT,TO_DATE('2018-01-25 08:57:47','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'S' AS STATE,'BB' AS STUDENT,TO_DATE('2018-01-25 10:55:15','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'E' AS STATE,'BB' AS STUDENT,TO_DATE('2018-01-25 12:06:37','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'S' AS STATE,'CC' AS STUDENT,TO_DATE('2018-01-25 13:27:40','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'E' AS STATE,'CC' AS STUDENT,TO_DATE('2018-01-25 13:50:59','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'S' AS STATE,'CC' AS STUDENT,TO_DATE('2018-01-26 09:02:56','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'E' AS STATE,'CC' AS STUDENT,TO_DATE('2018-01-26 09:19:52','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'S' AS STATE,'CC' AS STUDENT,TO_DATE('2018-01-26 10:59:19','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'E' AS STATE,'CC' AS STUDENT,TO_DATE('2018-01-26 12:02:41','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL UNION ALL
SELECT 'S' AS STATE,'DD' AS STUDENT,TO_DATE('2018-01-25 08:56:47','YYYY-MM-DD HH24:MI:SS') AS DATEETIME FROM DUAL 

 

 

by 마농 [2018.05.16 16:35:16]
SELECT student
     , sec
     , FLOOR(sec / (24*60*60)) days
     , TO_CHAR(TO_DATE(MOD(sec, 24*60*60), 'SSSSS'), 'hh24:mi:ss') hours
  FROM (SELECT student
             , ROUND(SUM(e_dt - s_dt) * 24*60*60) sec
          FROM (SELECT student
                     , dateetime e_dt
                     , state     e_st
                     , LAG(dateetime) OVER(PARTITION BY student ORDER BY dateetime) s_dt
                     , LAG(state    ) OVER(PARTITION BY student ORDER BY dateetime) s_st
                  FROM t
                )
         WHERE s_st = 'S'
           AND e_st = 'E'
         GROUP BY student
        )
;

 


by 또니 [2018.05.16 16:54:15]

감사합니다 마농님!

DATETIME을 LAG써봐야 겠다는 생각은 했었는데 STATE도 LAG사용할 생각은 못했었네요...

진짜 감사합니다!!!

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