SQL_04(집계함수, group by, having, with rollup, grouping)

/* **************************************************************************
집계(Aggregation) 함수와 GROUP BY, HAVING
************************************************************************** */
use hr;
/* ******************************************************************************************
집계함수, 그룹함수, 다중행 함수
- 인수(argument)는 컬럼.
  - sum(): 전체합계
  - avg(): 평균
  - min(): 최소값
  - max(): 최대값
  - stddev(): 표준편차
  - variance(): 분산
  - count(): 개수
        - 인수: 
            - 컬럼명: null을 제외한 값들의 개수.
            -  *: 총 행수 - null과 관계 없이 센다.
  - count(distinct 컬럼명): 고유값의 개수.
  
- count(*) 를 제외한 모든 집계함수들은 null을 제외하고 집계한다. 
	- (avg, stddev, variance는 주의)
	-avg(), variance(), stddev()은 전체 개수가 아니라 null을 제외한 값들의 평균, 분산, 표준편차값이 된다.=>avg(ifnull(컬럼, 0))
- 문자타입/일시타입: max(), min(), count()에만 사용가능
	- 문자열 컬럼의 max(): 사전식 배열에서 가장 마지막 문자열, min()은 첫번째 문자열. 
	- 일시타입 컬럼은 오래된 값일 수록 작은 값이다.
    
편차 : 값이 평균으로부터 얼마나 떨어져있는지.
평균 : 10, value : '5'
표준편차 : 편차들의 평균
분사 ㅣ 표준편차의 제곱
******************************************************************************************* */

-- EMP 테이블에서 급여(salary)의 총합계, 평균, 최소값, 최대값, 표준편차, 분산, 총직원수를 조회 
select sum(salary),
	   avg(salary),
       min(salary),
       max(salary),
       stddev(salary),
       variance(salary),
       count(*),
       count(emp_id)
from emp;

-- count(컬럼명) 그 컬럼에서 데이터개수(null이 아닌 값들)
select count(comm_pct) from emp;

select avg(comm_pct),  -- null을 뺀 평균, 커미션을 받는 사람들 기준 평균.
	   avg(ifnull(comm_pct, 0)) -- 전체 직원 기준 평균.
from emp;


-- EMP 테이블에서 가장 최근 입사일(hire_date)과 가장 오래된 입사일을 조회
select max(hire_date), 
	   min(hire_date)
from emp;       


-- EMP 테이블의 부서(dept_name) 의 개수를 조회
select count(dept_name)
from emp;

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

 

group by 절 : 특정 컬럼(들)의 값별로 행들을 나누어 집계할 때 기준컬럼을 지정하는 구문
 - 구문 : group by 컬럼명 [, 컬럼명]
- 컬럼 : 범주형 컬럼을 사용
- select의 where 절 다음에 기술
- select 절에는 group by에서 선언한 컬럼들만 집계함수와 같이 올 수 있다.

 

/* **************
group by 절
- 특정 컬럼(들)의 값별로 행들을 나누어 집계할 때 기준컬럼을 지정하는 구문.
	- 예) 업무별 급여평균. 부서-업무별 급여 합계. 성별 나이평균
- 구문: group by 컬럼명 [, 컬럼명]
	- 컬럼: 범주형 컬럼을 사용 - 부서별 급여 평균, 성별 급여 합계
	- select의 where 절 다음에 기술한다.
	- select 절에는 group by 에서 선언한 컬럼들만 집계함수와 같이 올 수 있다.
	
****************/

-- 업무(job)별 급여의 총합계, 평균, 최소값, 최대값, 표준편차, 분산, 직원수를 조회
select job,
	   sum(salary),
	   round(avg(salary), 2),
       min(salary),
       max(salary),
       round(stddev(salary), 2),
       round(variance(salary), 2),
       count(*)
from emp
group by job;

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

-- 부서명(dept_name) 이 'Sales'이거나 'Purchasing' 인 직원들의 업무별 (job) 직원수를 조회
select job,
       count(*) 직원수 -- 3. 집계
from emp -- 0. 테이블 선택.
where dept_name in ('Sales', 'Purchasing') -- 1. 조건이 True인 행을 조회
group by job; -- 2. job 별로 그룹을 나눈다.

-- 부서(dept_name), 업무(job) 별 최대, 평균급여(salary)를 조회.
select dept_name,
	   job,
	   max(salary),
	   avg(salary)
from emp	
group by dept_name, job -- dept_name과 job이 같은 직원들이 같은 그룹으로 묶인다.
order by 1;

-- 급여(salary) 범위별 직원수를 출력. 급여 범위는 10000 미만,  10000이상 두 범주.
select case when salary < 10000 then "10000 미만"
			else "10000 이상" end as "급여 범위",
		count(*) as "직원수"
from emp
group by case when salary < 10000 then "10000 미만"
			else "10000 이상" end ;
            

select case when salary < 10000 then 1
			else 2 end
from emp;

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

 

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

-- 직원수가 10 이상인 부서의 부서명(dept_name)과 직원수를 조회
select dept_name 부서명,
	   count(*) 직원수
from emp
group by dept_name
having count(*) >= 10;       
       

-- 직원수가 10명 이상인 부서의 부서명과 그 부서 직원들의 평균 급여를 조회.
select dept_name 부서명,
       round(avg(salary), 2) as "평균 급여"
from emp
group by dept_name
having count(*) >= 10;

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

grouping(컬럼명) : 컬럼명으로 나뉜 데이터들을 집계한 경우 0 반환, 아닌 경우 1을 반환 -> 1이 반환 된 경우는 그 행의 결과는 총계이거나 중간소계이다.

 

/* ***************************************************************************************
# with rollup : group by 뒤에 붙인다.
 - group by로 묶어 집계할 때 총계나 중간 집계(group by 컬럼이 여러개일경우) 를 계산한다.
 - 구문 : group by 컬럼명[, .. ] with rollup
 - ex) group by job with rollup
 
 
# 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을 곱해서 더한다.

* ***************************************************************************************/

-- EMP 테이블에서 업무(job)별 급여(salary)의 평균과 평균의 총계도 같이나오도록 조회.
select ifnull(job, "총 평균") as "job", -- rollup으로 인해 평균의 총계가 나타난 값의 job이 null값이기에 총 평균으로 바꾼다.
	   round(avg(salary), 2) as "평균급여"
from emp
group by job with rollup;       

-- EMP 테이블에서 부서(dept)별 급여(salary)의 평균과 평균의 총계도 같이나오도록 조회.
select -- dept_name 부서명,
	   if(grouping(dept_name) = 0, dept_name, '총평균') 부서명, -- 결과값에서 1이 나온경우 그 행의 결과가 총계이거나 중간소계이다.
	   round(avg(salary), 2) 평균급여
from emp
group by dept_name with rollup;       

-- EMP 테이블에서 부서(dept_name), 업무(job) 별 salary의 합계와 직원수를 소계와 총계가 나오도록 조회
select dept_name 부서,
	   job 업무,
       grouping(dept_name, job),
	   sum(salary) 급여합계,
	   count(*) 직원수
from emp
group by dept_name, job with rollup;       

select case grouping(dept_name, job) when 0 then concat(ifnull(dept_name, '미배치'), '-', job)
									 when 1 then ifnull(dept_name, '미배치')
                                     when 3 then '총계' end as '구분',
	   sum(salary) 급여합계, 
       count(*) 직원수
from emp
group by dept_name, job with rollup;