SQL_TOP-N분석과 분석함수, 집합연산자, 계층형쿼리

TOP-N 분석과 분석함수

 

TOP-N 분석

• 조건에 맞는 최상위 데이터 N개 혹은 최하위 데이터 N개를 조회하는 것이다

 

사용예

• 급여를 가장 많이 받는 사원 3명
• 가장 최근에 입사한 사원 5명
• 최근 3개월동안 가장 많이 판매된 상품 10가지
• 판매실적이 가장 높은 영업사원 3명

 

TOP-N쿼리의 형식

  SELECT ROWNUM, column, column, column
  FROM (SELET column, column, column
        FROM table
        ORDER BY 분석대상컬럼이 정렬기준이된다)
  WHERE ROWNUM <= N

  -- 인라인뷰 :  order by절을 사용해서 데이터를 원하는 순서로 정렬한다.
  -- ROWNUM : 서브쿼리에서 반환되는 각 행에 1부터 시작하는 순번을 할당한다.
  -- WHERE절 : 최종적으로 반환될 행의 갯수를 지정한다.

 

• ROWNUM은 오라클에서 제공하는 가상컬럼이다.
• ROWNUM은 SELECT절에서 사용할 경우, 추출하는 데이터에 1부터 시작하는 순번을 부여하는 용도로 사용된다 WHERE절에서 사용할 경우, 추출한 데이터 중 일부만 가져오는 용도로 활용할 수 있다
• ROWNUM은 순번을 1부터 부여하는 가상컬럼이기 때문에 ROWNUM의 시작을 1이 아닌 값부터 추출하려 할 경우에는 데이터가 조회되지 않는다
• ORDER BY절로 데이터를 정렬한 후 ROWNUM으로 몇 개의 데이터만 추출해야 하는 경우, ORDER BY절을 먼저 처리하도록 인라인 뷰를 만들고, 그 뷰를 감싸는 바깥쪽 SQL에서 ROWNUM 처리를 하자

 

TOP-N쿼리 사용예시

 

 

 

오라클의 분석함수

 

• 테이블의 데이터를 특정 용도로 분석하여 결과를 반환하는 함수

 

분석함수 종류

순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계 함수	SUM, MIN, MAX, AVG, COUNT

 

 

분석함수 형식

  SELECT 분석함수([컬럼]) OVER ([PARTITION BY 컬럼] [ORDER BY 컬럼])
  FROM 테이블명
  -- OVER : 분석함수임을 나타내는 키워드
  -- PARTITION BY : 계산 대상을 그룹화한다.
  -- ORDER BY : 정렬한다.

 

 

RANK 함수

• 특정 컬럼을 기준으로 정렬한 다음 순위를 구하는 함수
• 중복 순위 다음은 해당 데이터의 갯수만큼 증가된 값을 반환한다

 

RANK 함수의 예시

RANK 함수의 예시 결과출력

 

 

DENSE_RANK 함수

• 중복된 데이터행에 상관없이 순차적으로 순위를 반환한다

 

DENSE_RANK함수의 예시

 

R1행은 RANK를 사용했고, R2는 DENSE_RANK를 사용했다

동일한 값이 출력되었을 때 RANK는 동일한 값이 나오는 만큼 숫자가 커지고 DENSE_RANKE는 동일한 값이 몇개가 나오든 신경쓰지 않고 다음수를 반환한다

DENSE_RANK 사용예시 및 결과출력

 

 

 

ROW_NUMBER 함수

• RANK나 DENSE_RANK 함수가 동일한 값에 대해서 동일한 순위를 부여하는 반면에 ROW_NUMBER 함수는 같은 값이여도 고유한 순번이 부여된다
• ROW_NUMBER 함수를 활용하면 특정 범위내의 데이터를 조회할 때 유리

 

ROW_NUMBER 함수의 예시

 

RANK와 DENSE_RANK와 다르게 같은 값이 나와도 정렬된 순서대로 값을 부여한다

ROW_NUMBER 사용예시 및 결과출력

 

 

 

SUM 함수

• SUM 함수는 파티션별 데이터 행의 합계를 반환한다
• SUM( ) ~ 그룹함수 : 집합그룹에 대해 결과값이 1개
• SUM( ) ~ OVER ~ 분석함수 : 행 마다 결과가 나온다

 

SUM 함수의 예시

SUM 함수 사용예시 및 결과출력

 

 

 

그룹함수와 분석함수의 차이

 

그룹함수

• 테이블 전체 혹은 그룹당 결과가 하나 반환된다
• 테이블당 하나

  select sum(salary)
  from employees;

• 그룹당 하나

  select sum(salary)	
  from employees; 
  group by department_id;

 

 

분석함수

• 행마다 결과가 하나 반환된다

  select first_name, sum(salary) over()
  from employees;
  -- 전체 사원들의 급여 총액이 행마다  표시

  select first_name, sum(salary) over (partition by department_id)
  from employees;
