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

Oracle Q&A 41515 게시물 읽기

No. 41515

표형태 쿼리 도움좀 주세요
작성자	김명찬	작성일	2018-01-23 00:24ⓒ 2018-01-23 13:09ⓜ	조회수	6,841

아래 표형태로 쿼리를 만들다가 실패를 했습니다.

도와주시면 감사하겠습니다.

조건은

담당자가 모두 2016, 2017년 다 있었던게 아닙니다.

또 담당자가 항상 1~12월 모두 있었던건 아닙니다.

외부 내부 다 있는것도 아닙니다.

테이블 1개에서 아래와 같은 구조로 만들어야 하는데

group by rollup 을 써가면서 했는데 잘 안되네요

부서	년도	담당자	구분	1월				2월				…	11월				12월				합계								평균
부서	년도	담당자	구분	A	B	C	합계	A	B	C	합계	…	A	B	C	합계	A	B	C	합계	A	A증가율	B	B증가율	C	C증가율	합계	합계증감율	A	B	C	합계
구매팀	2016	김구매	외부	5	6	0	11	3	2	3	8	…	5	6	0	11	3	2	3	8	50		49		37		136		4.2	4.1	3.1	11.3
			내부	12		3	15	7	4	1	12	…	12		3	15	7	4	1	12	105		120		80		305		8.8	10.0	6.7	25.4
			계	17	6	3	26	10	6	4	20	…	17	6	3	26	10	6	4	20	155		169		117		441		12.9	14.1	9.8	36.8
	2017	김구매	외부	5	6	0	11	3	2	3	8	…	5	6	0	11	3	2	3	8	80	60.0	74	51.0	100	170.3	254	86.8	6.7	6.2	8.3	21.2
			내부	12		3	15	7	4	1	12	…	12		3	15	7	4	1	12	110	4.8	150	25.0	89	11.3	349	14.4	9.2	12.5	7.4	29.1
			계	17	6	3	26	10	6	4	20	…	17	6	3	26	10	6	4	20	190	22.6	224	32.5	189	61.5	603	36.7	15.8	18.7	15.8	50.3
	2016	정구매	외부	0	0	0	0	0	0	0	0	…	0	0	0	0	2	5	1	8	2		5		1		8		0.2	0.4	0.1	0.7
			내부	0	0	0	0	0	0	0	0	…	0	0	0	0	16	4	7	27	16		4		7		27		1.3	0.3	0.6	2.3
			계	0	0	0	0	0	0	0	0	…	0	0	0	0	18	9	8	35	18		9		8		35		1.5	0.8	0.7	2.9
	2017	정구매	외부	3	2	3	8				0	…	6	2	3	11	3	2	4	9	80	3900.0	74	1380.0	100	9900.0	254	3075.0	6.7	6.2	8.3	21.2
			내부	7	4	1	12				0	…	7	2	9	18	7	3	6	16	110	587.5	150	3650.0	89	1171.4	349	1192.6	9.2	12.5	7.4	29.1
			계	10	6	4	20	0	0	0	0	…	13	4	12	29	10	5	10	25	190	955.6	224	2388.9	189	2262.5	603	1622.9	15.8	18.7	15.8	50.3
	2016	소계	외부	10	12	0	22	6	4	6	16	…	10	12	0	22	6	4	6	16	130		123		137		390		10.8	10.3	11.4	32.5
			내부	24	0	6	30	14	8	2	24	…	24	0	6	30	14	8	2	24	215		270		169		654		17.9	22.5	14.1	54.5
			계	34	12	6	52	20	12	8	40	…	34	12	6	52	20	12	8	40	345		393		306		1044		28.8	32.8	25.5	87.0
	2017	소계	외부	5	6	0	11	3	2	3	8	…	5	6	0	11	5	7	4	16	82	-36.9	79	-35.8	101	-26.3	262	-32.8	6.8	6.6	8.4	21.8
			내부	12	0	3	15	7	4	1	12	…	12	0	3	15	23	8	8	39	126	-41.4	154	-43.0	96	-43.2	376	-42.5	10.5	12.8	8.0	31.3
			계	17	6	3	26	10	6	4	20	…	17	6	3	26	28	15	12	55	208	-39.7	233	-40.7	197	-35.6	638	-38.9	17.3	19.4	16.4	53.2
