[Day8] Oracle 8 - RANK, HAVING, ROLLUP, CUBE

[Day8] Oracle 8 [10/6]

--1. emp , salgrade 테이블을 사용해서 아래와 같이 출력. [JOIN] 사용
--
--    ename   sal    grade
--    ---------------------
--    SMITH   800       1
--    ALLEN   1900   3
--    WARD   1750   3
--    JONES   2975   4
--    MARTIN   2650   4
--    BLAKE   2850   4
--    CLARK   2450   4
--    KING   5000   5
--    TURNER   1500   3
--    JAMES   950   1
--    FORD   3000   4
--    MILLER   1300   2

ㄱ. 정의 : 같거나 서로 다른 두 개 이상의 테이블에서 컬럼을 검색할 때 사용한다
    emp      : ename   sal
    salgrade : grade
ㄴ. WHERE 조인 조건
ㄷ. 8 가지 종류
ㄹ. 형식
  1)

  SELECT ename, sal, grade
  FROM emp, salgrade
  WHERE sal BETWEEN losal AND hisal;

2) JOIN ~ ON 구문

  SELECT ename, sal, grade
  FROM emp JOIN salgrade  ON sal BETWEEN losal AND hisal;

3) JOIN X - CASE 함수 사용.

  SELECT ename, sal
    , CASE 
             WHEN sal BETWEEN 700 AND 1200  THEN 1
             , WHEN sal BETWEEN 700 AND 1200  THEN 1
             , WHEN sal BETWEEN 700 AND 1200  THEN 1
             , WHEN sal BETWEEN 700 AND 1200  THEN 1
             , WHEN sal BETWEEN 700 AND 1200  THEN 1
      END grade
  FROM emp;

  [문제] deptno, dname, ename, hiredate, sal , grade 컬럼 조회

  emp      : [deptno] , ename, hiredate, sal
  dept     : [deptno], dname
  salgrade : grade

  1)

  SELECT dept.deptno, dname, ename, hiredate, sal , grade
  FROM emp, dept, salgrade
  WHERE  emp.deptno = dept.deptno AND sal BETWEEN losal AND hisal;

  SELECT dept.deptno, dname, ename, hiredate, sal , grade
  FROM emp JOIN dept         ON   emp.deptno = dept.deptno
           JOIN salgrade     ON   sal BETWEEN losal AND hisal;

3) 테이블의 별칭을 사용해서 작성

  SELECT d.deptno, dname, ename, hiredate, sal , grade
  FROM emp e, dept  d, salgrade
  WHERE  e.deptno = d.deptno AND sal BETWEEN losal AND hisal;

--1-2. 위의 결과에서 등급(grade)가 1등급인 사원만 조회하는 쿼리 작성
--  ( 조건 :  TOP-N 방식 사용 )
--
--결과)
--     EMPNO ENAME             SAL      GRADE
------------ ---------- ---------- ----------
--      7369 SMITH             800          1
--      7900 JAMES             950          1

 SELECT t.empno, t.ename, t.sal, t.grade
 FROM (
      SELECT empno, ename, sal, grade
          , RANK() OVER( ORDER BY  grade)  seq
      FROM emp , salgrade
      WHERE  sal BETWEEN losal AND hisal
  ) t 
 WHERE t.seq = 1 ;

  SELECT t.*
  FROM ( 
      SELECT empno, ename, sal, grade
      FROM emp, salgrade
      WHERE sal BETWEEN losal AND hisal
  ) t
  WHERE grade = 1;

--
--2. emp 에서 최고급여를 받는 사원의 정보 출력 ( JOIN ~ ON 구문 )
--  ( 조건 : 아래 컬럼 출력 )
--DNAME          ENAME             PAY
---------------- ---------- ----------
--ACCOUNTING     KING             5000

1)
WITH 
   temp AS (
          SELECT  ename, deptno, sal+NVL(comm,0) pay
          FROM emp
          WHERE sal = ( SELECT MAX(sal)  FROM emp )
  )
SELECT dname, ename, pay
FROM temp t JOIN dept d ON t.deptno = d.deptno;

