[Oracle] Analytic Functions 1 (분석 · 집계 함수)

Analytic Functions

데이터의 통계적인 자료를 분석하고 집계하는 기능이다. 실무에서도 많이 사용되어지고 있다.

COUNT(), OVER(), OVER( PARTITION BY ), OVER( ORDER BY )

샘플 데이터

CREATE TABLE school(
    student_id    NUMBER           NOT NULL,  -- 학번
    dep_name      VARCHAR2(100)    NOT NULL,  -- 학과명
    grade         NUMBER           NOT NULL,  -- 학년
    score         NUMBER           NOT NULL,  -- 성적
    PRIMARY KEY(student_id)
);

INSERT INTO school VALUES(101, '컴퓨터공학과', 2, 2);
INSERT INTO school VALUES(102, '컴퓨터공학과', 3, 3);
INSERT INTO school VALUES(103, '경영학과', 2, 2);
INSERT INTO school VALUES(104, '경영학과', 2, 3);
INSERT INTO school VALUES(105, '경영학과', 3, 4);
INSERT INTO school VALUES(106, '전자공학과', 3, 2);

 

SELECT * FROM school;

 

[결과]

 

---

집계함수

SELECT COUNT(), MIN(), MAX(), AVG(), STDDEV() ...

데이터의 갯수, 최솟값, 최댓값, 평균, 표준편차 등을 집계하여 보여주는 함수이다.

 

SELECT COUNT(*), MIN(score), MAX(score), AVG(score) FROM school

 

[결과]

 

COUNT(data) : data의 갯수

MIN(data) : data의 최솟값

MAX(data) : data의 최댓값

AVG(data) : data의 평균값

 

표와 같이 모든 데이터의 집계 결과를 하나의 행으로 보여주고 있다.

 

필요하다면, GROUP BY를 사용할 수 있다.

SELECT dep_name, COUNT(*), MIN(score), MAX(score), AVG(score)
FROM school
GROUP BY dep_name

 

[결과]

 

모든 데이터의 집계가 아닌 그룹별 집계 데이터를 보여준다.

 

하지만 데이터는 모든 데이터대로 나오고 집계함수의 결과는 결과대로 보여주는 즉, 모든 row를 보고싶을 때 사용하는 기능이 OVER이다.

 

---

OVER

전체 데이터와 집계함수의 결과를 보여준다.

 

SELECT S.*, COUNT(*) OVER () AS total_count
FROM school S

 

[결과]

모든 데이터에 총 갯수를 보여준다.

 

다른 집계함수도 보여줄 수 있다.

SELECT S.*,
    COUNT(*) OVER() AS total_count,
    MIN(score) OVER() AS mins,
    MAX(score) OVER() AS maxs,
    AVG(score) OVER() AS avgs,
    SUM(score) OVER() AS sums
FROM school S

 

[결과]

 

모든 데이터와 집계함수의 결과를 보여준다.

 

하지만 경우에 따라 그룹별 집계함수의 결과를 알고싶거나 그룹별 집계함수의 누적된 값을 알고 싶을 때가 있다. 이 때 PARTITION BY와 ORDER BY를 사용하면 된다.

 

---

OVER( PARTITION BY ), OVER( ORDER BY )

1. PARTITION BY

그룹영역을  지정하여 그룹별로 집계함수의 값을 보여준다.

 

SELECT S.*,
    COUNT(*) OVER(
        PARTITION BY dep_name
    ) AS school_per_depName,
    SUM(score) OVER(
        PARTITION BY dep_name
    ) AS score_per_depName
FROM school S

 

[결과]

 

OVER() 안에 PARTITION BY를 추가하고 그룹핑하려는 속성을 지정해주면 결과와 같이 그 그룹의 집계함수 결과만을 보여준다.

 

이 맥락에선 dep_name을 기준으로 그룹핑한 후 각 그룹의 갯수(COUNT)와 합계(SUM)을 모든 레코드에서 보여주고 있다.

 

2. ORDER BY

