LuceteMango:)

집계 함수란 여러행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수이다.

집계함수(Aggregate function)의 이해

• - GROUP BY절을 이용하여 그룹 당 하나의 결과로 그룹화 할 수 있다.
• - HAVING절을 사용하여 집계함수를 이용한 조건 비교를 할 수 있다.
• - MIN, MAX 함수는 모든 자료형에 사용 할 수 있다.
• - 일반적으로 가장 많이 사용하는 집계함수에는AVG(평균), COUNT(개수), MAX(최대값), MIN(최소값), SUM(합계) 등이 있다.

COUNT

COUNT 함수는 검색된 행의 수를 반환 한다.

-- 검색된 행의 총 수 4개를 반환. 즉 4개의 부서가 존재한다.
SELECT COUNT(deptno) FROM dept;

COUNT(DEPTNO)
-------------
            4

MAX

MAX 함수는 컬럼값 중에서 최대값을 반환 한다.

-- sal 컬럼값 중에서 제일 큰값을 반환. 즉 가장 큰 급여를 반환.
SELECT MAX(sal) salary FROM emp;

SALARY
-------
  5000   

MIN

MIN 함수는 컬럼값 중에서 최소값을 반환 한다.

-- sal 컬럼값 중에서 가장 작은 값 반환. 즉 가장 적은 급여를 반환
SELECT MIN(sal) salary FROM emp;

 SALARY
-------
    800    

AVG

AVG 함수는 평균 값을 반환 한다.

-- 부서번호 30의 사원 평균 급여를 소수점 1자리 이하에서 반올림
SELECT ROUND(AVG(sal),1) salary 
  FROM emp 
 WHERE deptno = 30;

 SALARY
 ------
 1566.7

SUM

SUM 함수는 검색된 컬럼의 합을 반환 한다.

-- 부서번호 30의 사원 급여 합계를 조회.
SELECT SUM(sal) salary 
  FROM emp 
 WHERE deptno = 30;

 SALARY
-------
   9400    

STDDEV

STDDEV 함수는 표준편차를 반환 한다.

-- 부서번호 30의 사원 급여 표준편차를 반환.    
SELECT ROUND(STDDEV(sal),3) salary 
  FROM  emp 
 WHERE deptno = 30;

  SALARY
--------
 668.331  

집계함수 예

아래는 부서별 사원수, 최대급여, 최소급여, 급여합계, 평균급여를 급여합게 순으로 조회하는 예이다.

SELECT deptno 부서번호, COUNT(*) 사원수, 
       MAX(sal) 최대급여, MIN(sal) 최소급여, 
       SUM(sal) 급여합계, ROUND(AVG(sal)) 평균급여
  FROM emp
 GROUP BY deptno
 ORDER BY SUM(sal) DESC;


부서번호   사원수  최대급여  최소급여   급여합계   평균급여
------- -------- --------- -------- --------- ---------
     20       5      3000       800    10875     2175
     30       6      2850       950     9400     1567
     10       3      5000      1300     8750     2917

Outer Join 이란?

• - Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에서 값이 없다면 테이터를 반환하지 못한다.
• - 동일 조건에서 조인 조건을 만족하는 값이 없는 행들을 조회하기 위해 Outer Join을 사용 한다.
• - Outer Join 연산자는 "(+)" 이다.
• - 조인시 값이 없는 조인측에 "(+)"를 위치 시킨다.
• - Outer Join 연산자는 표현식의 한 편에만 올 수 있다.

Outer Join 예제

Equi Join과 Outer Join의 비교.

 
-- Equi Join 으로 부서 번호를 조회하는 예제
SELECT DISTINCT(e.deptno), d.deptno, d.dname
  FROM emp e, dept d
 WHERE e.deptno = d.deptno;

DEPTNO     DEPTNO
------ ----------
    10         10
    20         20
    30         30

-- Outer Join 으로 부서 번호를 조회하는 예제
SELECT DISTINCT(e.deptno), d.deptno
  FROM emp e, dept d
 WHERE e.deptno(+) = d.deptno;

DEPTNO  DEPTNO
 ------- --------
     10       10
     20       20
     30       30
              40

Outer Join을 사용하는 테이블에 추가로 조건절이 있다면 (+)연산자를 모두 해야 한다.

 
-- ename LIKE 조건절에 (+)연산자가 누락된 경우
SELECT DISTINCT(a.deptno), b.deptno
  FROM emp a, dept b
 WHERE a.deptno(+) = b.deptno
   AND a.ename LIKE '%';

DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30

-- ename LIKE 조건절에 (+)연산자를 추가해야 정상적으로 데이터가 조회 된다. 
SELECT DISTINCT(a.deptno), b.deptno
  FROM emp a, dept b
 WHERE a.deptno(+) = b.deptno
   AND a.ename(+) LIKE '%';

