LuceteMango:)

GROUP BY절

• - GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
• - 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
• - 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
• - 아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
• 
  

-- 부서별 사원수 조회
SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
  FROM emp
 GROUP BY deptno
 ORDER BY COUNT(*) DESC;


YEAR     부서번호     사원수
------ ---------- ----------
2005년         30          6
2005년         20          5
2005년         10          3

아래 예제는 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회하는 예제이다.

 
SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균", 
       ROUND(SUM(sal)) "급여합계"
  FROM emp
 GROUP BY deptno;


  DEPTNO   COUNT(*)    급여평균    급여합계
-------- ---------- ---------- ----------
      30          6       1567       9400
      20          5       2175      10875
      10          3       2917       8750

아래 예제는 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회하는 예제이다.

 
SELECT job, COUNT(empno) "인원수", AVG(sal) "평균급여액",
       MAX(sal) "최고급여액", MIN(sal) "최저급여액", 
       SUM(sal) "급여합계"
  FROM emp
 GROUP BY job;


JOB           인원수   평균급여액   최고급여액   최저급여액    급여합계
----------- -------- ---------- ---------- ---------- ----------
CLERK              4     1037.5       1300        800       4150
SALESMAN           4       1400       1600       1250       5600
PRESIDENT          1       5000       5000       5000       5000
MANAGER            3 2758.33333       2975       2450       8275
ANALYST            2       3000       3000       3000       6000 

• - GROUP BY 절은 집계 함수 없이도 사용 될 수 있다.(DISTINCT와 용도가 비슷해 짐)
• - 아래 예제를 보면 GROUP BY는 말 그대로 그룹을 나누는 역할을 한다.

-- GROUP BY를 이용한 부서번호 조회 예
SELECT deptno
  FROM emp
 GROUP BY deptno;


DEPTNO
------
    30
    20
    10

DISTINCT와 GROUP BY절

• - DISTINCT와 GROUP BY 개념에 대해서 좀 더 이해를 해보자.
• - DISTINCT는 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회하는 경우 사용한다.
• - GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용한다.
• - 하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며, 일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
• - 두 기능 모두 Oracle9i까지는 sort를 이용하여 데이터를 만들었지만, Oracle10g 부터는 모두 Hash를 이용하여 처리한다.
• - 그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 좋을지 고민되는 경우들이 가끔 있다.

아래의 예제는 동일한 결과를 반환한다.

-- DISTINCT를 사용한 중복 데이터 제거
SELECT DISTINCT deptno FROM emp;


-- GROUP BY를 사용한 중복 데이터 제거
SELECT deptno FROM emp GROUP BY deptno;


DEPTNO
------
    30
    20
    10

하지만 곰곰히 생각해 보면 GROUP BY와 DISTINCT는 각자 고유의 기능이 있다

집계함수를 사용하여 특정 그룹으로 구분 할 때는GROUP BY 절을 사용하며, 특정 그룹 구분없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용 하도록 하자

-- 아래와 같은 기능은 DISTINCT를 사용하는 것이 훨씬 효율적이다.
SELECT COUNT(DISTINCT d.deptno) "중복제거 수", 
       COUNT(d.deptno) "전체 수"
  FROM emp e, dept d
 WHERE e.deptno = d.deptno;


-- 집계 함수가 필요한 경우는 GROUP BY를 사용해야 한다.
SELECT deptno, MIN(sal)
  FROM emp 
 GROUP BY deptno;

HAVING 절

• - WHERE 절에서는 집계함수를 사용 할 수 없다.
• - HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
• - HAVING절은 GROUP BY절과 함께 사용이 된다.

아래 예제는 사원수가 다섯 명이 넘는 부서와 사원수를 조회하는 예제이다.

SELECT b.dname, COUNT(a.empno) "사원수" 
  FROM emp a, dept b
 WHERE a.deptno = b.deptno
 GROUP BY dname
HAVING COUNT(a.empno) > 5;


DNAME          사원수
------------ -------
SALES              6