통계 결과의 누적된 값을 보여준다.

 

SELECT S.*,
    COUNT(*) OVER(
        ORDER BY student_id
    ) AS total_count,
    SUM(score) OVER(
        ORDER BY student_id
    ) AS total_sum
FROM school S

 

[결과]

 

OVER() 안에 ORDER BY를 추가하고 누적되는 컬럼을 명시해주어야 한다. 그래서 ORDER BY 다음은 반드시 UNIQUE한 속성을 명시해 주어야한다. 그렇지 않으면 누적이 되지 않는 현상을 볼 수 있다.

 

이 맥락에선 COUNT의 누적값이라 +1씩 올라가는 것을 볼 수 있다.

또한 누적된 합계(TOTAL_SUM)는 모든 컬럼의 점수(SCORE)의 누적된 값을 볼 수 있다.

 

이 맥락과 같이 누적된 값을 보고 싶을 때에는 반드시 ORDER BY 다음에 UNIQUE한 속성을 명시해야 한다.

 

3. PARTITION BY · ORDER BY

PARTITION BY와 ORDER BY 기능 둘 다 사용 시

SELECT S.*,
    COUNT(*) OVER(
        PARTITION BY dep_name
        ORDER BY student_id
    ) AS student_per_depName,
    SUM(score) OVER(
        PARTITION BY dep_name
        ORDER BY student_id
    ) AS score_per_depName
FROM school S

 

[결과]

 

각 그룹별로 누적된 값을 볼 수 있다.

 

---

Filtering

WHERE 조건에 집계함수를 사용하여 필터를 할 수 있는지에 대해 알아보자.

 

SELECT dep_name, COUNT(*) FROM school
WHERE COUNT(*) OVER( PARTITION BY dep_name ) >= 2

 

dep_name을 그룹핑 한 COUNT의 갯수가 2개 이상인 데이터들만 보고싶을 때 이러한 쿼리문을 실행하면

 

 

'그룹 함수는 허가되지 않습니다' 라는 오류를 볼 수 있다.

이유는 간단하다.

쿼리문 실행순서는 FROM -> WHERE -> GROUP BY -> SELECT 이다.

즉, FROM절에서 테이블에 있는 값을 기준으로 WHERE절에서 체크를 하는데 현재 COUNT(*) OVER( PARTITION BY dep_name)은 SELECT절에서 실행하는 쿼리문이기 때문에 WHERE절에서 찾지 못하는 것이다.

쉽게 말해 그룹함수는 SELECT절에서 사용하는 것인데 실행순서상 SELECT절은 WHERE절보다 늦게 읽혀지기 때문에 WHERE절에서는 그룹함수를 사용할 수 없는 것이다.

 

이럴 경우에는 2가지 방법이 있다.

1. GROUP BY를 사용하여 미리 그룹화 해서 나온 HAVING절에 그룹함수를 넣는다.

2. FROM절에 SELECT구문을 한번 더 감싼 다음 사용한다.

 

1.

SELECT dep_name, COUNT(*)
FROM school
GROUP BY dep_name
HAVING COUNT(*) >= 2

 

그룹함수를 HAVING절에서 사용한다.

 

[결과]

 

2. 

SELECT *
FROM (
    SELECT S.*, COUNT(*) OVER( PARTITION BY dep_name) dep_count
    FROM school S
    )
WHERE dep_count >= 2

 

FROM절에서 그룹함수를 사용한 테이블을 만든 후 WHERE절에서 조건처리를 해준다.

 

[결과]

 

쿼리문 실행 순서를 생각해보면 당연한 결과이다.

필터 처리는 이런 식으로 해주면 된다.

 

---

마무리

분석 · 집계 함수인 Analytic Functions에 대하여 공부해 보았다.

실무에서 자주 쓰이는 기능이라 제대로 확실하게 배우고 넘어가면 좋을 것 같다.

다음 시간에는 더욱 중요한 기능들인 ROWNUM, RANK, LAG, FIRST VALUE... 등을 배우려고 한다.