[SQL Fundamentals] 04. Group By와 집계 함수(Aggregate Function)

🖥️ 데이터 분석 SQL Fundamentals (권철민, 인프런)

 

섹션4. Group By와 집계 함수 (Aggregate Function)

4-1. Group By의 이해

Group By

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

결과 집합은 Group By 절에 기술된 컬럼값으로 반드시 1의 집합을 가지게 된다.

select 절에는 Group By 절에 기술된 컬럼(또는 가공 컬럼)과 집계 함수만 사용될 수 있다.

 

(사용법)

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

 

(예시) emp 테이블의 group by 예시

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

 

위의 예시로 데이터가 filtering 되는 순서를 살펴보자.

먼저 WHERE 절에 의해 job이 'SALARYMAN'이 아닌 데이터가 1차적으로 filtering 된다.

그 다음 Group By 절에 의해 deptno로 그룹화를 수행한다.

마지막으로 having 절에 의해 sal의 평균값이 2000 이상인 그룹들로 한 번 더 filtering된다.

 

Group By 적용 로직

 

 

 

 

위와 같은 과정을 통해 Group By가 동작한다.

 

(예시) emp 테이블의 group by 예시

select deptno, count(*) as emp_count, sum(sal) as sum_sal, round(avg(sal),3) as avg_sal, max(sal) as max_sal
from hr.emp
group by deptno
order by deptno;

 

 

emp 테이블의 PK는 empno다.

즉, emp 테이블의 데이터는 empno에 대해 unique하며, empno로 1의 집합을 가진다.

하지만 group by가 수행되면서 해당 집합이 뭉그러지게 되며, group by 결과 집합은 deptno를 PK로 가지게 된다.

즉, group by 결과 집합은 deptno로 unique하며, deptno로 1의 집합을 가진다.

 

4-2. Group By 실습

실습 예제

1. emp 테이블에서 부서별 최대 급여, 최소 급여, 평균 급여를 구하되 평균 급여가 2000 이상인 경우만 추출

select deptno, max(sal) as max_sal, min(sal) as min_sal, round(avg(sal),2) as avg_sal
from emp
group by deptno
having avg(sal) >= 2000
order by deptno;

 

round(val [, n])

postgreSQL 반올림 함수

첫 번째 파라미터만 주어진 경우 : 첫 번째 파라미터로 주어진 값을 반올림한 뒤 가장 가까운 정수값을 리턴한다.

첫 번째, 두번째 파라미터가 모두 주어진 경우 : 첫 번째 파라미터로 주어진 값을 반올림한 뒤 두 번째 파라미터로 주어진 값만큼 소수점 아래 자리수를 표시한다.

(예시1) round(avg(sal))

avg(sal)을 반올림하여 가장 가까운 정수값을 리턴한다.

(예시2) round(avg(sal), 2)

avg(sal)의 데이터를 반올림하여 소수점 아래 2자리까지 표시한다.

 

2. emp 테이블에서 부서별 최대 급여, 최소 급여, 평균 급여를 구하되 평균 급여가 2000 이상인 경우만 추출 (with 절을 이용)

with 
temp as (
select deptno, max(sal) as max_sal, min(sal) as min_sal, round(avg(sal),2) as avg_sal
from emp
group by deptno
)
select deptno, max_sal, min_sal, avg_sal
from temp
where avg_sal >= 2000;

 

3. 부서명 SALES와 RESEARCH 소속 직원별로 과거부터 현재까지 모든 급여를 취합한 평균 급여 구하기

select b.empno, max(b.ename) as ename, round(avg(c.sal),2) as avg_sal
from hr.dept a
    join hr.emp b on a.deptno = b.deptno
    join hr.emp_salary_hist c on b.empno = c.empno
where a.dname in('SALES', 'RESEARCH')
group by b.empno
order by 1;

 

(풀이 순서)

1) 부서명 SALES, RESEARCH 데이터만 filtering 한다.

from hr.dept a

where a.dname in('SALES', 'RESEARCH')

 

2) join을 통해 해당 부서 소속 직원 정보와 직원의 급여이력 정보를 가져온다.

