집계함수

집계함수

: 조회 결과를 묶어 집계 처리하는 함수 ( = 그룹함수 = 다중행 함수)

• 기본적으로 전체 행을 기준으로 계산
• select시 group by 절을 이용해 그룹으로 묶을 기준 컬럼을 지정 가능
  → 지정한 컬럼의 값이 같은 행끼리 하나의 그룹으로 묶인다

📍 집계함수

• avg(column) : 평균 값 조회
• sum(column) : 합계 값 조회
• max(column) : 제일 큰 값 조회
• min(column) : 가장 작은 값 조회
• stddev() : 표준편차 조회
• variance() : 분산 조회
• count(column) : 행 수 조회 개수 조회
  → 컬럼명: null을 제외한 값들의 개수

  → * : 총 행수 - null과 관계 없이 셈

• count(distinct column) : (고유 값의 개수)행 수 조회 개수 조회 / 중복된 값은 1 개로 처리

• count(*) 를 제외한 모든 집계함수들은 null을 제외하고 집계한다
  • (avg, stddev, variance는 주의)
  • avg(), variance(), stddev()은 전체 개수가 아니라 null을 제외한 값들의 평균, 분산, 표준편차 값이 된다.=>avg(ifnull(컬럼, 0))
• 문자타입/일시타입: max(), min(), count()에만 사용가능
  • 문자열 컬럼의 max(): 사전식 배열에서 가장 마지막 문자열, min()은 첫번째 문자열.
  • 일시타입 컬럼은 오래된 값일 수록 작은 값

편차: 값이 평균으로부터 얼마나 떨어져 있는지
평균: 10, value: 5 5
표준편차: 편차들의 평균
분산: 표준편차의 제곱

ex)

select sum(salary),
	   avg(salary),
       min(salary),
       max(salary),
       stddev(salary),
       variance(salary),
       count(*),
       count(emp_id)
from emp;

group_by 절

📍 group by 절

• 특정 컬럼(들)의 값별로 행들을 나누어 집계할 때 기준컬럼을 지정하는 구문.

  • 예) 업무별 급여평균. 부서-업무별 급여 합계. 성별 나이평균

• 구문: group by 컬럼명 [, 컬럼명]

  • 컬럼: 범주형 컬럼을 사용 - 부서별 급여 평균, 성별 급여 합계
  • select의 where 절 다음에 기술한다.
  • select 절에는 group by 에서 선언한 컬럼들만 집계함수와 같이 올 수 있다.

---

having 절

📍 having 절

• group by 로 나뉜 그룹을 filtering 하기 위한 조건을 정의하는 구문.
• group by 다음 order by 전에 온다.
• 구문
  having 제약조건
• 연산자는 where절의 연산자를 사용한다.
• 피연산자는 집계함수(의 결과)
• • where절은 행을 filtering한다.
    having절은 group by 로 묶인 그룹들을 filtering한다.

emp 라는 table에 group A,B,C,D가 있을 때

group A → count(*) =5

group B → count(*) =20

group C → count(*) =3

group D → count(*) =70

가 존재하고 있는 상황이라면

select avg(salary)
from emp
group by dept_name
having count(*)  > 10
order by 1;

이러한 코드에 의하여 group b, group d의 결과만 도출한다고 할 수 있다

⇒ 여기서 실행 순서는 from , group by, having, select 순이라고 할 수 있다.

---

with rollup 사용

📍 with rollup : group by 뒤에 붙임

• group by로 묶어 집계할 때 총계나 중간 집계(group by 컬럼이 여러개일경우) 를 계산한다.
• 구문 : group by 컬럼명[, .. ] with rollup
• ex) group by job with rollup

grouping 사용

📍 grouping(컬럼명 [, 컬럼명]) : select 절에서 사용

• group by 컬럼명 with rollup 으로 집계했을 때 grouping(컬럼명)의 컬럼이 집계시 값들을 그룹으로 나누는데 사용되었으면 0 사용되지 않았으면 1을 반환한다.
  1이 반환 된 경우는 그 행의 결과는 총계이거나 중간소계임을 말한다.
• grouping(컬럼1, 컬럼2, 컬럼3) 과 같이 여러개 컬럼을 지정한 경우
  집계에 모든 세개의 컬럼이 다 사용되었으면 0
  앞의 두개만 사용되었으면 1
  앞의 한개만 사용되었으면 3
  세개 다 사용되지 않았으면 7

  컬럼1      컬럼2       컬럼3
  2**2  +  2**1    +  2**0      각각 참여하면 0, 참여 안하면 1을 곱해서 더한다.

ex)

-- EMP 테이블에서 부서(dept)별 급여(salary)의 평균과 평균의 총계도 같이나오도록 조회
	# grouping(컬럼명) -> 컬럼명으로 나뉜 데이터들을 집계한 경우 0 반환, 아닌 경우 1 반환

select -- dept_name,    -> 이 대신에 아래의 if를 사용할 수 있다
if(grouping(dept_name) = 0, dept_name, "총 평균") as "부서명",
round(avg(salary), 2) as "평균 급여"
from emp
group by dept_name with rollup;

ex)

-- EMP 테이블에서 부서(dept_name), 업무(job) 별 salary의 합계와 직원수를 소계와 총계가 나오도록 조회

select case grouping(dept_name, job) when 0 then concat(ifnull(dept_name, "미배치"), '-', job)  -- 전체 집계
									when 1 then ifnull(dept_name, '미배치')   -- 2개로만 집계
                                    when 3 then '총계' end as "구분",  -- 대분류만으로 집계
        sum(salary) as "급여 합계",
        count(*) as "직원 수"
from emp
group by dept_name, job with rollup;