2)
SELECT t.*
FROM(
    SELECT dname, ename, sal+NVL(comm,0) pay
    FROM emp e JOIN dept d ON e.deptno = d.deptno
) t
WHERE t.pay = (SELECT MAX(sal+NVL(comm,0)) FROM emp);

-- 1)

SELECT dname, ename , sal + NVL(comm, 0) pay
FROM dept, emp
WHERE dept.deptno = emp.deptno  AND sal + NVL(comm, 0) = ( SELECT MAX( sal + NVL(comm, 0) ) max_pay
FROM emp );

-- 2)

SELECT dname, ename , sal + NVL(comm, 0) pay
FROM dept d  JOIN  emp e  ON d.deptno = e.deptno
WHERE sal + NVL(comm, 0) = ( SELECT MAX( sal + NVL(comm, 0) ) max_pay FROM emp );

--  순위 처리 방법 : RANK(), ROW_NUMBER(), TOP-N 방식

--2-2. emp 에서 각 부서별 최고급여를 받는 사원의 정보 출력 ( JOIN )
--
--    DEPTNO DNAME          ENAME             PAY
------------ -------------- ---------- ----------
--        10 ACCOUNTING     KING             5000
--        20 RESEARCH       FORD             3000
--        30 SALES          BLAKE            2850

1) 상관 서브쿼리 + JOIN

   SELECT d.deptno, dname, ename , sal + NVL(comm, 0) pay
   FROM dept d  JOIN  emp e  ON d.deptno = e.deptno
   WHERE sal + NVL(comm, 0) = (  
                                   SELECT MAX( sal + NVL( comm, 0) ) max_pay
                                   FROM emp 
                                   WHERE deptno = e.deptno
                              );

2) RANK 순위 관련 함수 - ROW_NUMBER()
   -- 각 부서별 최급 pay 받는 사원의 정보 조회
   -- ㄱ. [각 부서별 pay 별 순위] 매겨서
   -- ㄴ. 조건 1등 만 조회

   【형식】
      ROW_NUMBER ()
                   OVER ([query_partition_clause] order_by_clause )

SELECT e.deptno, d.dname , ename, pay, seq
   FROM (
           SELECT deptno, ename, sal + NVL(comm, 0) pay
               , ROW_NUMBER() OVER( PARTITION BY deptno  ORDER BY sal + NVL(comm, 0) DESC )  seq
           FROM emp
        ) e  JOIN dept d ON d.deptno = e.deptno
   WHERE seq = 1;

SELECT deptno, dname, ename, pay
FROM(
    SELECT d.deptno, dname, ename, sal+NVL(comm,0) pay
     ,ROW_NUMBER() OVER( PARTITION BY d.deptno ORDER BY sal+NVL(comm,0) DESC)  seq
    FROM emp e JOIN dept d ON e.deptno = d.deptno
) t
 WHERE t.seq = 1 ;