from hr.dept a
    join hr.emp b on a.deptno = b.deptno
    join hr.emp_salary_hist c on b.empno = c.empno

 

3) group by를 통해 데이터 집합을 empno로 1의 집합이 되도록 한다.

group by b.empno

 

4) 집계 함수를 통해 원하는 집계성 정보를 구한다.

select b.empno, max(b.ename) as ename, round(avg(c.sal),2) as avg_sal

group by b.empno

 

4. 부서명 SALES와 RESEARCH 소속 직원별로 과거부터 현재까지 모든 급여를 취합한 평균 급여 구하기 (with 절로 풀기)

with 
temp as (
select e.empno, e.ename, esh.fromdate, esh.todate, esh.sal
from dept d
    join emp e on d.deptno = e.deptno
    join emp_salary_hist esh on e.empno = esh.empno
where d.dname in ('SALES', 'RESEARCH')
)
select empno, max(ename), round(avg(sal),2) as avg_sal
from temp
group by empno
order by 1
;

 

이때, select 절에는 group by 절에 기술된 컬럼(또는 가공컬럼)이나 집계 함수만 올 수 있으므로 직원 이름(ename)을 가져오기 위해서는 집계 함수 max()를 이용해야 한다.

(참고) group by 기준 컬럼이 empno이므로 우리는 해당 집합이 empno에 대해 unique한 집합임을 알고 있다. 따라서 직원 이름을 구하기 위해 안심하고 max()함수를 쓸 수 있다.

만약 그냥 ename이라고 쓴 경우 다음과 같은 오류가 발생한다.

 

(오류 메시지) SQL Error [42803]: ERROR: column "temp.ename" must appear in the GROUP BY clause or be used in an aggregate function

 

4-3. 집계 함수 (Aggregate Function)와 count (distinct)의 이해

집계 함수 (Aggregate Function)

count(*)

정해진 집합 레벨에서 데이터 건수를 가져옴

 

count(distinct 컬럼명)

정해진 집합 레벨에서 해당 컬럼으로 중복을 배제하고 고유한 건수를 가져옴

 

sum(컬럼명)

정해진 집합 레벨에서 지정된 컬럼값의 총합을 가져옴

 

min(컬럼명)

정해진 집합 레벨에서 지정된 컬럼값의 최소값을 가져옴

 

max(컬럼명)

정해진 집합 레벨에서 지정된 컬럼값의 최대값을 가져옴

 

avg(컬럼명)

정해진 집합 레벨에서 지정된 컬럼값의 평균값을 가져옴

 

집계 함수 적용 시 유의 사항

1. 집계 함수는 Null 을 계산하지 않는다.

2. min, max 함수의 경우 숫자값 뿐만 아니라 문자열, 날짜/시간형도 가능하다.

 

4-4. 집계 함수 (Aggregate Function)와 count (distinct) 실습

실습 예제

실습용 테이블 emp_test 조회

select * from hr.emp_test order by deptno;

 

1. comm 컬럼에 대한 집계 함수 (aggregate function) 적용

select deptno, count(*) as cnt, sum(comm), max(comm), min(comm), avg(comm)
from hr.emp_test
group by deptno
order by deptno;

 

 

집계 함수는 Null 을 집계하지 않는다.

특히 평균(avg)을 구할 때 주의해야 한다.

부서 30의 경우 전체 건수인 count(*)는 6이지만, comm 값이 Null 이 아닌 건수는 4이다.

따라서 avg(comm)는 2200 / 6 = 366.67 이 아닌 2200 / 4 = 550 이 된다.

 

2. max, min 함수는 숫자값뿐만 아니라, 문자열, 날짜/시간 타입에도 적용 가능

select deptno, max(job), min(ename), max(hiredate), min(hiredate) --, sum(ename) --, avg(ename)
from hr.emp
group by deptno
order by deptno;

 

실제 같은 부서 내에도 다양한 job, ename, hiredate 값이 존재하지만 그 중에 하나의 값만 추출하고 싶을 때 max, min 함수를 이용한다.

