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 40982 게시물 읽기
No. 40982
퇴직율 구하는 쿼리 튜닝
작성자
쿼리요청
작성일
2015-10-28 16:28ⓒ
2015-10-28 17:04ⓜ
조회수
9,589

쿼리 문의 드리고자 합니다.

 

 

우선 환경은 오라클 10G 를 사용하고 있습니다.

원하는 것은 인사원장 테이블을 이용하여 아래와 같이 퇴직율을 구하고자 합니다.

 

구분 | 1월 | 2월 | 12월 | 누계

| 월말인원 | 퇴직인원 | 퇴직율 | 월말인원 | 퇴직인원 | 퇴직율 | 월말인원 | 퇴직인원 | 퇴직율 | 월말평균인원 | 퇴직인원 | 퇴직율

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

회사전체 |

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

A부문 |

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

B부문 |

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

C부문 |

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

 

쿼리 능력이 부족하여 단순 무식하게 작업했는데,

월말인원은 재직여부를 아래와 같이 월별로 구하여 12개월치의 서브쿼리를 생성했습니다.

SELECT YR01.BUMUN_CD,

YR01.ALL_CNT,

YR01.RETIRE_CNT,

YR01.RETIRE_RATIO,

YR02.ALL_CNT,

YR02.RETIRE_CNT,

YR02.RETIRE_RATIO,

FROM

(

SELECT BUMUN_CD,

COUNT(GG) ALL_CNT,

COUNT(DECODE(GG,2,1)) RETIRE_CNT,

ROUND(COUNT(DECODE(GG,2,1)) / COUNT(GG) * 100,2) RETIRE_RATIO

FROM (

SELECT CASE WHEN WORK_YN = 'Y' AND HIRE_YMD <= LAST_DAY(TO_CHAR(BASE.STD_YMD,'YYYY')||'0101') THEN

1

WHEN WORK_YN = 'N' AND RETIRE_YMD BETWEEN TO_DATE(TO_CHAR(BASE.STD_YMD,'YYYY')||'0101','YYYYMMDD') AND LAST_DAY(TO_CHAR(BASE.STD_YMD,'YYYY')||'0101') THEN

2

ELSE 0

END AS GG,

BUMUN_CD,

EMP.*

FROM EMP_MASTER EMP, -- 임직원테이블

(SELECT TO_DATE('20151028','YYYYMMDD') STD_YMD FROM DUAL) BASE -- 기준일자(조회할때 변동가능)

-- 현재 재직이면서 기준월 말일이전에 입사했거나, 기준월 1일 이후퇴사이면서 기준월 말일이전 입사자 기준

WHERE ((WORK_YN = 'Y' AND HIRE_YMD <= LAST_DAY(TO_CHAR(BASE.STD_YMD,'YYYY')||'0101'))

OR (WORK_YN = 'N' AND RETIRE_YMD >= TO_DATE(TO_CHAR(BASE.STD_YMD,'YYYY')||'0101','YYYYMMDD') AND HIRE_YMD <= LAST_DAY(TO_CHAR(BASE.STD_YMD,'YYYY')||'0101')))

)

GRUOP BY ROLLUP(BUMUN)

ORDER BY BUMUN NULLS FIRST

) YR01,

