2022_11_10 SQL 정리

04_그룹함수

AVG() --평균
SUM() --합계
MIN() --최솟값
MAX() --최댓값
COUNT() --행의수

COUNT(*) --NULL을 포함한 행의 갯수
COUNT(컬럼)  --NULL값을 제외한 행의 갯수

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

--------------------------------------GROUP BY
GROUP BY 절은 where절 다음 order절 사이에 쓰임
그룹별로 그룹함수를 사용하고싶을때 사용함
ex ) 특정 집단별로 (avg/sum/min/max/count)를 사용하고싶을떄
GROUP BY 는 2개 이상도 가능함
Ex) GROUP BY department_id, job_id;
--부서별 급여 평균
SELECT department_id, TRUNC(AVG(salary))
FROM employees
GROUP BY department_id;

-- group절에 묶이지 않은 컬럼은 SELECT절에 사용 불가( 데이터베이스 별로 다름)
--SELECT department_id, job_id, TRUNC(AVG(salary))
--FROM employees
--GROUP BY department_id;

--2개이상의 그룹
SELECT department_id, job_id FROM employees GROUP BY department_id, job_id ORDER BY department_id DESC;

SELECT department_id, 
        job_id, 
        sum(salary), 
        count(*),              --그룹별 수
        count(*) over()        --전체 행 수
FROM employees GROUP BY department_id, job_id ORDER BY department_id desc;

SELECT department_id, 
        job_id, 
        count(*) OVER()        --전체 행 수
FROM employees ;
        
--WHERE절에 그룹함수 사용 불가
--SELECT department_id
--FROM employees
--WHERE sum(salary) >=5000    
--GROUP BY department_id;

--------------------------------------------Having 
HAVING은 GROUP BY 절의 조건
WHERE에는 열선태에 대한 조건이 들어감
HAVING엔 GROUPING에 대한 조건이 들어가야함
--HAVING(그룹에 대한 조건)

SELECT department_id, sum(salary)
FROM employees
GROUP by department_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;

select* from employees;

--직무 SA가 포함된 데이터의 그룹별 사원수, 그룹별 급여합
SELECT job_id, count(*) AS 사원수, sum(salary) AS 급여합
FROM employees
where job_id LIKE '%SA%'
GROUP BY job_id
ORDER BY job_id;


---------------------------------------GROUP BY 앞에 붙음
--ROLLUP 
GROUP BY 절에 의해서 그룹지어진 결과의 합계를 보여줌
--CUBE
GROUP BY 절에 의해서 그룹지어진 결과의 합계를 보여줌
--ROLLUP       --주 그룹의 토탈
--그룹핑 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;

--CUBE         --주, 서브그룹의 토탈( 구할 수 있는 모든 합)
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
JOIN  - 관계형 데이터베이스에서 제일 중요한 부분
JOIN은 두가지 종류가 있음
-ANSI 조인 - 모든 데이터베이스에서 공용으로 사용함
-오라클 조인
ANSI JOIN -> 이너와 아우터의 차이를 알아야함
 join 앞에 종률설정이 없으면 기본값임 inner로 설정됨
  
  -> Inner JOIN   -  NULL은 제외하고 합침
    -> Outter JOIN  
-LEFT JOIN        - 왼쪽테이블이 기준으로 잡혀서 오른쪽 테이블에 대한 값이 없다면 NULL값으로 출력됨
-RIGHT JOIN       - 오른쪽이 기준이기 때문에 왼쪽 테이블 중 오른쪽에 없는 데이터는 NULL값으로 출력됨
-FULL OUTER JOIN  - 양쪽의 모든 값을 가져오는데 반대쪽 테이블에 연관되는 값이 없다면 NULL로 출력함
  -> CLOSS jOIN   - 첫번째 테이블의 모든 행 마다 오른쪽 테이블이 붙음
  
  

--INNER JOIN  NULL값을 제외한 후 합쳐짐
SELECT * FROM info INNER JOIN auth ON info.auth_id = auth.auth_id;

--LEFT OUTER JOIN 왼쪽 기준으로 합쳐지고 오른쪽테이블과 연관된 값이 없다면 NULL을 리턴함
SELECT * FROM info LEFT OUTER JOIN auth ON info.auth_id = auth.auth_id;

--RIGHT OUTER JOIN 오른쪽 기준으로 합쳐지고 왼쪽테이블과 연관된 값이 없다면 NULL을 리턴함
SELECT * FROM info RIGHT OUTER JOIN auth ON info.auth_id = auth.auth_id;

--FULL OUTER JOIN 전체 데이터가 합쳐지는데 반대 테이블에 연관된 값이 없다면 null을 리턴함
SELECT * FROM info FULL OUTER JOIN auth ON info.auth_id = auth.auth_id;

--CROSS JOIN 왼쪽 테이블의 모든 행마다 오른쪽 테이블이 붙음
SELECT * FROM info CROSS JOIN auth;