[ORACLE] roll up / cube / grouping / join

 

 

 

 

 

※ select SQL문 실행순서

1. select 컬럼
2. from 테이블
3. where 조건
4. group by 그룹핑할 컬럼
5. having 조건
6. order by 컬럼

​▶ 일반적으로 쿼리 실행 순서는 다음과 같습니다.

1. FROM 절에서 테이블을 선택합니다.
2. WHERE 절에서 조건을 필터링합니다.
3. GROUP BY 절에서 그룹을 만듭니다.
4. HAVING 절에서 그룹을 필터링합니다.
5. SELECT 절에서 필요한 열을 선택합니다.
6. ORDER BY 절에서 결과를 정렬합니다.

위 쿼리의 경우 FROM 절에서 두 개의 테이블을 조인하고, WHERE 절에서 조건을 필터링하며, GROUP BY 절에서 그룹을 만들고, SELECT 절에서 열을 선택합니다. 따라서 일반적인 실행 순서에 따르면 위 쿼리는 FROM, JOIN, WHERE, GROUP BY, SELECT 절의 순서로 실행될 것입니다.

---

 

▶ group by 

 

▷ roll up 

  - 주어진 데이터들의 총계를 구해준다

  - GROUP BY 절에 의해서 그룹지어진 집합 결과에 대한 총계반환.
     ex ) 학과별(group by) 연봉(컬럼)의 합계(sum 그룹함수)의 총계 (roll up) 

  - roll up (컬럼) :  컬럼의 개수+1 개의 소계가 만들어짐

select deptno 학과, round(avg(nvl(pay, 0)),1) 평균급여 
from professor
group by rollup(deptno)  
order by deptno;

   == > 학과별평균급여 + 학과별평균급여의 총계

 

 

 

▷ cube 

  - 주어진 데이터의 총계와 그의 총계를 구한다 / 총계 , 총총계

  - 학과별, 직급별(group by)의 전체평균(avg(nvl()))과 그의 총계

  - cube(컬럼) : 2의 컬럼제곱 개의 소계가 만들어짐 (모든 경우의 수)

      - ex) cube(a,b,c) => 2의 3제곱 개=>8개

select deptno, position, round(avg(nvl(pay,0)))
from professor
group by cube(deptno, position)
order by deptno, position;

 

== > 각 부서, 직급별 평균연봉 + 부서별 평균연봉의 평균 + 직급별 평균연봉의 평균 + 총총 평균연봉

 

 

▶ grouping

  - rollup, cube 함수와 같이 사용됨

  - 소계에 대한 요약 정보를 줄 때 사용

  - 그룹핑 작업에 사용되었으면 0, 사용되지 않았으면 1을 리턴함

select deptno, round(avg(nvl(pay, 0))) 평균급여, grouping(deptno)
from professor
group by rollup(deptno)      // deptno 에 rollup 을 썼으니 deptno 별 평균의 합 ! 이 나오겠쥬
order by deptno;

== > null 로 출력되는 deptno 별 평균의 합 ! grouping(deptno) 값은 1 인것이다

 

 

  ▷ group by 한 컬럼이 2개인 경우

select decode(grouping(deptno),1,
    decode(grouping(position),1,'[전체]','[직급별 소계]'), deptno) "학과", 
decode(grouping(position),1, decode(grouping(deptno),1,'[소계]','[학과별 소계]'),position) 
    "직급", 
avg(nvl(pay,0)), grouping(deptno),grouping(position)
from professor
group by cube(deptno,position)
order by deptno, position;

위에서부터 코드해석 :

 

deptno grouping 값이 1이고                                                                         1이 아니면 deptno값

                                      position grouping 값이 1이면 [전체] 아니면 [직급별소계]

으로 반환하고 별칭은 학과라고 하여라,

 

position grouping 값이 1이고                                                                        1이 아니면 position값

                                      deptno grouping 값이 1이면 [소계] 아니면 [학과별소계]

 

으로 반환하고 별칭은 직급이라고 하여라

 

<아래는 그냥 위에 반환하는데 필요한 재료임>

 

professor 테이블에서 pay 평균, deptno grouping 값, position grouping 값을 출력해라

deptno, position 별로 구할수 있는 소계를 다 구해라

deptno, position 내림차순 정렬해라

 

 

 

▶ grouping sets 

  - 원하는 집계만 수행할 수 있다

  - 그룹핑 조건이 여러 개일 경우 유용하게 사용

      - ex ) 학년별로 학생들의 인원수 합계와

              학과별로 학생들의 인원수 합계를 구해라    => 그룹핑조건이 2개 ! 

select grade, deptno1, count(*) 인원수
from student
group by grouping sets((grade),(deptno1))
order by grade, deptno1;

  = > 머 이렇게 쓴다는데 솔직히 잘 모르겠음

 

---

 

※ join 

  - 각각의 테이블에 분리되어 있는 연관성 있는 데이터들을 연결하거나 조합하는 일련의 작업들

  - 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서
      가상의 테이블간 공통된 열을 기준으로 검색

 

 

▶ 조인의 종류

    1) 내부조인(inner join)
       - 양쪽 테이블에 모두 데이터가 존재해야 결과가 나옴
    2) 외부조인(outer join)    
    3) self 조인
    4) cross join(카디션 곱)

 

