database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41515 게시물 읽기
No. 41515
표형태 쿼리 도움좀 주세요
작성자
김명찬
작성일
2018-01-23 00:24:32ⓒ
2018-01-23 13:09:43ⓜ
조회수
1,351

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

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

조건은

담당자가 모두 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:06에 작성한 댓글입니다.

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

 

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

 

증가율은은 전년도(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:09에 작성한 댓글입니다.
이 댓글은 2018-01-23 11:08:14에 마지막으로 수정되었습니다. Edit

이미지가 안보이네요?

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

마농(manon94)님이 2018-01-23 11:35: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:39에 작성한 댓글입니다.
이 댓글은 2018-01-23 13:20:33에 마지막으로 수정되었습니다.

 

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

 

잘 사용하겠습니다.

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

김명찬님이 2018-01-23 13:14:08에 작성한 댓글입니다. 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 김상미 내부                                                
                                                 
  2018 김상미 내부                                                
  외부                                                
                                                 
  …….
  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:01에 작성한 댓글입니다. Edit

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


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

마농(manon94)님이 2018-01-24 14:26:42에 작성한 댓글입니다.
이 댓글은 2018-01-24 14:34:55에 마지막으로 수정되었습니다.
이렇게 했습니다.. 
아무리 비교해봐도 유추해 볼수 있는건
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:15에 작성한 댓글입니다. 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:55에 작성한 댓글입니다.
이 댓글은 2018-01-24 16:50:37에 마지막으로 수정되었습니다.

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

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:41에 작성한 댓글입니다.
이 댓글은 2018-01-24 17:14:25에 마지막으로 수정되었습니다. Edit

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

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

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

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

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

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

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

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

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

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

전체 말고 샘플요.

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

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

지금 확인해보니.. 김상미라는 담당자가 부서가 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:23에 작성한 댓글입니다. 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:37에 작성한 댓글입니다. 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:10에 작성한 댓글입니다.
이 댓글은 2018-01-25 10:18:29에 마지막으로 수정되었습니다.

아 네 알겠습니다. 

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

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

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

귀찮게 해서 죄송하고

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

김명찬님이 2018-01-25 11:26:56에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41518한글 <ㅡ> 영문 변환 관련 질문입니다. [1]
허광민
2018-01-29
1201
41517저번에 마농님이 봐 주신 sql 입니다. [1]
봉달이
2018-01-26
1247
41516oracle client12c -> Linux redhat 7.4에 설치시 오류 [1]
oracle
2018-01-24
1011
41515표형태 쿼리 도움좀 주세요 [20]
김명찬
2018-01-23
1351
41514결과가 한줄로 나옵니다. [1]
초보자
2018-01-19
1154
41513가로의 결과를 세로로 [2]
봉달이
2018-01-18
1259
41512characterset 다른 2개 서버로 클라이언트에서 접속 문의
개발자
2018-01-16
961
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2018 DSN, All rights reserved.
작업시간: 0.550초, 이곳 서비스는
	PostgreSQL v10.4로 자료를 관리합니다