[DB/SQL] 오라클 PARTITION BY 정리 | 집계함수 사용 | 순위함수 사용

 

오늘은 쿼리를 작성할 때 유용하게 사용되는 PARTITION BY 문법을 정리해 보겠습니다.

 

함수() OVER([PARTITION BY 열이름] ORDER BY 열 이름)

 

오라클에서 집계(그룹화)하여 표현할 때는 GROUP BY절을 이용해서 보여줄 수 있지만, 각각 행마다 집계된 값을 표시하기 위해서는 PATITION BY 절로도 표시할 수 있다.

 

CREATE TABLE emp_table
(   emp_id      NUMBER           NOT NULL,
    emp_name    VARCHAR2(100)    NOT NULL,
    gender      VARCHAR2(10)         NULL,
    age         NUMBER               NULL,
    position    VARCHAR2(10)         NULL,
    hire_date   DATE                 NULL,
    etc         VARCHAR2(300)        NULL,
    PRIMARY KEY (emp_id)                  );
    

INSERT INTO emp_table VALUES (1,'홍길동','남',28,'사원',sysdate,'');
INSERT INTO emp_table VALUES (2,'홍수람','남',30,'사원',sysdate,'');
INSERT INTO emp_table VALUES (3,'홍수나','여',38,'대리',sysdate,'');
INSERT INTO emp_table VALUES (4,'홍수리','여',39,'대리',sysdate,'');
INSERT INTO emp_table VALUES (5,'홍수남','남',43,'과장',sysdate,'');
INSERT INTO emp_table VALUES (6,'홍수혜','여',45,'과장',sysdate,'');

/* 직책별 나이 | GROUP BY*/
SELECT 
    AVG(AGE)
    ,POSITION
FROM EMP_TABLE 
GROUP BY POSITION
;

/* 직책별 나이 | PARTITION BY*/
SELECT 
    AVG(EMP_TABLE.age) OVER( PARTITION BY EMP_TABLE.POSITION ) AVG
    ,EMP_TABLE.POSITION
FROM EMP_TABLE
;

 

직책별 나이 ❘ GROUP BY

 

직책별 나이 ❘ PARTITION BY

 

결과를 보면 그룹핑을 하는 목적은 동일하나 각 행을 그룹핑을 해서 보여주냐, 전체 결과를 그룹핑하냐에 다른 점을 볼 수 있다.

 

 

집계 함수 PARTITION BY

SELECT 
        EMP_ID
        ,EMP_NAME
        ,GENDER
        ,AGE
        ,POSITION
        ,HIRE_DATE
        ,ETC
        ,MAX(EMP_TABLE.age) OVER( PARTITION BY EMP_TABLE.POSITION ) MAX_AGE
  FROM EMP_TABLE
  ;

조회된 결과를 보면 직원들 직책별 나이가 가장 많은 값을 같이 보여주고 있다.

 

이와 같이 집계함수를 이용해서 보여줄 수 있다.

(집계분석함수 : COUNT, MAX, MIN, SUM, AVG, STDDEV, VARIANCE)

 

 

 

순위 함수 PARTITION BY

SELECT 
        EMP_ID
        ,EMP_NAME
        ,GENDER
        ,AGE
        ,POSITION
        ,HIRE_DATE
        ,ETC
        ,RANK() OVER( PARTITION BY EMP_TABLE.POSITION ORDER BY EMP_TABLE.AGE ) RANK
  FROM EMP_TABLE

 

조회된 결과를 보면  직책(POSITION) 별 나이에 순위를 보여주고 있다.

순위 함수를 이용해서 원하는 칼럼에 따라 그룹화하여 순위를 보여줄 수 있다.

 

-RANK : 공통 순위를 출력하되 공통 순위만큼 건너뛰어 다음 순위를 출력한다. (1,2,2,4 ~)

-DENSE_RANK : 공통 순위를 출력하되 건너뛰지 않고 바로 다음 순위를 출력한다. (1,2,2,3 ~)

-ROW_NUMBER : 공통 순위를 없이 출력한다.(1,2,3,4 ~)

 

 

감사합니다.