(

SELECT BUMUN_CD,

COUNT(GG) ALL_CNT,

COUNT(DECODE(GG,2,1)) RETIRE_CNT,

ROUND(COUNT(DECODE(GG,2,1)) / COUNT(GG) * 100,2) RETIRE_RATIO

FROM (

SELECT CASE WHEN WORK_YN = 'Y' AND HIRE_YMD <= LAST_DAY(TO_CHAR(BASE.STD_YMD,'YYYY')||'0201') THEN

1

WHEN WORK_YN = 'N' AND RETIRE_YMD BETWEEN TO_DATE(TO_CHAR(BASE.STD_YMD,'YYYY')||'0201','YYYYMMDD') AND LAST_DAY(TO_CHAR(BASE.STD_YMD,'YYYY')||'0201') THEN

2

ELSE 0

END AS GG,

BUMUN_CD,

EMP.*

FROM EMP_MASTER EMP, -- 임직원테이블

(SELECT TO_DATE('20151028','YYYYMMDD') STD_YMD FROM DUAL) BASE -- 기준일자(조회할때 변동가능)

-- 현재 재직이면서 기준월 말일이전에 입사했거나, 기준월 1일 이후퇴사이면서 기준월 말일이전 입사자 기준

WHERE ((WORK_YN = 'Y' AND HIRE_YMD <= LAST_DAY(TO_CHAR(BASE.STD_YMD,'YYYY')||'0201'))

OR (WORK_YN = 'N' AND RETIRE_YMD >= TO_DATE(TO_CHAR(BASE.STD_YMD,'YYYY')||'0201','YYYYMMDD') AND HIRE_YMD <= LAST_DAY(TO_CHAR(BASE.STD_YMD,'YYYY')||'0201')))

)

GRUOP BY ROLLUP(BUMUN)

ORDER BY BUMUN NULLS FIRST

) YR02,

....

 

이렇게 구하다 보니 데이터가 엉망이 되네요.

 

해당 쿼리를 참고(만 하시고)하여 윗 표 형태로 조회가 가능할지 문의드립니다.

 

능력자 분들의 도움을 받을 수 있으면 좋겠습니다. 미리 감사드립니다.
이 글에 대한 댓글이 총 3건 있습니다.

조회 기준일자가 변동되는 것이라면?
  - work_yn 에 대한 조건은 잘못된 조건입니다.
  - work_yn 에 의 데이터는 특정시점 데이터가 아닌 현시점의 데이터죠.
  - 현시점인 10월 조회시 1월 자료에는 기 퇴직자가 모두 제외되겟네요.
  - 입사일자와 퇴사일자만으로 판단해야 합니다.
특정 시점이라는 조건이 있지만
  - 결국 일자는 사용되지 않고 년도만 사용되네요
  - 연도조건만 주면 될 것 같네요
인원 및 비율 대한 정의가 모호 하네요
  - 월말인원에 당월 퇴직자가 포함되어 있네요?
  - 비율 구할 때는 포함되는게 맞겠지만 말이죠.
재직자의 퇴직일자 항목에는 어떤 값이 들어 있나요?
  - 널이 들어 있나요?
  - '99991231' 가 들어 있나요?

마농(manon94)님이 2015-10-28 19:56에 작성한 댓글입니다.
이 댓글은 2015-10-28 20:30에 마지막으로 수정되었습니다.