개발	2016	박개발	외부				0				0	…				0				0
			내부				0				0	…				0				0
			계	0	0	0	0	0	0	0	0	…	0	0	0	0	0	0	0	0
	2017	박개발	외부				0				0	…				0				0
			내부				0				0	…				0				0
			계	0	0	0	0	0	0	0	0	…	0	0	0	0	0	0	0	0
	2016	조개발	외부				0				0	…				0				0
			내부				0				0	…				0				0
			계	0	0	0	0	0	0	0	0	…	0	0	0	0	0	0	0	0
	2017	조개발	외부				0				0	…				0				0
			내부				0				0	…				0				0
			계	0	0	0	0	0	0	0	0	…	0	0	0	0	0	0	0	0
	2016	소계	외부				0				0	…				0				0
			내부				0				0	…				0				0
			계	0	0	0	0	0	0	0	0	…	0	0	0	0	0	0	0	0
	2017	소계	외부				0				0	…				0				0
			내부				0				0	…				0				0
			계				0				0	…				0				0

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

증가율은 뭔가요?

원본 예시 자료 몇건 보여주시고

그걸 기준으로 결과표의 빈칸을 채워주세요.

마농(manon94)님이 2018-01-23 08:57에 작성한 댓글입니다.

파일 첨부를 못해 한참을 해맸습니다.

엑셀 임시 파일을 만든후 이미지 캡쳐해서 올렸습니다.

증가율은은 전년도(2016) 대비 금년도(2017)대비 증감율입니다.

평균은 임시로 나누기 12로 했습니다만.

2016년 정구매 담당자 같은경우 일한게 12월부터라

실제로는 나누기 근무월(여기서는 1)이 되면 좋긴 하겠습니다.

읽어주셔서 감사합니다.

크롬에서는 이미지가 보이고 ie에서는 보이지 않아 이미지 링크를 올려봅니다.

http://postfiles7.naver.net/MjAxODAxMjNfMTU0/MDAxNTE2NjcyNDg2MDU3.CcilKe61hSc7JEQE6dpsnOJkPXpxBlo1AcGid3T-xjsg.4R88q0pA46ilKFg2h-qqbPgqq5O1W1r36uYikMEgKlYg.PNG.twmari/%EB%85%84%EB%B9%84%EA%B5%90_%EC%9E%A5%ED%91%9C.png?type=w2

김명찬님이 2018-01-23 11:03에 작성한 댓글입니다.
이 댓글은 2018-01-23 11:08에 마지막으로 수정되었습니다. Edit Ｘ

이미지가 안보이네요?

이미지 보다는 그냥 엑셀 드래그 복사해서 그대로 붙여넣기 해주시는게 좋습니다.

마농(manon94)님이 2018-01-23 11:35에 작성한 댓글입니다.