3) TOP-N 방식

   SELECT t.*,  ROWNUM seq
   FROM (
           SELECT e.deptno, dname, ename, sal + NVL( comm, 0) pay
           FROM emp e JOIN dept d ON e.deptno = d.deptno
           ORDER BY  pay DESC
   ) t

   -- RANK 함수  -- : 정리 : https://crosstheline.tistory.com/104
   1) DENSE_RANK 함수
    그룹 내에서 차례로 된 행의 rank를 계산하여 NUMBER 데이터타입으로 순위를 반환한다.
     해당 값에 대한 우선순위를 결정(중복 순위 계산 안함)

    【Aggregate 형식】
          DENSE_RANK ( expr[,expr,...] ) WITHIN GROUP
            (ORDER BY expr [[DESC ¦ ASC] [NULLS {FIRST ¦ LAST} , expr,...] )
       
    【Analytic 형식】
          DENSE_RANK ( ) OVER ([query_partion_clause] order_by_clause )

   2)   PERCENT_RANK 함수
        이 함수는 CUME_DIST 함수와 유사하게 percent_rank 값을 반환한다.

        【aggregate 형식】
              PERCENT_RANK(expr,...) WITHIN GROUP 
                (ORDER BY expr { [DESC ¦ ASC]
                                 [NULLS {FIRST ¦ LAST}]
                               },...)
        또는 
        【Analytic 형식】
               PERCENT_RANK() OVER ( 
                                     [query_partition_clause]
                                      order_by_clause

   3)RANK 함수
        이 함수는 그룹 내에서 위치를 계산하여 반환한다.
        해당 값에 대한 우선순위를 결정(중복 순위 계산함)

        반환되는 데이터타입은 NUMBER이다.

   【Aggregate 형식】
                RANK(expr[,...]) WITHIN GROUP
                    (ORDER BY {expr [DESC ¦ ASC] [NULLS {FIRST ¦ LAST}]
                              } )
        
        【Analytic 형식】
            RANK() OVER( [query_partition_clause] order_by_clause
                           )

   4) FIRST, LAST
   5) ROW_NUMBER
                    rn  dr  r
   MARTIN   1250   9   9   9
   WARD   1250       10   9   9
   JAMES   950       11   10   11

   SELECT ename, sal
     , ROW_NUMBER() OVER( ORDER BY  sal DESC)  rn_seq
     , DENSE_RANK() OVER( ORDER BY  sal DESC)  dr_seq
     , RANK() OVER( ORDER BY  sal DESC)         r_seq
   FROM emp;

  [문제]  emp 테이블에서   sal 가 상위 20% 사원의 정보를 조회.

  -- 총사원수 12명

 SELECT t.*
 FROM ( 
  SELECT deptno, ename, sal
     , RANK() OVER( ORDER BY sal DESC ) seq
  FROM emp
  ) t
  WHERE t.seq <=  ( 12*0.2 );

-- PERCENT_RANK() 백분률 함수

SELECT t.*
FROM (
  SELECT deptno, ename, sal
     , PERCENT_RANK() OVER( ORDER BY sal DESC ) seq
  FROM emp
) t
WHERe t.seq <= 0.2;

--3. emp 에서 각 사원의 급여가 전체급여의 몇 %가 되는 지 조회.
--       ( %   소수점 3자리에서 반올림하세요 )         ROUND( a[, b])
--            무조건 소수점 2자리까지는 출력.. 7.00%,  3.50%
--           TO_CHAR( [NUMBER], DATE , VARCHAR2 )
--           \12,345
--ENAME             PAY   TOTALPAY 비율
------------ ---------- ---------- -------
--SMITH             800      27125   2.95%
--ALLEN            1900      27125   7.00%
--WARD             1750      27125   6.45%
--JONES            2975      27125  10.97%
--MARTIN           2650      27125   9.77%
--BLAKE            2850      27125  10.51%
--CLARK            2450      27125   9.03%
--KING             5000      27125  18.43%
--TURNER           1500      27125   5.53%
--JAMES             950      27125   3.50%
--FORD             3000      27125  11.06%
--MILLER           1300      27125   4.79%

SELECT t.*
       , TO_CHAR (   ROUND(  t.pay / t.totalpay * 100 ,  2 ) ,  '999.00' )  || '%'  비율
FROM ( 
        SELECT ename, sal + NVL( comm, 0) pay
             , ( SELECT SUM( sal + NVL( comm, 0)) FROM emp ) totalpay
        FROM emp
     ) t;

--4. emp 에서 가장 빨리 입사한 사원 과 가장 늦게(최근) 입사한 사원의 차이 일수 ?
--

SELECT  MIN( hiredate )  
        , MAX( hiredate ) -- 최근 입사
        , ABS(  MIN( hiredate ) - MAX( hiredate )  )
FROM emp;

-- FIRST_VALUE 함수 -- 현재 행까지의 첫 번째 값
FIRST_VALUE는 분석 함수로 정렬된 값중에서 첫 번째 값을 반환한다.
만약 첫번째가 NULL이라면, IGNORE NULLS를 지정하지 않았다면 NULL을 반환하게 된다.
즉, 윈도우에서 정렬된 값 중에서 첫 번째 값을 반환하는 함수이다.
LAST_VALUE 함수도 분석 함수로 정렬된 값중에서 마지막 값을 반환하는 함수이다.

