[Oracle] 다중행 함수(Multiple Row Function)

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

 

다중행 함수(Multiple Row Function)

SELECT ENAME, SUM(SAL)
FROM EMP;

다중행 함수는 여러 행이 입력되어 하나의 행으로 결과가 출력되는 특징을 가지고 있다. 그래서 위의 예시와 같이  SELECT 절에서 여러 행이 이 결과로 나올 수 있는 열(함수 : 연산자가 사용된 데이터도 포함)을 함께 사용할 수 없다.

 

실행결과(오류)

 

- 자주 사용하는 다중행 함수

함  수	설  명
SUM	지정한 데이터의 합 반환
COUNT	지정한 데이터의 개수 반환
MAX	지정한 데이터 중 최대값 반환
MIN	지정한 데이터 중 최소값 반환
AVG	지정한 데이터의 평균값 반환

 DISTINCT 나  ALL 을 생략하면 기본값으로  ALL 이 된다. 

 

 

  ■ 합계 (SUM)

SUM([DISTINCT, ALL중 하나를 선택하거나 아무값도 지정하지 않음(선택)] 
    [합계를 구할 열이나 연산자, 함수를 사용한 데이터])

-- ex
SELECT SUM(DISTINCT SAL),
    SUM(ALL SAL),
    SUM(SAL)
FROM EMP;

합계를 구할 때  SUM 함수를 사용한다.  SUM 함수는 NULL데이터를 제외하고 합계를 구한다. 

 

옵션     DISTINCT  : 같은 결과 값을 가진 데이터는 합계에서 한 번만 사용됨(중복 데이터 제외)    ALL  : 중복 데이터를 포함해서 사용 (옵션을 사용하지 않은 결과값과 같음)

 

실행결과

 

SUM([DISTINCT, ALL중 하나(선택)] 
    [합계를 구할 열이나 연산자, 함수를 사용한 데이터])
OVER(분석을 위한 여러 문법을 지정)(선택)

 SUM 함수를 분석하는 용도로 사용한다면  OVER 절을 사용할 수도 있다.

 

 

 

  ■ 데이터의 개수 (COUNT)

COUNT([DISTINCT, ALL중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
    [개수를 구할 열이나 연산자 함수를 사용한 데이터]) 
OVER(분석을 위한 여러 문법 지정)(선택)

결과값의 개수를 구할 때  COUNT 함수를 사용한다. 보통  WHERE 절의 조건식과 함께 사용해서 특정 조건을 만족하는 데이터의 개수를 세거나 할 때 사용한다.  NULL 이 데이터로 포함되어 있을 경우 개수에서 제외된다.

 

 

 

  ■ 최대값과 최소값 (MAX, MIN)

-- 최대값 --
MAX([DISTINCT, ALL중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
    [최대값을 구할 열이나 연산자, 함수를 사용한 데이터])
OVER(분석을 위한 여러 문법 지정)(선택)

-- 최소값 --
MIN([DISTINCT, ALL중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
    [최대값을 구할 열이나 연산자, 함수를 사용한 데이터])
OVER(분석을 위한 여러 문법 지정)(선택)

최대값과 최소값은 데이터의 중복과 무관하게 같은 결과 값을 반환하기 때문에  DISTINCT 나  ALL 을 지정하지 않는다.

 

SELECT MAX(HIREDATE)
FROM EMP
WHERE DEPTNO = 20;

오라클 데이터베이스에서는 날짜 및 문자 데이터도 비교가 가능하기 때문에  MAX ,  MIN 함수를 사용할 수 있다. 위의 예제는  DEPTNO (부서번호)가  20 인 사람들 중 가장 최근에 입사한 날짜가 언제인지 구하는 예제이다.

 

실행결과

 

 

 

  ■ 평균값 (AVG)

AVG([DISTINCT, ALL중 하나를 선택하거나 아무값도 지정하지 않음(선택)]
    [평균 값을 구할 열이나 연산자, 함수를 사용한 데이터])
OVER(분석을 위한 여러 문법을 지정)(선택)

데이터의 평균값을 구할 때  AVG 를 사용한다.

 DISTINCT 를 지정하면 중복 값을 제외하고 평균값을 구하므로 결과 값이 달라질 수 있다.

 

 

 

 

 

---

GROUP BY절

SELECT   [조회할 열1 이름], [열2 이름], ..., [열N 이름]     FROM    [조회할 테이블 이름]     WHERE   [조회할 행을 선별하는 조건식]     GROUP BY   [그룹화할 열을 지정(여러 개 지정 가능)]     ORDER BY   [정렬하려는 열 지정]

 GROUP BY 절은 여러 데이터에서 의미 있는 하나의 결과를 특정 열 값 별로 묶어서 출력할 때 사용한다.(이 과정을 '그룹화'라고 함)  GROUP BY 절의 순서는 위의 형식과 같고 별칭이 인식되지 않기 때문에 열 이름이나 연산식을 그대로 지정해 주어야 한다.

 

-- ERROR --
SELECT ENAME, DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

또한 다중행 함수를 사용하지 않은 일반 열은  GROUP BY 절에 명시하지 않으면  SELECT 절에서 사용할 수 없다(==  GROUP BY 절을 사용한 그룹화는 그룹화된 열 외에 일반 열을  SELECT 절에 명시할 수 없다.). 위의 예제도  DEPTNO (부서번호)를 기준으로 그룹화되어  DEPTNO (부서번호)열과  AVG(SAL) (평균월금)열은 한 행으로 출력되지만,  ENAME (사원이름)열은 여러 행으로 구성되어 각 열 별 데이터 수가 달라지기 때문에 출력이 불가능하다.

 

실행결과

 

 

 

 

 

---

HAVING절

SELECT    [조회할 열1 이름], [열2 이름], ..., [열N 이름]      FROM     [조회할 테이블 이름]      WHERE   [조회할 행을 선별하는 조건식]      GROUP BY   [그룹화할 열을 지정(여러 개 지정 가능)]      HAVING   [출력 그룹을 제한하는 조건식]     ORDER BY   [정렬하려는 열 지정]

 HAVING 절은  SELECT 문에  GROUP BY 절이 존재할 때만 사용할 수 있다.  GROUP BY 절을 통해 그룹화된 결과 값의 범위를 제한하는 데 사용된다.

 

 

WHERE절과 HAVING절의 차이점

만약 출력 결과를 제한하기 위해  HAVING 을 사용하지 않고  WHERE 절에 명시하면  SELECT 문이 실행되지 않고 오류가 발생한다.

(출력 행을 제한하는  WHERE 절에서는 그룹화된 데이터를 제한하는 조건식을 지정할 수 없다.)

 

 

 

 

---

나중에 정리할 함수들(복습할 때 다시 정리할 예정입니다.)

ROLLUP, CUBE, GROUPING SETS

GROUPING, GROUPING_ID

LISTAGG, PIVOT, UNPIVOT