SQLP 8회 2013.03.09

실기 1번 문제에 대한 후기입니다.

 

1. 문제 요약 : 사원 테이블을 '한번' 만 읽고 다음을 출력하라. 

 

전체 사원수 | 부서개수 | 부서별 사원수의 평균 | 부서별 사원의 MAX | 부서별 사원의 MIN | 사원수가 가장 적은 부서|사원수가 가장 많은 부서

 

* DEPT_NO (부서번호) 는 IS NOT NULL

* 위의 정보는 SELECT * FROM EMP WHERE DEPT_NO = : DEPT_NO 에 대한 인덱스 설계치 참고할 정보임

* 위의 정보는 '한줄' 로 표시됨

 

 

2. 생각나는 방법

* EMP : 사원 테이블 (EMP_NO, DEPT_NO,EMP_NM)

 

2.1 윈도우 함수 사용 (FIRST_VALUE)

 

SELECT SUM(CNT) "전체사원수",COUNT(DEPT_NO) "부서개수", SUM(CNT)/COUNT(DEPT_NO) "부서별 평균 사원수",

       MAX(CNT) "부서별 사원수의 MAX", MIN(CNT) "부서별 사원수의 MIN",

       MAX(MIN_EMP_CNT) "사원수가 가장 적은 부서",

       MAX(MAX_EMP_CNT) "사원수가 가장 많은 부서"

FROM (

        SELECT DEPT_NO,COUNT(EMP_NO) AS CNT,

               FIRST_VALUE(DEPT_NO) OVER(ORDER BY COUNT(EMP_NO) ASC) AS MIN_EMP_CNT,

               FIRST_VALUE(DEPT_NO) OVER(ORDER BY COUNT(EMP_NO) DESC) AS MAX_EMP_CNT

        FROM EMP

        GROUP BY DEPT_NO

      ) X;

 

** 생각해보니 2.2 도 rank 윈도함수라.ㅋㅋ 부제제목 수정이요.

2.2 윈도우 함수 FIRST_VALUE 미사용, 다른 윈도우 함수 사용 (수정함)

 

SELECT SUM(CNT) "전체사원수",COUNT(DEPT_NO) "부서개수", SUM(CNT)/COUNT(DEPT_NO) "부서별 평균 사원수",

       MAX(CNT) "부서별 사원수의 MAX", MIN(CNT) "부서별 사원수의 MIN",

       MAX(MIN_RN_DEPT) "사원수가 가장 적은 부서",

       MAX(MAX_RN_DEPT) "사원수가 가장 많은 부서"

FROM (

        SELECT DEPT_NO,CNT, 

               DECODE(MIN_RN,1,DEPT_NO,NULL) "MIN_RN_DEPT",

               DECODE(MAX_RN,1,DEPT_NO,NULL) "MAX_RN_DEPT"

        FROM (

                SELECT DEPT_NO,CNT,

                       RANK() OVER(ORDER BY CNT ASC) "MIN_RN",

                       RANK() OVER(ORDER BY CNT DESC) "MAX_RN"

                FROM (

                        SELECT DEPT_NO,COUNT(EMP_NO) AS CNT

                        FROM EMP

                        GROUP BY DEPT_NO      

                     ) X

             ) Y

      ) Z;

 

 

** Update :3.12

** 댓글에 MAX() KEEP 절이 나와서 추가해 봅니다~ MAX KEEP 절은 첨보네요~ 

 

2.3 MAX() KEEP(DENSE_RANK FIRST[LAST] ORDER BY .. ) 사용

 

SELECT SUM(CNT) "전체사원수",COUNT(DEPT_NO) "부서개수", SUM(CNT)/COUNT(DEPT_NO) "부서별 평균 사원수",

       MAX(CNT) "부서별 사원수의 MAX", MIN(CNT) "부서별 사원수의 MIN",

       MAX(DEPT_NO) KEEP(DENSE_RANK FIRST ORDER BY CNT ASC) "사원수가 가장 적은 부서",

       MAX(DEPT_NO) KEEP(DENSE_RANK LAST ORDER BY CNT ASC) "사원수가 가장 많은 부서"

FROM (

        SELECT DEPT_NO,COUNT(EMP_NO) AS CNT

        FROM EMP

        GROUP BY DEPT_NO

      ) X;

      

* 참고 : MAX(column_name1) KEEP(DENSE_RANK FIRST[LAST] ORDER BY column_name2 )

=> WINDOW 함수처럼 각 ROW 에 표시되는 형태가 아닌 집합함수입니다.즉, GROUP BY 표현식이 있어야 함 

=> GROUP BY 에 표시된 GROUP 에서 우선적으로 column_name2 로 ORDER BY (ASC,DESC) 하여 DENSE_RANK 

    의 첫번째(FIRST), 마지막(LAST) 를 KEEP 합니다. (즉, 그룹된 기준에서 임의의 컬럼으로 순서를 정렬한 집합중

    첫번째 또는 마지막 그룹을 저장해둔다는 의미겠네요.

=> KEEP 된 상태의 그룹에서 DENSE_RANK 의 첫번째(마지막) 그룹중 임의의 컬럼 column_name1 의 max 를 가져옴

 

* SQL 설명

=> DEPT_NO 로 그룹화 된 DEPT_NO, 그룹별 사원수에서 전체를 대상으로 그룹화 하여 사원수를 ASC 방식으로 정렬한것중

    첫번째 RANK 를 KEEP 하고 그 그룹중 DEPT_NO 의 MAX 를 가져옴 (많은 부서는 반대로 LAST 로 표현)

 

* 간단하게 보고 적은거라 틀린점이 있을수도 있습니다~~

 

** Update :3.13

** 댓글에 있는 윈도우 함수를 쓰지 않는 방법을 추가합니다. 댓글에 있는 내용을 바탕으로 위의 SQL 과 동일포맷~

 

2.4 윈도우 함수 미사용

 

SELECT SUM(CNT) "전체사원수", COUNT(DEPT_NO) "부서개수", AVG(CNT) "부서별 평균 사원수",

       MAX(CNT) "부서별 사원수의 MAX", MIN(CNT) "부서별 사원수의 MIN",

       SUBSTR(MIN(LPAD(CNT, 10, '0')||DEPT_NO),11) "사원수가 가장 적은 부서",

       SUBSTR(MAX(LPAD(CNT, 10, '0')||DEPT_NO),11) "사원수가 가장 많은 부서"

FROM

    (

     SELECT DEPT_NO, COUNT(*) AS CNT

     FROM EMP

     GROUP BY DEPT_NO

     );

 

cafe.naver.com/sqlpd/1865