데이터베이스 질문과 답변
sql 작성에 대한 질문두개만 드립니다.
현재 오라클 9i 사용중인데요.
아래와 같은 데이터가 있다고 했을때
WITH T AS (
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생1' STUDENT#, '102700' START#, '103000' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생1' STUDENT#, '102800' START#, '104000' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생1' STUDENT#, '102900' START#, '105000' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생2' STUDENT#, '104500' START#, '110600' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생2' STUDENT#, '110700' START#, '110700' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생2' STUDENT#, '110800' START#, '110800' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생3' STUDENT#, '142100' START#, '142100' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생3' STUDENT#, '142200' START#, '142200' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생3' STUDENT#, '102600' START#, '103100' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너1' TRAINER#, '교육생3' STUDENT#, '142400' START#, '142400' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너2' TRAINER#, '교육생4' STUDENT#, '095400' START#, '095400' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너2' TRAINER#, '교육생4' STUDENT#, '095400' START#, '095400' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너2' TRAINER#, '교육생4' STUDENT#, '095600' START#, '095600' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너2' TRAINER#, '교육생4' STUDENT#, '110000' START#, '110000' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너2' TRAINER#, '교육생4' STUDENT#, '110100' START#, '110100' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너3' TRAINER#, '교육생5' STUDENT#, '163300' START#, '184100' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너3' TRAINER#, '교육생6' STUDENT#, '163300' START#, '184100' END# FROM DUAL UNION ALL
SELECT '20110830' DATE#, '트레이너3' TRAINER#, '교육생7' STUDENT#, '140700' START#, '161100' END# FROM DUAL
)
1. 첫번째 질문
한명의 트레이너는 여러명의 교육생을 가르칠수가 있으나 동시에 가르칠수는 없다고 할때
교육이 중복된 데이터를 추출할려고 합니다.
1명의 트레이너는 동일한 교육생에게는 교육시간이 전체중복 또는 일부중복은 괜찮으나
다른 교육생에게는 허용이 안된다면 결과에서 시간이 중복이 되는 교육생이 있습니다.
20110830 트레이너1 교육생1 102900 105000
20110830 트레이너1 교육생2 104500 110600
20110830 트레이너1 교육생1 102700 103000
20110830 트레이너1 교육생1 102800 104000
20110830 트레이너1 교육생3 102600 103100
20110830 트레이너3 교육생5 163300 184100
20110830 트레이너3 교육생6 163300 184100
이 데이터를 추출할수는 없을련지요?
2. 두번째 질문
트레이너를 기준으로 서로 다른 교육생의 교육시작 시간이 1시간이후인 데이터만을 추릴수 없을련지요?
동일한 교육생을 대상으로는 시간의 인터벌은 관계없으나 다른 교육생에 한해서는 1시간이내에 시작시간이 나와야 합니다.
결과에서는
20110830 트레이너1 교육생2 110800 110800
20110830 트레이너1 교육생3 142100 142100
20110830 트레이너2 교육생4 095600 095600
20110830 트레이너2 교육생4 110000 110000
20110830 트레이너2 교육생4 110100 110100
20110830 트레이너3 교육생5 163300 184100
가 되겠네요.
첫번째 교육생의 마지막 교육시간의 종료시간과 서로 다른 교육생의 첫번째 시작시간을 비교하면 될듯한데
아직 내공이 부족하여 공통적인 부분을 찾는데 어려움이 있습니다.
전부 윈도우 관련 함수들로 해결이 되어야 할듯 한데 한계를 느끼고 있습니다.
도움을 요청드립니다.
SELECT *
FROM t a
WHERE EXISTS
(
SELECT *
FROM t
WHERE trainer# = a.trainer#
AND student# <> a.student#
AND start# < a.end#
AND end# > a.start#
)
;
-- 일단 다음과 같이 풀어봤습니다. 결과는 다르게 나오네요.
SELECT date#, trainer#, student#, start#, end#
FROM
(
SELECT date#, trainer#, student#, start#, end#
, DECODE(student#
, LAG(student#) OVER(PARTITION BY date#, trainer# ORDER BY start#, end#), ''
, LAG(end#) OVER(PARTITION BY date#, trainer# ORDER BY start#, end#) ) lag_end
, DECODE(student#
, LEAD(student#) OVER(PARTITION BY date#, trainer# ORDER BY start#, end#), ''
, LEAD(start#) OVER(PARTITION BY date#, trainer# ORDER BY start#, end#) ) lead_start
FROM t
)
WHERE TO_CHAR(TO_DATE(start#, 'hh24miss') - 1/24, 'hh24miss') > lag_end
OR TO_CHAR(TO_DATE(end#, 'hh24miss') + 1/24, 'hh24miss') < lead_start
;
) 버튼을 클릭하여 작성 하시면 됩니다.