꿈꾸는 개발자, DBA 커뮤니티 구루비

[도움요청]오라클 쿼리 일자별 그룹을 DECODE, PIVOT 등 쿼리로 해결이 안되어 도움을 요청합니다.

by 오바라미 [SQL Query] QUERY [2022.12.01 10:35:39]

오라클 문의.xlsx (12,109Bytes)

안녕하세요.
부서별 일정관리를 만들고 있는데요.
쿼리가 잘 안되서 문의드립니다.

DB : 오라클 11G

- 부서/일자로 등록된 일정을 부서별, 당일 / 익일~5일까지 / 6일~10일까지 / 11일~20일까지 / 21일~30일까지
그룹핑하여 부서별 상단부터 순차적으로 표현하려고 합니다.

- 글로 표현하려니 잘안되어서 내용을 엑셀파일로 첨부합니다

.- DECODE, PIVOT, RANK NUMBER으로 다 해봤는데도
제가 스킬이 부족한지 결과값이 잘 안나옵니다. 쿼리한계가 오네요 ㅠㅠ

- 제가 어떤함수, 어떻게 쿼리를 짜야 엑셀파일처럼 결과가 나올수 있을까요?

- 수일째 동일한 쿼리로 고민고민 하다가 해결하지 못하여
도움을 받고자 글을 올립니다.

감사합니다.

아래는 관련 쿼리입니다.

----------------------------

WITH W_DATA AS
(
SELECT '2022-12-01' 일자, '01' 전표번호, '입고예정1' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-02' 일자, '02' 전표번호, '입고예정2' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-03' 일자, '03' 전표번호, '입고예정3' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-04' 일자, '04' 전표번호, '입고예정4' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-05' 일자, '05' 전표번호, '입고예정5' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-06' 일자, '06' 전표번호, '입고예정6' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-07' 일자, '07' 전표번호, '입고예정7' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-08' 일자, '08' 전표번호, '입고예정8' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-09' 일자, '09' 전표번호, '입고예정9' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-10' 일자, '10' 전표번호, '입고예정10' 제목, '생산' 부서 FROM DUAL UNION ALL
SELECT '2022-12-01' 일자, '11' 전표번호, '지시1' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-02' 일자, '12' 전표번호, '지시2' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-03' 일자, '13' 전표번호, '지시3' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-04' 일자, '14' 전표번호, '지시4' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-05' 일자, '15' 전표번호, '지시5' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-06' 일자, '16' 전표번호, '지시6' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-07' 일자, '17' 전표번호, '지시7' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-08' 일자, '18' 전표번호, '지시8' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-09' 일자, '19' 전표번호, '지시9' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-10' 일자, '20' 전표번호, '지시10' 제목, 'MD' 부서 FROM DUAL UNION ALL
SELECT '2022-12-11' 일자, '21' 전표번호, '출고1' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-12' 일자, '22' 전표번호, '출고2' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-13' 일자, '23' 전표번호, '출고3' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-14' 일자, '24' 전표번호, '출고4' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-15' 일자, '25' 전표번호, '출고5' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-16' 일자, '26' 전표번호, '출고6' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-17' 일자, '27' 전표번호, '출고7' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-18' 일자, '28' 전표번호, '출고8' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-19' 일자, '29' 전표번호, '출고9' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-20' 일자, '30' 전표번호, '출고10' 제목, '물류' 부서 FROM DUAL UNION ALL
SELECT '2022-12-11' 일자, '31' 전표번호, '판매A 1' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-12' 일자, '32' 전표번호, '판매A 2' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-13' 일자, '33' 전표번호, '판매A 3' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-14' 일자, '34' 전표번호, '판매A 4' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-15' 일자, '35' 전표번호, '판매A 5' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-16' 일자, '36' 전표번호, '판매A 6' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-17' 일자, '37' 전표번호, '판매A 7' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-18' 일자, '38' 전표번호, '판매A 8' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-19' 일자, '39' 전표번호, '판매A 9' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-20' 일자, '40' 전표번호, '판매A 10' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-21' 일자, '41' 전표번호, '판매B 1' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-22' 일자, '42' 전표번호, '판매B 2' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-23' 일자, '43' 전표번호, '판매B 3' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-24' 일자, '44' 전표번호, '판매B 4' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-25' 일자, '45' 전표번호, '판매B 5' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-26' 일자, '46' 전표번호, '판매B 6' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-27' 일자, '47' 전표번호, '판매B 7' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-28' 일자, '48' 전표번호, '판매B 8' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-29' 일자, '49' 전표번호, '판매B 9' 제목, '영업' 부서 FROM DUAL UNION ALL
SELECT '2022-12-30' 일자, '50' 전표번호, '판매B 10' 제목, '영업' 부서 FROM DUAL
)