【형식】

   FIRST_VALUE ¦ LAST_VALUE (expr [IGNORE NULLS] )
    OVER (
      [PARTITION BY expr2] [,...]
      ORDER BY expr3 [collate_clause] [ASC ¦ DESC]
      [NULLS FIRST ¦ NULLS LAST])
   )

-- LAST_VALUE 함수 -- 현재 행까지의 마지막 번째 값

  SELECT
      --ename, hiredate
       FIRST_VALUE ( hiredate ) OVER( ORDER BY hiredate DESC  )  -- MAX(hiredate)
       , FIRST_VALUE ( hiredate ) OVER( ORDER BY hiredate ASC  )  -- MIN(hiredate)
      --, LAST_VALUE ( hiredate ) OVER( ORDER BY hiredate DESC  ) 
  FROM emp;

--5. insa 에서 사원들의 만나이 계산해서 출력
--  ( 만나이 = 올해년도 - 출생년도          - 1( 생일이지나지 않으면) )

-- 만나이   = 올해년도 - 생일년도    (  -1  생일 지나지 않으면  )   AmericanAge
--         = 세는나이 -1       [생일지남여부 -1]
-- 세는나이 = 올해년도 - 생일년도 + 1            CountingAge
           -- ㄱ

SELECT t.name, t.ssn,  ㄱ-ㄴ+1           CountingAge
            , ㄱ-ㄴ + DECODE( isBCheck, -1, -1 , 0 )   AmericanAge
FROM (            
            SELECT name, ssn
               , TO_CHAR( SYSDATE, 'YYYY') ㄱ
                -- 성별 1,2,5,6  1900
                 --     3,4,7,8  2000
                 --      9,0      1800
               , SUBSTR( ssn, 0, 2 )  
               , CASE
                    WHEN SUBSTR( ssn, -7, 1)  IN ( 1,2,5,6 ) THEN 1900 + SUBSTR( ssn, 0, 2 )  
                    WHEN SUBSTR( ssn, -7, 1)  IN ( 3,4,7,8 ) THEN 2000 + SUBSTR( ssn, 0, 2 )  
                    WHEN SUBSTR( ssn, -7, 1)  IN ( 9,0 )     THEN 1800 + SUBSTR( ssn, 0, 2 )  
                 END  ㄴ
               , SIGN(   TRUNC( SYSDATE) -  TO_DATE( SUBSTR( ssn, 3,4), 'MMDD') )  isBCheck  -- -1,0,1
            FROM insa
     ) t;

--6. insa 테이블에서 아래와 같이 결과가 나오게 ..
-- [총사원수] [남자사원수] [여자사원수] [남사원들의 총급여합] [여사원들의 총급여합] [남자-max(급여)] [여자-max(급여)]
------------ ---------- ---------- ---------- ---------- ---------- ----------
-- 60 31 29 51,961,200 41,430,400 2650000 2550000

SELECT  COUNT(*) 총사원수
      , COUNT( DECODE(  MOD( SUBSTR( ssn, -7, 1), 2) , 1, 'O' )  )    남자사원수
      , COUNT( DECODE(  MOD( SUBSTR( ssn, -7, 1), 2) , 0, 'X' )  )    여자사원수
      , TO_CHAR( SUM( DECODE(  MOD( SUBSTR( ssn, -7, 1), 2) , 1, basicpay + sudang )  ) , 'L999,999,999' )  "남사원들의 총급여합"
      , TO_CHAR(SUM( DECODE(  MOD( SUBSTR( ssn, -7, 1), 2) , 0, basicpay + sudang )  ) , 'L999,999,999' )  "여사원들의 총급여합"
      , TO_CHAR(MAX( DECODE(  MOD( SUBSTR( ssn, -7, 1), 2) , 1, basicpay + sudang )  ) , 'L999,999,999' ) "남사원들의 MAX급여"
      , TO_CHAR(MAX( DECODE(  MOD( SUBSTR( ssn, -7, 1), 2) , 0, basicpay + sudang )  ) , 'L999,999,999' )  "여사원들의 MAX급여"
