210521 _DB3 (SQL)

👌

  그룹 함수 : GROUP BY 절, GROUP 함수 선언하고 활용할 수 있다.
  HAVING 절을 사용해서 GROUP 제한을 줄 수 있다.
  ROLLUP 연산을 사용해서 하위 총계 값을 계산할 수 있다.
  CUBE 연산을 사용하여 모든 열 조합에 대한 그룹 함수를 적용할 수 있다.
  GROUPING 함수를 사용해서 RULLUP, CUBE를 통해 만들어진 행 값을 식별할 수 있다.
  GROUPING SETS절을 사용해서 원하는 조합을 설정할 수 있다.

 

 

그룹 함수

    : 다중행 함수 (MULTYROW) , 여러 개의ROW 데이터를 묶어서 처리한다.
      여러 행들의 그룹이 모여서 그룹당 하나의 결과를 리턴하는 함수
      SELECT, HAVING, ORDER BY 절에 사용될 수 있다.
      SELECT 문장 내의GROUP BY 절은
      HAVING 절을 그룹을 제한하기 위해 사용한다.
      집합 함수, 집계 함수, 그룹 함수라고 한다.

 

 ✔ 종류 

 

  COUNT (*) : NULL값을 포함한 행의 수
  COUNT (표현식) : 표현식의 값이 NULL값을 제외한 행의 수
  SUM ([DISTINCT|ALL] 표현식) : 표현식의 값이 NULL값을 제외한 합 ((숫자 데이터 가능)
  AVG ([DISTINCT|ALL] 표현식): 표현식의 값이 NULL값을 제외한 평균 ((숫자 데이터 가능)

  MIN ([DISTINCT|ALL] 표현식) : 표현식의 값이 NULL값을 제외한 최솟값(문자,(문자, 날짜 데이터 사용 가능))
  MAX ([DISTINCT|ALL] 표현식) : 표현식의 값이 NULL값을 제외한 최댓값(문자,(문자, 날짜 데이터 사용 가능))

  STDDEV ([DISTINCT|ALL] 표현식) : 표현식의 값이 NULL값을 제외한 표준편차 ((숫자 데이터 가능)
  VARIAN ([DISTINCT|ALL] 표현식) : 표현식의 값이 NULL값을 제외한 분산을 출력 ((숫자 데이터 가능)

 

 

 

  □ COUNT (*),  COUNT (표현식)

 

 

 

    □ AVG       

 

      SELECT AVG(COMM), AVG(NVL(COMM,0))

      FROM EMP;

 

Q1)

 

  # 사원 테이블에서 모든 SALESMAN에 대하여 급여의 평균, 최고액, 최저액, 합계를 구해보자.

 1) SELECT AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL)
     FROM EMP
     WHERE JOB = 'SALESMAN';  

  2) SELECT AVG(SAL), MAX(SAL), MIN(SAL), SUM(SAL)
     FROM EMP
     WHERE JOB LIKE 'SAL%';

 

✔ 그룹 함수 구문

 

  SELECT 그룹 함수([DISTINCT]|ALL, {COLUMN, , ,})
  FROM 테이블명
  WHERE 조건 → 단 그룹의 조건문은 사용할 수 없다.
  GROUP BY COLUMN1, COLUMN2 , , , → 단 별칭은 사용할 수 없다.
  HAVING 그룹 조건
  ORDER BY COLUMN1, COLUMN2 , , ,

 

  Q2)

 

     # 부서별 사원의 부서 번호를 출력해보자.

    SELECT DEPTNO
    FROM EMP
    GROUP BY DEPTNO
    ORDER BY DEPTNO;

 

  Q3)

 

    # 부서별 사원의 봉급 평균을 구하자

 

   SELECT DEPTNO, AVG(SAL)
   FROM EMP
   GROUP BY DEPTNO
   ORDER BY DEPTNO;

 

  Q4)

 

   # 부서별 사원의 인원수, 평균 급여, 급여의 합, 최대급여, 최저급여를 구하자

 

   SELECT DEPTNO, COUNT(*), AVG(SAL), SUM(SAL), MAX(SAL), MIN(SAL)
   FROM EMP
   GROUP BY DEPTNO;

 

 

   Q5)

 

    # 부서별 사원의 인원수, 평균 급여, 급여의 합, 최대급여, 최저급여를 구하자
       단 급여의 합이 가장 많은 순으로 출력하자.

   SELECT DEPTNO, COUNT(*), AVG(SAL), SUM(SAL), MAX(SAL), MIN(SAL)
   FROM EMP
   GROUP BY DEPTNO
   ORDER BY 4 DESC;

 

   Q6)

 

    # 직업과 부서별 사원의 기준으로 인원수, 평균 급여, 급 여의합, 최대급여, 최저급여를 출력하자.

 

   SELECT JOB, DEPTNO, COUNT(*), AVG(SAL), SUM(SAL), MAX(SAL), MIN(SAL)
   FROM EMP
   GROUP BY JOB, DEPTNO;

 