WITH tmp_earn(earndate, part, damdang, gbn, a, b, c) AS
(
          SELECT '20170122', '구매팀', '김구매', '외부', 9, 9, 9 FROM dual
UNION ALL SELECT '20170122', '구매팀', '김구매', '내부', 6, 9, 6 FROM dual
UNION ALL SELECT '20170122', '구매팀', '정구매', '외부', 9, 9, 9 FROM dual
UNION ALL SELECT '20170122', '구매팀', '정구매', '내부', 6, 9, 6 FROM dual
UNION ALL SELECT '20160122', '구매팀', '정구매', '외부', 3, 3, 6 FROM dual
UNION ALL SELECT '20170222', '구매팀', '정구매', '내부', 3, 6, 9 FROM dual
UNION ALL SELECT '20170222', '구매팀', '정구매', '외부', 3, 6, 9 FROM dual
)
SELECT c.part
     , a.yyyy
     , NVL(c.damdang, '소계') damdang
     , NVL(b.gbn, '계') gbn
     , SUM(DECODE(mm, '01', a)) m01_a
     , SUM(DECODE(mm, '01', b)) m01_b
     , SUM(DECODE(mm, '01', c)) m01_c
     , SUM(DECODE(mm, '01', s)) m01_s
     , SUM(DECODE(mm, '02', a)) m02_a
     , SUM(DECODE(mm, '02', b)) m02_b
     , SUM(DECODE(mm, '02', c)) m02_c
     , SUM(DECODE(mm, '02', s)) m02_s
     , SUM(DECODE(mm, '03', a)) m03_a
     , SUM(DECODE(mm, '03', b)) m03_b
     , SUM(DECODE(mm, '03', c)) m03_c
     , SUM(DECODE(mm, '03', s)) m03_s
     , SUM(DECODE(mm, '04', a)) m04_a
     , SUM(DECODE(mm, '04', b)) m04_b
     , SUM(DECODE(mm, '04', c)) m04_c
     , SUM(DECODE(mm, '04', s)) m04_s
     , SUM(DECODE(mm, '05', a)) m05_a
     , SUM(DECODE(mm, '05', b)) m05_b
     , SUM(DECODE(mm, '05', c)) m05_c
     , SUM(DECODE(mm, '05', s)) m05_s
     , SUM(DECODE(mm, '06', a)) m06_a
     , SUM(DECODE(mm, '06', b)) m06_b
     , SUM(DECODE(mm, '06', c)) m06_c
     , SUM(DECODE(mm, '06', s)) m06_s
     , SUM(DECODE(mm, '07', a)) m07_a
     , SUM(DECODE(mm, '07', b)) m07_b
     , SUM(DECODE(mm, '07', c)) m07_c
     , SUM(DECODE(mm, '07', s)) m07_s
     , SUM(DECODE(mm, '08', a)) m08_a
     , SUM(DECODE(mm, '08', b)) m08_b
     , SUM(DECODE(mm, '08', c)) m08_c
     , SUM(DECODE(mm, '08', s)) m08_s
     , SUM(DECODE(mm, '09', a)) m09_a
     , SUM(DECODE(mm, '09', b)) m09_b
     , SUM(DECODE(mm, '09', c)) m09_c
     , SUM(DECODE(mm, '09', s)) m09_s
     , SUM(DECODE(mm, '10', a)) m10_a
     , SUM(DECODE(mm, '10', b)) m10_b
     , SUM(DECODE(mm, '10', c)) m10_c
     , SUM(DECODE(mm, '10', s)) m10_s
     , SUM(DECODE(mm, '11', a)) m11_a
     , SUM(DECODE(mm, '11', b)) m11_b
     , SUM(DECODE(mm, '11', c)) m11_c
     , SUM(DECODE(mm, '11', s)) m11_s
     , SUM(DECODE(mm, '12', a)) m12_a
     , SUM(DECODE(mm, '12', b)) m12_b
     , SUM(DECODE(mm, '12', c)) m12_c
     , SUM(DECODE(mm, '12', s)) m12_s
     , SUM(a) a
     , ROUND( SUM(a)
            / NULLIF(LAG(SUM(a)) OVER(PARTITION BY part, damdang, b.gbn
                                          ORDER BY a.yyyy), 0)
            * 100 - 100, 2) r_a
     , SUM(b) b
     , ROUND( SUM(b)
            / NULLIF(LAG(SUM(b)) OVER(PARTITION BY part, damdang, b.gbn
                                          ORDER BY a.yyyy), 0)
            * 100 - 100, 2) r_b
     , SUM(c) c
     , ROUND( SUM(c)
            / NULLIF(LAG(SUM(c)) OVER(PARTITION BY part, damdang, b.gbn
                                          ORDER BY a.yyyy), 0)
            * 100 - 100, 2) r_c
     , SUM(s) s
     , ROUND( SUM(s)
            / NULLIF(LAG(SUM(s)) OVER(PARTITION BY part, damdang, b.gbn
                                          ORDER BY a.yyyy), 0)
            * 100 - 100, 2) r_s
     , ROUND(AVG(a), 2) avg_a
     , ROUND(AVG(b), 2) avg_b
     , ROUND(AVG(c), 2) avg_c
     , ROUND(AVG(s), 2) avg_s
FROM (SELECT '2016' yyyy FROM dual
        UNION ALL SELECT '2017' FROM dual
        ) a
CROSS JOIN
       (SELECT '외부' gbn FROM dual
        UNION ALL SELECT '내부' FROM dual
        ) b
LEFT OUTER JOIN
       (SELECT part
             , SUBSTR(earndate, 1, 4) yyyy
             , damdang
             , gbn
             , SUBSTR(earndate, 5, 2) mm
             , SUM(a) a
             , SUM(b) b
             , SUM(c) c
             , SUM(a + b + c) s
          FROM tmp_earn
         WHERE earndate BETWEEN '20160101' AND '20171231'
         GROUP BY part
             , SUBSTR(earndate, 1, 4)
             , damdang
             , gbn
             , SUBSTR(earndate, 5, 2)
        ) c
PARTITION BY (c.part, c.damdang)
    ON a.yyyy = c.yyyy
   AND b.gbn = c.gbn
GROUP BY c.part, a.yyyy, CUBE(c.damdang, b.gbn)
ORDER BY c.part, c.damdang, a.yyyy
     , b.gbn DESC NULLS LAST
;

마농(manon94)님이 2018-01-23 13:08에 작성한 댓글입니다.
이 댓글은 2018-01-23 13:20에 마지막으로 수정되었습니다.

아 마농님 쿼리 너무 너무 감사합니다.

잘 사용하겠습니다.

공부 많이 하겠습니다. ^^

김명찬님이 2018-01-23 13:14에 작성한 댓글입니다. Edit Ｘ

마농님께 다시 부탁좀 드립니다.

열심히 원인을 찾아봤으나 실패해서 다시좀 질문드립니다.

1. 일단 실제 원 Data는 아래처럼 gbn이 외부 1줄 뿐입니다.

with 절에 실제 DATA

2. with 절 내에서 해당 부서(XX팀)만을 조건으로 넣으면

with 절에 해당부서만 조회했을때(정상)

처럼 정상적으로 보입니다.

3. 하지만 with절에 부서를 걸지 않았을때