아래 예제는 전체 월급이 5000을 초과하는 JOB에 대해서 JOB과 월급여 합계를 조회하는 예이다. 단 판매원(SALES)은 제외하고 월 급여 합계로 내림차순 정렬하였다.

SELECT job, SUM(sal) "급여합계"
  FROM emp  
 WHERE job != 'SALES'      -- 판매원은 제외
 GROUP BY job              -- 업무별로 Group By 
HAVING SUM(sal) > 5000     -- 전체 월급이 5000을 초과하는
 ORDER BY SUM(sal) DESC;   -- 월급여 합계로 내림차순 정렬


JOB                  급여합계
------------------ ----------
MANAGER                  8275
ANALYST                  6000
SALESMAN                 5600

GROUPING SETS

• - GROUPING SETS 함수는 GROUP BY의 확장된 형태로 하나의 GROUP BY절에 여러개의 그룹 조건을 기술할 수 있다.
• - GROUPING SETS 함수의 결과는 각 그룹 조건에 대해 별도로 GROUP BY한 결과를 UNION ALL한 결과와 동일하다.
• - GROUPING SETS 함수를 사용하면 UNION ALL등을 사용하여 복잡하게 SQL문장을 작성했던 것을 간단하게 한 문장으로 해결 할 수 있다.

아래는 GROUPING SETS함수와 GROUP BY, UNION ALL을 이용하여 동일한 결과를 출력하는 예제이다.

--  GROUPING SETS 예제
SELECT deptno, job, SUM(sal)
 FROM emp
 GROUP BY GROUPING SETS(deptno, job);


-- GROUP BY와 UNION ALL을 이용한 예제
SELECT NULL deptno,  job, SUM(sal)
  FROM emp
 GROUP BY job
 UNION ALL
SELECT deptno, NULL job, SUM(sal)
  FROM emp
 GROUP BY deptno;


-- 조회결과
   DEPTNO JOB                  SUM(SAL)
--------- ------------------ ----------
          CLERK                    4150
          SALESMAN                 5600
          PRESIDENT                5000
          MANAGER                  8275
          ANALYST                  6000
       30                          9400
       20                         10875
       10                          8750

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

집계함수(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 개의 행이 선택되었습니다.

-주키 테이블 생성시 선언-
create table 테이블명
(
empno varchar2(4),
jumin varchar2(10),
constraint pk_테이블명 primary key(empno)
);

-테이블생성후 주키선언-
alter table 테이블명
add constraint pk_테이블명 primary key(empno);

-외래키 테이블생성시 선언-
create table 테이블명
(
empno varchar2(4),
jumin varchar2(10),
constraint fk_테이블명 foreign key(empno)
reference 참조테이블명(empno)
[on delete cascade] //부모를삭제하면 자식까지 삭제
);

-테이블생성후 외래키선언-
alter table 테이블명
add constraint fk_테이블명 foreign key(empno)
reference 참조테이블명(empno);

- CASE : 기능적 측변에서 DECODE와 흡사하지만 좀 더 확장된 기능을 제공

• - CASE 함수는 DECODE함수가 제공하지 못하는 비교연산의 단점을 해결할 수 있는 함수이다.
• - DECODE함수에서 비교연산을 수행하기 위해서는 GREATEST, LEAST등의 함수를 사용해야 하지만, 
•   CASE함수에서는 조건 연산자를 모두 사용 할 수 있다.
• - CASE함수는 IF.. THEN .. ELSE 구문과 비슷 하다. WHEN절 다음에 여러 조건이 올 수 있다.

- 사용법

--위의  DECODE예제를 CASE함수로 변환한 예이다. 
SELECT deptno, 
       CASE deptno
         WHEN 10 THEN 'ACCOUNTING'
         WHEN 20 THEN 'RESEARCH'
         WHEN 30 THEN 'SALES'
         ELSE 'OPERATIONS'
       END as "Dept Name"
  FROM dept;

DEPTNO Dept Name
------- ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS 

--급여별로 인상율을 다르게 계산하였다. 
SELECT ename ,
       CASE
          WHEN sal < 1000  THEN sal+(sal*0.8)
          WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)
          WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)
          ELSE sal+(sal*0.1)
       END sal
  FROM emp; 