FROM insa;

--7. TOP-N 방식으로 풀기 ( ROWNUM 의사 컬럼 사용 )
--   emp 에서 최고급여를 받는 사원의 정보 출력
--
--    DEPTNO ENAME             PAY   PAY_RANK
------------ ---------- ---------- ----------
--        10 KING             5000          1
--

 SELECT  t.* , ROWNUM PAY_RANK
         FROM (
                         SELECT deptno, ename, sal + NVL(comm, 0) pay
                         FROM emp
                         ORDER BY pay DESC
              ) t
         WHERE ROWNUM = 1;                     
         WHERE ROWNUM <= 3;

         WHERE ROWNUM BETWEEN  3  AND 5;       -- 주의
         WHERE ROWNUM > 3;                     -- 주의
         -- WHERE PAY_RANK = 1;  -- ORA-00904: "PAY_RANK": invalid identifier

--8.순위(RANK) 함수 사용해서 풀기
--   emp 에서 각 부서별 최고급여를 받는 사원의 정보 출력
--
--    DEPTNO ENAME             PAY DEPTNO_RANK
------------ ---------- ---------- -----------
--        10 KING             5000           1
--        20 FORD             3000           1
--        30 BLAKE            2850           1
  ㄴ.   + emp 조인

SELECT t.deptno, e.ename, e.sal , e.hiredate , 1  AS DEPTNO_RANK
FROM (  
  SELECT deptno, MAX( sal ) maxpay
  FROM emp
  GROUP BY deptno
  ) t , emp e
WHERE t.deptno = e.deptno AND t.maxpay = e.sal;

ㄱ. 순위 함수

SELECT *
FROM (
        SELECT deptno, ename, sal + NVL(comm,0) pay
       , RANK() OVER( PARTITION BY deptno ORDER BY  sal + NVL(comm,0) DESC  ) deptno_rank
FROM emp
)
WHERE deptno_rank = 1;

--9. emp테이블에서 각 부서의 사원수, 부서총급여합, 부서평균을 아래와 같이 출력하는 쿼리 작성.
--결과)
--    DEPTNO       부서원수       총급여합            평균
------------ ----------       ----------    ----------
--        10          3          8750       2916.67
--        20          3          6775       2258.33
--        30          6         11600       1933.33

