데이터 분석 SQL - Group by와 집계 함수(Aggregate Function)

Group by 이해

- Group by 절에 기술된 컬럼 값(또는 가공 컬럼값)으로 그룹화 한 뒤 집계(Aggregation) 함수와 함께 사용되어 그룹화된 집계정보를 제공

- Group by 절에 기술된 컬럼 값으로 반드시 1의 집합을 가지게 됨

- Select 절에는 Group by 절에 기술된 컬럼(또는 가공 컬럼)과 집계 함수만 사용될 수 있음

SELECT <column(s)> FROM <table> WHERE <condition> GROUP BY <column(s)>  -- group by 적용할 컬럼명 HAVING <condition>         -- group by 결과의 filtering 조건 ORDER BY <column(s)>

select deptno, sum(sal) as sum_salary from hr.emp where job != 'SALARYMAN' group by deptno having arg(sal) > 2000 order by deptno

 

집계 함수(Aggregation Function) 의 이해

집계 함수 유형	설명
Count(*)	정해진 집합 레벨에서 데이터 건수를 가져옴
Count(distinct 컬럼명)	정해진 집합 레벨에서 해당 컬럼으로 중복을 배제하고 고유한 건수를 가져옴
Sum(컬럼명)	정해진 집합 레벨에서 지정된 컬럼값의 총합을 가져옴
Min(컬럼명)	정해진 집합 레벨에서 지정된 컬럼값의 최소값을 가져옴
Max(컬럼명)	정해진 집합 레벨에서 지정된 컬럼값의 최대값을 가져옴
Avg(컬럼명)	정해진 집합 레벨에서 지정된 컬럼값의 평균값을 가져옴

- 집계 함수 적용시 유의 사항

    ㆍ집계 함수는 Null을 계산하지 않음

    ㆍMin, Max 함수의 경우 숫자값 뿐만 아니라 문자열, 날짜/시간형도 가능

- Group by count(distinct) 케이스

    ㆍdistinct된 컬럼은 중복은 세지 않는다.

 

Group by와 Aggregate 함수의 case when을 이용한 피봇팅(Pivoting)

- Group by시 행 레벨로 만들어진 데이터를 열 레벨로 전환할 때 Aggregate와 case when을 결합하여 사용

행 레벨	열 레벨 전환 (피봇팅)
Select Year, Month     , sum(revenue) as Revenue From sales Group by Year, Month	Select Year, sum(revenue) as Year_rev     , sum(case when Month = 1 then revenue end) as Jan     , sum(case when Month = 2 then revenue end) as Feb     , sum(case when Month = 3 then revenue end) as Mar               ............     , sum(case when Month = 12 then revenue end) as Dec From sales Group by Year

 

Group by Rollup 과 Cube

- Rollup과 Cube는 Group by와 함께 사용되어 Group by절에 사용되는 컬럼들에 대해서 추가저인 Group by를 수행

- Rollup은 계층적인 방식으로 Group by 추가 수행

- Cube는 Group by 절에 기재된 컬럼들의 가능한 combination으로 Group by 수행

select deptno, job, sum(sal)
from hr.emp
group by rollup(deptno, job)
order by 1,2;
-- 1. (deptno, job)
-- 2. (deptno)
-- 3. ()

select deptno, job, sum(sal)
from hr.emp
group by cube(deptno, job)
order by 1,2;
-- 1. (dept, job)
-- 2. (dept)
-- 3. (job)
-- 4. ()

- Group by 시 Rollup을 함께 사용하면 Rollup에 적용된 컬럼의 순서대로 계층적인 Group by를 추가적으로 수행

- Group by 시 Cube를 함께 사용하면 Cube에 나열된 컬럼들의 가능한 결합으로 Group by 수행

Rollup(YEAR, MONTH, DAY)	Cube(YEAR, MONTH, DAY)
YEAR, MONTH, DAY ( 일 매출 ) YEAR, MONTH          ( 월 매출 ) YEAR                         ( 년 매출 ) ()                                ( 전체 매출 )	YEAR, MONTH, DAY YEAR, MONTH YEAR, DAY YEAR MONTH, DAY MONTH DAY ()
Group by 절의 나열된 컬럼수가 N개이면  Group by는 N+1회 수행	Group by 절의 나열된 컬럼수가 N개 이면  Group by는 2^N 회 수행