with 절에 부서 전체일때(비정상)

처럼

1) XX팀이 아닌 다른 부서 사람들이 나타나며

2) 그나마 외부 내부 계 형태도 아닌 내부 계 형태로 외부가 표현도 되지 않습니다. 이 것은 XX팀이 아닌 다른부서에서도 나타나기도 합니다.


with 절에 실제 DATA
ENDDATE	PART	DAMDANG	GBN	A	B	C
20171102	XX팀	XX/YY담당	외부	0	2000	0

with 절에 해당부서만 조회했을때(정상)
PART	YYYY	DAMDANG	GBN	M01_A	M01_B	M01_C	M01_S	M11_A	M11_B	M11_C	M11_S	M12_A	M12_B	M12_C	M12_S	TOTA	R_A	TOTB	R_B	TOTC	R_C	TOTS	R_S	AVG_A	AVG_B	AVG_C	AVG_S
XX팀	2017	XX/YY담당	내부
			외부					0	2000	0	2000					0		2000		0		2000		0	166.67	0	166.67
			계					0	2000	0	2000					0		2000		0		2000		0	166.67	0	166.67
	2018	XX/YY담당	내부
			외부
			계
	2017	소계	내부
			외부					0	2000	0	2000					0		2000		0		2000		0	166.67	0	166.67
			계					0	2000	0	2000					0		2000		0		2000		0	166.67	0	166.67
	2018	소계	내부
			외부
			계


with 절에 부서 전체일때(비정상)
PART	YYYY	DAMDANG	GBN	M01_A	M01_B	M01_C	M01_S	M11_A	M11_B	M11_C	M11_S	M12_A	M12_B	M12_C	M12_S	TOTA	R_A	TOTB	R_B	TOTC	R_C	TOTS	R_S	AVG_A	AVG_B	AVG_C	AVG_S
XX팀	2017	김상미	내부
	2017	김상미	계
	2018	김상미	내부
			외부
			계
	…….
	2017	오지윤	내부
	2017	오지윤	계
	2018	오지윤	내부
			외부
			계
	2017	XX/YY담당	내부
			외부					0	2000	0	2000					0		2000		0		2000		0	166.67	0	166.67
			계					0	2000	0	2000					0		2000		0		2000		0	166.67	0	166.67
	2018	XX/YY담당	내부
			외부
			계
	2017	소계	내부
			외부					0	2000	0	2000					0		2000		0		2000		0	166.67	0	166.67
			계					0	2000	0	2000					0		2000		0		2000		0	166.67	0	166.67
	2018	소계	내부
			외부
			계

김명찬님이 2018-01-24 13:18에 작성한 댓글입니다. Edit Ｘ

저는 다른 부서 자료 추가 후 테스트 해봐도 정상으로 나오네요.
실제 적용하신 쿼리를 보여주세요.
제가 드린 쿼리와 어딘가 다른 부분이 있을 것 같네요.

아! 뭔지 알 것 같네요.
김상미와 오지윤이 왜 거기서 나와? 이거죠?
조건절에서 실수하신 듯 하네요.
년도는 '2017', '2018' 인데
날짜 조건을 안바꾸신 듯
WHERE earndate BETWEEN '20160101' AND '20171231' -- 안 바꾼 듯
WHERE earndate BETWEEN '20170101' AND '20181231' -- 요렇게 바꿔야 함
아마도 김상미와 오지윤은 2016년 자료에 있을 것 같네요.

마농(manon94)님이 2018-01-24 14:26에 작성한 댓글입니다.
이 댓글은 2018-01-24 14:34에 마지막으로 수정되었습니다.

이렇게 했습니다..

아무리 비교해봐도 유추해 볼수 있는건

with 절 내에 데이타가 아닐까 싶은데 정확히 모르겠습니다.

WITH tmp_earn(earndate, part, damdang, gbn, a, b, c) AS

(

SELECT

earndate

, com.FN_ZS_GETDEPTNAME('053', a.ORDDEPTCD, to_char(sysdate,'yyyymmdd')) as part

, com.FN_ZS_GETUSERNM (a.userid, to_char(sysdate,'yyyymmdd')) as damdang

, decode(IOtype, 'O','외부','I','내부', IOtype) as gbn

, (case when matflag in ('1','2','3','5') then 0 else sum(pay + nopy + allown + spec) end) as suma

, (case when matflag in ('1','2','3','5') then sum(pay + nopy + allown + spec) else 0 end) as sumb

, sum(dr) as sumc

FROM dres a

WHERE instcd = '053'

AND earndate between '20170101' and '20181231'

AND IOtype != 'S'

GROUP BY earndate, orddeptcd, userid, IOtype, matflag

)

SELECT c.part

, a.yyyy

, NVL(c.damdang, '소계') damdang

