SQL - 다중행 함수

 

1. 하나의 열에 출력 결과를 담는 다중행 함수 

 

  > 그룹 함수 또는 복수행 함수로 불림 

  > 여러행을 가지고 계산한 결과를 하나의 행으로 return

  > 다중행 함수를 사용한 SELECT절에는 기본적으로 여러 행이 결과로 나올 수 있는 열을 함께 사용 불가 

 

* MYSQL과 MARIADB에선 가능 -> 가장 위에 있는 값 그냥 붙여줌 

 

- SUM,COUNT,MAX,MIN,AVG 

   

  > SUM: null값 제외 DISTINCT 중복 제외

  > COUNT: *(null포함 행의 수 ), 특정 열(null제외), DISTINCT

  > MAX,MIN: 날짜데이터에서 날짜가 최근에 가까울 수록 큼 

  > AVG : 평균 DISTINCT 가능 

 

 *NULL값을 제외하는 특징이 있음으로, sum이나 AVG 구할 때 null처리 해야함에 유의

2. GROUP BY절

 

- 다중행 함수는 지정 테이블의 데이터 가공하여 하나의 결과 출력 

- 테이블의 데이터를 그룹으로 나누면, 그룹별 결과 출력 가능 

- GROUP BY [그룹화 열 지정(여러개)] -> 특정 열 또는 데이터 기준으로 데이터를 그룹으로 묶는다.

- 먼저 지정한 열을 대그룹, 다음 열을 소그룹으로 나눔 

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

 * SELECT절에 그룹바이한 절 혹은 다중행 함수 적용한 절만 사용가능 

 

3. HAVING절 

 - HAVING절은 SELECT문에 GROUP BY절이 존재할 때 사용가능 

 - 그룹바이 결과 값의 범위를 제한하는데 사용한다. 

SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO, JOB;

* HAVING절과 WHERE의 차이점:  - WHERE가 실행시점이 더 빠르다, WHERE절에 그룹함수로 조건  x

 

4. 그룹화와 관련된 함수 (ROLLUP, CUBE, GROUPING SETS)

 

- GROUP BY절에 사용할 수 있는 특수 함수이다.

 

4.1 ROLLUP, CUBE,GROUPING SETS

 

  > 그룹화 데이터 합계 출력 시 유용하게 사용할 수 있다. 

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

-- 
GROUP BY DEPTNO, ROLLUP(JOB);

- ROULLUP함수는 명시한 열을 소그룹부터 대그룹의 순서로 각 그룹별 결과를 출력하고 마지막 총 데이터 결과 출력 

  (n -> n+1조합의 출력 a,b,c -> a,b,c 그룹별 -> a그룹별 b 그룹 결과 -> a그룹 결과-> 전체 결과 )

   - 대그룹 A 기준으로 점점 좁아짐  

  

- CUBE함수는 지정한 모든 열에 가능한 조합을 생성합니다. 

  (n-> 2^n조합  CUBE(A,B,C) -> A그룹별 B그룹별 C그룹 -> (A그룹별B, A그룹별C) -> B그룹별C -> (각A,B,C그룹 결과)-> 전체 데이터 결과)

 

* 그룹이 늘어날 수록 수가 기하급수적으로 늘어남 따라서 필요한 조합(보고싶은 조합만) ROLLUP, CUBE도 가능 

   이를 분할 롤업,큐브라고 함 

* 처음에 사용하기 어렵다. -> 일단 그룹으로 나누고 ROLLUP,CUBE를 조금씩 적용해보자 

 

- GROUPING SETS 함수 같은 수준의 그룹화 열이 여러 개 일때, 각 열별 그룹화를 통해 결과 값 출력

  > 앞선 함수들은 대그룹,소그룹이 나뉘어져 있었지만, 위 함수는 지정한 모든 열이 각각 대그룹으로 처리됨 

SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB)
ORDER BY DEPTNO,JOB;

 > 지정 열이 계층적으로 분리되는 것이 아니라, 각각 그룹화되어 연산 결과 수행

 

4.2 그룹화 함수 (GROUPING, GROUPING_ID -> SELECT절에서 사용)

 

 - 데이터 가공, 연산 수행하지 않음 -> 그룹화 데이터 식별, 가독성 높이기 위한 목적 

 

- GROUPING 함수 

  > ROLLUP이나, CUBE함수에서 사용한 그룹바이절에 그룹화 대상 열에 적용

  > 그룹화된 상태로 결과가 집계되었는지 확인하는 데 사용함

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

 > 각각 DEPTNO으로 그룹화되어 있으면, 0 아니면 1

            JOB으로 그룹화되어 있으면, 0 아니면 1 

 

- GROUPING_ID 

  > 특정 열이 그룹화되었는지 출력 

  > GROUPING함수와 동일 다만, 여러열 지정 가능 

  > 그룹id함수를 사용한 결과는 그룹화 비트 벡터값으로 나타낸다.

    ex(그룹 a,b -> 비트벡터 00 -> 0,

         a로만 그룹 -> 비트벡터 01 -> 1,

         b로만 그룹 -> 비트벡터 10 -> 2,

         없음 ->            비트벡터 11 -> 3)

 

- LISTAGG 함수 

 

  > 그룹바이절에 명시하지 않고, SELECT에 열 사용가능 

 

SELECT DEPTNO,
       LISTAGG(ENAME,',')
       WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
       FROM EMP
       GROUP BY DEPTNO;

 >WITHIN GROUP(ORDER BY 나열할 열의 정렬기준 열))

 

- PIVOT, UNPIVOT 함수 > 행과 열 반전 ** 필요할 때 찾아보자

- 윈도우 함수등은 나중에 찾아보잣..

 

 

참고자료: 오라클로 배우는 데이터베이스 입문(이지훈)