[정보처리기사 실기]SQL응용 - 데이터분석함수

데이터 분석함수

    * 단일행을 기반으로 산출하지 않고, 복수 행을 그룹별로 모아놓고 그룹당 단일계산결과를 반환한다.

    * GROUP BY구문을 이용하여 복수행을 그룹핑한다.

    * SELECT, HAVING, ORDER BY등의 구문에 활용한다

 

• 종류

        * 집계함수 : 여러 행 또는 테이블 전체행으로부터 하나의 결괏값을 반환하는 함수 ex)Count,Sum,Avg...

        * 그룹함수 : 소그룹간의 중간 합계 분석 데이터를 산출하는 함수 ex)RollUp, Cube, Grouping Set ...

        * 윈도함수(OLAP) : 데이터베이스를 사용한 온라인 분석처리용도로 사용하기 위해서 표준 SQL에 추가된 기능 ex) 순위함수

 

☆OLAP함수 : 의사결정지원시스템으로 사용자가 다차원 데이터분석을 할 수 있도록 도와주는 기술

 

☞기억하기

Group By 구문

    - Null값을 갖는 Row는 제외 후 산출한다

    - Select에서 사용하는것과 같은 Alias 사용이 불가하다.

    - Where구문은 Group by 구문보다 먼저 실행되고, 대상이 되는 단일행을 사전에 선별한다.

Having 구문

    - 그룹화된 집합에 대한 조건지정 시 사용하고 상수나 집약함수, 집약 키를 사용할 수 있다.

---

Rollup 함수

    - 소그룹의 합계 등 중간 집계값을 산출하기위한 그룹함수

    - 지정 컬럼의 수보다 하나 더 큰 레벨만큼의 중간집계값이 생성된다.

    - Rollup의 지정컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 결과가 바뀜을 주의한다.

    - 소계집계대상이되는 컬럼을 Rollup뒤에, 아닌경우 Group By 뒤에 기재한다.

예시) select dept,job,sum(salary) from dept_salary group by rollup(dept,job)

=> dept와 job을 정렬하고 정렬이 끝난 후에 salary를 sum한다. dept가 앞에 있기 때문에 dept가 바뀔때를 기준으로 sum함

 

Cube함수

    - 결합가능한 모든 값에 대해 다차원 집계를 생성하는 그룹함수이다.

    - 연산이 많아 시스템에 부담을 준다.

예시) select dept,job,sum(salary) from dept_salary group by cube(dept,job)

=> 가능한 모든 합의 경우를 모두 출력

 

Grouping sets 

    - 순서와 무관하게 개별집계를 할 수 있다.

예시) select dept,job,sum(salary) from dept_salary group by grouping sets(dept,job,())

=> salary총합, dept별 job별 총합 출력

---

순위함수

    - rank : 동일 순위의 레코드 존재 시 후순위는 넘어감

예시) 1위,2위,2위,4위,5위,6위 ...

    - dense_rank : 동일 순위의 레코드 존재 시에도 후순위로 넘어가지 않음

예시) 1위,2위,2위,3위,4위,5위 ...

    - row_number : 동일 순위의 레코드값이 존재해도 이와 무관하게 번호를 연속적으로 부여한다

예시) 1위,2위,3위,4위,5위,6위 ...