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

Oracle Q&A 40428 게시물 읽기

No. 40428

난이도 있는? 쿼리 질문입니다.
작성자	착한넘	작성일	2014-04-10 20:08	조회수	8,303

WITH TEST AS(

SELECT '나의사1' DRNAME, '나환자1' PNAME, '20140101' MRDATE, '시작' GUBUN FROM DUAL UNION ALL

SELECT '나의사1', '나환자2', '20140101', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자2', '20140102', '중간'FROM DUAL UNION ALL

SELECT '나의사2', '나환자2', '20140103', '종료'FROM DUAL UNION ALL

SELECT '나의사2', '나환자2', '20140101', '시작'FROM DUAL UNION ALL

SELECT '나의사2', '나환자5', '20140204', '시작'FROM DUAL UNION ALL

SELECT '나의사2', '나환자4', '20140201', '중간'FROM DUAL UNION ALL

SELECT '나의사2', '나환자3', '20150105', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자3', '20150107', '종료'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '중간'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150102', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150103', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '시작'FROM DUAL

)

SELECT * FROM TEST;

의사	년도	구분	계	월평균	일평균	1월	2월	3월	4월	5월	6월	7월	8월	9월	10월	11월	12월
나의사1	2014	시작	10	10	10
나의사1	2014	중간	5	5	5
나의사1	2014	종료	5	5	5
나의사1	2014	시작/종료	2.00	0	0
나의사1	2014	시작/중간	2.00	0	0
나의사1	2014	중간/종료	1.00	0	0

위 예시의 값은 쿼리와 무관하게 입력하였습니다.(형태만 보시길..)

구분에 시작, 중간, 종료 값은 뽑을 수 있겠는데 "시작/종류.." 이 데이터를 어떻게 뽑아야 할지 감을 못 잡겟네요~~

고수님들 좀 도와주세요 ㅠㅠ

이 글에 대한 댓글이 총 3건 있습니다.

안녕하세요 .

대충 쿼리 작성했어요

문의 하신 제일 힘든 부분을 했는데요 참조 부탁 드립니다

WITH TEST AS(

SELECT '나의사1' DRNAME, '나환자1' PNAME, '20140101' MRDATE, '시작' GUBUN FROM DUAL UNION ALL

SELECT '나의사1', '나환자2', '20140101', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자2', '20140102', '중간'FROM DUAL UNION ALL

SELECT '나의사2', '나환자2', '20140103', '종료'FROM DUAL UNION ALL

SELECT '나의사2', '나환자2', '20140101', '시작'FROM DUAL UNION ALL

SELECT '나의사2', '나환자5', '20140204', '시작'FROM DUAL UNION ALL

SELECT '나의사2', '나환자4', '20140201', '중간'FROM DUAL UNION ALL

SELECT '나의사2', '나환자3', '20150105', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자3', '20150107', '종료'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '중간'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150102', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150103', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '시작'FROM DUAL UNION ALL

SELECT '나의사1', '나환자4', '20150101', '시작'FROM DUAL

)

,w2 as (

select DRNAME , PNAME ,max(decode(GUBUN,'시작','1')) a1

,max(decode(GUBUN,'중간','2')) a2

,max(decode(GUBUN,'종료','3')) a3

from test

group by DRNAME , PNAME

)

,w3 as (

select DRNAME , PNAME ,GUBUN from test

union all

select DRNAME , PNAME ,case when a1 = '1' and a2 = '2' and a3 = '3' then '시작-종료'

when a1 = '1' and a2 = '2' and a3 is null then '시작-중간'

when a1 is null and a2 = '2' and a3 = '3' then '중간-종료'

end a4

from w2

)

SELECT

DRNAME,GUBUN ,count(*)

FROM w3

group by DRNAME,GUBUN

;

정송화(qidan3500)님이 2014-04-13 18:59에 작성한 댓글입니다.

아 ㅠㅠ 신경써 주셔서 감사합니다.

제 설명이 부족했네요~~

구분에 "시작,중간,종료" 계는 총 갯수를 의미하고..

