20201029_24 순위, 누적, 집계, 조인

#1

SELECT EMPno, ename, SAL,
RANK() OVER(ORDER BY sal DESC) 급여순위
FROM EMP;

높은 순서대로 등급 부여

 

 

 

 

DESC없애주면 기본값인 ASC니까 낮은순서대로

 

 

 

 

 

 

• 지정하는 값의 순위를 조회하고 싶다면, RANK(순위를 알고자하는 값) 사용

SELECT RANK(50) WITHIN GROUP(ORDER BY sal DESC) AS 순위
FROM EMP;

 

 

 

 

 

 

 

SELECT ename, deptno, SAL,
    RANK() OVER (ORDER BY SAL DESC) 순위1,
    DENSE_RANK() OVER (ORDER BY SAL DESC) 순위2,
    DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) 순위3 
FROM emp
ORDER BY  순위1;

 

 

 

 

 

• SELECT하고 WHERE로 걸러줌

SELECT * FROM (
    SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) 순위 FROM emp
    )WHERE 순위 <= 5;

 

 

 

 

 

• 순위가 10인사람 찾고싶을때

SELECT * FROM (
    SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) 순위 FROM emp)
WHERE 순위 = 10;

 

 

 

 

 

• 누적분산

SELECT ename, sal,
    ROUND(CUME_DIST() OVER (ORDER BY sal), 1) 누적분산
FROM emp;

 

 

 

 

 

• NTILE

SELECT ename, sal,
    NTILE(4) OVER(ORDER BY sal DESC) 등급
FROM emp;

sal을 4개로 분류

 

 

 

 

SELECT ename, sal,
    NTILE(5) OVER(ORDER BY sal DESC) 등급
FROM emp;

sal을 5개로 분류

 

 

 

 

• ROW_NUMBER

SELECT empno, ename, sal, hiredate,
    ROW_NUMBER() OVER (ORDER BY sal DESC, hiredate ASC) 순번
FROM emp;

 

 

 

• 집계함수
  집계함수는 여러행 또는 테이블 전체 행으로부터 그룹별로 집계하여 결과를 반환한다.

SELECT deptno
, SUM(sal) s_sal
FROM emp
GROUP BY deptno

10번부서가 제일 많이 받는걸 알 수있음

 

 

 

 

SELECT EMP_ID, SALARY,
SUM(SALARY) OVER (PARTITION BY DEPT_ID
ORDER BY EMP_ID
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) "win1",
-- rows : 부분그룹인 윈도우의 크기를 물리적인 단위로 행집합을 지정
-- unbounded preceding : 윈도우의 첫행
-- unbounded following : 윈도우의 마지막행
SUM(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY EMP_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW) "win2",
-- 윈도우의 시작행에서 현재 위치(current row) 까지의 합계를 구해서 win2에
SUM(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY EMP_ID
ROWS BETWEEN CURRENT ROW AND
UNBOUNDED FOLLOWING) "win3"
-- 현재 위치에서 윈도우의 마지막행까지의 합계를 구해서 win3에
FROM EMPLOYEE
WHERE DEPT_ID = '60';

 

 

 

 

 

 

• RATIO_TO_REPORT
  -- 해당 구간에서 차지하는 비율을 리턴하는 함수
  -- 직원들의 총급여를 3000원 증가 시킬 때, 기존 월급비율을 적용해서
  -- 각 직원이 받게될 급여의 증가액은?

SELECT ename, SAL,
    LPAD(TRUNC(RATIO_TO_REPORT(sal) OVER() * 100, 0), 5) || ' %' 비율,
    TO_CHAR(TRUNC(RATIO_TO_REPORT(sal) OVER() * 3000, 0), 'L00,999,999')"추가로 받게될 급여"
FROM emp;

 

 

 

• LEAD(다음행값조회)

SELECT ename, deptno, sal,
    LEAD(sal, 1, 0) OVER (ORDER BY sal DESC) 다음값
FROM emp;

 

 

 

 

 

 

• FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
  10번부서에는 SMITH가 제일 많이받음

SELECT DEPTNO, ENAME, SAL
, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING) as DEPT_RICH
FROM EMP

 

 

 

 

 

 

---

 

SELECT * 
FROM emp e
JOIN dept d
ON e.deptno = d.deptno;

SELECT e.* , d.dname, d.loc
FROM emp e
JOIN dept d
ON e.deptno = d.deptno;

 

 

 

 

 

 

 

 

• 비등가조인 : 같은값아니라도 범위지정해서 조인할 수 있음

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal 
    BETWEEN s.losal AND s.hisal;

예를 들어 SMITH를 보면 sal이 1200

 

salgrade테이블을 보면 1200은 1등급에 속하니까 GRADE는 1로 나옴

 

 

 

 

안시기준

SELECT e.ename, e.sal, s.grade
FROM emp e 
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

#2

 

 

 

 

• 자가조인 
  하나의 테이블에서 자신의 테이블과 Equi Join이 발생하는 것이다

