데이터베이스 질문과 답변

순차적인 시간을 그룹핑하기 0 3 1,029

by 윤병희 [SQL Query] group by [2019.11.27 18:09:11]

시간별로 IN/OUT 되는 테이블이 있습니다.

이것을 IN과 OUT을 같은 ROW로 표현하고 싶은데 쉽지가 않네요.

원본

SEQ KEY IN/OUT TIME
1 AAA IN 20191127101010
2 AAA OUT 20191127101015
3 AAA IN 20191127101015
4 AAA OUT 20191127101020
5 BBB IN 20191127101020
6 BBB OUT 20191127101025

 

결과

KEY IN_TIME OUT_TIME
AAA 20191127101010 20191127101015
AAA 20191127101015 20191127101020
BBB 20191127101020 20191127101025

 

이렇게 결과를 쿼리로 만들 수 있을까요?

고수님들 한수 가르침 부탁드립니다.

by 잠만보 [2019.11.27 20:32:05] 

WITH T AS (
SELECT 6 SEQ, 'BBB' KEY, 'OUT' TYPE, 20191127101025 TIME FROM DUAL UNION ALL
SELECT 1 SEQ, 'AAA' KEY, 'IN' TYPE, 20191127101010 TIME FROM DUAL UNION ALL
SELECT 2 SEQ, 'AAA' KEY, 'OUT' TYPE, 20191127101015 TIME FROM DUAL UNION ALL
SELECT 3 SEQ, 'AAA' KEY, 'IN' TYPE, 20191127101015 TIME FROM DUAL UNION ALL
SELECT 4 SEQ, 'AAA' KEY, 'OUT' TYPE, 20191127101020 TIME FROM DUAL UNION ALL
SELECT 5 SEQ, 'BBB' KEY, 'IN' TYPE, 20191127101020 TIME FROM DUAL
)

SELECT KEY,
       IN_TIME,
       OUT_TIME
  FROM (SELECT T.*,
               NVL(LAG(TIME) OVER(PARTITION BY KEY ORDER BY TIME), TIME) IN_TIME,
               NVL(LEAD(TIME) OVER(PARTITION BY KEY ORDER BY TIME), TIME) OUT_TIME
          FROM T
       )
 GROUP BY KEY,
          IN_TIME,
          OUT_TIME
 ORDER BY KEY
by 마농 [2019.11.28 08:35:46] 
SELECT key
     , in_time
     , out_time
  FROM (SELECT key
             , type
             , time in_time
             , LEAD(time) OVER(PARTITION BY key ORDER BY time) out_time
          FROM t
        )
 WHERE type = 'IN'
;

 

by 윤병희 [2019.11.28 15:05:00]

와우~ 역쉬 고수님들...

감사합니다~

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