max 함수를 문자열에 적용하면 첫 글자의 알파벳 순서가 가장 뒤에 있는 값을 가져오며, 날짜에 적용하면 가장 큰 날짜값을 가져온다.

min 함수를 문자열에 적용하면 첫 글자의 알파벳 순서가 가장 앞에 있는 값을 가져오며, 날짜에 적용하면 가장 작은 날짜값을 가져온다.

 

3. count(distinct 컬럼명)는 지정된 컬럼명으로 중복을 제거한 고유한 건수를 추출

select deptno, count(*) as cnt, count(distinct job) from hr.emp_test group by deptno;

 

 

실제 각 부서의 데이터 건수는 3, 4, 6건이지만 job 컬럼으로 중복을 제외한 고유한 건수를 추출하면 각각 3, 3, 3건이 나온다.

이를 통해 부서 10에는 job 컬럼에 중복된 데이터가 없고, 부서 20에는 1건, 부서 30에는 3건의 중복된 데이터가 있음을 알 수 있다.

 

4-5. Group By절에 가공 컬럼 및 case when 적용 실습

Group By 절에는 테이블에 정의된 컬럼 뿐만 아니라 가공 컬럼도 올 수 있다. 

이를 통해 더 다양한 방식으로 데이터를 그룹화할 수 있다.

실습 예제를 통해 더 자세히 알아보자.

 

실습 예제

1. 1000미만, 1000-1999, 2000-2999와 같이 1000단위 범위내에 sal이 있는 레벨로 group by 하고 해당 건수를 구함

 

(나의 생각)

select round(sal, -3) as sal_range, count(*) as cnt
from hr.emp
group by round(sal, -3)
order by round(sal, -3);

 

1000 단위 범위로 sal을 그룹화하는 방식이 생각이 안 나서 대충(?) round 함수를 썼더니 데이터가 완전 이상하게 묶여버렸다.

 

 

round(sal, -3)은 sal 데이터를 정수 세번째 자리에서 반올림해버린다.

 

원래 emp 테이블의 sal 데이터를 살펴보자.

 

 

sal 값이 800, 950인 경우 정수 세번째 자리에서 반올림하면 1000이 된다.

1250, 1300인 경우에도 정수 세번째 자리에서 반올림하면 1000이 된다.

따라서 내가 썼던 방식으로 그룹화하면 1000 미만으로 묶이는 데이터는 한 건도 나오지 않게 된다.

sal 컬럼의 데이터를 1000 단위로 그룹화하려면 아래처럼 floor 함수를 사용해야 한다.

 

(정답)

select floor(sal/1000)*1000, count(*) 
from hr.emp
group by floor(sal/1000)*1000
order by floor(sal/1000)*1000;

 

 

우선 sal/1000을 할 경우, 0이상 1000 미만의 값들은 모두 0.xxx의 값이 나온다.

여기에 floor() 함수를 적용하여 소수점 이하 값들을 모두 버리면 0이 된다.

0에 다시 1000을 곱해도 결국 0이 된다.

마찬가지로 sal/1000을 할 경우, 1000 이상 2000 미만의 값들은 모두 1.xxx의 값이 나온다.

여기에 floor() 함수를 적용하여 소수점 이하 값들을 모두 버리면 1이 되며, 이 값에 다시 1000을 곱하면 1000이 된다.

이런 방식으로 0 이상 1000 미만의 값들은 0으로, 1000 이상 2000 미만의 값들은 1000으로, (생략), 5000 이상 6000 이하의 값들은 5000으로 그룹화할 수 있다.

 

🤔 postgreSQL의 반올림 관련 함수들

 

round(val [,n]) : 일반적인 반올림 함수

(참고) https://www.postgresqltutorial.com/postgresql-math-functions/postgresql-round/

PostgreSQL ROUND() Function By Practical Examples

 

ceil(numeric_expression) : 입력값을 하나만 받는다. 소수점 아래 숫자를 무조건 올린다. 천장으로 만든다는 의미이다.

(참고) https://www.postgresqltutorial.com/postgresql-math-functions/postgresql-ceil/

PostgreSQL CEIL Function By Practical Examples

 

