SQL : 05 함수 (2) 집계함수

_플레이데이터 빅데이터캠프 공부 내용 _5/26~5/27

MySQL : 05 함수 (2) 집계함수

1. 집계함수, 그룹함수, 다중행 함수

: 조회 결과를 묶어 집계 처리하는 함수들로 그룹함수, 다중행 함수라고도 한다.

- 기본적으로 전체 행을 기준으로 계산한다.

  전체 데이터에 대해서 집계를 한다.! 전체 데이터를 집계해서 한 행으로 결과값을 나타냄.

  단일함수는 한 행 처리, 한 행 처리, 이렇게 한행에 대한 처리 결과를 107행 테이블로 봄.

  => 그룹화 때 컬럼으로 쓰이는 거 아니면 select에서 함께 쓰지 않는다.

- 인수(argument)는 컬럼. 단일행함수 처리한 컬럼 값도 인수로 쓰일 수 있다.

- SELECT 시 GROUP BY 절을 이용해 그룹으로 묶을 기준 컬럼을 지정할 수 있다.

    -> 지정한 컬럼의 값이 같은 행끼리 하나의 그룹으로 묶여 집계하는 것. 어떤 기준인지 같이 조회해주는 게 good

 

 1.1 구문

(1) sum(): 전체합계

(2) avg(): 평균

(3) stddev(): 표준편차. 편차의 평균
(4) variance(): 분산

 

- 문자타입/일시타입: max(), min(), count()에만 사용가능

(5) min(): 최소값
(6) max(): 최대값

 

- 일시타입 컬럼은 오래된 값일 수록 작은 값이다.

-- EMP 테이블에서 가장 최근 입사일(hire_date)과 가장 오래된 입사일을 조회
-- GUIDE: 일시 타입과 문자열(다음것)의 순서 
select  min(hire_date) "최근입사일"
        , max(hire_date) "가장 오래된 입사일" 
from emp;

 

- 문자열 컬럼의 max(): 사전식 배열에서 가장 마지막 문자열, min()은 첫번째 문자열. 

select min(emp_name), max(emp_name) from emp; -- 문자열(특수문자 < 숫자 < 대문자 < 소문자)

 

** EX 급여(salary)의 총합계, 평균, 최소값, 최대값, 표준편차, 분산를 조회 

select  sum(salary) "총합계"
        , round(avg(salary), 2) "평균"
        , min(salary) "최소값"
        , max(salary) "최대값"
        , ceil(stddev(salary)) "표준편차"
        , round(variance(salary), 2) "분산"
from   emp;

 

(7) count(): 개수

- 인수: 
     - 컬럼명  : null을 제외한 값들의 개수.
     -  *  : 총 행수 ==> null과 관계 없이 센다.

select count(*) from emp;

- count(distinct 컬럼명): 고유값의 개수.  컬럼의 값 종류 개수를 조회할 때.

-- emp 테이블에서 job 종류의 개수 조회
select count(distinct job) from emp;

select count(distinct dept_name) from emp; -- null은 빼고 센다.

select count(distinct ifnull(dept_name, '미배치')) from emp; -- null 포함해서 센다.

- count(*) 를 제외한 모든 집계함수들은 null을 제외하고 집계한다. (avg, stddev, variance는 주의)
          avg(), variance(), stddev()은

          전체 개수가 아니라 null을 제외한 값들의 평균, 분산, 표준편차값이 된다.

  ex.) 100개 중 60개만 값이 있다면 (60개의 합계)/60  이다.  
              => 전체 데이터에 대한 평균이 아니라 값이 있는 것들의 평균이 되는 것

              =>만약 NULL 값도 포함하고 싶다면, avg(ifnull(컬럼, 0)) -- null을 0으로 하고.

-- null 제외 35개의 평균 0.222857
select avg(comm_pct)
from emp;

-- 전체 평균 0.072897
select avg(ifnull(comm_pct, 0))
from emp;

 

-  null인 값 세기 : null을 세는 함수는 없다. => 아래 코드로 조회한다.