, NVL(b.gbn, '계') gbn

, SUM(DECODE(mm, '01', a)) m01_a

, SUM(DECODE(mm, '01', b)) m01_b

, SUM(DECODE(mm, '01', c)) m01_c

, SUM(DECODE(mm, '01', s)) m01_s

-- 중간 생략

, SUM(DECODE(mm, '11', a)) m11_a

, SUM(DECODE(mm, '11', b)) m11_b

, SUM(DECODE(mm, '11', c)) m11_c

, SUM(DECODE(mm, '11', s)) m11_s

, SUM(DECODE(mm, '12', a)) m12_a

, SUM(DECODE(mm, '12', b)) m12_b

, SUM(DECODE(mm, '12', c)) m12_c

, SUM(DECODE(mm, '12', s)) m12_s

, SUM(a) a

, ROUND( SUM(a)

/ NULLIF(LAG(SUM(a)) OVER(PARTITION BY part, damdang, b.gbn

ORDER BY a.yyyy), 0) * 100 - 100, 2) r_a

, SUM(b) b

, ROUND( SUM(b)

/ NULLIF(LAG(SUM(b)) OVER(PARTITION BY part, damdang, b.gbn

ORDER BY a.yyyy), 0) * 100 - 100, 2) r_b

, SUM(c) c

, ROUND( SUM(c)

/ NULLIF(LAG(SUM(c)) OVER(PARTITION BY part, damdang, b.gbn

ORDER BY a.yyyy), 0) * 100 - 100, 2) r_c

, SUM(s) s

, ROUND( SUM(s)

/ NULLIF(LAG(SUM(s)) OVER(PARTITION BY part, damdang, b.gbn

ORDER BY a.yyyy), 0) * 100 - 100, 2) r_s

, ROUND(AVG(a), 2) avg_a

, ROUND(AVG(b), 2) avg_b

, ROUND(AVG(c), 2) avg_c

, ROUND(AVG(s), 2) avg_s

FROM (SELECT '2017' yyyy FROM dual

UNION ALL SELECT '2018' FROM dual

) a

CROSS JOIN

(SELECT '외부' gbn FROM dual

UNION ALL SELECT '내부' FROM dual

) b

LEFT OUTER JOIN

(SELECT part

, SUBSTR(earndate, 1, 4) yyyy

, damdang

, gbn

, SUBSTR(earndate, 5, 2) mm

, SUM(a) a

, SUM(b) b

, SUM(c) c

, SUM(a + b + c) s

FROM tmp_earn

WHERE earndate BETWEEN '20170101' AND '20181231'

GROUP BY part

, SUBSTR(earndate, 1, 4)

, damdang

, gbn

, SUBSTR(earndate, 5, 2)

) c

PARTITION BY (c.part, c.damdang)

ON a.yyyy = c.yyyy

AND b.gbn = c.gbn

GROUP BY c.part, a.yyyy, CUBE(c.damdang, b.gbn)

ORDER BY c.part, c.damdang, a.yyyy

, b.gbn NULLS LAST

;

김명찬님이 2018-01-24 15:42에 작성한 댓글입니다. Edit Ｘ

with 절에 해당부서만 조회했을때(정상) 이라는게...

- (WHERE instcd = '053') 이 조건을 준걸 말하는 건가요?

with 절에 부서 전체일때(비정상) 이라는게...

- (WHERE instcd = '053') 이 조건을 뺀걸 말하는 건가요?

- 혹시 이조건 빼면서 함수에서 사용한 상수 '053' 은 그대로 둔거 아닌가요?

- 함수에서 상수값 '053' 대신 컬럼 instcd 로 바꾸셔야 하는 것 아닌지?

- 변경전 : com.FN_ZS_GETDEPTNAME('053', a.ORDDEPTCD, to_char(sysdate,'yyyymmdd')) as part

- 변경후 : com.FN_ZS_GETDEPTNAME(instcd, a.ORDDEPTCD, to_char(sysdate,'yyyymmdd')) as part

마농(manon94)님이 2018-01-24 16:49에 작성한 댓글입니다.
이 댓글은 2018-01-24 16:50에 마지막으로 수정되었습니다.

감사합니다. 내용은 아래와 같이 한것입니다.

WITH tmp_earn(earndate, part, damdang, gbn, a, b, c) AS

(

SELECT

earndate

, com.FN_ZS_GETDEPTNAME('053', a.ORDDEPTCD, to_char(sysdate,'yyyymmdd')) as part

, com.FN_ZS_GETUSERNM (a.userid, to_char(sysdate,'yyyymmdd')) as damdang

, decode(IOtype, 'O','외부','I','내부', IOtype) as gbn

, (case when matflag in ('1','2','3','5') then 0 else sum(pay + nopy + allown + spec) end) as suma

, (case when matflag in ('1','2','3','5') then sum(pay + nopy + allown + spec) else 0 end) as sumb

, sum(dr) as sumc

FROM dres a

WHERE instcd = '053' <- 이건 고정입니다

AND earndate between '20170101' and '20181231'

AND IOtype != 'S'

AND orddeptcd = 'WAD' <- 이렇게 해당 부서만 보였을때 말한겁니다

GROUP BY earndate, orddeptcd, userid, IOtype, matflag

)

