[Oracle] 오라클 함수 정리

오라클에서 전체 합계 대비 비율 또는 백분율을 구하기 위해서는
RATIO_TO_REPORT 이용

SELECT ename
    , sal
    , RATIO_TO_REPORT(sal) OVER() AS sal_ratio
FROM emp
WHERE job = 'MANAGER'

 

NTILE OVER : 특정 컬럼의 값을 기준으로 N개의 등급 부여하기
(오름차순, 내림차순 순서대로 분류가 필요할 때)

1. 컬럼K의 값 기준으로 N개의 등급으로 분류하여 조회하려는 경우 (등급 분류 기준은 오름차순)
2. select 컬럼X, 컬럼Y, ..., ntile(숫자N) over (order by 컬럼K) as 별칭K from 테이블A;
3. 범주형 변수인 컬럼L 집단별로 컬럼K를 기준으로 N개 등급으로 분류하여 조회하려는 경우 (등급 분류 기준은 내림차순 - descending)

select 컬럼X, 컬럼Y, ...,
    ntile(숫자N) over (partition by 컬럼L order by 컬럼K desc) as 별칭K
from 테이블A
where 조건;

3. 범주형 변수인 컬럼L 집단별로 컬럼K를 기준으로 N개 등급으로 분류하여 내림차순 조회한 결과에서, 등급이 M인 결과만 조회하려는 경우

-> 2번과 같은 결과에서 특정한 등급 m에 속한 레코드만 조회하려고 한다면, 서브쿼리 사용.

select * from (
    select 컬럼X, 컬럼Y, ...,
        ntile(숫자N) over (partition by 컬럼L order by 컬럼K desc) as 별칭K 
    from 테이블A
    where 조건) 
where 별칭k = 숫자M;

 

CASE ~ WHEN ~ THEN ~ ELSE ~ END

 

CASE 컬럼명 | 표현식 WHEN 조건식1 THEN 결과1  
                   WHEN 조건식2 THEN 결과2  
                                                 .......  
                   WHEN 조건식n THEN 결과n   
                   ELSE 결과
     END

 

CASE문 중첩 사용

- 중복해서 사용 가능하나, 가독성이 떨어짐.

 

단일행 서브쿼리, 다중행 서브쿼리

• 서브쿼리 : 쿼리문 안의 또 다른 쿼리문
  

서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다.
메인쿼리에서 서브쿼리의 컬럼을 사용할 수 있는 경우 -> 스칼라 서브쿼리, 인라인 뷰

 

서브쿼리의 종류 : 스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리

• 스칼라 서브쿼리(Scalar Subquery) : SELECT 절에 위치, 한 레코드 당 정확히 하나의 값 반환하는 서브쿼리 (단일행, 단일컬럼 반환)
• 인라인 뷰(Inline View) : FROM 절에 위치하는 서브쿼리, 하나의 테이블로 리턴, SQL문이 실행될 때 임시적으로 생성되는 동적인 뷰 (Dynamic View)
• 중첩 서브쿼리(Nested Subquery) : WHERE 절에 위치, 결과집합을 한정하기 위한 서브쿼리

• 서브쿼리는 where, having 조건에서 테이블 내의 고유값을 비교하기 위해 사용한다.
• 괄호 안에 비교하고 싶은 값을 select 형식으로 작성한다.

 

1. 단일행 서브쿼리 : SELECT절에서 짧은 단문만 가능, 한 테이블에 있는 정보만 가져올 수 있다.

• 우리가 실생활에서 사용하는 복잡한 데이터의 쿼리를 출력할 때는 단일행 서브쿼리만으로 출력할 수 없다.

2. 다중행 서브쿼리 : 특정 쿼리에서 검색한 여러 개의 값을 다른 쿼리에서 받아 검색하기 위해 사용. 서브쿼리에서 여러 개의 값이 반환되는 경우.
   따라서 서브쿼리에서 출력할 행이 여러 개일 경우, 단일행 서브쿼리 연산자 =을 사용하면 에러가 발생한다. 다중행 서브쿼리 연산자 IN 을 사용해야 한다.

 

LAG, LEAD 함수

이전 행의 값, 다음 행의 값을 찾기 위해 LAG, LEAD 함수를 사용하면 된다.
주로 시계열 데이터를 분석할 때 많이 사용된다.

LAG 함수 : 이전 행의 값을 리턴
LEAD 함수 : 다음 행의 값을 리턴
expr : 대상 컬럼명
offset : 값을 가져올 행의 위치 기본값은 1, 생략가능
default : 값이 없을 경우 기본값, 생략가능
partition_by_clause : 그룹 컬럼명, 생략가능
order_by_clause : 정렬 컬럼명, 필수

SELECT empno 
    , ename
    , job
    , sal
    , LAG(empno, 2) OVER(PARTITION BY job ORDER BY empno) AS empno_prev
    , LEAD(empno, 2) OVER(PARTITION BY job ORDER BY empno) AS empno_next
FROM emp
WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')

 

- PARTITION BY : 그룹 컬럼 기준으로 이전 값 또는 다음 값 표시