구분에 "시작/종료, 시작/중간, 시작/종료"은 "SUM(시작)/SUM(종료)"..를 의미합니다.

자답 쿼리 입니다. 데이터가 나오긴 하는데.. 같은 테이블을 3번이나 조회하네요

SELECT DRID, DRNAME, YYYY, ROWNO, GUBUN

, CASE ROWNO WHEN 1 THEN SUM(CNT)

WHEN 2 THEN CASE WHEN SUM(CNTSUCES) = 0 THEN 0 ELSE ROUND(SUM(CNTSTART)/SUM(CNTSUCES),2) END

WHEN 3 THEN CASE WHEN SUM(CNTFIRST) = 0 THEN 0 ELSE ROUND(SUM(CNTSTART)/SUM(CNTFIRST),2) END

WHEN 4 THEN CASE WHEN SUM(CNTFIRST) = 0 THEN 0 ELSE ROUND(SUM(CNTSUCES)/SUM(CNTFIRST),2) END

ELSE 0 END "SUM"

, CASE ROWNO WHEN 1 THEN ROUND(SUM(CNT)/12, 2) ELSE 0 END AVGMONTH

, CASE ROWNO WHEN 1 THEN ROUND(SUM(CNT)/SUM(CNTDAY),2) ELSE 0 END AVGDAY

, MAX(CASE MON WHEN '01' THEN MONTHCNT ELSE 0 END) MONTH01

, MAX(CASE MON WHEN '02' THEN MONTHCNT ELSE 0 END) MONTH02

, MAX(CASE MON WHEN '03' THEN MONTHCNT ELSE 0 END) MONTH03

, MAX(CASE MON WHEN '04' THEN MONTHCNT ELSE 0 END) MONTH04

, MAX(CASE MON WHEN '05' THEN MONTHCNT ELSE 0 END) MONTH05

, MAX(CASE MON WHEN '06' THEN MONTHCNT ELSE 0 END) MONTH06

, MAX(CASE MON WHEN '07' THEN MONTHCNT ELSE 0 END) MONTH07

, MAX(CASE MON WHEN '08' THEN MONTHCNT ELSE 0 END) MONTH08

, MAX(CASE MON WHEN '09' THEN MONTHCNT ELSE 0 END) MONTH09

, MAX(CASE MON WHEN '10' THEN MONTHCNT ELSE 0 END) MONTH10

, MAX(CASE MON WHEN '11' THEN MONTHCNT ELSE 0 END) MONTH11

, MAX(CASE MON WHEN '12' THEN MONTHCNT ELSE 0 END) MONTH12