SELECT 
     , COUNT( DECODE( 10 )
     , SUM( DECODE( 10 )
     , AVG( DECODE( 10 )
FROM emp;
-- 40 0 ( 암기 ) [] OUTER JOIN
SELECT d.deptno
    , COUNT( e.empno ) 부서원수
FROM emp  e, dept d  -- RIGHT OUTER JOIN
WHERE e.deptno(+) = d.deptno
GROUP BY d.deptno
ORDER BY d.deptno ASC;

-- JOIN ON 구문 사용해서 수정

SELECT d.deptno
    , COUNT( e.empno ) 부서원수
    , NVL( SUM( sal + NVL(comm,0)), 0) 총급여합
    , NVL( ROUND(  AVG( sal + NVL(comm,0)) , 2 ), 0) 평균
--FROM emp  e JOIN  dept d  ON e.deptno(+) = d.deptno
--FROM emp  e RIGHT OUTER JOIN  dept d  ON e.deptno = d.deptno
FROM emp  e RIGHT JOIN  dept d  ON e.deptno = d.deptno
GROUP BY d.deptno
ORDER BY d.deptno ASC;

--10-1.  emp 테이블에서 30번인 부서의 최고, 최저 SAL을 출력하는 쿼리 작성.
--결과)
--  MIN(SAL)   MAX(SAL)
------------ ----------
--       950       2850

SELECT MAX( sal ) , MIN( sal )
FROM emp
WHERE deptno = 30;

-- 각 부서별 최고, 최저 sal 출력

SELECT deptno, MAX( sal ) , MIN( sal )
FROM emp
GROUP BY deptno
HAVING deptno = 30;     -- GROUP BY 절의 조건절이 HAVING절이다.
--ORDER BY deptno;

--10-2.  emp 테이블에서 30번인 부서의 최고, 최저 SAL를 받는 사원의 정보 출력하는 쿼리 작성.
--
--결과)
--     EMPNO ENAME      HIREDATE        SAL
------------ ---------- -------- ----------
--      7698 BLAKE      81/05/01       2850
--      7900 JAMES      81/12/03        950

-- ORA-00907: missing right parenthesis
--              오른쪽 괄호 누락

SELECT EMPNO, ENAME,      HIREDATE ,       SAL
FROM emp
WHERE deptno = 30 AND sal IN (   
                                  (  SELECT MAX(sal) FROM emp WHERE deptno=30  )
                                , ( SELECT MIN(sal) FROM emp WHERE deptno=30   )
                              );

-- WHERE deptno = 30 AND sal IN ( 2850, 950 );

-- JOIN

SELECT empno, ename, hiredate ,sal
FROM ( 
        SELECT MAX(sal) max_sal, MIN(sal) min_sal
        FROM emp
        WHERE deptno = 30
) t , emp e
WHERE e.deptno = 30 AND e.sal = t.max_sal OR e.sal = t.min_sal;

--11.  insa 테이블에서
--[실행결과]
--부서명     총사원수 부서사원수 성별  성별사원수  부/전% 부성/전% 성/부%
--개발부       60 14 F 8 23.3% 13.3%       57.1%
--개발부       60 14 M 6 23.3% 10% 42.9%
--기획부       60 7 F 3 11.7% 5% 42.9%
--기획부       60 7 M 4 11.7%   6.7% 57.1%
--영업부       60 16 F 8       26.7% 13.3% 50%
--영업부       60 16 M 8        26.7%   13.3% 50%
--인사부       60 4 M 4        6.7%   6.7% 100%
--자재부       60 6 F 4        10%      6.7% 66.7%
--자재부       60 6 M 2       10%       3.3% 33.3%
--총무부       60 7 F 3       11.7%   5%           42.9%
--총무부       60 7 M 4      11.7% 6.7% 57.1%
--홍보부       60 6 F 3      10%      5%           50%
--홍보부       60 6 M 3      10%       5%           50%

SELECT t2.*
   , ROUND( 부서사원수/총사원수*100, 2) || '%' "부/전%"
   , ROUND( "성별사원수"/총사원수*100, 2) || '%' "부성/전%"
   , ROUND( "성별사원수"/부서사원수*100, 2) || '%' "성/부%"
FROM (
            SELECT buseo
                 , gender
                 , (SELECT COUNT(*) FROM insa) 총사원수
                 , (SELECT COUNT(*) FROM insa WHERE buseo = t.buseo ) 부서사원수
                 , COUNT(*) "성별사원수"
            FROM ( 
                SELECT buseo, name, ssn  
                      , DECODE(  MOD( SUBSTR( ssn, -7, 1), 2), 1 ,'M','F' )  gender
                FROM insa 
            ) t
            GROUP BY buseo , gender
            ORDER BY buseo , gender 
) t2;

--12. insa테이블에서 여자인원수가 5명 이상인 부서만 출력.
-- ㄱ.

SELECT *
FROM( 
        SELECT buseo, COUNT(*) 여자사원수
        FROM insa
        WHERE MOD( SUBSTR( ssn, -7, 1), 2 ) = 0
        GROUP BY buseo 
) t
WHERE t.여자사원수 >= 5;

-- ㄴ. HAVING 절 : GROUP BY 조건절

 5 SELECT buseo, COUNT(*) 여자사원수
 1 FROM insa
 2 WHERE MOD( SUBSTR( ssn, -7, 1), 2 ) = 0
 3 GROUP BY buseo
 4 HAVING COUNT(*) >= 5;

--13. insa 테이블에서 급여(pay= basicpay+sudang)가 상위 15%에 해당되는 사원들 정보 출력

SELECT *
FROM (
            SELECT buseo, name,  basicpay+sudang pay
               , RANK() OVER( ORDER BY basicpay+sudang DESC )  pay_rank
            FROm insa
) t
WHERE t.pay_rank <= (SELECT COUNT(*) FROM insa)*0.15;
WHERE t.pay_rank <= 9;

--14. emp 테이블에서 sal의 전체사원에서의 등수 , 부서내에서의 등수를 출력하는 쿼리 작성
-- ROW_NUMBER()

        SELECT deptno,  empno, ename, sal
              , RANK() OVER(ORDER BY sal DESC)  w_rank   -- 전교등수
              , RANK() OVER( PARTITION BY deptno ORDER BY sal DESC)  b_rank  -- 반등수
        FROM emp
        ORDER BY deptno, b_rank ASC;

-- 앞 뒤에 있는 특정 문자를 제거하는 함수

SELECT '    ADMIN    '        -- JAVA : trim()
     -- , REPLACE( '    ADMIN    ' , ' ', '' )
     -- , TRIM( 특정문자  FROM 문자열)
     , TRIM( ' '  FROM '    ADMIN    ') 
     , TRIM( '*'  FROM '****AD*MIN****') 
FROM dual;

-- GROUP BY + HAVING 절 --
1)

