서브쿼리, 집합 연산자, window 함수

서브 쿼리

1. 인라인 뷰 (Inline View) : SELCT 문에서 컬럼 역할로 사용하는 서브쿼리

 

SELECT ENAME,
		(SELECT CLNAME FROM CLUB B WHERE A.CLUBNO = B.CLUBNO)
    FROM EMP A;

 

 

2. 스칼라 (Scalar) : FROM 구에 SELECT 문을 사용하는 서브쿼리

SELECT * FROM (SELECT ROWNUM NUM, SNAME FROM STUDENT) a WHERE NUM < 5

 

 

 

3. 중첩 서브 쿼리 (Nested) : WHERE절/HAVING 절에서 사용하는 서브쿼리

SELECT ENAME,
		JOB,
        SAL
    FROM EMP A
    WHERE SAL > (SELECT AVG(SAL) FROM EMP);

 

 

 

 

 

집합 연산자

두개의 쿼리 사이에 집합 연산자를 추가해서 합,교,차 집합 조회를 할 수 있음

 

UNION : 합집합, 중복을 제거한 결과의 합을 조회

UNION ALL : 합집합, 중복을 포함한 결과의 합을 조회

INTERSECT : 교집합, 양쪽 모두에서 포함된 행을 조회

MINUS : 차집합, 첫번째 검색 결과에서 두번째 검색 결과를 제외한 나머지 조회

 

select employee_id, job_id

from employees

union

select employee_id, job_id

from job_history;

 

 

Window 함수

행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수가 윈도우 함수

윈도우 함수는 분석 함수나 순위 함수로도 알려져 있다. 

윈도우 함수는 기존에 사용하던 집계 함수도 있고, 새로이 윈도우 함수 전용으로 만들어진 기능도 있다. 

윈도우 함수는 다른 함수와 달리 중첩해서 사용은 못하지만, 서브쿼리에는 사용할 수 있다. 

 

 

종류

1. 그룹 내 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER
2. 그룹 내 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT (sql server는 OVER 절의 OREDER BY 지원 X)
3. 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (오라클에서만 지원)
4. 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT  
5. 선형 분석을 포함한 통계 분석 함수 (하지만 설명은 생략한다 -_-)

 

WINDOW FUNCTION 문법

over 키워드가 필수로 포함

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ;

 

• WINDOW_FUNCTION : 윈도우 함수 
• ARGUMENTS(인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다. 
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.   
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다.
• WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. (sql server 에서는 지원하지 않음)

 

 

예시

SELECT   JOB, ENAME, SAL,
         RANK() OVER (ORDER BY SAL DESC) ALL_RANK,  -- 급여 높은 순
         RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK -- job 별로 급여 높은 순
FROM     EMP ;