Q7)

 

  □ HAVING

 

  HAVING : 그룹 생성(행이 분류된다) → 그룹 함수가 그룹에 적용 → HAVING절의 조건에 일치하는 그룹에 표시된다.
  HAVING 절에는 GROUP BY에 참여하는 컬럼이나 Aggregate함수만 사용가능

    # 사원테이블에서 부서인원이 4명보다 많은 부서의 인원수, 급여의 합을 출력하자.

 

   SELECT DEPTNO, COUNT(*), SUM(SAL)
   FROM EMP
   GROUP BY DEPTNO
   HAVING COUNT(*)>4;

 

 

Q8)

 

   # 급여가 최대 2900 이상인 부서에 대해 부서번호, 평균급여, 급여의 합을 구해보자.

 

   SELECT DEPTNO, AVG(SAL), SUM(SAL)
   FROM EMP
   GROUP BY DEPTNO
   HAVING MAX(SAL) >= 2900;

 

 

Q9)

 

   # 직업별 평균 급여가 3000 이상인 직업에 대해서 직업, 평균급여, 급여의 합을 구해보자.

 

    SELECT JOB, AVG (SAL), SUM(SAL)
    FROM EMP
    GROUP BY JOB
    HAVING AVG (SAL) >= 3000;

 

Q10)

 

   # 부서별 평균 급여중 최대값이 얼마인지 리턴하자. (그룹함수는 2번까지 중첩이가능)

 

   SELECT MAX(AVG(SAL))
   FROM EMP
   GROUP BY DEPTNO;

 

 

 

😎 알아두기 1

   👉 SQL 실행

   JOIN 을 통해 큰 테이블을 만든다 → FROM
   테이블로 부터 한 ROW씩 읽어 조건을 만족하는 결과만 출력한다. → WHERE
   원하는 그룹별로 행들을 GROUPING한다. → GROUP BY
   원하는 조건을 만족하는 그룹을 남긴다. → HAVING
   주어진 조건에 따라 정렬한다. → ORDER BY
   원하는 결과만 출력한다. → SELECT

 

  👉 SQL실행순서

    SELECT → 6
    FROM → 1
    WHERE → 2
    GROUP BY → 3
    HAVING → 4
    ORDER BY → 5

 

😎 알아두기 2 (아래의 본문이 틀린 이유)

   1)  SELECT POSITION 포지션, AVG(HEIGHT) 평균키
       FROM PLAYER_T → 포지션은 여러줄인데 AVG는 한 행

   2) SELECT POSITION POS, AVG(HEIGHT) 평균키
      FROM PLAYER_T
      GROUP BY POS; → 별칭 올 수 없다.

  3) SELECT POSITION POS, AVG(HEIGHT) 평균키
     FROM PLAYER_T
     WHERE AVG(HEIGHT) >= 180 → HAVING에 있어야한다.
     GROUP BY POSITION ; 

 

GROUP BY 에 ROLLUP 및 CUBE 연산자 사용

  ROLLUP 및 CUBE 연산자를 질의의 GROUP BY절에 지정할 수 있다.
  ROLLUP 그룹화는 정규 그룹화 행과 하위 총계 값을 포함하는 결과 집합을 산출한다.
  CUBE 연산자를 GROUP BY 절에 사용하면 지정된 표현식에서 가능한 모든 조합값에 따라

    선택된 행이 그룹화 되고 각 그룹에 대한 요약 정보를 나타내는 행이 반환된다.
  CUBE : ROLLUP 의 결과 행 및 교차 도표화 행을 포함하는 결과 집합을 산출한다.


✔ ROLLUP 연산자

 

   보고서를 작성할 때 집합에서 통계 및 요약 정보를 추출하는데 사용할 수 있다.
   GROUP BY 절에 지정된 열 목록을 따라 오른쪽에서 왼쪽 방향으로 하나씩 그룹을 만든다.
   → 그런다음 그룹함수로를 생성한 그룹에 적용한다.
   ROLLUP 연산자없이 N(GROUP BY 열 수)차원의 하위 총계를 산출하려면 N+1개의 SELECT문을 UNION ALL로 연결 해야 한다. 그러면 모든 SELECT문이 각각 테이블에 액세스 하므로 질의가 비효율적으로 실행된다.
  단 한번 테이블에 액세스하여 해당 결과를 취합한다.
  하위 총계를 산출하는데 필요한 열이 많은 경우 유용하다.

 

Q11)

 

  # 부서별 인원수 봉급의 합을 조회시 ROLLUP을 이용해서 총 집계를 조회하자. → 자동정렬

 

   SELECT DEPTNO, COUNT(*), SUM(SAL)
   FROM EMP
   GROUP BY ROLLUP(DEPTNO);

 

 ※ ROLLUP 실행 전 예제

   SELECT DEPTNO, COUNT(*), SUM(SAL)
   FROM EMP
   GROUP BY DEPTNO;

 

Q12)

 

   # 부서별, 직업별 봉급의 합을 조회시 ROLLUP을 이용해서 총 집계를 조회하자.

 

   SELECT DEPTNO,JOB, SUM(SAL)
   FROM EMP
   GROUP BY ROLLUP(DEPTNO,JOB);

 

 