FROM(

SELECT DRID, DRNAME, YYYY, ROWNO, MON

, CASE ROWNO WHEN 2 THEN '시작/진단' WHEN 3 THEN '시작/초진' WHEN 4 THEN '진단/초진' ELSE GUBUN END GUBUN

, SUM(CNTSTART) CNTSTART, SUM(CNTFIRST) CNTFIRST, SUM(CNTSUCES) CNTSUCES

, SUM(CNTDAY) CNTDAY, SUM(CNT) CNT

, CASE WHEN ROWNO = 1 THEN SUM(CNT)

WHEN ROWNO = 2 AND SUM(CNTSUCES) > 0 THEN ROUND(SUM(CNTSTART)/SUM(CNTSUCES),2)

WHEN ROWNO = 3 AND SUM(CNTFIRST) > 0 THEN ROUND(SUM(CNTSTART)/SUM(CNTFIRST),2)

WHEN ROWNO = 4 AND SUM(CNTFIRST) > 0 THEN ROUND(SUM(CNTSUCES)/SUM(CNTFIRST),2)

ELSE 0 END MONTHCNT

FROM (

SELECT DISTINCT DRID, DRNAME

, SUBSTR(MRDATE,1,4) YYYY

, SUBSTR(MRDATE,5,2) MON

, '시작' GUBUN

, COUNT(*) OVER(PARTITION BY DRID, SUBSTR(MRDATE,1,4), SUBSTR(MRDATE,5,2)) CNT

, COUNT(*) OVER(PARTITION BY DRID, SUBSTR(MRDATE,1,4), SUBSTR(MRDATE,5,2)) CNTSTART

, 0 CNTSUCES, 0 CNTFIRST

, COUNT(DISTINCT MRDATE) OVER(PARTITION BY DRID, SUBSTR(MRDATE,1,4), SUBSTR(MRDATE,5,3)) CNTDAY

FROM TABLE01

AND GUBUN = '시작'

UNION ALL

SELECT DISTINCT DRID, DRNAME

, SUBSTR(MRDATE,1,4) YYYY

, SUBSTR(MRDATE,5,2) MON

, '진단' GUBUN

, COUNT(*) OVER(PARTITION BY DRID, SUBSTR(MRDATE,1,4), SUBSTR(MRDATE,5,2)) CNT

, 0 CNTSTART

, COUNT(*) OVER(PARTITION BY DRID, SUBSTR(MRDATE,1,4), SUBSTR(MRDATE,5,2)) CNTSUCES

, 0 CNTFIRST

, COUNT(DISTINCT MRDATE) OVER(PARTITION BY DRID, SUBSTR(MRDATE,1,4), SUBSTR(MRDATE,5,3)) CNTDAY

FROM TABLE01

AND GUBUN = '진단'

UNION ALL

SELECT DISTINCT DRID, DRNAME

, SUBSTR(MRDATE,1,4) YYYY

, SUBSTR(MRDATE,6,2) MON

, '초진' GUBUN

, COUNT(*) CNT

, 0, 0

, COUNT(*) CNTNEW

, COUNT(DISTINCT MRDATE) CNTDAY

FROM TABLE01

AND GUBUN = '초진'

GROUP BY DRID, DRNAME, SUBSTR(NEWDATE,1,4), SUBSTR(NEWDATE,6,2)

) a

JOIN (SELECT ROWNUM ROWNO

FROM DUAL

CONNECT BY LEVEL <= 4 ) b ON b.ROWNO <= 4

GROUP BY DRID, DRNAME, YYYY, ROWNO, MON

, CASE ROWNO WHEN 2 THEN '시작/진단' WHEN 3 THEN '시작/초진' WHEN 4 THEN '진단/초진' ELSE GUBUN END

) a

GROUP BY DRID, DRNAME, YYYY, ROWNO, GUBUN

ORDER BY DRNAME, YYYY, ROWNO;

좀 더 좋은 방법이 있을것 같은데 ㅠㅠ

착한넘님이 2014-04-14 09:51에 작성한 댓글입니다. Edit Ｘ

쿼리에 기준이 뭔지 모호하네요.
SUBSTR(MRDATE,5,2)
SUBSTR(MRDATE,5,3)
SUBSTR(MRDATE,6,2)
SUBSTR(NEWDATE,6,2)
왜 이렇게 다르게 사용하는거죠?
각각이 다른 의미를 가지고 사용된 것인지? 아니면 같은걸 잘못 사용한건지?

마농(manon94)님이 2014-04-15 15:18에 작성한 댓글입니다.
이 댓글은 2014-04-15 15:20에 마지막으로 수정되었습니다.

[Top]

No.	제목	작성자	작성일	조회
40432	[ORA-01438]초보자입니다. 도와주세요. [2]	이용훈	2014-04-16	6318
40431	오라클 쿼리에 몇가지 의문이 있어 질문 드립니다. 확인 좀 부탁드려요^^;; [2]	김종수	2014-04-16	7243
40429	[질문] 순차적으로 가로로 데이터를 추출한는 query 문의 드립니다. [13]	안동석	2014-04-14	8217
40428	난이도 있는? 쿼리 질문입니다. [3]	착한넘	2014-04-10	8303
40427	ORACLE 연결 Time out 문제 [1]	허양민	2014-04-10	6709
40426	오라클 셀렉트쿼리에 관해서 질문있어요 [2]	도토리	2014-04-09	7238
40425	오라클 커서 질문입니다.	서호현	2014-04-09	6510