SQL을 활용한 데이터 분석

그룹

카테고리 : SQL을 활용한 데이터 분석

그룹

그룹 함수

  • 그룹 함수는 하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등 하나의 결과로 나타낸다.
  • 그룹함수나 GROUP BY 절이 들어간 SELECT 문장이다.
  • 테이블에 있는 데이터들을 특정 형태로 집계한 결과를 반환하거나 특정 목적에 맞는 총합, 평균, 최대 및 최소 갑을 반환한다
구분설명
SUM그룹의 누적 합계를 반환한다.
AVG그룹의 평균을 반환한다.
COUNT그룹의 총 개수를 반환한다.
MAX그룹의 최대값을 반환한다.
MIN그룹의 최소값을 반환한다.
STDDEV그룹의 표준편차를 반환한다.
VARIANCE그룹의 분산을 반환한다.
  • 위는 그룹 함수의 종류를 정리한 표이다.

합계 구하는 SUM 함수

  • SUM 함수는 해당 컬럼 값들에 대한 총합을 구하는 함수 이다.

SELECT SUM(SAL) FORM EMP;
  • 위는 급여 총액을 구하는 예제이다.

SELECT SUM(COMM) FORM EMP;
  • 위는 커미션 총액을 구하는 예제이다.
  • 이때 해당 컬럼 값이 NULL 인 것에 대해서는 제외하고 계산한다.

평균을 구하는 AVG 함수

  • AVG 함수는 해당 컬럼 값들에 대한 평균을 구하는 함수 이다. 이때 해당 컬럼 값이 NULL 인 것에 대해서는 제외하고 계산한다.

SELECT AVG(SAL) FORM EMP;
  • 위는 금여 평균 구하는 예제이다.

최대값 구하는 MAX, 최소값 구하는 MIN 함수

  • 지정한 컬럼 값들 중에서 최대값을 구하는 함수가 MAX이고, 최소값을 구하는 함수가 MIN이다.

SELECT MAX(SAL), MIN(SAL) FORM EMP;
  • 위는 가장 높은 급여와 가장 낮을 급여를 구하는 예제이다.

로우 개수 구하는 COUNT 함수

  • COUNT 함수는 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수이다. COUNT 함수에 특정 컬럼을 기술하면 해당 컬럼 값을 갖고 있는 로우의 개수를 계산하여 준다.

SELECT COUNT(COMM) FORM EMP;
  • 위는 사원 테이블의 사원들 중에서 커미션을 받은 사원의 수를 구하는 예제이다.
  • COUNT 함수는 NULL 값에 대해서는 개수를 세지 않는다.
  • 만일 COUNT 함수에 COUNT(*)처럼 *을 적용하면 테이블의 전체 로우 수를 구하게 된다.

SELECT COUNT(*), COUNT(COMM) FORM EMP;
  • 위는 전체 사원의 수와 커미션을 받는 사원의 수를 구하는 예제이다.
  • 직업의 종류가 몇 개인지 즉, 중복되지 않는 직업의 개수를 카운트 해보자
  • 이 때 중복 행 제거 키워드 DISTINCT를 써서 다음과 같이 질의 한다.

SELECT COUNT(DISTINCT JOB) 업무수 FROM EMP;

GROUP BY 절

  • 그룹함수를 쓰되 어떤 컬럼 값을 기준으로 그룹함수를 적용할 경우 GROUP BY 절 뒤에 해당 컬럼을 기술하면 된다.
  • 합계, 평균, 최대값, 최소값 등을 어떤 컬럼을 기준으로 그 컬럼의 값 별로 보자고 할 때 GROUP BY 절 뒤에 해당 컬럼을 기술 하면 된다.
  • GROUP BY 절을 사용할 때 주의할 점은 GROUP BY 절 다음에는 컬럼의 별칭을 사용할 수 없고, 반드시 컬럼명을 기술해야 한다는 점이다.

HAVING 절

  • SELECT 절에 조건을 사용하여 결과를 제한할 때는 WHERE 절을 사용하지만 그룹의 결과를 제한할 때는 HAVING 절을 사용한다.
  • WHERE 절에는 group 함수를 사용할 수 없다.
  • WHERE 절의 조건이 먼저 처리되며, WHERE 절에서 걸러진 로우를 대상으로 다시 HAVING 절 조건을 처리한다.

ROLLUP과 CUBE

  • ROLLUP : 그룹핑된 결과에 그룹별 합계 정보
    • 일반적인 누적에 대한 총계를 구할 때 아주 편리하게 사용
  • CUBE : 그룹핑된 컬럼의 모든 가능한 조합에 대한 합계 정보
  • 모두 GROUP BY 절에서 사용

ROLLUP


SELECT DEPTNO, JOB, SUM(SAL) SAL FROM EMP GROUP BY DEPTNO, JOB
UNION ALL
SELECT DEPTNO, NULL JOB, SUM(SAL) SAL FROM EMP GROUP BY DEPTNO
UNION ALL
SELECT NULL DEPTNO, NULL JOB, SUM(SAL) SAL FROM EMP
ORDER BY DEPTNO, JOB;

CUBE


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

GROUPING


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

PIVOT


SELECT * FROM ( SELECT ... FROM [테이블명])
PIVOT
( SUM([집계 할 컬럼]) FOR [대상이 될 컬럼]
  IN ([PIVOT시 컬럼 이름])
)
  • 위는 PIVOT 문의 기본 형식이다.
  • PIVOT 함수 사용하기

SELECT * FROM (SELECT DEPTNO, JOB, SAL FROM EMP)
    PIVOT(SUM(SAL) FRO JOB
    IN ('CLERK' AS "CLERK", 'MANAGER' AS "MANAGER", 'PRESIDENT' AS "PRESIDENT", 'ANALYST' AS "ANALYST",'SALESMAN' AS "SALESMAN"))
    OREDER BY DEPTNO;

UNPIVOT


CREATE TABLE EMP2 AS
SELECT * FROM (SELECT DEPTNO, JOB, SAL FROM EMP)
    PIVOT(SUM(SAL) FRO JOB
    IN ('CLERK' AS "CLERK", 'MANAGER' AS "MANAGER", 'PRESIDENT' AS "PRESIDENT", 'ANALYST' AS "ANALYST",'SALESMAN' AS "SALESMAN"))
    OREDER BY DEPTNO;

SELECT * FROM EMP2
UNPIVOT(SUM_SAL FOR JOB IN (CLERK, MANAGER, PRESIDENT, ANALYST, SALESMAN));

© 2020. All rights reserved.

SIKSIK