[6/16] 분석함수

08 다중테이블데이터표현.pdf

0.20MB

[분석함수]

분석함수	윈도우 함수(그룹함수)에 의해 발생된 결과값을 제공받아 처리하여 반환하는 함수
- 그룹함수를 사용할 경우, 다른 검색대상을 선언하면 에러발생 - 그룹함수와 분석함수를 같이 사용할 경우 다른검색대상 선언 가능함 - GROUP BY 구문 대신 분석함수에 PARTITION BY 기능을 제공함

--GROUP BY 사용 불가
SELECT 윈도우함수 OVER ([PARTITION BY 컬럼명]) [ORDER BY 컬럼명] 
						WINDOW,검색대상..... FROM 테이블명 [WHERE 검색조건] 

--PARTITION BY를 사용하면 그룹을 만들 수 있다.
--오류발생
SELECT EMPNO, ENAME, SAL, DEPTNO, CEIL(AVG(SAL) OVER()) "부서별 평균급여" FROM EMP GROUP BY DEPTNO;
--맞는 형식
SELECT EMPNO, ENAME, SAL, DEPTNO, CEIL(AVG(SAL) OVER(PARTITION BY DEPTNO)) "부서별 평균급여"
		 FROM EMP

--가장 최고 연봉을 받는 사원을 검색하라
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL = 5000;--유지보수의 효율성에 생김
SELECT EMPNO, ENAME, SAL, MAX(SAL) OVER() FROM EMP;

 

• 분석함수에 ORDER BY기능을 이용하면 정렬하면서 윈도우 함수의 결과를 차례로 처리하여 반환할 수 있다.
• 정렬값이 같은 경우 윈도우 함수의 동시처리 결과값이 반환된다.
• 정렬값이 동일한 경우 다른 정렬값을 제공하면 동시처리 결과값 미반환한다.

--))급여합계를 급여로 내림차순하라
--모든 사원의 급여합계가 검색된다.
SELECT EMPNO, ENAME,SAL, SUM(SAL) OVER() "급여 합계" FROM EMP ORDER BY SAL DESC;
--윈도우 함수 안에 ORDER BY 사용
SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL DESC, ENAME) "급여 합계" FROM EMP;

윈도우 함수 없이 그냥 사용

윈도우 함수 사용

• 분석함수에 WINDOWING 기능을 이용하염 정렬값이 동일한 경우에도 동시처리 결과값 반환 방지
• WINDOWSING : 순차적인 검색행을 기준으로 이전 또는 이후에 존재하는 행(범위)를 표현하는 기능

--순차적인 검색행을 기준으로 이전에 존재하는 모든 행들을 검색대상으로 본다(본인 다음은 보지 않겠다)
 SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) "순차적 급여합계" FROM EMP;

--검색사원의 급여, 검색사원 직전 사원의 급여
SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL) "급여합계" FROM EMP;

--앞쪽 하나와 뒤쪽하나만 검색대상으로 보겠다라는 의미
SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "급여합계" FROM EMP;

 

--검색사원의 급여보다 100적거나 200큰 사원의 인원수를 급여로 오름차순으로 정렬하여 검색
SELECT EMPNO, ENAME, SAL, COUNT(*) OVER(ORDER BY SAL RANGE 
                    BETWEEN 100 PRECEDING AND 200 -1 FOLLOWING) "인원수" FROM EMP;

• 스미스 기준으로 100적고 200많은 사람은 2명 이런식

 

[순위함수]

• RANK, DNSE_RANK, ROW_NUMBER 가 있다.
• 분석함수와 같이 사용해야 하는 함수

RANK	정렬값이 같은 경우 같은 순위로 검색되며 다음 순위를 건너뛰고 검색하는 함수
DNSE_RANK	정렬값이 같은 경우 같은 순위로 검색되며 다음 순위를 건너뛰지 않고 검색하는 함수
ROW_NUMBER	정렬값이 같은 경우에도 다른 순위로 검색하는 함수 -행번호를 주는것과 같은 기능을 함

RANK()함수

DENSE_RANK

ROW_NUMBER

[순서함수]

