오라클 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의 검색결과가
반드시 하나의 행/동일한 자료형/하나의 값이어야 한다.