-- 각 부서별 급여 총액이 행마다 표시

 

 

 

집합연산자

• 집합 연산자를 이용하면 여러 개의 SQL문 조회 결과를 연결하여 하나의 결과로 만들 수 있다
• 보통 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합칠 때 사용한다
• 혹은 동일한 테이블에 대해서 서로 다른 SQL문 조회를 수행하여 그 결과를 하나로 합칠 때 사용한다

 

집합연산자 형식

SELECT 컬럼, 컬럼, 컬럼
        FROM 테이블1
        [WHERE 조건식]
        집합연산자
        SELECT 컬럼, 컬럼,컬럼
        FROM 테이블2
        [WHERE 조건식]

 

 

집합연산자 제약사항

• SELECT절의 컬럼수가 동일해야 해야한다
• SELECT절의 동일한 위치에 존재하는 컬럼의 데이터 타입이 상호 호환 가능해야 한다 ( 데이터타입이 반드시 동일한 타입일 필요는 없다 )

 

집합연산자 종류

UNION	여러 SQL문의 결과에 대한 합집합이다 모든 중복된 행은 하나의 행으로 만든다
UNION ALL	여러 SQL문의 결과에 대한 합집합이다 중복된 행도 그래도 표시된다 여러 SQL문의 결과를 단순히 합쳐놓은 결과를 만든다
INTERSECT	여러 SQL문의 결과에 대한 교집합이다 중복된 행은 하나의 행으로 만든다
MINUS	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다 중복된 행은 하나로 만든다 SQL의 순서에 따라서 결과가 다르게 나온다

 

 

 

UNION 사용예시

 

 

 

INTERSECT 사용예시

 

MINUS 사용예시

 

 

 

 

 

EXISTS 연산자 (다중행 서브쿼리 연산자)

 

• EXISTS(서브쿼리)에서 서브쿼리는 조건을 만족하는 데이터가 여러 건이 있다고 하더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다
• 실제 프로젝트에서 특정 조건을 만족하는지 여부를 묻는 로직이 많이 사용되는데, COUNT(*)로 조건을 만족하는 행의 갯수를 조회하는 것은 SQL 실행 성능에 나쁘다. 따라서, EXISTS연산자를 사용할 수 있는지 고려해야 한다
• EXISTS(서브쿼리)에서 SELECT절에서 업무적으로 의미없는 상수값(1,'X')을 반환하도록 한다

 

EXISTS 연산자 형식

SELECT A.컬럼, A.컬럼, ...
        FROM 테이블명 A
        WHERE EXISTS (서브쿼리)
        
        UPDATE 테이블
        SET
            컬럼명 = 값,
            컬럼명 = 값,
            ..
        WHERE EXISTS (서브쿼리)
--서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 연산자다
-- 조건을 만족하는 데이터가 여러 건이라 하더라도, 1건만 찾으면 더이상 검색을 하지 않는다

 

 

 

WHERE 컬럼 연산자 (서브쿼리) : 컬럼값과 비교가능한 의미있는 값을 서브쿼리가 제공해야 한다

WHERE  값    연산자 (서브쿼리) : 제시된 값과 비교가능한 의미있는 값을 서브쿼리가 제공해야한다

WHERE EXISTS        (서브쿼리) : 서브쿼리는 의미있는 값을 제공하지 않아도 된다. 의미없는 상수값을 반환해도 된다

 

 

 

EXISTS 사용예시

 

 

 

 

 

계층형 쿼리

 

• 테이블에 계층형 데이터가 존재하는 경우 그 데이터를 조회하기 위해서 사용하는 쿼리다
• 계층형 데이터란 동일한 테이블에 계층적으로 상위/하위 개념이 포함된 데이터다

 

계층형 쿼리 예시

• 직원테이블에 사원과 관리자
• 조직도테이블에서 상위조직과 하위조직
• 메뉴테이블에서 상위메뉴와 하위메뉴
• 카테고리테이블에서 상위카테고리와 하위카테고리

 

계층형 쿼리 형식

SELECT 컬럼명, 컬럼명, ...
        FROM 테이블명
        [WHERE 조건식]
        START WITH
        CONNECT BY PRIOR 조건식;

 

 

START WITH : 계층 검색의 시작지점을 지정한다

CONNECT BY : 부모행과 자식행 간의 관계가 있는 컬럼을 지정한다

 

CONNECT BY PRIOR 부모키(기본키) = 자식키(외래키) : 계층구조에서 부모 -> 자식 방향으로 내려가는 순방향 검색

CONNECT BY PRIOR 자식키(외래키) = 부모키(기본키) : 계층구조에서 자식 -> 부모 방향으로 올라가는 역방향 검색

 

 

계층형 쿼리 순방향 검색예시

 

 

계층형 쿼리 역방향 검색예시