※ ROLLUP 실행 전 예제

 

   SELECT DEPTNO,JOB, SUM(SAL)
   FROM EMP
   GROUP BY DEPTNO,JOB;

 

 

  
   # 부서별, 직업별, 매니저, 봉급의 합을 조회시 ROLLUP을 이용해서 총 집계를 조회하자.

   SELECT DEPTNO, JOB, MGR, SUM(SAL)
   FROM EMP
   GROUP BY ROLLUP(DEPTNO, JOB, MGR);

   → GROUP BY DEPTNO, JOB, MGR, SUM + GROUP BY DEPTNO + 총합계 + GROUP BY JOB + 총합계 

✔ CUBE

 

   : GROUP BY 확장형이다.
     ROLLUP은 하위 총계 조합중에 일부만 산출하지만,

     CUBE는 GROUP BY 절에 지정된 모든 그룹의 조합에 대해 하위총계와 최상위 총계를 산출한다.
     집계함수에 CUBE연산자를 사용하면 결과 집합에 추가 행이 만들어진다.
     GROUP BY 절에 N개의 열이 있을경우 상위 집계 조합수는 2의 N승 개가 만들어진다.

  Q13)

 

   # 부서별 인원수 봉급의 합을 조회시 CUBE을 이용해서 총 집계를 조회하자. 

 

   SELECT DEPTNO, COUNT(*), SUM(SAL)
   FROM EMP
   GROUP BY CUBE(DEPTNO);

  Q14)

 

   # 부서별, 직업별 봉급의 합을 조회시 CUBE을 이용해서 총 집계를 조회하자.

 

   SELECT DEPTNO,JOB, SUM(SAL)
   FROM EMP
   GROUP BY CUBE(DEPTNO,JOB);

 

  # 부서별, 직업별, 매니저 봉급의 합을 조회시 CUBE을 이용해서 총 집계를 조회하자.

     : CUBE연산자가 그룹함수가 적용되어 일반적으로 CROSS TABULATION 결과 집합을 산출한다.

 

    SELECT DEPTNO,JOB,MGR, SUM(SAL)
    FROM EMP
    GROUP BY CUBE(DEPTNO, JOB, MGR); → 명령문은 8개의 그룹화를 계산한다. (2 * 2 * 2)

 

 

✔ GROUPING 

   GROUPING 함수는 ROLLUP, CUBE, 연산자와 함께 사용한다.
   행에서 하위 총계를 형성한 그룹을 찾을 수 있다.
   ROLLUP, CUBE를 통해 만들어진 NULL값과 지정된 (저장된) NULL값을 구분할 수 있다.
   0(해당열) 또는 1(집계연산결과 NULL), 을 리턴한다.

 

    SELECT DEPTNO, JOB, SUM(SAL), GROUPING(DEPTNO),GROUPING(JOB)
    FROM EMP
    GROUP BY ROLLUP(DEPTNO,JOB);

 

 

   SELECT DEPTNO, JOB, SUM(SAL), GROUPING(DEPTNO),GROUPING(JOB)
   FROM EMP
   GROUP BY CUBE

   (DEPTNO,JOB);

 

 

✔ GROUPING SETS : 여러그룹을 정의할 수 있다.

 

     SELECT DEPTNO,JOB,MGR, AVG(SAL)
     FROM EMP
     GROUP BY GROUPING SETS((DEPTNO,JOB,MGR), (DEPTNO,MGR), (JOB,MGR));

 

 

 

□ UNION ALL : SELECT 합치기 

 : 위의 GROUPING SET 과 같은 결과 확인

 

SELECT DEPTNO, JOB, MGR, AVG(SAL) FROM EMP
GROUP BY DEPTNO, JOB, MGR
UNION ALL
SELECT DEPTNO, NULL, MGR, AVG(SAL) FROM EMP
GROUP BY DEPTNO, MGR
UNION ALL
SELECT NULL, JOB, MGR, AVG(SAL) FROM EMP
GROUP BY JOB, MGR

 

 

 

✔ 조합열 

 : 조합열을 ROLLUP, CUBE와 사용하면 특정 레벨에 대한 집계를 건너뛴다.

SELECT DEPTNO, JOB, MGR, SUM(SAL)
FROM EMP
GROUP BY ROLLUP (DEPTNO, (JOB,MGR)); → 조합열

   1. GROUP BY DEPTNO, JOB, MGR
   2. GROUP BY DEPTNO
   3. GROUP BY NULL

 

😎 알아두기 3

     GROUP BY GROUPING SETS(A, B, C)

                      ▼

     GROUP BY A

     UNION ALL
     GROUP BY B

     UNION ALL
     GROUP BY C

    GROUP BY GROUPING SETS(A, B, (B,C)) 

                    ▼

    GROUP BY A

    UNION ALL
    GROUP BY B

    UNION ALL
    GROUP BY B, C

 

 

    GROUP BY GROUPING SETS((A, B, C))

                    ▼

    GROUP BY A, B, C

 

    GROUP BY GROUPING SETS(A, (B), ()) 

                    ▼

    GROUP BY A

    UNION ALL
    GROUP BY B

    UNION ALL
    GROUP BY ()