SELECT bumun_cd
     , COUNT(DECODE(mm, '01', h)) cnt_01
     , COUNT(DECODE(mm, '01', r)) ret_01
     , COUNT(DECODE(mm, '01', r)) / SUM(DECODE(mm, '01', 1)) rat_01
     , COUNT(DECODE(mm, '02', h)) cnt_02
     , COUNT(DECODE(mm, '02', r)) ret_02
     , COUNT(DECODE(mm, '02', r)) / SUM(DECODE(mm, '02', 1)) rat_02
     , COUNT(DECODE(mm, '03', h)) cnt_03
     , COUNT(DECODE(mm, '03', r)) ret_03
     , COUNT(DECODE(mm, '03', r)) / SUM(DECODE(mm, '03', 1)) rat_03
     , COUNT(DECODE(mm, '04', h)) cnt_04
     , COUNT(DECODE(mm, '04', r)) ret_04
     , COUNT(DECODE(mm, '04', r)) / SUM(DECODE(mm, '04', 1)) rat_04
     , COUNT(DECODE(mm, '05', h)) cnt_05
     , COUNT(DECODE(mm, '05', r)) ret_05
     , COUNT(DECODE(mm, '05', r)) / SUM(DECODE(mm, '05', 1)) rat_05
     , COUNT(DECODE(mm, '06', h)) cnt_06
     , COUNT(DECODE(mm, '06', r)) ret_06
     , COUNT(DECODE(mm, '06', r)) / SUM(DECODE(mm, '06', 1)) rat_06
     , COUNT(DECODE(mm, '07', h)) cnt_07
     , COUNT(DECODE(mm, '07', r)) ret_07
     , COUNT(DECODE(mm, '07', r)) / SUM(DECODE(mm, '07', 1)) rat_07
     , COUNT(DECODE(mm, '08', h)) cnt_08
     , COUNT(DECODE(mm, '08', r)) ret_08
     , COUNT(DECODE(mm, '08', r)) / SUM(DECODE(mm, '08', 1)) rat_08
     , COUNT(DECODE(mm, '09', h)) cnt_09
     , COUNT(DECODE(mm, '09', r)) ret_09
     , COUNT(DECODE(mm, '09', r)) / SUM(DECODE(mm, '09', 1)) rat_09
     , COUNT(DECODE(mm, '10', h)) cnt_10
     , COUNT(DECODE(mm, '10', r)) ret_10
     , COUNT(DECODE(mm, '10', r)) / SUM(DECODE(mm, '10', 1)) rat_10
     , COUNT(DECODE(mm, '11', h)) cnt_11
     , COUNT(DECODE(mm, '11', r)) ret_11
     , COUNT(DECODE(mm, '11', r)) / SUM(DECODE(mm, '11', 1)) rat_11
     , COUNT(DECODE(mm, '12', h)) cnt_12
     , COUNT(DECODE(mm, '12', r)) ret_12
     , COUNT(DECODE(mm, '12', r)) / SUM(DECODE(mm, '12', 1)) rat_12
     , COUNT(h) / 12 cnt_avg
     , COUNT(r) ret_tot
     , COUNT(r) / SUM(1) * 12 rat_01
  FROM (SELECT bumun_cd
             , mm
             , CASE WHEN NVL(retire_ymd, '99991231') >  edt THEN 1 END h
             , CASE WHEN NVL(retire_ymd, '99991231') <= edt THEN 1 END r
          FROM emp_master
             , (SELECT LPAD(LEVEL, 2, '0') mm
                     , TO_CHAR(ADD_MONTHS(ym, LEVEL-1), 'yyyymmdd') sdt
                     , TO_CHAR(ADD_MONTHS(ym, LEVEL)-1, 'yyyymmdd') edt
                  FROM (SELECT TO_DATE('2015'||'01', 'yyyymm') ym FROM dual)
                 CONNECT BY LEVEL <= 12
                )
         WHERE hire_ymd <= edt
           AND NVL(retire_ymd, '99991231') >= sdt
        )
 GROUP BY ROLLUP(bumun_cd)
 ORDER BY GROUPING(bumun_cd) DESC, bumun_cd
;

마농(manon94)님이 2015-10-28 20:26에 작성한 댓글입니다.
이 댓글은 2015-10-30 16:56에 마지막으로 수정되었습니다.

마농님 감사합니다.

제가 약간 착각했던 부분이 있었네요.

 

도움주신 쿼리를 바탕으로 일부 변경하여 처리하였습니다.

 

쿼리요청님이 2015-10-29 15:32에 작성한 댓글입니다. Edit Ｘ
[Top]
No.
제목
작성자
작성일
조회
40986오라클 805버젼인데 복구할수있는방법은 없는지요??? [1]
조승식
2015-11-02
7981
40985변경이력 관련 질문드립니다. [1]
새내기
2015-10-30
8509
40983SQL클라이언트 프로그램들이 (sqlldr, sqlplus) 먹통되는 현상 혹시 경험하신분 문의드립니다. [1]
메지나
2015-10-29
8331
40982퇴직율 구하는 쿼리 튜닝 [3]
쿼리요청
2015-10-28
9589
40980SQLP 문제 이해가 안갑니다;; [2]
J
2015-10-22
8726
40979특정 시점 이후 데이터가 수정된 테이블 찾는 방법을 알고싶습니다. [1]
한동훈
2015-10-21
8491
40978카운트 함수 여러개 사용 어떻게 하나요(서브쿼리없이) [1]
어려워
2015-10-20
8474
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.019초, 이곳 서비스는 PostgreSQL v16.2로 자료를 관리합니다