-- 커미션 비율(comm_pct)이 없는 직원의 수를 조회 
-- : null을 세는 함수는 없다. => 아래 코드로 조회한다.

--1
select count(*) - count(comm_pct) 
from emp;

-- 2
select count(ifnull(comm_pct,1))
from emp
where comm_pct is null;

 

기타 활용 EX.

-- TODO: 가장 긴 이름(emp_name)이 몇글자 인지 조회.
select max(char_length(emp_name)) "가장긴이름글자수", min(char_length(emp_name)), sum(char_length(emp_name)) "모든 이름 글자수 합계"
from emp;

2. group by 절

- 특정 컬럼(들)의 값별로 행들을 나누어 집계할 때 기준컬럼을 지정하는 구문.
- 예) 업무별 급여평균.    부서-업무별 급여 합계.    성별 나이평균

- 구문: group by 컬럼명 [, 컬럼명]
   - 컬럼: 범주형 컬럼을 사용 - 부서별 급여 평균, 성별 급여 합계
- select의 where 절 다음에 기술한다.

- select 절에는 group by 에서 선언한 컬럼들만 집계함수와 같이 올 수 있다.

 

- 아래 코드처럼 어떤 분류로 그룹화하였는 지 알기위해 그룹화한 컬럼도 조회해준다.

-- 입사연도 별 직원들의 급여 평균.
select  year(hire_date) "입사년도",
        round(avg(salary), 2) "평균급여"
from    emp
group by year(hire_date)  -- 입사년도 별
order by 1; -- 입사년도 별 오름차순

 

- N차 분류 가능. 

  쉼표 단위로 계속 쓰면 된다.

select   dept_name, job, count(*) "직원수"
from   emp
where  dept_name in ('Sales', 'Purchasing')
group by dept_name, job
order by 1;

 

- 범주형을 범위로 설정할 수 있다. => 'CASE문 사용'

-- 급여(salary) 범위별 직원수를 출력. 급여 범위는 10000 미만,  10000이상 두 범주.

select case when salary < 10000 then '$10000미만' else '$10000이상' end "급여 범위",
            count(*) "직원수"
from   emp
group by case when salary < 10000 then '$10000미만' else '$10000이상' end;

 

- 집계함수 결과에 따른 정렬. 업무별 직원수가 많은 것( COUNT(*) )부터 정렬.

-- 업무별(job) 직원수를 조회. 직원수가 많은 것부터 정렬.
select  job, 
        count(*)
from emp 
group by job
order by 2 desc;

 

** 기타 EX. 

-- 1 부서별(dept_name) 직원수 조회하는데 부서명(dept_name)이 null인 것은 제외하고 조회.

select   dept_name, 
         count(*) "직원수"
from emp
where dept_name is not null
group by dept_name
order by "직원수";


-- 2 급여 범위별 직원수를 출력. 
-- 급여 범위는 5000 미만, 5000이상 10000 미만, 10000이상 20000미만, 20000이상. 범위 오름차순.

select  case when salary < 5000 then '5000미만'
             when salary >= 5000 and salary < 10000 then '5000~10000'
             when salary between 10000 and 19999 then '10000~20000'
             else '20000이상'
        end "등급", 
        count(*) "등급별 직원수"
from emp
group by 1
order by case when salary < 5000 then 1
             when salary >= 5000 and salary < 10000 then 2
             when salary between 10000 and 19999 then 3
             else 4
        end;

[조회 구문 실행 처리 순서]

select 컬럼 정의
from 테이블 정의
where 설정 조건에 따라 행을
group by 어떻게 그룹 묶을 것 [with rullup]
having 집계 처리 결과에 관한 조건에 따라 행을
order by 어떻게 정렬할 것인가?
=> 실행되는 순서가 있다. from절 where절 group절 having절 select절 order절
- 조회를 하고 그룹으로 나눈다고 생각하며 안된다.
- emp 테이블을 들고와(from)  -> 기준조건(기준 컬럼 or 범주 조건)대로 그룹으로 나누고(group by)

  - > 그룹별 값 조건 비교해서 맞는 행 남기고(having) -> select에 작상한 컬럼 속성에 대한 열을 조회하는 것.

