오라클(데이터 웨어하우스) 분석 함수(효과적인 데이터 다차원 분석을 위한 강력한 도구)
우리는 대량의 데이터를 다차원적으로 분석해야 하는 경우가 많습니다
이때 분석 함수는 SQL 명령문을 간결하게 표현하고 효과적으로 실행할 수 있는 강력한 기능을 제공합니다
분석 함수는 여러 번의 SQL 실행 없이도 다양한 작업을 수행할 수 있으며, PARTITION 절을 통해 결과 집합을 그룹으로 분할하거나 윈도우 기능을 사용하여 부분 집합을 대상으로 분석할 수 있습니다
오라클에서는 RANK, DENSE_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LEAD, LAG 등 다양한 분석 함수를 제공합니다
분석 함수의 실행 단계
분석 함수의 실행 단계는 세 가지로 구성됩니다. 첫 번째 단계는 일반 질의 처리로, 기존의 조인, WHERE, GROUP BY, HAVING 등의 질의문을 실행하여 결과를 검색하는 단계입니다. 두 번째 단계는 분석 함수를 적용하는 단계로, 일반 질의 처리의 결과에 분석 함수를 적용합니다. 마지막 단계는 필요에 따라 생략 가능한 정렬 단계로, ORDER BY 절에 의해 결과 집합을 정렬합니다.
분석 함수의 사용법과 예시
RANK 분석 함수
RANK 함수와 DENSE_RANK 함수는 특정 칼럼 값을 기준으로 정렬된 결과에 순위를 부여하는 함수입니다
예를 들어, 학생들의 학점을 성적 순으로 검색하여 상위 순으로 등수를 부여하는 경우 RANK 함수를 사용할 수 있습니다
DENSE_RANK 함수는 RANK 함수와 비슷하지만,동일한 순위가 있을 경우에도 다음 순위를 증가시키지 않습니다(1등이 2건인 경우에도 다음 순위를 2등으로 부여합니다)
사용법
RANK() OVER (ORDER BY [ASC|DESC])
예시
SELECT studno, name, height,
RANK() OVER (ORDER BY height DESC) AS height_rank,
DENSE_RANK() OVER (ORDER BY height DESC) AS height_dense
FROM student;
Top-N 분석
Top-N 분석은 전체 칼럼 값 중에서 큰 값이나 작은 값 순으로 상위 N개를 출력하는 기능입니다
RANK나 DENSE_RANK 함수와 함께 사용되며, 서브쿼리나 인라인 뷰에서 ORDER BY 절을 사용하여 정렬한 후 WHERE 절에서 ROWNUM을 활용하여 상위 N개의 데이터를 출력합니다
사용법
SELECT [column_list], ROWNUM
FROM (
SELECT [column_list]
FROM table
ORDER BY Top-N_column
)
WHERE ROWNUM <= N;
예시
SELECT studno, name, height, rank_value
FROM (
SELECT studno, name, height,
RANK() OVER (ORDER BY height DESC) AS rank_value
FROM student
)
WHERE rank_value <= 3;
NTILE 분석 함수
NTILE 분석 함수는 출력 결과를 사용자가 지정한 그룹 수로 나누어 출력합니다
예를 들어, 10개의 결과를 2그룹으로 나누어 1부터 5까지는 1번 그룹, 6부터 10까지는 2번 그룹으로 나눌 수 있습니다
사용법
NTILE(n) OVER (ORDER BY )
예시
SELECT studno, name, birthdate, NTILE(4) OVER (ORDER BY birthdate) AS class
FROM student;
ROW_NUMBER 분석 함수
ROW_NUMBER 분석 함수는 분할별로 정렬된 결과에 대해 순위를 부여하는 기능을 제공합니다
전체 행을 특정 칼럼을 기준으로 분리하여 순위를 부여하며, GROUP BY 절과 유사한 개념입니다
사용법
ROW_NUMBER() OVER (PARTITION BY ORDER BY )
예시
SELECT deptno, weight, name,
RANK() OVER (PARTITION BY deptno ORDER BY weight) AS weight_rank,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY weight) AS weight_dense,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY weight) AS weight_row
FROM student
ORDER BY deptno, weight, name;
윈도우 분석 함수
윈도우 분석 함수는 AVG, COUNT, MAX, MIN, STDDEV, SUM, FIRST_VALUE, LAST_VALUE 등 다양한 함수를 제공합니다
이 함수들은 윈도우(부분 집합)에서 정렬된 값의 특정 범위를 대상으로 분석을 수행합니다
예를 들어, FIRST_VALUE 함수는 윈도우에서 정렬된 값 중 첫 번째 값을 반환하고, LAST_VALUE 함수는 마지막 값을 반환합니다
LAG와 LEAD 분석 함수
LAG 함수는 현재 행을 기준으로 이전 값을 참조하고, LEAD 함수는 이후 값을 참조합니다
이 함수들은 동일한 테이블에 있는 다른 행의 값을 참조하기 위해 사용되며, 연속된 데이터 값을 분석하는 데 유용합니다
분석 함수는 데이터 웨어하우스 업무에서 효과적으로 대량의 데이터를 다차원적으로 분석하기 위한 강력한 도구입니다
RANK, DENSE_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LEAD, LAG 등 다양한 분석 함수를 적절히 활용하면 복잡한 작업을 간결하게 표현할 수 있으며, PARTITION 절과 윈도우 기능을 통해 데이터를 그룹화하고 분석할 수 있습니다
데이터 분석 작업을 효율적으로 수행하기 위해 필요에 따라 분석 함수를 적절히 활용해 보세요
이전 글 – 오라클 스케줄러와 JOB 관리 방법
이전 글 – 오라클 테이블 복구(테이블 데이터 복원하기)