오라클 DB 용어, 명령문 정리03 - 분석함수, JOIN함수, 서브쿼리

2019.05.21 필기

 

 

<분석함수 OVER()>

---------------------------------------------기본 개념---------------------------------------------

윈도우함수에 의해 발생된 결과를 이용하여 처리 후 결과를 반환하는 함수

※ 윈도우 - 분석함수에서 사용하는 로우별 그룹

※ 윈도우함수 - 그룹함수, 순위함수, 순서함수 등

-----------------------------------------------------------------------------------------------------

 

- 분석함수는 통계, 데이터 분석 쪽에 활용될 수 있는 함수이다.

 

 

<함께 쓰일 수 있는 윈도우 함수들>

- 그룹함수

- 순위함수(반드시 분석함수와 함께 사용/독립사용 불가)
               : RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

- 순서함수(반드시 분석함수와 함께 사용/독립사용 불가)

               : FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()

- 집계함수 : SUM(), MIN(), MAX(), AVG(), COUNT()
- 기타함수 : LEAD(), LAG(), RATIO_TO_REPORT(), KEEP(), LISTAGG()

 

 

---------------------------------------------기본 구문---------------------------------------------

SELECT OVER(

           [PARTITION BY 컬럼명]

           [ORDER BY 컬럼명]

           [WINDOWING]

           )

          , 검색대상

FROM 테이블명;

-----------------------------------------------------------------------------------------------------

 

<각 명령어 설명>

OVER - 분석함수임을 알려주는 선언

PARTITION BY - 분석 함수의 계산대상 그룹 지정 --- GROUP BY와 같은 역할.
ORDER BY - 계산 대상 그룹에 대해 정렬
WINDOWING - 분석 함수 대상이 되는 기준을 좀 더 세부적으로 정의

 

그룹함수를 분석함수와 같이 사용할 경우 다른 검색대상과 함께 사용 가능(원래는 안 됨)
분석함수는 GROUP BY 명령을 쓸 수 없다. 대신 PARTITION BY로 같은 기능 수행

--- order by를 over() 안에 쓰기를 권장

 

ROWS UNBOUNDED PRECEDING : 검색행을 기준으로 이전에 존재하는 모든 행만을 검색대상으로 설정

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 이전 행 하나에서 이후 행 하나 검색

 

RANK() : 순위함수. 정렬값이 동일한 경우 같은 순위를 지정하고 다음 순위를 건너띄고 순위로 검색.

DENSE_RANK() : 정렬값이 동일한 경우 같은 순위를 지정하며, 다음 순위로 검색

ROW_NUMBER() : 행번호를 이용하기 때문에 정렬값이 동일한 경우에도 다른 순위를 지정

------ 즉, 1등-2등-2등(같은 순위 나옴) 뒤의 순위를 RANK()는 4등으로 지정, DENSE_RANK()는 3등으로 지정한다. 반면 ROW_NUMBER()는 애초에 1등-2등-3등으로 같은 순위를 뽑아내지 않는다.

 

FIRST_VALUE() : 첫번째 검색행의 컬럼값을 반환

LAST_VALUE() : 마지막 검색행의 컬럼값을 반환

 

LAG() : 검색행 이전 행을 컬럼값을 반환

LAG(column, index_preceding, index_chage) : 이전 위치 행의 컬럼값을 검색하되, 이전 행이 없으면 변경값 반환

--- 바로 전이면 index_preceding 값이 1, 전전이면 2

 

LEAD() : 검색행 이후 행의 컬럼값을 반환

LEAD(column, index_following, index_chage) : 이후 위치 행의 컬럼값을 검색하되, 이후 행이 없으면 변경값 반환

--- 바로 뒷자리면 index_following 값이 1, 뒷뒷자리면 2

 

 

<조인함수 JOIN()>

JOIN 함수란? : 두 개 이상의 테이블에서 원하는 행을 검색하기 위한 기능. / 객체관계형!

--- 즉, 어떤 검색 결과가 2개 이상의 테이블을 검색해야 나올 수 있을 때 사용

--- 여러 개의 테이블에서 행을 검색하기 위해서는 반드시 조인 조건 사용 : WHERE 명령에 조인조건을 명시

 

카타시안 프로덕트(CATASIAN PRODUCT)란? : 조인조건을 명시하지 않은 조인을 뜻함

--- 조인조건이 명시되지 않은 경우, 테이블들의 모든 행을 교차조인하여 검색

 

동등 조인(EQUI JOIN) : 조인조건에 = 연산자를 이용하여 조인 테이블의 컬럼값이 같을 경우 조인하여 검색

--- 조인 테이블에 동일한 컬럼명이 존재할 경우, 반드시 테이블명 명시 >> [형식] table.column

--- 조인 테이블의 동일한 컬럼명을 동시에 검색할 경우, 두 번째 컬럼명은 자동으로 변경되어 검색

--- 따라서, 이 경우 두 번째 컬럼에 별명을 부여하는 것을 권장(ALIAS 시키기)

--- ※ 테이블 별명(TABLE ALIAS) : 조인 테이블에 '새로운 이름'을 부여하는 기능

