데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

됩니다..

self 조인을 이용했습니다만 능력자 분들이 더 훌륭하게 짜주실지 모르겠네요..

/* Formatted on 2011/08/05 13:54 (Formatter Plus v4.8.8) */

WITH t AS

(SELECT TO_DATE ('2011-08-01 9:23:44', 'yyyy-mm-dd hh:mi:ss') tdate,'LOGIN' gb, 'user02' userid

FROM DUAL

UNION ALL

SELECT TO_DATE ('2011-08-01 9:59:07', 'yyyy-mm-dd hh:mi:ss') tdate,'LOGOUT' gb, 'user02' userid

FROM DUAL

UNION ALL

SELECT TO_DATE ('2011-08-01 9:13:41', 'yyyy-mm-dd hh:mi:ss') tdate,'LOGIN' gb, 'user01' userid

FROM DUAL

UNION ALL

SELECT TO_DATE ('2011-08-01 9:49:06', 'yyyy-mm-dd hh:mi:ss') tdate,'LOGOUT' gb, 'user01' userid

FROM DUAL

UNION ALL

SELECT TO_DATE ('2011-08-04 11:27:10', 'yyyy-mm-dd hh:mi:ss') tdate,'LOGIN' gb, 'user02' userid

FROM DUAL

UNION ALL

SELECT TO_DATE ('2011-08-04 11:40:40', 'yyyy-mm-dd hh:mi:ss') tdate,'LOGOUT' gb, 'user02' userid

FROM DUAL

UNION ALL

SELECT TO_DATE ('2011-08-04 10:27:10', 'yyyy-mm-dd hh:mi:ss') tdate,'LOGIN' gb, 'user01' userid

FROM DUAL

UNION ALL

SELECT TO_DATE ('2011-08-04 10:40:40', 'yyyy-mm-dd hh:mi:ss') tdate,'LOGOUT' gb, 'user01' userid

FROM DUAL)

SELECT a.tdate, a.gb, a.userid, MIN (b.tdate) tdate, b.gb

FROM t a LEFT OUTER JOIN t b

ON a.userid = b.userid AND a.tdate < b.tdate AND b.gb = 'LOGOUT'

WHERE a.gb = 'LOGIN'

GROUP BY a.tdate, a.gb, a.userid, b.gb

1님이 2011-08-05 13:54에 작성한 댓글입니다.
이 댓글은 2011-08-05 13:56에 마지막으로 수정되었습니다. Edit Ｘ

WITH t AS
(
SELECT TO_DATE('20110801092344', 'yyyymmddhh24miss') tm, 'LOGIN' gb, 'user02' id FROM dual
UNION ALL SELECT TO_DATE('20110801095907', 'yyyymmddhh24miss'), 'LOGOUT', 'user02' FROM dual
UNION ALL SELECT TO_DATE('20110801091341', 'yyyymmddhh24miss'), 'LOGIN' , 'user01' FROM dual
UNION ALL SELECT TO_DATE('20110801094906', 'yyyymmddhh24miss'), 'LOGOUT', 'user01' FROM dual
UNION ALL SELECT TO_DATE('20110804112710', 'yyyymmddhh24miss'), 'LOGIN' , 'user02' FROM dual
UNION ALL SELECT TO_DATE('20110804114040', 'yyyymmddhh24miss'), 'LOGOUT', 'user02' FROM dual
UNION ALL SELECT TO_DATE('20110804102710', 'yyyymmddhh24miss'), 'LOGIN' , 'user01' FROM dual
UNION ALL SELECT TO_DATE('20110804104040', 'yyyymmddhh24miss'), 'LOGOUT', 'user01' FROM dual
)
SELECT id
     , stm
     , etm
     , NUMTODSINTERVAL(etm - stm, 'day') interval
FROM (
        SELECT gb, id
             , tm stm
             , LEAD(tm, 1, sysdate) OVER(PARTITION BY id ORDER BY tm) etm
          FROM t
        )
WHERE gb = 'LOGIN'
;

마농(manon94)님이 2011-08-05 16:36에 작성한 댓글입니다.