[3장] SQL 복수 행 함수 (그룹 함수)

 

 
1. GROUP 함수의 종류
​

함수 이름	의  미
COUNT	입력되는 데이터들의 건수를 출력
SUM	입력되는 데이터들의 합계값을 출력
AVG	입력되는 데이터들의 평균값을 출력
MAX	입력되는 데이터들 중 최고 값을 출력
MIN	입력되는 데이터들 중 최저 값을 출력
STDDEV	입력되는 데이터 값들의 표준 편차값 출력
VARIANCE	입력되는 데이터 값들의 분산값 출력
ROLLUP	입력되는 데이터들의 소계값을 자동으로 계산해서 출력
CUBE	입력되는 데이터들의 소계 및 전체 총계를 자동 계산 후 출력
GROUPING	해당 칼럼이 그룹에 사용되었는지 여부를 1 또는 0으로 반환
GROUPINGSET	한번의 질의로 여러개의 그룹화 가능

 

 

  

 (1) COUNT 함수 : 입력되는 데이터의 총 건수 반환, NULL 값은 제외

 

 (2) SUM 함수 : 입력된 데이터들의 합계 값을 구하는 함수

 

 (3) AVG 함수 : 입력된 값들의 평균 값을 구해주는 함수

 

 

> 올바른 평균 값 x

 

 

 

 

 

 (4) MAX / MIN 함수

    : 여러 건의 데이터를 입력 받아서 순서대로 정렬 후 그 중에 최대/최소 값을 추출

     이 원리 때문에 시간이 오래 걸리는 함수 중 하나, 인덱스를 활용하는 것을 적극 권장 함

 

 

 (5) STDDEV / VARIANCE 함수 : 표준 편차(STDDEV)와 분산(VARIANCE)을 구하는 함수

 

 

 

 

 

2. 특정 조건으로 세부적인 그룹화 하기(GROUP BY 절 사용하기)

 

 1) SELECT 절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 함

     그렇지 않을 경우 아래와 같은 에러 발생

 

 

 

 

 2) GROUP BY 절에 사용된 컬럼은 SELECT 절에 사용되지 않아도 됨

 

  

 

 3) GROUP BY 절에는 반드시 컬럼명이 사용되어야 하며 컬럼 Alias는 사용 X

 

 

 

 

3. 조건을 주고 검색하기(HAVING 절 사용하기)

 

 

 

 

  ※ WHERE 절은 그룹 함수를 비교 조건으로 쓸 수 없기 때문에 WHERE 대신에 HAVING 절을 사용한다.

 

 

 

- 그룹 함수를 비교 조건으로 사용하려면 반드시 HAVING 절을 사용해야 한다.

-  HAVING 절은 GROUP BY 절 이전/이후 상관없이 사용 가능하다.

- 그룹 함수를 사용하는 SQL이라 하더라도 WHERE 문장을 쓸 수 있다. 단, GROUP 함수 부분에만 WHERE를 사용하면 안됨.

 

 

     

 

4. 자동으로 소계 / 합계를 구해주는 함수

 

 (1) ROLLUP 함수 : 주어진 데이터들의 소계

 

 

 

 

 

 (2) CUBE 함수 : 주어진 데이터들의 소계와 전체 총계

 

 

< deptno, position 모두를 CUBE한 결과 >

 

 

 

 

< position 별로 grouping하고 position 별로 pay를 SUM한 결과 >

 

 

 

5. 다른 그룹핑 관련 함수들 살펴보기

 

 (1) GROUPING 함수 : Grouping 작업에 사용되었는지 아닌지를 구별해주는 함수(0:집계, 1:집계X)

 

 

< 부서별로 급여 합계를 구하는 쿼리 >

 

[설명] 가장 마지막 합계 부분만 사용되지 않았고(1), 나머지 부분은 모두 사용됨(0)

 

 

 

 

 (2) GROUPING_ID 함수 : GROUPING 함수를 보다 편하게 사용하기 위해 기능을 합쳐 둔 것.

즉, 어떤 컬럼이 그룹핑에 사용되었는지 안되었는지를 확인하기 위해 GROUPING 함수를 사용하는데 만약 컬럼이 여러 개가 있을 경우 GROUPING 함수를 여러 번 써야 하기 때문에 그 부분을 보다 쉽게 확인하기 위해 기능을 합쳐 둔 것이 GROUPING_ID 함수.

 

GROUPING_ID 함수는 여러 개의 컬럼을 동시에 사용하기 때문에 컬럼의 순서가 중요하며 아래와 같은 의미가 있습니다.

 