floor(numeric_expression) : 입력값을 하나만 받는다. 소수점 아래 숫자를 무조건 버린다. 바닥으로 만든다는 의미이다.

(참고) https://www.postgresqltutorial.com/postgresql-math-functions/postgresql-floor/

PostgreSQL FLOOR Function By Practical Examples

 

2. job이 SALESMAN인 경우와 그렇지 않은 경우만 나누어서 평균/최소/최대 급여를 구하기

select case when job = 'SALESMAN' then 'SALESMAN' else 'OTHERS' end as job, 
    round(avg(sal),2) as avg_sal, min(sal) as min_sal, max(sal) as max_sal
from hr.emp
group by case when job = 'SALESMAN' then 'SALESMAN' else 'OTHERS' end;

 

case when은 select 절 뿐만 아니라 where, group by, having 절에도 쓸 수 있다.

다른 프로그래밍 언어의 if ~ else 와 같은 역할을 한다.

위의 예시에서는 job이 'SALESMAN'인 경우는 'SALESMAN'으로, 그 외에는 모두 'OTHERS'로 나누도록 하여 이를 group by 절과 select 절에서 사용했다.

 

(참고) postgreSQL의 case문

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-case/

PostgreSQL CASE

 

4-6. Group By와 집계 함수의 case when을 이용한 피봇팅 이해

Group By 시 행 레벨로 만들어진 데이터를 열 레벨로 전환할 때, 집계 함수 (aggregate function)와 case when을 결합하여 사용한다.

보통 가독성을 위해 Pivoting 을 사용하는 경우가 많다.

 

 

Pivoting 하면서 Group By의 레벨은 Year + Month 에서 Year 로 확장됐지만, select 절에서 다시 Month 단위로 쪼개서 보여주고 있다.

 

4-7. Group By와 집계 함수의 case when을 이용한 피봇팅 실습

실습 예제

1-1. deptno, job 별로 group by하고 건수 구하기

select deptno, job, count(*) as cnt
from hr.emp
group by deptno, job
order by deptno, job;

 

수행 결과

 

1-2. group by Pivoting시 조건에 따른 건수 계산 유형(count case when then 1 else null end)

select deptno, count(*) as cnt
    , count(case when job = 'SALESMAN' then 1 end) as salesman_cnt
    , count(case when job = 'MANAGER' then 1 end) as manager_cnt
    , count(case when job = 'ANALYST' then 1 end) as analyst_cnt
    , count(case when job = 'CLERK' then 1 end) as clerk_cnt
    , count(case when job = 'PRESIDENT' then 1 end) as president_cnt
from hr.emp
group by deptno
order by 1;

 

수행 결과

 

건수 계산을 위해 count() 함수를 사용할 때 유의할 점이 있다.

만약 count() 함수 내의 case 문에서 else null 대신 else 0을 쓴다면 어떤 결과가 나올까?

 

1-3. group by Pivoting시 조건에 따른 건수 계산의 잘못된 사례(count case when then 1 else 0 end)

select deptno, count(*) as cnt
    , count(case when job = 'SALESMAN' then 1 else 0 end) as sales_cnt
    , count(case when job = 'MANAGER' then 1 else 0 end) as manager_cnt
    , count(case when job = 'ANALYST' then 1 else 0 end) as analyst_cnt
    , count(case when job = 'CLERK' then 1 else 0 end) as clerk_cnt
    , count(case when job = 'PRESIDENT' then 1 else 0 end) as president_cnt
from emp
group by deptno
order by 1;

 

수행 결과

 

모든 컬럼에 총 건수가 집계됐다.

왜 이런 결과가 나왔을까?

count() 함수는 건수를 셀 때 1을 1건으로, 0을 0건으로 세는 것이 아니라 null 이 아닌 모든 값을 1건으로 세기 때문이다.

따라서 count case when then 1 else null end 를 썼을 때와 count case when then 1 else 0 end 를 썼을 때의 결과가 다르게 나온다.

(참고) case 문에서 else null 을 생략하고 case when then 1 end 라고 쓰면 알아서 else null 구문으로 수행된다.

 

