데이터베이스 질문과 답변
안녕하세요. 고민하다 질문드립니다.
테이블에 대해 설명드리면
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사용할 생각은 못했었네요...
진짜 감사합니다!!!
) 버튼을 클릭하여 작성 하시면 됩니다.