SELECT 
   COUNT(*)
   , COUNT(   DECODE(deptno, 10, 'O')  )
   , COUNT(   DECODE(deptno, 20, 'O')  )
   , COUNT(   DECODE(deptno, 30, 'O')  )
   , COUNT(   DECODE(deptno, 40, 'O')  )
FROM emp;

SELECT d.deptno , COUNT(e.empno)
FROM emp e, dept d 
WHERE e.deptno(+) = d.deptno
GROUP BY d.deptno
ORDER BY d.deptno ASC;

( 문제 ) emp 테이블에서  20, 40번 부서원은 제외하고
         그 외 부서의 사원수를 계산하고
         그 부서의 사원수가 4명 이상인 부서정보를 출력.
         -- FROM (

SELECT deptno, COUNT(*)
FROM emp
WHERE deptno NOT IN ( 20, 40 )
GROUP BY deptno
HAVING COUNT(*) >= 4;

  ( 추가문제 )       deptno, [ dname ], COUNT(*)  부서명도 함께 출력하고자 한다.  *****
           dept : dname , dpetno
           emp :  deptno

           1) 조인
       --  ORA-00979: not a GROUP BY expression *****
       -- GROUP BY 절  + 집계함수

         SELECT d.deptno, dname,  COUNT(*)
         FROM emp e JOIN dept d ON e.deptno = d.deptno
         WHERE d.deptno NOT IN ( 20, 40 )
         GROUP BY d.deptno , dname
         HAVING COUNT(*) >= 4;

(문제) insa테이블에서 각 부서별로 과장 몇명, 대리 몇명, 사원 몇명 등등 직급별 사원수를 조회.

  SELECT buseo, jikwi, COUNT(*) 사원수
  FROM insa
  GROUP BY buseo , jikwi
  ORDER BY buseo ASC, jikwi ASC;

  (추가문제) 어떤 부서의 사원이 없는 직위명이라도      과정  0  으로 출력.
  SELECT DISTINCT jikwi
  FROM insa;

    과장
    대리
    부장
    사원

--  [ ROLLUP  과  CUBE   설명 ] 정리 : https://for-my-wealthy-life.tistory.com/44

-- (문제) insa 테이블에서 남자사원수,여자사원수를 조회 + 총사원수 조회

 SELECT DECODE(  MOD(  SUBSTR( ssn, -7, 1) , 2 ) , 1 , '남자', '여자') gender
       , COUNT(*)
 FROM insa
 GROUP BY   MOD(  SUBSTR( ssn, -7, 1) , 2 ) 
 UNION ALL -- 컬럼갯수,
 SELECT ' ' , COUNT(*)
 FROM insa;