김명찬님이 2018-01-24 17:09에 작성한 댓글입니다.
이 댓글은 2018-01-24 17:14에 마지막으로 수정되었습니다. Edit Ｘ

오류 데이터를 샘플로 뽑아 주실수 있는지요?

마농(manon94)님이 2018-01-24 17:24에 작성한 댓글입니다.

with 절에 해당 하는 내역을 엑셀에 넣어뒀는데

전달을 어떻게 해야 할지요?

김명찬님이 2018-01-24 17:43에 작성한 댓글입니다. Edit Ｘ

엑셀 드래그 선택 > Ctrl+C > 댓글쓰기창 > Ctrl+V

마농(manon94)님이 2018-01-24 17:52에 작성한 댓글입니다.

Data 자체가 문제일지 몰라 전체를 Drag 해서 올렸더니

많은지 올라가질 않네요..

김명찬님이 2018-01-24 19:01에 작성한 댓글입니다.
이 댓글은 2018-01-24 19:01에 마지막으로 수정되었습니다. Edit Ｘ

전체 말고 샘플요.

마농(manon94)님이 2018-01-25 08:28에 작성한 댓글입니다.
이 댓글은 2018-01-25 08:28에 마지막으로 수정되었습니다.

해당 내역을 이 쿼리로 하면 제가 말한 내용이 나옵니다.

지금 확인해보니.. 김상미라는 담당자가 부서가 1개가 아닌 2군데로 되어 있습니다.

확인결과 김상미는 실제 부서가 2군데 소속입니다.

아마 이런 케이스는 다 해당될듯합니다.

감사합니다.

PART	YYYY	DAMDANG	GBN
류마팀	2017	송승택	내부
류마팀	2017	송승택	외부
류마팀	2017	송승택	계
류마팀	2018	송승택	내부
류마팀	2018	송승택	외부
류마팀	2018	송승택	계
류마팀	2017	소계	내부
류마팀	2017	소계	외부
류마팀	2017	소계	계
류마팀	2018	소계	내부
류마팀	2018	소계	외부
류마팀	2018	소계	계
응센팀	2017	김상미	외부
응센팀	2017	김상미	계
응센팀	2017	송승택	내부
응센팀	2017	송승택	계
응센팀	2018	송승택	내부
응센팀	2018	송승택	외부
응센팀	2018	송승택	계
응센팀	2017	소계	내부
응센팀	2017	소계	외부
응센팀	2017	소계	계
응센팀	2018	소계	내부
응센팀	2018	소계	외부
응센팀	2018	소계	계
이인팀	2017	김상미	내부
이인팀	2017	김상미	외부
이인팀	2017	김상미	계
이인팀	2018	김상미	내부
이인팀	2018	김상미	외부
이인팀	2018	김상미	계

WITH tmp_earn(earndate, part, damdang, gbn, a, b, c) AS

(

select '20171115','XX팀','XX/YY담당','외부','0','2000','0' from dual union all

select '20171117','응센팀','김상미','외부','0','7000','0' from dual union all

select '20171121','응센팀','김상미','외부','0','3000','0' from dual union all

select '20171206','이인팀','김상미','내부','0','3000','0' from dual union all

select '20171101','류마팀','송승택','내부','44599','262349','0' from dual union all

select '20171101','류마팀','송승택','외부','1616728','1789524','0' from dual union all

select '20171209','정건팀','이재영','외부','1770507','1096093','0' from dual union all

select '20180122','정형팀','김태형','내부','1400306','3101099','0' from dual union all

select '20180122','정형팀','김태형','외부','0','685278','0' from dual

)

SELECT c.part

, a.yyyy

, NVL(c.damdang, '소계') damdang

, NVL(b.gbn, '계') gbn

, SUM(DECODE(mm, '01', a)) m01_a

, SUM(DECODE(mm, '01', b)) m01_b

, SUM(DECODE(mm, '01', c)) m01_c

, SUM(DECODE(mm, '01', s)) m01_s

, SUM(DECODE(mm, '11', a)) m11_a

, SUM(DECODE(mm, '11', b)) m11_b

, SUM(DECODE(mm, '11', c)) m11_c

, SUM(DECODE(mm, '11', s)) m11_s

, SUM(DECODE(mm, '12', a)) m12_a

, SUM(DECODE(mm, '12', b)) m12_b