오라클용 조인 - where
표준 ANSI 조인  - join , on

  = > 오라클용 조인도 배웠지만

        앞으로 사용하게 될 것은 ansi join 이기때문에 이것만 적도록하겠다.

 

---

1) 내부조인(inner join)

  - 조인하려는 두 테이블의 교집합 컬럼을 이어주어 연결(=조인) 한다

 

 

 ▷ ineer join 기본형식

SELECT *           // 조회할 컬럼을 선택한다 (모든 컬럼 조회)
FROM TABLE1  T1                             
INNER JOIN TABLE2  T2    // INNER 는 생략가능
ON (T1.KEY = T2.KEY)     // 두 테이블을 연결할 조건을 명시한다

 

 ▷ 3개 테이블 inner join

    - (A - B) , (B - C) 이렇게 각각 교집합을 찾아서 이어주면 됨

    - ex ) student, department, professor 을 join 하여 

            학생이름, 학과 이름, 지도교수 이름을 출력

select s.studno, s.name, s.grade, d.dname 학과명, p.name 교수명
from student s join department d
on s.deptno1=d.deptno
join professor p
on s.profno=p.profno;

 

 ▷ 검색조건있는 경우 inner join

   - and 으로 검색조건 달아주면 됨 (where 나 having 처럼)

   - ex ) 1전공(depton1)이 101번인 학생들의 학생이름과 지도교수 이름을 출력하시오.

select s.*, p.name 교수명
from student s join professor p
on s.profno=p.profno --조인 조건
and s.deptno1=101;

 

▷ group by 로 묶는 경우 inner join

  - ex ) 부서이름별 평균급여를 구하고

           그 중 부서이름이 '영업' 으로 시작하는 부서의 평균만 조회해라

select d.dname 부서명, avg(nvl(pay,0)) 평균급여
from emp2 e join dept2 d
on e.deptno=d.dcode
group by d.dname
having d.dname like '영업%';

 

---

 

2) 외부조인(outer join)    

  - 내부조인 : 두 테이블의 교집합을 이어주어 연결하는 것이기때문에

                  반드시 양쪽에 데이터가 모두 존재하는 것들만 조회된다

                  한쪽에라도 데이터가 없으면 제외됨.

  - 데이터가 누락될 수 있는 내부조인을 보완할 수 있는 외부조인 ! 

  - 한쪽에 데이터가 없더라도 출력해줌.

 

 

 ▷ outer join 기본형식

   - 데이터가 무조건 있는 쪽을 향해 left || right 를 써주면된다 ! 

      - 왼쪽 조인은 왼쪽 테이블의 모든 레코드와 오른쪽 테이블에서 일치하는 레코드를 조인합니다. 만약 오른쪽 테이블에 일치하는 레코드가 없으면 NULL 값으로 채워집니다. 이 때, 왼쪽 테이블이 기준이 되며, 왼쪽 테이블의 모든 레코드는 결과에 포함됩니다.

SELECT *         //  조회할 컬럼을 선택한다 (모든 컬럼 조회)
FROM TABLE1  T1                             
  	LEFT JOIN TABLE2 T2               
ON (T1.KEY = T2.KEY)        // 두 테이블을 연결할 조건을 명시한다

 - ex ) student 테이블과 professor 테이블을 조인하여 학생이름과 지도교수 이름을 출력하시오.
         단, 지도학생이 결정되지 않은 교수의 명단과 지도교수가 결정 안 된 학생 명단을 한꺼번에 출력하시오.

         학생정보 출력, 학과명, 지도교수명도 출력, 학생 데이터는 전부 출력

select s.*, d.dname, p.name
from student s left join department d
on s.deptno1=d.deptno
left join professor p
on s.profno=p.profno;

 - ex ) employees, departments, locations 테이블에서 city별, department_name별, job_id별로 그룹화하여

         city, department_name, job_id, 인원수, salary합계 구하기
         사원전체 출력(사원-부서간), 부서전체 출력(부서-지역간)

select l.city, d.department_name, e.job_id, count(*) 인원수, sum(e.salary) 급여합계
from employees e left join departments d
on e.department_id = d.department_id
left join  locations l
on d.location_id=l.location_id
group by l.city, d.department_name, e.job_id
order by l.city, d.department_name, e.job_id;

 

---

 

3) self join

  - 원하는 데이터가 하나의 테이블에 다 들어있을 경우 하나의 테이블을 메모리상에서 별명을 두 개로 사용해서

    가상으로 2개의 테이블로 만든 후 조인 작업을 수행

  - ex ) 부서테이블에서 상위부서코드(pdept)에 해당하는 상위부서명 출력

select a.*, b.dname 상위부서명
from dept2 a left join dept2 b
on a.pdept=b.dcode
order by a.dcode;

 

4) cross join(카티션 곱)

  - 조인 조건이 없는 경우
    두 테이블의 데이터를 곱한 개수만큼의 데이터가 출력됨

select e.*, d.*
from emp e cross join dept d
order by empno, d.deptno;

emp 데이터 : 14개, dept 데이터 : 4개 => 총 56개 데이터 출력됨