GROUPING 칼럼	BIT	GROUPING 결과	의  미
A , B	0 0	0	두 컬럼 다 GROUPING에 사용됨
A	0 1	1	A 칼럼만 GROUPING에 사용됨
B	1 0	2	B 칼럼만 GROUPING에 사용됨
-	1 1	3	두 칼럼 모두 사용 안 됨

 

 

 (3) GROUPING SETS : 같은 테이블에 같은 함수를 쓸 때 사용, 심플한 쿼리를 위해 사용한다.

   

<  기  존  방  법  >                                                             < GROUPING SETS 이용 >

 

 

 

 

 (4) LISTAGG 함수(11g에서 추가됨)

 

 

 

[설명] LISTAGG에 나열하고 싶은 칼럼 이름을 먼저 적고, 데이터들을 구분할 구분자를 홑따옴표 사이에 기록

WITHIN GROUP 사이에 가로로 나열하고 싶은 규칙을 ORDER BY 로 적어주기만 하면 됨
위 예에서는 출력 순서가 hiredate 값을 기준으로 나열했습니다.

만약 데이터를 구분할 구분자를 주지 않으면 아래와 같이 모든 데이터가 한 줄로 연결되어 출력 됩니다.

 

WITHIN GROUP ( ) 절에 아무 조건도 주지 않으면 에러가 발생

그리고 LISTAGG 함수의 두 번째 파라미터(각 데이터끼리의 구분자)에 예약어도 사용될 수 없음

 

 

 

(5) PIVOT 함수(11g부터 추가)

    : PIVOT 함수는 row 단위를 column 단위로 변경해 주고

      UNPIVOT 함수는 반대로 column 단위를 row 단위로 변경해 주는 함수

 

 

▶ PIVOT 기능을 사용하여 달력 만들기

 

 

[설명] 가상의 뷰를 하나 생성 후 Pivot 기능을 활용하여 결과를 만듬, IN 연산자 뒤에는 서브쿼리를 사용할 수 없다.

PIVOT 절에 MAX(num_day) 절은 DECODE 문장에서 사용되는 함수를 적으면 되고 FOR 절에는 화면에 집계될 그룹핑 할 컬럼을 적으면 됨

 

 

▶ EMP 테이블에서 부서별로 각 직급별 인원이 몇명인지 계산해서 출력하세요.

 

- DECODE 함수를 사용하는 방법

 

 

 

- PIVOT 함수를 사용하는 방법

 

 

[설명] PIVOT 부분에 DECODE 함수에서 사용했던 그룹함수( COUNT 함수) 를 쓰고 FOR 부분에 그룹핑을 할 컬럼 이름과 IN 뒷부분에 분류할 목록을 적어 주는 것을 알 수 있습니다.

 

 

 

6. 그 외 주요 그룹 함수

 

 (1) LAG 함수 : 이전 행 값을 가져올 때 사용하는 함수

 

[문법] LAG (출력할 컬럼명, OFFSET, 기본 출력값)

OVER (Query_partition구문 , ORDER BY 정렬할 컬럼)

 

 

 

 

 (2) LEAD 함수 : 이후의 값을 가져올 때 사용하는 함수

 

[문법] LEAD (출력할 컬럼명, OFFSET, 기본 출력값)

OVER (Query_partition구문 , ORDER BY 정렬할 컬럼)

 

 

 

 

  (3) RANK 함수 : 순위 출력 함수

 

     - 집계용 문법

[문법] RANK(조건값) WITHIN GROUP (ORDER BY 조건값 칼럼명 [ ASC | DESC ] )

 

 

 

 

     - 분석용 문법

[문법] RANK() OVER (ORDER BY 조건값 칼럼명 [ ASC | DESC ] )

 

 

▶ 교수 테이블( professor ) 테이블에서 교수들의 교수번호와 이름, 급여, 급여순위를 출력하세요.

 

 

 

 

 

▶ Emp 테이블에서 10번 부서에 속한 직원들의 사번과 이름, 급여, 해당 부서내의 급여순위를 출력하세요.

 

 

 

 

▶ emp 테이블을 사용하여 사번, 이름, 급여, 부서번호, 부서별 급여순위를 출력하세요.

 

 

 

 

 

 (4) 누적 합계 구하기

 

▶ panmae 테이블을 사용하여 1000 번 대리점의 판매 내역을 출력하되

    판매일자, 제품코드, 판 매량, 누적 판매금액을 출력

 

 

​

​

​

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.116-144