집계 함수
여러 행에 대해 하나의 결과를 출력하는 그룹 함수를 이용하여 여러가지집계연산을수행
COUNT()
열의 행 개수를 구하는 함수
--salary 컬럼의 행의 개수를 모두 추출(null이 아닌거)SELECT COUNT(salary) FROM employees;
--manager_id 컬럼의 행의 개수를 모두 추출(null이 아닌거)SELECT COUNT(manager_id) FROM employees;
--commission_pct 행의 값의 개수를 모두 추출(null이 아닌거)SELECT COUNT(commission_pct) FROM employees;
--모든 행의 개수를 추출SELECT COUNT(*) FROM employees;
SUM() / AVG()
열의합계를구하는SUM()함수,열의평균을구하는AVG()함수
--salary의 합계, 평균SELECT SUM(salary), AVG(salary) FROM employees;
--salary의 평균SELECT SUM(salary) / COUNT(salary) FROM employees;
-- salary 값을 first_name의 순서대로 더하는 것으로, 각 행에서 이전 행까지의 모든 값을 누적하여 합산SELECT first_name, salary, SUM(salary) OVER (ORDER BY first_name)FROM employees;
MIN()/MAX()
열의최솟값을구하는MIN()함수,열의최댓값을구하는MAX()함수
--first_name과 salary를 그룹으로 묶고, 그룹별로 first_name과 salary를 first_name순으로 정렬하여 출력SELECT first_name, salary FROM employees GROUP BY first_name, salary ORDER BY first_name;
--salary의 최솟값과 최댓값을 출력SELECT MIN(salary), MAX(salary) FROM employees;
--first_name의 최솟값과 최댓값을 출력(이름순의 최솟값과 최댓값)SELECT MIN(first_name), MAX(first_name) FROM employees;
STDDEV()/VARIANCE()
표준편차를구하는STDDEV()함수,분산을구하는VARIANCE()함수
--salary의 표준편차, 분산을 출력SELECT STDDEV(salary), VARIANCE(salary) FROM employees;
--department_id가 50이고, first_name, salary, salary의 분산을 first_name순으로 정렬하여 출력SELECT first_name, salary, STDDEV(salary) OVER (ORDER BY first_name)FROM employeesWHERE department_id = 50;
GROUP BY
지정한열의데이터값을기준으로그룹화하여집계함수적용
GROUP BY 동작 순서
- 테이블에서 WHERE 조건식에 맞는 데이터 값만 구분
- 지정한 열 기준으로 같은 데이터 값으로 그룹화
- 지정한 열들의 그룹화된 집계 결과 출력
GROUP BY 절 특징
- WHERE 절은 그룹화 되기 전에 조건식 적용
- GROUP BY 절 사용시 SELECT 절에 지정된 기준 열을 지정
- SELECT 절에 그룹 함수 없이도 GROUP BY 절 사용 가능
--job_id로 그룹을 묶고, 그룹별로 job_id와 salary의 합계와 평균을 출력SELECT job_id, SUM(salary), AVG(salary)FROM employeesGROUP BY job_id;
--job_id로 그룹을 묶고,department_id가 50인 것만 ,그룹별로 job_id와 salary의 합계와 평균을 출력SELECT job_id, SUM(salary), AVG(salary)FROM employeesWHERE department_id = 50GROUP BY job_id;
--department_id로 묶고, 그룹별로 department_id, salary의 최솟값과, 최댓값 출력SELECT department_id, MIN(salary), MAX(salary)FROM employeesGROUP BY department_id;
--department_id로 묶고, hire_date가 2007년 01월 01일 이후인 사람만, 그룹별로 department_id, salary의 최솟값과, 최댓값 출력SELECT department_id, MIN(salary), MAX(salary)FROM employeesWHERE hire_date > '20070101'GROUP BY department_id;
--country_id로 묶고, country_id순으로 정렬하고, 그룹별로 country_id와 country_id컬럼의 열의 개수를 출력SELECT country_id, COUNT(country_id)FROM locationsGROUP BY country_idORDER BY country_id;
다중 GROUP BY 절
--job_id, department_id로 묶고, department_id가 50~100 사이만, job_id를 기준으로 정렬하고, 그룹별로 job_id, department_id, salary의 합계, 평균 출력SELECT job_id, department_id, SUM(salary), AVG(salary)FROM employeesWHERE department_id BETWEEN 50 AND 100GROUP BY job_id, department_idORDER BY job_id;
SELECT department_id, manager_id, SUM(salary), AVG(salary)FROM employeesWHERE department_id = 50GROUP BY department_id, manager_idORDER BY manager_id;
--manager_id, department_id, job_id로 묶고, manager_id가 100또는 101인 것만, manager_id와 department_id 기준으로 정렬하고--그룹별로 manager_id, department_id, job_id, salary의 합계, 최솟값, 최댓값 출력SELECT manager_id, department_id, job_id, SUM(salary), MIN(salary), MAX(salary)FROM employeesWHERE manager_id IN (100, 101)GROUP BY manager_id, department_id, job_idORDER BY manager_id, department_id;
HAVING 절
WHERE절에서는그룹함수를사용할수없음
그룹화된 집계 결과에 조건식을 적용할 때 HAVING 절 사용
--job_id로 묶고, salary의 평균이 10000초과인 것만, 그룹별로 job_id, salary 합계, 평균 출력SELECT job_id, SUM(salary), AVG(salary)FROM employeesGROUP BY job_idHAVING AVG(salary) > 10000;
--department_id로 묶고, salary의 최댓값이 7000초과인 것만, 그룹별로 department_id, salary의 최솟값, 최댓값 출력SELECT department_id, MIN(salary), MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary) > 7000;
--country_id로 묶고, country_id의 개수가 2초과인 것만, country_id기준으로 정렬하고, --그룹별로country_id, country_id의 개수 출력SELECT country_id, COUNT(country_id)FROM locationsGROUP BY country_idHAVING COUNT(country_id) > 2ORDER BY country_id;
--job_id, department_id로 묶고, department_id가 50~100사이 것만, salary평균이 9000이상인 것만--job_id를 기준으로 정렬해서 그룹별로 job_id, department_id, salary의 합계, 평균 출력SELECT job_id, department_id, SUM(salary), AVG(salary)FROM employeesWHERE department_id BETWEEN 50 AND 100GROUP BY job_id, department_idHAVING AVG(salary) > 9000ORDER BY job_id;
--manager_id, department_id, job_id로 묶고, manager_id가 100 또는 101인 것만--salary가 10000과 40000 사이의 것만, manager_id, department_id 기준으로 정렬--그룹별로 manager_id, department_id, job_id, salary의 합계, 최솟값, 최댓값을 출력SELECT manager_id, department_id, job_id, SUM(salary), MIN(salary), MAX(salary)FROM employeesWHERE manager_id IN (100, 101)GROUP BY manager_id, department_id, job_idHAVING SUM(salary) BETWEEN 10000 AND 40000ORDER BY manager_id, department_id;
연습 문제
--employees 테이블에서 salary가 8000이상인 직원의 수를 조회SELECT COUNT(salary) FROM employees WHERE salary > 8000;
--employees 테이블에서 hire_date가 2007년 1월 1일 이후인 직원의 수를 조회SELECT COUNT(hire_date) FROM employees WHERE hire_Date > '20070101';
--jobs 테이블에서 max_salary 값의 합계와 평균을 조회SELECT MAX(max_salary), AVG(max_salary) FROM jobs;
--employees 테이블에서 job_id가 ‘IT_PROG’인 직원의 salary 합계와 평균을 조회SELECT SUM(salary), AVG(salary)FROM employeesWHERE job_id = 'IT_PROG';
--employees 테이블에서 department_id가 50과 80 사이인 직원의 first_name, salary,--그리고 commission_pct의 평균값을 first_name 정렬 기준으로 조회 (null 값은 0으로 출력)SELECT first_name, salary, AVG(NVL(commission_pct, 0)) OVER (ORDER BY first_name)FROM employeesWHERE department_id BETWEEN 50 AND 100;
--jobs 테이블에서 max_salary 값의 최솟값과 max_salary 값의 최댓값을 조회SELECT min(max_salary), MAX(max_salary) FROM jobs;
--jobs 테이블에서 job_title이 ‘Programmer’인 직업의 max_salary 값의 최솟값과 max_salary 값의 최댓값을 조회SELECT MIN(max_salary), MAX(max_salary)FROM jobsWHERE job_title = 'Programmer';
--employees 테이블에서 department_id가 50인 데이터의 hire_date 최소값과 최댓값 조회SELECT MIN(hire_date), MAX(hire_date)FROM employeesWHERE department_id = 50;
--employees 테이블에서 department_id가 100인 데이터의 first_name, salary,--그리고 salary의 분산값을 hire_date 정렬 기준으로 조회SELECT first_name, salary, VARIANCE(salary) OVER (ORDER BY hire_date)FROM employeesWHERE department_id = 100;
--employees 테이블에서 hire_date 값이 --2004년 1월 1일부터 2006년 12월 31일 사이의 데이터를 job_id 기준으로--그룹화한 뒤에 job_id와 salary 최솟값과 최대값을 조회SELECT job_id, MIN(salary), MAX(salary)FROM employeesWHERE hire_date BETWEEN '20040101' AND '20061231'GROUP BY job_id;
--employees 테이블에서 department_id 가 50과 80인 데이터를--department_id와 job_id 기준으로 그룹화한 뒤에 department_id와--job_id, salary 합계, 최솟값, 최대값을 job_id 기준으로 정렬하여 조회SELECT department_id, job_id, SUM(salary), MIN(salary), MAX(salary)FROM employeesWHERE department_id IN (50, 80)GROUP BY department_id, job_idORDER BY job_id;
--employees 테이블에서 department_id와 job_id 기준으로 그룹화한--뒤에 salary 평균값이 12000 이상인 데이터만 department_id와--job_id, salary 최솟값, 최대값, 평균을 department_id 기준으로 정렬하여 조회SELECT department_id, job_id, MIN(salary), MAX(salary), AVG(salary)FROM employeesGROUP BY department_id, job_idHAVING AVG(salary) > 12000ORDER BY department_id;