, SUM(DECODE(mm, '12', c)) m12_c

, SUM(DECODE(mm, '12', s)) m12_s

, SUM(a) a

, ROUND( SUM(a) / NULLIF(LAG(SUM(a)) OVER(PARTITION BY part, damdang, b.gbn ORDER BY a.yyyy), 0) * 100 - 100, 2) r_a

, SUM(b) b

, ROUND( SUM(b) / NULLIF(LAG(SUM(b)) OVER(PARTITION BY part, damdang, b.gbn ORDER BY a.yyyy), 0) * 100 - 100, 2) r_b

, SUM(c) c

, ROUND( SUM(c) / NULLIF(LAG(SUM(c)) OVER(PARTITION BY part, damdang, b.gbn ORDER BY a.yyyy), 0) * 100 - 100, 2) r_c

, SUM(s) s

, ROUND( SUM(s) / NULLIF(LAG(SUM(s)) OVER(PARTITION BY part, damdang, b.gbn ORDER BY a.yyyy), 0) * 100 - 100, 2) r_s

, ROUND(AVG(a), 2) avg_a

, ROUND(AVG(b), 2) avg_b

, ROUND(AVG(c), 2) avg_c

, ROUND(AVG(s), 2) avg_s

FROM (SELECT '2017' yyyy FROM dual

UNION ALL SELECT '2018' FROM dual

) a

CROSS JOIN

(SELECT '외부' gbn FROM dual

UNION ALL SELECT '내부' FROM dual

) b

LEFT OUTER JOIN

(SELECT part

, SUBSTR(earndate, 1, 4) yyyy

, damdang

, gbn

, SUBSTR(earndate, 5, 2) mm

, SUM(a) a

, SUM(b) b

, SUM(c) c

, SUM(a + b + c) s

FROM tmp_earn

WHERE earndate BETWEEN '20170101' AND '20181231'

GROUP BY part, SUBSTR(earndate, 1, 4), damdang, gbn, SUBSTR(earndate, 5, 2)

) c

PARTITION BY (c.part, c.damdang)

ON a.yyyy = c.yyyy

AND b.gbn = c.gbn

GROUP BY c.part, a.yyyy, CUBE(c.damdang, b.gbn)

ORDER BY c.part, c.damdang, a.yyyy, b.gbn NULLS LAST

;

김명찬님이 2018-01-25 09:31에 작성한 댓글입니다. Edit Ｘ

감사하고 또 자꾸 귀찮게 해드려 죄송합니다.

with 절 안을 아래처럼 몇개 없앴는데요

XX/YY담당은 XX팀인데 응센팀에도 생기네요

역시 내부외부 계가 아닌 내부 계 형태이기도 하구요

감사합니다.

PART	YYYY	DAMDANG	GBN
XX팀	2017	XX/YY담당	내부
XX팀	2017	XX/YY담당	외부
XX팀	2017	XX/YY담당	계
XX팀	2018	XX/YY담당	내부
XX팀	2018	XX/YY담당	외부
XX팀	2018	XX/YY담당	계
XX팀	2017	소계	내부
XX팀	2017	소계	외부
XX팀	2017	소계	계
XX팀	2018	소계	내부
XX팀	2018	소계	외부
XX팀	2018	소계	계
응센팀	2017	XX/YY담당	내부
응센팀	2017	XX/YY담당	계
응센팀	2018	XX/YY담당	내부
응센팀	2018	XX/YY담당	외부
응센팀	2018	XX/YY담당	계
응센팀	2017	김상미	외부
응센팀	2017	김상미	계
응센팀	2017	소계	내부
응센팀	2017	소계	외부
응센팀	2017	소계	계
응센팀	2018	소계	내부
응센팀	2018	소계	외부
응센팀	2018	소계	계
이인팀	2017	김상미	내부
이인팀	2017	김상미	외부
이인팀	2017	김상미	계
이인팀	2018	김상미	내부
이인팀	2018	김상미	외부
이인팀	2018	김상미	계

WITH tmp_earn(earndate, part, damdang, gbn, a, b, c) AS

(

select '20171115','XX팀','XX/YY담당','외부','0','2000','0' from dual union all

select '20171117','응센팀','김상미','외부','0','7000','0' from dual union all

select '20171121','응센팀','김상미','외부','0','3000','0' from dual union all

select '20171206','이인팀','김상미','내부','0','3000','0' from dual union all

select '20180122','정형팀','김태형','내부','1400306','3101099','0' from dual union all

select '20180122','정형팀','김태형','외부','0','685278','0' from dual

)

김명찬님이 2018-01-25 09:46에 작성한 댓글입니다. Edit Ｘ

저는 잘 나오네요. 11G EE 11.2.0.1.0
버그인 듯.
Partition Outer Join 대신 다르게 풀어볼께요.