- DECODE문 : 자바문법으로 따지면 IF ~ ELSE IF ~ ELSE 와 같은 구조

- DECODE 함수는 조건에 따라 데이터를 다른 값이나 컬럼값으로 추출 할 수 있다.

- DECODE(VALUE, IF1, THEN1, IF2, THEN2...) 형태로 사용 할 수 있다.

- VALUE 값이 IF1일 경우에 THEN1 값을 반환하고, VALUE 값이 IF2일 경우에는 THEN2 값을 반환한다.

- DECODE 함수 안에 DECODE함수를 중첩으로 사용 할 수 있다.

- 구문

DECODE({column | expression}, search1, result1 [,search2,result2] ...

[,default] )

: column 값이 search1 이면, result1 출력, 아니면 default 출력

- 예제

아래는 DECODE 함수의 일반적인 예제이다.

-- 부서번호가 10이면 ACCOUNTING, 20이면 RESEARCH, 30이면 SALES
-- 나머지는 OPERATIONS를 출력하는 예제
SELECT deptno, DECODE(deptno, 10 , 'ACCOUNTING' ,
                              20 , 'RESEARCH' ,
                              30 , 'SALES', 'OPERATIONS') name
  FROM dept;

DEPTNO NAME
------ ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS

아래는 DECODE 함수에서 집계 함수를 사용한 예제이다

-- 10부서는 급여합계를, 20부서는 최대값을, 30부서는 최소값을 출력하는 예제
SELECT deptno, DECODE(deptno, 10 , SUM(sal),
                              20 , MAX(sal),
                              30 , MIN(sal)) sal
  FROM emp
 GROUP BY deptno; 

DEPTNO        SAL
--------- --------
       30      950
       20     3000
       10     8750 

DECODE함수는 집계함수와 함께 통계 데이터를 추출할 때 많이 사용한다. 아래는 부서별로 급여합계를 조회하는 예이다

-- 부서별로 급여 합계를 출력한다. 
SELECT deptno, NVL(SUM(DECODE(deptno, 10, sal)),0) deptno10, 
               NVL(SUM(DECODE(deptno, 20, sal)),0) deptno20,
               NVL(SUM(DECODE(deptno, 30, sal)),0) deptno30,
               NVL(SUM(DECODE(deptno, 40, sal)),0) deptno40
  FROM emp
 GROUP BY deptno; 

DEPTNO   DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
------- --------- --------- ---------- ----------
     30         0         0       9400          0
     20         0     10875          0          0
     10      8750         0          0          0 

아래 부서별 급여합계 예를 보면 일반적인 집계함수를 사용할 때는 급여 합계가 행으로 조회가 되지만, DECODE와 MAX함수를 사용하면 열로 값을 표시할 수 있다.

-- 부서별로 급여 합계를 행으로 출력한다. 
SELECT d.deptno, NVL(SUM(e.sal),0) sal
  FROM emp e, dept d
 WHERE e.deptno(+) = d.deptno
 GROUP BY d.deptno; 

DEPTNO        SAL
-------- ----------
      10       8750
      20      10875
      30       9400
      40          0



-- 부서별로 급여 합계를  열로 출력한다. 
SELECT MAX(NVL(SUM(DECODE(deptno, 10, sal)),0)) deptno10, 
       MAX(NVL(SUM(DECODE(deptno, 20, sal)),0)) deptno20,
       MAX(NVL(SUM(DECODE(deptno, 30, sal)),0)) deptno30,
       MAX(NVL(SUM(DECODE(deptno, 40, sal)),0)) deptno40
  FROM emp
 GROUP BY deptno; 

DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
--------- ---------- ---------- ----------
    8750      10875       9400          0