3. having 절

- 집계결과에 대한 행 제약 조건
- group by 다음, order by 전에 온다.
- 구문
    having 제약조건 

     (연산자는 where절의 연산자 를 사용한다. 피연산자는 집계함수(의 결과) )

-- 직원수가 10 이상인 부서의 부서명(dept_name)과 직원수를 조회
select dept_name, count(*) -- 5. 조건 만족 열에 대해 조회 처리
from emp -- 1 테이블 찾고
group by dept_name -- 3 그룹 묶고
having count(*) >= 10; -- 4 조건 맞는 집계 결과 true  열 찾고

-- 평균 급여가(salary) $5000 이상인 부서의 이름(dept_name)과 직원수를 조회
-- 조건에 들어가는 컬럼이 꼭 select에 들어가는 게 아님.
select dept_name, count(*) "직원수"
from emp
group by dept_name
having avg(salary)>= 5000
order by 1;

 

3.1 with rollup

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

    ex) 합계면 분류 전 전체 행 합계. 평균이면 분류 전 전체 행 평균.
 - N차라면? 대분류의 집계 (소분류 안 했을 때처럼),  총 집계 (그룹바이 안 했을 때 처럼)

 

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

: group by  컬럼명 with rollup 으로 집계했을 때

  이게 해당 그룹으로 묶인 처리 결과 행인지, 안 묶인 처리 결과 행인지 알아야 한다.

 ->  grouping(컬럼명)의 컬럼이 집계시

      값들을 그룹으로 나누는데 사용되었으면 0, 사용되지 않았으면 1을 반환
       1이 반환 된 경우는 그 행의 결과는 총계이거나 중간소계임을 말한다.
- if 문과 함께 써서

grouping(dept_name)이 1(True)이면 안 묶인 처리 결과행이므로  - >  '총계' 가 출력되게

                                       0 (False)이면 그룹으로 묶인 처리 결과행이므로 - > 기존 dept_name 컬럼의 값 되게

-- EMP 테이블에서 부서(dept)별 급여(salary)의 평균과 평균의 총계도 같이나오도록 조회.
select if(grouping(dept_name), '총계', dept_name) , avg(salary) "평균급여" 
from emp
group by dept_name with rollup -- group by 없는 처럼. select avg(salary) from emp; 과 같이 전체 행에 대한 급여 평균이 함께 출력 되는 것.
order by 1;

- N 차 분류일 때

   소분류를 빼고 대분류를 했을 때 집계. 대분류까지빼고 그룹바이 안했을 때 처럼 총 집계.

select if (grouping(dept_name),'총계', dept_name) "dept_name", 
       if (grouping(job), '소계', job) "job",
       sum(salary) "급여합계",
       count(*) "직원수"
from emp
group by dept_name, job with rollup;

 

 

- grouping(컬럼1, 컬럼2, 컬럼3) 과 같이 여러개 컬럼을 지정한 경우

     (  2 번 예제 처럼 풀면 되서 이 방법 알아만 두기  )
   집계에 모든 세개의 컬럼이 다 사용되었으면 0
   앞의 두개만 사용되었으면 1
   앞의 한개만 사용되었으면 3
   세개 다 사용되지 않았으면 7
    
    컬럼1      컬럼2       컬럼3
     2**2  +  2**1    +  2**0      각각 참여하면 0, 참여 안하면 1을 곱해서 더한다.
     4*참여여부 + 2*참여여부 +1*참여여부(0,1)

    (dept_name이 1번 컬럼  2**1 =2) * 0 + (job이 2번 컬럼 2**0 = 1) * 0 = 0  ==> dept_name, job 집계
    (dept_name이 1번 컬럼  2**1 =2) * 0 + (job이 2번 컬럼 2**0 = 1) * 1 = 1  => 소집계
    (dept_name이 1번 컬럼  2**1 =2) * 1 + (job이 2번 컬럼 2**0 = 1) * 1 = 3  => 총계