SELECT 부서, 일자그룹, 전표번호, 제목
FROM (
SELECT 부서, 전표번호, 제목
, CASE WHEN 일자 = '2022-12-01' THEN '당일'
WHEN 일자 BETWEEN TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 1, 'YYYY-MM-DD') AND TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 5, 'YYYY-MM-DD') THEN '5일전'
WHEN 일자 BETWEEN TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 6, 'YYYY-MM-DD') AND TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 10, 'YYYY-MM-DD') THEN '10일전'
WHEN 일자 BETWEEN TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 11, 'YYYY-MM-DD') AND TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 20, 'YYYY-MM-DD') THEN '20일전'
WHEN 일자 BETWEEN TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 21, 'YYYY-MM-DD') AND TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 30, 'YYYY-MM-DD') THEN '30일전'
END 일자그룹
FROM W_DATA
WHERE 일자 BETWEEN TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD'), 'YYYY-MM-DD') AND TO_CHAR(TO_DATE('2022-12-01','YYYY-MM-DD') + 30, 'YYYY-MM-DD')
)

ORDER BY 부서

by 마농 [2022.12.01 11:23:43]

WITH w_data AS
(
SELECT '2022-12-01' 일자, '01' 전표번호, '입고예정1' 제목, '생산' 부서 FROM dual
UNION ALL SELECT '2022-12-02', '02', '입고예정2' , '생산' FROM dual
UNION ALL SELECT '2022-12-03', '03', '입고예정3' , '생산' FROM dual
UNION ALL SELECT '2022-12-04', '04', '입고예정4' , '생산' FROM dual
UNION ALL SELECT '2022-12-05', '05', '입고예정5' , '생산' FROM dual
UNION ALL SELECT '2022-12-06', '06', '입고예정6' , '생산' FROM dual
UNION ALL SELECT '2022-12-07', '07', '입고예정7' , '생산' FROM dual
UNION ALL SELECT '2022-12-08', '08', '입고예정8' , '생산' FROM dual
UNION ALL SELECT '2022-12-09', '09', '입고예정9' , '생산' FROM dual
UNION ALL SELECT '2022-12-10', '10', '입고예정10', '생산' FROM dual
UNION ALL SELECT '2022-12-01', '11', '지시1'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-02', '12', '지시2'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-03', '13', '지시3'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-04', '14', '지시4'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-05', '15', '지시5'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-06', '16', '지시6'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-07', '17', '지시7'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-08', '18', '지시8'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-09', '19', '지시9'     , 'MD'   FROM dual
UNION ALL SELECT '2022-12-10', '20', '지시10'    , 'MD'   FROM dual
UNION ALL SELECT '2022-12-11', '21', '출고1'     , '물류' FROM dual
UNION ALL SELECT '2022-12-12', '22', '출고2'     , '물류' FROM dual
UNION ALL SELECT '2022-12-13', '23', '출고3'     , '물류' FROM dual
UNION ALL SELECT '2022-12-14', '24', '출고4'     , '물류' FROM dual
UNION ALL SELECT '2022-12-15', '25', '출고5'     , '물류' FROM dual
UNION ALL SELECT '2022-12-16', '26', '출고6'     , '물류' FROM dual
UNION ALL SELECT '2022-12-17', '27', '출고7'     , '물류' FROM dual
UNION ALL SELECT '2022-12-18', '28', '출고8'     , '물류' FROM dual
UNION ALL SELECT '2022-12-19', '29', '출고9'     , '물류' FROM dual
UNION ALL SELECT '2022-12-20', '30', '출고10'    , '물류' FROM dual
UNION ALL SELECT '2022-12-11', '31', '판매A 1'   , '영업' FROM dual
UNION ALL SELECT '2022-12-12', '32', '판매A 2'   , '영업' FROM dual
UNION ALL SELECT '2022-12-13', '33', '판매A 3'   , '영업' FROM dual
UNION ALL SELECT '2022-12-14', '34', '판매A 4'   , '영업' FROM dual
UNION ALL SELECT '2022-12-15', '35', '판매A 5'   , '영업' FROM dual
UNION ALL SELECT '2022-12-16', '36', '판매A 6'   , '영업' FROM dual
UNION ALL SELECT '2022-12-17', '37', '판매A 7'   , '영업' FROM dual
UNION ALL SELECT '2022-12-18', '38', '판매A 8'   , '영업' FROM dual
UNION ALL SELECT '2022-12-19', '39', '판매A 9'   , '영업' FROM dual
UNION ALL SELECT '2022-12-20', '40', '판매A 10'  , '영업' FROM dual
UNION ALL SELECT '2022-12-21', '41', '판매B 1'   , '영업' FROM dual
UNION ALL SELECT '2022-12-22', '42', '판매B 2'   , '영업' FROM dual
UNION ALL SELECT '2022-12-23', '43', '판매B 3'   , '영업' FROM dual
UNION ALL SELECT '2022-12-24', '44', '판매B 4'   , '영업' FROM dual
UNION ALL SELECT '2022-12-25', '45', '판매B 5'   , '영업' FROM dual
UNION ALL SELECT '2022-12-26', '46', '판매B 6'   , '영업' FROM dual
UNION ALL SELECT '2022-12-27', '47', '판매B 7'   , '영업' FROM dual
UNION ALL SELECT '2022-12-28', '48', '판매B 8'   , '영업' FROM dual
UNION ALL SELECT '2022-12-29', '49', '판매B 9'   , '영업' FROM dual
UNION ALL SELECT '2022-12-30', '50', '판매B 10'  , '영업' FROM dual
)
SELECT *
  FROM (SELECT 부서, 전표번호, 제목
             , gb
             , ROW_NUMBER() OVER(PARTITION BY 부서, gb ORDER BY 일자) rn
          FROM (SELECT 부서, 전표번호, 제목, 일자
                     , CASE WHEN 일자 <= TO_CHAR(dt +  0, 'yyyy-mm-dd') THEN 1
                            WHEN 일자 <= TO_CHAR(dt +  5, 'yyyy-mm-dd') THEN 2
                            WHEN 일자 <= TO_CHAR(dt + 10, 'yyyy-mm-dd') THEN 3
                            WHEN 일자 <= TO_CHAR(dt + 20, 'yyyy-mm-dd') THEN 4
                            ELSE 5 END gb
                  FROM w_data
                --     , (SELECT TO_DATE('2022-12-01', 'yyyy-mm-dd') dt FROM dual)
                     , (SELECT TRUNC(sysdate) dt FROM dual)
                 WHERE 일자 BETWEEN TO_CHAR(dt +  0, 'yyyy-mm-dd')
                                AND TO_CHAR(dt + 30, 'yyyy-mm-dd')
                )
        )
 PIVOT (MIN(전표번호) 전표번호, MIN(제목) 제목
        FOR gb IN ( 1 "당일"
                  , 2 "5일전"
                  , 3 "10일전"
                  , 4 "20일전"
                  , 5 "30일전"
                  ) )
 ORDER BY 부서, rn
;

by 오바라미 [2022.12.01 12:09:35]

아... 정말 감사드립니다.

간단하네요..

PIVOT함수를 정확하게 이해를 못한상태에서 작성하려니 제가 실수를 했습니다.

한개 더 배워서 갑니다.

감사해요..

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변