SELECT e.ename,e.empno, m.mgr
FROM emp e, emp m;

 

 

 

 

SELECT e.ename, m.ename "Manager"
FROM emp e, emp m
WHERE e.empno = m.mgr;

 

 

안시기준

SELECT e.ename, m.ename "Manager“
FROM emp e JOIN emp m
ON e.empno = m.mgr;

 

 

 

 

 

 

 

 

• 이너조인
  emp 테이블과 dept 테이블을 조인하여 직원들의 부서명을 조회하는 예제

SELECT *
FROM emp e 
INNER JOIN dept d
ON e.deptno = d.deptno;

 

 

 

 

정답

SELECT e.ename, d.dname
FROM emp e 
INNER JOIN dept d
ON e.deptno = d.deptno;

 

 

 

 

 

 

• OUTER JOIN

 

 

 

RIGHT OUTER JOIN

SELECT e.ename, e.deptno, d.dname
FROM emp e 
	RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno
ORDER BY e.deptno;

조건만족하지않아도 출력해줌

근데 보통 잘 안쓰임

 

 

 

 

 

LEFT OUTER JOIN

SELECT employee.ename employee, manager.ename manager
FROM emp employee LEFT OUTER JOIN emp manager
ON employee.mgr = manager.empno;

 

 

 

 

 

 

 

 

INNER JOIN (INNER는 생략가능): 정확히 조건일치하는 부분만 출력

SELECT *
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

 

 

 

• USING 직관적임

SELECT *
FROM emp e 
INNER JOIN dept d
USING (deptno);

부서번호로 조인거는것을 알수있음

 

이 때, USING키워드에 제공되는 컬럼명은 테이블별칭이 없는 형태여야 합니다.
- USING (deptno) (O)
- USING (e.deptno) (X)

 

 

 

 

 

 

 

• NATURAL JOIN : 컬럼기능안써도 같은 걸 기준으로 출력
  직관적이지않아서 선호x

SELECT empno, name
FROM emp NATURAL JOIN test_no;

 

 

• 서브쿼리

    SELECT deptno FROM dept WHERE dname = 'ACCOUNTING'

 

 

 

SELECT * 
FROM emp
WHERE deptno = (
    SELECT deptno FROM dept WHERE dname = 'ACCOUNTING'
);

쿼리안에 쿼리넣어줌

 

 

 

 

 

• 평균연봉 뛰어넘는사람출력하고싶을때

SELECT * FROM emp WHERE sal >= (SELECT avg(sal) FROM emp);

안에있는서브쿼리읽고 where조건절로 출력

평균연봉출력- where조건절

 

 

 

 

• 뉴욕과 달러스에 사는 직원들 출력

SELECT *
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc IN ('NEW YORK', 'DALLAS'));

 

 

 

 

• 부서별 급여를 제일 많이 받는 사원 조회 

--1: 그룹별로 가장 높은 연봉을 출력
SELECT max(sal)FROM emp GROUP BY deptno;

--2.emp, dept 조인( 등가조인)
--3.where 조건절에 IN(2850, 3000, 10000)
SELECT e.ename, d.dname, e.sal, d.deptno
FROM emp e
JOIN dept d
ON e.deptno = d.deptno
WHERE sal IN (SELECT max(sal) FROM emp GROUP BY deptno);

 

 

 

 

 

 

 

 

 

 

• ANY
  메인 쿼리의 비교 조건이 서브 쿼리의 여러 검색 결과 중 하나 이상 만족되면 반환한다.

SELECT empno, ename, job sal
FROm emp
WHERE sal > ANY (SELECT sal FROm emp WHERE JOB = 'SALESMAN');

 

 

 

• 서브쿼리의 데이터가 존재하는 지 여부를 먼저 따져 존재하는 값들만 결과로 출력한다.

SELECT empno, ename, sal
FROM emp e
WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr);

 

관리자로 등록되어 있는 사원들을 조회해 보자.

 

 

 

• 스칼라

SELECT EMPNO, ENAME,
    CASE 
        WHEN DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'NEW YORK')THEN 'hq'
ELSE '분점'
END AS 소속
FROM EMP E
ORDER BY 소속 DESC

 

 

 

 

• 상관관계 : 조인으로도 할 수 있음 

SELECT empno, ename, deptno, hiredate
FROM emp e
ORDER BY(SELECT dname FROM dept WHERE deptno = e.deptno) DESC;

SELECT * FROM emp e 
JOIN dept d ON e.deptno = d.deptno
ORDER BY d.dname DESC;

결과값은 같다

 

 

 

 

 

 

• UNION

SELECT empno, ename, JOB FROM emp WHERE sal < 1000
UNION 
SELECT empno, ename, JOB FROM emp WHERE sal < 2000
ORDER BY empno;

 

 

 

 

SELECT empno, ename, sal FROM emp
MINUS
SELECT empno, ename, sal FROm emp WHERE sal > 2000;

SELECT empno, ename, sal FROM emp WHERE sal <=2000;

위아래 같은 결과값이 나옴