-- 위의 쿼리를 ROLLUP 연산자 사용...
ROLLUP은 GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화 하고, 각 그룹에 대해 부분합을 구하는 연산자이다.
CUBE는 ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자이다. 즉, ROLLUP 연산자를 수행한 결과에 더해 GROUP BY 절에 기술된 조건에 따라 모든 가능한 그룹핑 조합에 대한 결과를 출력한다.

【형식】

        SELECT   컬럼명, 그룹함수(컬럼명)
        FROM     테이블명
        WHERE    조건
        GROUP BY [ROLLUP ¦ CUBE]그룹핑하고자하는 컬럼명,...
        HAVING   그룹조건
        ORDER BY 컬럼명 또는 위치번호

select 문의 prototype 참조
• ROLLUP과 CUBE는 GROUP BY 절 뒤에 기술한 컬럼 개수에 따라 출력되는 결과 셋이 달라진다.
• GROUP BY 뒤에 기술한 컬럼이 2개일 경우 ROLLUP은 n+1에서 3개의 그룹별 결과가 출력되고, CUBE는 2*n에서 2*2=4개의 결과 셋이 출력된다.

ex)

SELECT DECODE(  MOD(  SUBSTR( ssn, -7, 1) , 2 ) , 1 , '남자', 0, '여자', '합') gender
       , COUNT(*)
 FROM insa
 GROUP BY  ROLLUP (  MOD(  SUBSTR( ssn, -7, 1) , 2 ) );

--(문제)  insa 테이블에서
--       1차 그룹화 : 부서별 ,            2차 그룹화 : 직급별
--       COUNT(*), SUM(*), AVG(*) 등등 집계함수...

  -- ORA-00933: SQL command not properly ended
  --            SQL 명령어가 제대로 종료되지 않았다.

  SELECT buseo, jikwi, COUNT(*) 사원수
  FROM insa
  GROUP BY buseo, jikwi
  --ORDER BY buseo, jikwi
  UNION ALL
  SELECT buseo, '' jikwi,  COUNT(*) 
  FROM insa
  GROUP BY buseo
--   UNION ALL
--  SELECT '',  jikwi,  COUNT(*) 
--  FROM insa
--  GROUP BY jikwi
  UNION ALL
  SELECT '' buseo, '' jikwi,  COUNT(*) 
  FROM insa 
  ORDER BY buseo , jikwi;

  SELECT buseo, jikwi, COUNT(*) 사원수
  FROM insa
  -- GROUP BY  ROLLUP( buseo, jikwi )
  GROUP BY  CUBE( buseo, jikwi )
  ORDER BY buseo, jikwi   ;

  
GROUP BY    buseo, jikwi     부서별 / 직위      사원수
GROUP BY   ROLLUP(  buseo, jikwi )     부서별 / 직위      사원수
                                      부서별                총사원수 ( 부분합 )
                                                        전체 사원수 ( 부분합 )
  
GROUP BY   CUBE(  buseo, jikwi )     부서별 / 직위      사원수
                                      부서별                총사원수 ( 부분합 )
                                             직위별       총사원수( 부분합 )
                                                        전체 사원수 ( 부분합 )

저작자표시

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

[Day10] Oracle 10 - MODIFY, DROP COLUMN, Table copy, sub query(insert, update, delete), MERGE, Constraint (0)	2022.10.12
[Day9] Oracle 9 - PIVOT, 자료형(CHAR, VARCHAR, NUMBER, CLOB), CREATE/DROP/ALTER (0)	2022.10.07
[Oracle/Table] 오라클 테이블 (0)	2022.10.06
[Day7] Oracle 7 - JOIN, TON-N (ROW_NUMBER), CASE (0)	2022.10.06
[Day6] Oracle 6 - ALL, correlated subquery, 문자함수, 날짜함수, COALESCE, DECODE (0)	2022.10.05

[Day8] Oracle 8 - RANK, HAVING, ROLLUP, CUBE

[Day8] Oracle 8 [10/6]

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day8] Oracle 8 [10/6]

'👨‍💻 Web Development > Oracle' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바