위의 1-3 예시와 같이 else 0 을 사용하여 건수를 집계하고 싶다면 count() 함수 대신 sum() 함수를 사용해야 한다.

아래 예시를 통해 sum() 함수를 이용한 건수 집계 예시를 알아보자.

 

1-4. group by Pivoting시 조건에 따른 건수 계산에 sum()을 이용

select deptno, count(*) as cnt
    , sum(case when job = 'SALESMAN' then 1 else 0 end) as sales_cnt
    , sum(case when job = 'MANAGER' then 1 else 0 end) as manager_cnt
    , sum(case when job = 'ANALYST' then 1 else 0 end) as analyst_cnt
    , sum(case when job = 'CLERK' then 1 else 0 end) as clerk_cnt
    , sum(case when job = 'PRESIDENT' then 1 else 0 end) as president_cnt
from emp
group by deptno
order by 1;

 

수행 결과

 

4-8. Group By Rollup과 Group By Cube의 이해

Rollup 과 Cube 는 Group By 와 함께 사용되어 Group By 절에 사용되는 컬럼들에 대해서 추가적인 Group By 를 수행한다.

 

Group By Rollup

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

 

(예시)

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

 

 

Group By Cube

Group By 시 Cube 를 함께 사용하면 Cube 에 나열된 컬럼들의 가능한 combination 으로 Group By 를 수행한다.

 

(예시)

select deptno, job, sum(sal) as sum
from hr.emp
group by cube(deptno, job)
order by 1, 2;

 

Group By Rollup과 Cube 의 적용

Rollup(YEAR, MONTH, DAY)

YEAR, MONTH, DAY
YEAR, MONTH
YEAR
()

 

Group By Rollup 절에 나열된 컬럼수가 N개이면 Group By는 N + 1 회 수행

 

Cube(YEAR, MONTH, DAY)

YEAR, MONTH, DAY
YEAR, MONTH
YEAR, DAY
YEAR
MONTH, DAY
MONTH
DAY
()

 

Group By Cube 절에 나열된 컬럼수가 N개이면 Group By는 2^n 회 수행

 

4-9. Group By Rollup 실습

실습 예제

1. 년+월+일별 매출합 구하되, 월별 소계 매출합, 년별 소계 매출합, 전체 매출합을 함께 구하기

with 
temp_01 as (
select to_char(b.order_date, 'yyyy') as year
    , to_char(b.order_date, 'mm') as month
    , to_char(b.order_date, 'dd') as day
    , sum(a.amount) as sum_amount
from nw.order_items a
    join nw.orders b on a.order_id = b.order_id
group by rollup(to_char(b.order_date, 'yyyy'), to_char(b.order_date, 'mm'), to_char(b.order_date, 'dd'))
)
select case when year is null then '총매출' else year end as year
    , case when year is null then null
        else case when month is null then '년 총매출' else month end
      end as month
    , case when year is null or month is null then null
        else case when day is null then '월 총매출' else day end
      end as day
    , sum_amount
from temp_01
order by year, month, day
;

 

위의 예시에서는 Group By가 총 3 + 1 = 4번 수행된다.

 

4-10. Group By Cube 실습

Group By Cube 에 기술된 컬럼의 가능한 결합으로 Group By를 수행한다.

업무적으로 필요없는 조합으로 그룹화하거나 너무 많은 조합을 만들기 때문에 현업에서 실제 3개 이상 컬럼의 Group By Cube 를 수행하는 경우는 드물다.

 

실습 예제

1. 상품 카테고리, 상품별, 주문처리직원별 가능한 결합으로 Group by 수행

select c.category_name, p.product_name, e.first_name || e.last_name as employee_name, sum(oi.amount) as sum_amount
from nw.order_items oi
    join nw.orders o on oi.order_id = o.order_id
    join nw.employees e on o.employee_id = e.employee_id 
    join nw.products p on oi.product_id = p.product_id 
    join nw.categories c on p.category_id = c.category_id
group by cube(c.category_name, p.product_name, e.first_name || e.last_name)
order by 1,2,3;

 

위의 예시에서는 Group By가 총 2^3 = 8번 수행된다.