SELECT c.part
     , a.yyyy
     , NVL(c.damdang, '소계') damdang
     , NVL(b.gbn, '계') gbn
     , SUM(DECODE(mm, '01', a)) m01_a
     , SUM(DECODE(mm, '01', b)) m01_b
     , SUM(DECODE(mm, '01', c)) m01_c
     , SUM(DECODE(mm, '01', s)) m01_s
     , SUM(DECODE(mm, '11', a)) m11_a
     , SUM(DECODE(mm, '11', b)) m11_b
     , SUM(DECODE(mm, '11', c)) m11_c
     , SUM(DECODE(mm, '11', s)) m11_s
     , SUM(DECODE(mm, '12', a)) m12_a
     , SUM(DECODE(mm, '12', b)) m12_b
     , SUM(DECODE(mm, '12', c)) m12_c
     , SUM(DECODE(mm, '12', s)) m12_s
     , SUM(a) a
     , ROUND( SUM(a) / NULLIF(LAG(SUM(a)) OVER(PARTITION BY c.part, c.damdang, b.gbn ORDER BY a.yyyy), 0) * 100 - 100, 2) r_a
     , SUM(b) b
     , ROUND( SUM(b) / NULLIF(LAG(SUM(b)) OVER(PARTITION BY c.part, c.damdang, b.gbn ORDER BY a.yyyy), 0) * 100 - 100, 2) r_b
     , SUM(c) c
     , ROUND( SUM(c) / NULLIF(LAG(SUM(c)) OVER(PARTITION BY c.part, c.damdang, b.gbn ORDER BY a.yyyy), 0) * 100 - 100, 2) r_c
     , SUM(s) s
     , ROUND( SUM(s) / NULLIF(LAG(SUM(s)) OVER(PARTITION BY c.part, c.damdang, b.gbn ORDER BY a.yyyy), 0) * 100 - 100, 2) r_s
     , ROUND(AVG(a), 2) avg_a
     , ROUND(AVG(b), 2) avg_b
     , ROUND(AVG(c), 2) avg_c
     , ROUND(AVG(s), 2) avg_s
FROM (SELECT '2017' yyyy FROM dual
        UNION ALL SELECT '2018' FROM dual
        ) a
CROSS JOIN
       (SELECT '외부' gbn FROM dual
        UNION ALL SELECT '내부' FROM dual
        ) b
CROSS JOIN
       (SELECT DISTINCT part, damdang
          FROM tmp_earn
         WHERE earndate BETWEEN '20170101' AND '20181231' -- With 안에 넣는게 좋을 듯
        ) c
LEFT OUTER JOIN
       (SELECT part
             , SUBSTR(earndate, 1, 4) yyyy
             , damdang
             , gbn
             , SUBSTR(earndate, 5, 2) mm
             , SUM(a) a
             , SUM(b) b
             , SUM(c) c
             , SUM(a + b + c) s
          FROM tmp_earn
         WHERE earndate BETWEEN '20170101' AND '20181231' -- With 안에 넣는게 좋을 듯
         GROUP BY part, SUBSTR(earndate, 1, 4), damdang, gbn, SUBSTR(earndate, 5, 2)
        ) d
    ON a.yyyy    = d.yyyy
   AND b.gbn     = d.gbn
   AND c.part    = d.part
   AND c.damdang = d.damdang
GROUP BY c.part, a.yyyy, CUBE(c.damdang, b.gbn)
ORDER BY c.part, c.damdang, a.yyyy, b.gbn NULLS LAST
;

마농(manon94)님이 2018-01-25 09:58에 작성한 댓글입니다.
이 댓글은 2018-01-25 10:18에 마지막으로 수정되었습니다.

아 네 알겠습니다.

감사합니다. 확인해보겠습니다. ^^

김명찬님이 2018-01-25 10:12에 작성한 댓글입니다. Edit Ｘ

마농(manon94)님 대안으로 주신 쿼리로 잘 됩니다.

귀찮게 해서 죄송하고

너무 너무 감사드립니다. ^^

김명찬님이 2018-01-25 11:26에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
41518	한글 <ㅡ> 영문 변환 관련 질문입니다. [1]	허광민	2018-01-29	6414
41517	저번에 마농님이 봐 주신 sql 입니다. [1]	봉달이	2018-01-26	6363
41516	oracle client12c -> Linux redhat 7.4에 설치시 오류 [1]	oracle	2018-01-24	6114
41515	표형태 쿼리 도움좀 주세요 [20]	김명찬	2018-01-23	6841
41514	결과가 한줄로 나옵니다. [1]	초보자	2018-01-19	6151
41513	가로의 결과를 세로로 [2]	봉달이	2018-01-18	7116
41512	characterset 다른 2개 서버로 클라이언트에서 접속 문의	개발자	2018-01-16	5818