-분석함수와 같이 사용해야 한다.

FIRST_VALUE	첫번째 검색행의 컬럼값을 반환하는 함수
LAST_VALUE	마지막 검색행의 컬럼값을 반환하는 함수
LAG	검색행 이전 행의 컬럼값을 반환하는 함수 LAG(컬럼명, 이전행위치, 변경값) -이전 위치의 행의 컬럼값을 반환하되, 이전 행이 없는 경우 변경값을 반환
LEAD	검색행 이후 행의 컬럼값을 반환하는 함수 LEAD(컬럼명, 이전행위치, 변경값) -이후 위치의 행의 컬럼값을 반환하되, 이전 행이 없는 경우 기본값을 반환

 

--순서함수
--급여를 가장 많이 받는 사원을 검색
--급여를 가장 적게 받는 사원을 검색 -급여로 내림차순
SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC;
SELECT EMPNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER(ORDER BY SAL DESC) , LAST_VALUE(ENAME) OVER() FROM EMP;

SELECT EMPNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER(ORDER BY SAL DESC) , 
            LAST_VALUE(ENAME) OVER
            (ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM EMP;
            --현재행부터 마지막까지
            
SELECT EMPNO, ENAME, SAL, LAG(SAL, 1,0) OVER(ORDER BY SAL DESC) FROM EMP;

 

★★★★★[JOIN]★★★★★

• 두개이상의 테이블에서 원하는 컬럼값을 검색하기 위한 기능
• 두개이상의 테이블에서 컬럼값을 검색하기 위해서는 반드시 조인조건을 명시하여 사용
• 카타시안 프로덕트(CATASIAN PRODUCT) : 두개 이상의 테이블의 모든 행을 교차조인한 결과를 제공하는 조인
• 동등조인(EQUI JOIN) : 조인조건에서 조인 테이블의 컬럼값이 같은 행을 검색

--동등조인
SELECT EMPNO, ENAME, SAL, DNAME, LOV FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

• 조인조건은 WHERE구문의 조건식으로 표현하여 사용 - 교차조인된 검색결과에서 원하는 행 검색
• EMP테이블의 부서번호와 DEPT의 부서번호가 같은 경우의 행을 검색
• 테이블에 같은 이름의 칼럼이 존재할 경우 테이블명을 이용하여 컬럼을 명확히 표현("." 을 이용)해야 함
• 조인테이블의 같은 컬럼명을 검색한 경우 첫번째를 컬럼을 제외한 컬럼명은 자동으로 변경되어 검색된다. -> 별칭을 설정하는 것을 권장. 🔽
• 테이블 별칭(TABLE AILAS) : 조인테이블에 새로운 이름을 일시적으로 부여하는 기술
• 테이블 이름을 간단하게 표현하기 위해 사용하거나 같은 테이블을 여러번 조인할 경우 사용
• 테이블 별칭을 사용한 경우 기존 테이블명을 사용하면 에러 발생

카타시안 프로덕트

컬럼명은 자동으로 변경되어 검색된다.

• 비동등 조인(NON-EQUI JOIN) : = 연산자를 제외한 조인조건을 사용하여 행검색

--비동등 조인

--조인 조건 : 사원의 급여가 최소급여부터 최대급여 범위에 포함될 경우만 검색
SELECT EMPNO, ENAME, SAL, GRADE FROM EMP, SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;

 

• 외부조인(OUTER JOIN) : 조인조건이 맞는 행만 검색하는 것이 아니라, 조인조건이 맞지 않는 행도 검색한다.
• 조인조건이 맞지 않는 행이 저장된 테이블에 (+)를 사용하여 컬럼값이 NULL로 검색되도록 설정할 수 있다

SELECT DISTINCT DEPTNO FROM EMP; -- 10,20,30만 검색
SELECT DEPTNO, DNAME, LOC FROM DEPT; --10,20,30,40이 검색

--40번 부서에 근무하는 사원이 없으므로 40번 부서에 대한 부서이름과 부서위치 미검색
SELECT EMPNO, DNAME, SAL, ENAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

--사원이 없는 부서가 존재 할경우에도 검색이 될 수 있도록(외부 조인 사용)
--하지만 무결성이 지켜지지 않음 NULL값이 존재하기 때문
SELECT EMPNO, DNAME, SAL, ENAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO (+)= DEPT.DEPTNO; 

 

• 셀프조인(SELF JOIN) : 하나의 테이블에 다른 이름(테이블 별칭) 부여하여 조인하는것

--사원들의 매니저 사번을 볼 수 있음
SELECT EMPNO, ENAME, MGR FROM EMP;
--매니저번호에 NULL이 저장된 사원도 검색하기 위해 외부조건 사용
SELECT WORKER.EMPNO,WORKER.ENAME WORKER_ENAME,WORKER.MGR,MANAGER.ENAME MANAGER_ENAME
    	FROM EMP WORKER,EMP MANAGER WHERE WORKER.MGR=MANAGER.EMPNO(+);

 

---

• 1955 조인 : 1999년에 채택된 표준 SQL에 추가된 JOIN구문
• CROSS조인 : 조인 테이블의 모든 행들을 교차조인(조인조건없이 조인)
• SELECT 검색대상...FROM 테이블명 CROSS JOIN 테이블명2;

SELECT EMPNO, ENAME, SAL, DNAME, FOC FROM EMP CROSS JOIN DEPT;

• NATURAL JOIN : 조인 테이블에 같은 이름의 컬럼이 하나만 있는 경우 컬럼값을 비교하여 조인
• 주의 : 반드시 같은 이름의 컬럼이 존재해야함
• 같은 이름의 컬럼은 테이블을 명시하지 않아도 사용가능(NATURAL JOIN에서만 가능)
• SELECT 검색대상...FROM 테이블명 NATURAL JOIN 테이블명2;

SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP NATURAL JOIN DEPT;

• 조인테이블에 같은 이름의 컬럼에 여러개가 있는 경우 USING구문을 이용하여 컬럼을 명시하여 조인 가능
• SELECT 검색대상...FROM 테이블명1 JOIN 테이블명2 USING(컬럼명)

--즉, 굳이 테이블 명을 명시해 주지 않아도 알아서 처리 해준다.
SELECT EMPNO, ENAME, SAL, DEPTNO, DNAME, LOC FROM EMP (INNER) JOIN DEPT USING (DEPTNO);

• INNER JOIN :  조인조건을 명시하여 조인조건이 맞는 경우 검색(속도가 빠르다)
• 같은 이름의 컬럼은 테이블명을 반드시 명시하여 사용해야함
• SELECT 검색대상...FROM 테이블명1 JOIN 테이블명2 ON 조인조건

SELECT EMPNO, ENAME, SAL, DEPT.DEPTNO, DNAME, LOC FROM EMP JOIN DEPT ON (EMP.DEPTNO = DEPT.DEPTNO);
----
SELECT EMPNO, ENAME, SAL, GRADE FROM EMP JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL;
-----
SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO WHERE DNAME = 'SALES';

마지막 SQL문의 결과

• 외부조인(OUTER JOIN) : 조인조건을 명시하여 조인조건이 맞을 경우 뿐만아니라, 맞지 않을 경우에도 검색되게 한다
• SELECT 검색대상...FROM 테이블명1 (LEFT | RIGHT | FULL)  OUTER JOIN 테이블명2 ON 조인조건

1. LEFT OUTER JOIN : 왼쪽방향에 선언된 조인 테이블의 모든 행을 검색하고 조인조건이 맞지않는 오른쪽 방향의 조인테이블의 컬럼값은 NULL로 검색
2. RIGHT OUTER JOIN : 오른쪽방향에 선언된 조인 테이블의 모든 행을 검색하고 조인조건이 맞지않는 왼쪽 방향의 조인테이블의 컬럼값은 NULL로 검색(잘못된 데이터가 있는지 확인하기 위해 사용될때가 있음)
3. FULL OUTER JOIN : 양쪽방향으로 선언된 조인 테이블의 모든 행을 검색하고 조인조건이 맞지않는 조인테이블의 컬럼값은 *로 검색

• 셀프조인