---     **AS 명시하면 안 됨 & 새 이름 부여 뒤에 실제 테이블명 사용하면 에러(컬럼별명과 다른 점)

 

비동등 조인(NON-EQUI JOIN) : 조인조건에 = 연산자를 제외한 연산자로 비교하여 조인

 

외부조인(OUTER JOIN) : 조인조건이 맞지 않는 행까지도 검색

>> [형식] SELECT 검색대상 FROM 테이블1, 테이블2 WHERE table1.column (+) = table2.column;

--- 조건이 맞지 않는 행을 가진 테이블에 (+)를 사용하여 null로 검색되도록 설정 >> [형식] table.column (+)

--- 아우터조인은 무결성 위반한 데이터(무의미한 데이터)를 검사할 때 사용할 수 있다.

 

외부조인+온(OUTER JOIN ON)

>> [형식] SELECT 검색대상 FROM 테이블1 {LEFT|RIGHT|FULL} OUTER JOIN 테이블2 ON 조인조건;

--- LEFT OUTER JOIN : 왼쪽 방향에 선언된 조인 테이블1의 행에서 조인조건이 맞지 않는 건 null로 포함시켜 반환

--- RIGHT OUTER JOIN : 오른쪽 방향에 선언된 조인 테이블2의 행에서 조인조건이 맞지 않는 건 null로 포함시켜 반환

--- FULL OUTER JOIN : 양쪽 방향에 선언된 조인 테이블1, 2의 행에서 조인조건이 맞지 않는 건 null로 포함시켜 반환

 

셀프 조인(SELF JOIN) : 하나의 테이블에 다른 이름을 부여하여 조인 / 테이블 별명을 부여해야 함!

 

크로스 조인(CROSS JOIN) : 조인 테이블의 모든 행들을 교차 조인
>> [형식] SELECT 검색대상 FROM 테이블1 CROSS JOIN 테이블2;

--- 경우의 수를 구할 때처럼,  결과는 X x Y x Z x ... 의 갯수만큼 나온다.

 

NATURAL JOIN : 조인 테이블의 동일한 하나의 이름의 컬럼에 대한 컬럼값을 이용하여 조인

>>  [형식] SELECT 검색대상 FROM 테이블1 NATURAL JOIN 테이블2;

--- EMP 테이블과 DEPT 테이블에 공통으로 존재하는 DEPTNO 컬럼값을 이용하여 자동 조인

--- 동일한 컬럼명에 대한 검색에 대해 테이블명을 명시할 필요가 없다.

 

**이너 조인(INNER JOIN)** : 조인조건을 명시하여 조인조건이 맞는 행을 조인

--- 조인된 테이블들로 새로운 가상의 테이블어 검색하기 때문에, 조인해야 할 게 많을 때 크로스 조인 등에 비해

    처리속도가 빠르다.

 

>> [형식1] SELECT 검색대상 FROM 테이블1 [INNER] JOIN 테이블2 USING(column);
--- 조인 테이블의 동일한 여러 컬럼명 중 하나에 대한 컬럼값을 이용하여 조인할 경우 USING 키워드 사용
--- 동일한 컬럼명 검색에서는 테이블명을 명시할 필요 없다.

--- INNER 키워드는 생략 가능

 

>>  [형식2] SELECT 검색대상 FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건A JOIN 테이블3 ON 조인조건B;

--- 동일한 컬럼명 검색에 대해 테이블명 명시 

--- INNER 키워드는 생략 가능

 

 

 

<서브쿼리 SUBQUERY>

---------------------------------------------기본 개념---------------------------------------------

SQL 명령 안에 포함되어 선언된 SELECT 명령

즉, 쿼리문 안의 쿼리문이다.

-----------------------------------------------------------------------------------------------------

 

<서브쿼리가 사용 가능한 SQL 명령>

SELECT, CREATE, INSERT, DELETE, UPDATE, ORDER BY 등

 

--- FROM, WHERE, HAVING 뒤에 올 수 있다.

--- WHERE, HAVING 뒤에 쓰일 경우, 서브쿼리에서 나온 결과를 비교대상과 비교

 

---------------------------------------------기본 구문---------------------------------------------

SELECT 검색대상

FROM 테이블명

WHERE 비교대상 + 비교연산자 (

           SELECT ...

           FROM ...

           ....

           ) ;

-----------------------------------------------------------------------------------------------------

 

--- 바깥쪽 문장이 MAIN QUERY,

    안쪽 문장이 SUB QUERY

--- 명령 실행 순서 >> 서브쿼리가 먼저, 메인쿼리가 나중에 실행된다.

--- '오라클에서는' 서브쿼리 안에 또 다른 서브쿼리를 쓸 수 있다.

--- 서브쿼리를 사용하면 한 개의 SELECT 명령으로 원하는 결과 검색 가능

--- WHERE 뒤에 SUBQUERY를 사용할 경우, 비교대상과 SUBQUERY의 검색결과가

     반드시 하나의 행/동일한 자료형/하나의 값이어야 한다.