22-11-10 그룹함수, 조인(join)

# 아래 파일 통해서 조인이 어떻게 되는지 구조를 확인해보기

DB조인(실습파일) (1).xlsx

0.01MB

<그룹 함수>

-여러행에 대해서 하나의 값을 뽑아준다.

#AVG, SUM, MIN, MAX, COUNT 들은 다 알고있어야함.

-AVG([DISTINCT|ALL] n) Null 값을 무시한 n의 평균을 출력합니다.
-SUM([DISTINCT|ALL] n) Null 값을 무시한 n의 합계를 출력합니다.
-MIN([DISTINCT|ALL] expr) Null 값을 무시한 expr의 최솟값을 출력합니다.

-MAX([DISTINCT|ALL] expr) Null 값을 무시한 expr의 최댓값을 출력합니다.

-COUNT({*|[DISTINCT|AL L] expr})

 행의 수, expr은 Null 값을 제외하고 계산합니다.

 *를 사용하여 중복되거나 Null인 행들을 포함하여 모든 행을 계산합니다.

 숫자나 날짜에 많이 사용이됨.

--그룹 함수 AVG, MAX, MIN, SUM, COUNT
select AVG(salary), MAX(salary), MIN(salary), SUM(salary), count(*) from employees;
SELECT max(hire_Date), min(hire_date) from employees; --날짜에도 가능

#count함수 ★
 count(*)
 전체 행의 개수를 알 수 있음.
 count(expr) -> count(컬럼)
 expr에 의해 인식된 열에서 Null이 아닌 행의 수를 리턴.
 위 둘은 null값을 제외한 행의 갯수를 리턴한다.

--count(*), count(컬럼)
select count(*) from employees; --총 행 데이터의 수
select count(commission_pct), count(manager_id) from employees; --null이 아닌 행의 수

★그룹 함수는 일반 컬럼과 동시에 출력 불가★

<GROUP BY>

-GROUP BY 절은 where절 다음 order절 사이에 쓰입니다
-여러 행 데이터에서 특정 그룹별(같은 그룹별)로 묶어서 사용할 때 사용.★
 사용예시 : GROUP BY 칼럼
 주의할 점: 

--group절에 묶이지 않은 컬럼은 select절에 사용불가 (데이터베이스별로 다름)
select job_id, department_id from employees group by department_id;

--주의할 점: 그룹함수는 일반 컬럼과 동시에 출력 불가.
--SELECT employee_id, sum(salary) from employees;

-count(*) over() 
 over함수는 전체행수를 붙여서 나타내줌 ,잘못쓰면 sql이 느려지는 단점이 있음.
 count(*) over() -- 전체행 수 (그룹없이 사용가능)

select  department_id,
        job_id,
        count(*) over() --전체행 수 (그룹없이 사용가능)
from employees;

 
-where절에 그룹함수 사용불가
 select deparyment_id
 from employees
 where sum(salary) >= 5000 
 GROUP by deparyment_id 
 그룹핑 한것에 조건을 달려면 Having 절을 사용해야함.
 
<HAVING>

-그룹핑된 것들의 결과에 대한 조건

--having (그룹의 조건)
select deparyment_id, sum(salary)
from employees
group by deparyment_id
having sum(salary) > 100000;

select job_id, count(*)
from employees
group by job_id
having count(*) >= 20;

--부서 아이디가 50이상인 것들을 그룹화 시키고, 그룹 평균중 5000이상만 조회, 정렬 평균내림차순
select department_id, trunc(avg(salary))
from employees
where department_id >=50
GROUP BY department_id
having avg(salary)>=5000
order by avg(salary) desc;

--직무 SA가 포함된 데이터의 그룹별 사원수, 그룹별 급여합, 정렬해주기, 그루핑은 job_id로
select job_id, count(*) as 사원수, sum(salary) as 급여합
from employees
where job_id like 'SA%'
group by job_id
order by sum(salary) asc;

<ROLLUP, CUBE, GROUPING>

-sql 시험에 나와서 보고 가는것들.
-ROLLUP
 주 그룹에 대한 합계.
-CUBE 
 구할 수 있는 서브그룹까지의 모든 데이터의 소계, 총계까지 구해줌.
-Grouping
 그룹화 해서 나온 결과인지, 롤업 혹은 큐브를 사용해서 나온 행(결과)인지 구분해주는 함수.
 GROUPING 함수는 해당 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환
 하고, ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 된다.

--롤업 - 주그룹의 토탈
--그룹핑 1개 -> 총계출력
select department_id,
        sum(salary)
from employees
group by rollup(department_id)
order by department_id;

--그룹핑 2개 -> 총계와 주그룹의 토탈
select department_id, job_id, avg(salary), count(salary)
from employees
group by rollup(department_id, Job_id)
order by department_id, job_id;

--큐브 - 서브그룹까지의 토탈 (구할 수 있는 모든합)
select department_id, job_id, avg(salary), count(salary)
from employees
group by cube(department_id, Job_id)
order by department_id, job_id;

--GROUPING-
select  department_id, 
        DECODE (GROUPING(JOB_ID), 1, '소계', job_id),
        avg(salary), 
        count(salary),
        Grouping(Department_id),
        Grouping(JOB_ID)
from employees
group by ROLLUP(department_id, Job_id)
order by department_id, job_id;

JOIN 이란?
ANSI JOIN

-관계형 데이터베이스의 꽃, 빠짐없이 다 잘 알고있어야함.
-오라클에서만 허용되는 오라클 조인이 있음.
 모든 데이터베이스에서 사용되는 Ansi 조인이 있음.
 
#중요 조인의 종류
1. INNER JOIN - 내부 조인(EQUALS JOIN)  

--사용예시
SELECT*FROM INFO INNER JOIN AUTH ON INFO.AUTH_ID = AUTH.AUTH_ID

-붙을 수 없는건 출력이 안되는게 이너조인.

2. OUTER JOIN - 외부 조인
#LEFT (제일 중요, 가장 많이씀)

--사용예시
Select *
From INFO 
LEFT OUTER JOIN AUTH
ON INFO.AUTH_ID = AUTH.AUTH_ID;

-왼쪽에 있는 테이블을 기준으로 세워두고, 우측에 있는 테이블을 하나도 누락없이 그대로 갖다붙임.

#RIGHT

--사용예시
SELECT * 
FROM info 
RIGHT OUTER JOIN auth 
ON info.auth_id = auth.auth_id;

-letf 테이블과 반대. 오른쪽 테이블은 다 나옴. 그리고 왼쪽 테이블을 오른쪽 테이블에 갖다 붙이는 것.

#FULL OUTER JOIN
-양쪽 테이블 다 누락없이 다 나옴.

#그 밖의 조인의 종류
3. CLOSS JOIN
-정상적인 조인의 형태가 아님.
-사용예시

SELECT * FROM INFO CROSS JOIN AUTH;

4. SELF JOIN
내일 배울것
★inner join 과 outer join 을 구분해내는게 가장 중요.★