DEPTNO  DEPTNO
 ------- --------
     10       10
     20       20
     30       30
              40

LEFT, RIGHT, FULL Outer Join

Oracle9i 부터는 ANSI/ISO SQL 표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 한다.

LEFT OUTER JOIN

LEFT OUTERL JOIN은 오른쪽 테이블(아래 예제에서 emp테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.

-- LEFT OUTER JOIN 조인 예제
SELECT DISTINCT(e.deptno), d.deptno
  FROM dept d 
  LEFT OUTER JOIN emp e
  ON d.deptno = e.deptno;  

RIGHT OUTER JOIN

RIGHT OUTERL JOIN은 왼쪽 테이블(아래 예제에서 emp테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.

-- RIGHT OUTER JOIN 조인 예제
SELECT DISTINCT(e.deptno), d.deptno
  FROM emp e 
 RIGHT OUTER JOIN dept d
    ON e.deptno = d.deptno;

FULL OUTER JOIN

FULL OUTERL JOIN은 양쪽 테이블 모두 Outer Join걸어야 하는 경우 사용 한다.

-- FULL OUTER JOIN 조인 예제
SELECT DISTINCT(e.deptno), d.deptno
  FROM emp e 
  FULL OUTER JOIN dept d
    ON e.deptno = d.deptno;

Join 이란?

• - 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법 이다.
• - 보통 둘 이상의 행들의 공통된 값 Primary Key 및 Foreign Key 값을 사용하여 조인 한다.
• - 두 개의 테이블을 SELECT문장 안에서 조인 하려면 적어도 하나의 컬럼이 그 두 테이블 사이에서 공유 되어야 한다.

Join 방법과 방식

• - 조인의 방법 : Equi Join (등가 조인, 내부조인) , Non-Equi Join , Self Join , Outer Join
• - 조인의 방식 : Nested Loop Join, Sort Merge Join, Hash Join

Equi Join

• - 가장 일반적으로 사용하는 Equality Condition(=)에 의한 조인이다
• - Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.

-- dept 테이블과 emp 테이블을 조인하는 예제
SELECT e.empno, e.ename, d.dname
  FROM dept d, emp e
 WHERE d.deptno = e.deptno;

콤마(,) 대신 INNER JOIN을 사용 할 수 있으며, INNER는 생략 가능하다. Join 조건은 ON 절에 온다.

-- INNER JOIN절을 이용하여 조인하는 예제
SELECT e.empno, e.ename, d.dname
  FROM dept d 
 INNER JOIN emp e
    ON d.deptno = e.deptno;

NATURAL JOIN을 사용 하면 동일한 컬럼을 내부적으로 모두조인 하므로, ON절이 생략 가능하다.

-- NATURAL JOIN절을 이용하여 조인하는 예제
SELECT  e.empno, e.ename, d.dname
  FROM  dept d 
NATURAL JOIN emp e;

NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되는데, USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있다.

-- JOIN~USING절을 이용하여 조인하는 예제
SELECT e.empno, e.ename, deptno 
  FROM emp e 
  JOIN dept d 
 USING (deptno);

Non-Equi Join

• - 테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을 때 사용하고, 조인조건은 동등( = )이외의 연산자를 갖는다.
• - BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN
• - 거의 사용하지 않는다

-- emp 테이블과 salgrade 테이블의 Non-Equi Join 예제
SELECT e.ename,e.sal,s.grade
  FROM emp e, salgrade s
  WHERE e.sal 
BETWEEN s.losal 
    AND s.hisal;

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
...

Self Join

• - Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다르다.
• - 같은 테이블에 대해 두 개의 alias를 사용하여 FROM절에 두 개의 테이블을 사용하는 것 처럼 조인한다.

-- 사원의 매니저명을 조회하는 Self Join 예제
SELECT e.ename, a.ename "Manager"
  FROM emp e, emp a
 WHERE e.empno = a.mgr;

ENAME     Manager
------- ----------
FORD      SMITH
BLAKE     ALLEN
BLAKE     WARD
KING      JONES
...

Cartesian Product

• - 검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블들의 모든 데이터가 반환 되는 현상
• - Cartesian Product는 조인 조건을 정의하지 않은 경우 발생한다.
• - 테이블의 개수가 N이라면 Cartesian Product를 피하기 위해서는 적어도 N-1개의 등가 조건을 SELECT 문안에 포함시켜야 하며 각 테이블의 컬럼이 적어도 한번은 조건절에 참조되도록 해야 한다.
• - CROSS JOIN을 이용하면 Cartesian Product 값을 얻을 수 있다.

-- CROSS JOIN절을 이용하여 Cartesian Product 값을 얻는 예제
SELECT  e.empno, e.ename, d.dname
  FROM  dept d CROSS JOIN emp e;

56 개의 행이 선택되었습니다.