SQL 4. 그룹 함수를 사용한 데이터 집계
관련링크
본문
4-1. 그룹 함수
그룹 함수는 행 집합에 작용하여 그룹 당 하나의 결과를 생성한다.
단일 행 함수와 달리 그룹 함수는 행 집합에 작용하여 그룹 당 하나의 결과를 생성한다.
이 때 집합은 전체 테이블 또는 그룹화 된 테이블이다.
그룹 함수 종류
각 함수는 인수를 사용한다.
다음 표는 구문에서 사용 가능한 옵션을 설명합니다.
AVG( [DISTINCT | ALL] n) n의 평균 값이며 널 값은 무시 한다.
COUNT( {* | [DISTINCT | ALL] expr} ) expr이 널이 아닌 행의 수입니다. *를 사용하면 중복 행 및 널이 있는 행을 포함하여 선택한 모든 행을 센다.
MAX( {* | [DISTINCT | ALL] expr} ) expr의 최대값이며 널 값은 무시한다.
MIN( {* | [DISTINCT | ALL] expr} ) expr의 최소값이며 널 값은 무시한다.
STDDEV( {* | [DISTINCT | ALL] expr} ) n의 표준 편차이며 널 값은 무시한다.
SUM( {* | [DISTINCT | ALL] expr} ) n의 합계이며 널 값은 무시한다.
VARIANCE( {* | [DISTINCT | ALL] expr} ) n의 분산이며 널 값은 무시한다.
그룹 함수 구문
SELECT [column,] group function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY cloumn];
그룹 함수 사용 지침
․ DISTINCT 를 지정하면 함수는 중복되지 않는 값만 고려하고 ALL을 지정하면 중복 값을 포함한 모든 값을 고려한다.
기본갑은 ALL이므로 지정할 필요가 없습니다.
․ expr 인수를 가진 함수의 데이터 유형은 CHAR, VARCHAR2, NUMBER또는 DATE가 될 수 있다.
․ 모든 그룹 함수는 널 값을 무시한다.
널 값을 특정 값으로 치환하려면 NVL, NVL2 또는 COALESCE함수를 사용한다.
① AVG및 SUM함수 사용
숫자 데이터에 AVG 및 SUM을 사용할 수 있다.
예제)
오라클 디비 예제 : SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
☞설명)
숫자 데이터를 저장하는 열에 대해 AVG,SUM,MIN 및 MAX함수를 사용할 수 있다.
예제는 모든 영업사원에 대한 월급 평균액, 최고액, 최저액 및 총액을 표시한다.
MSSQL 디비 예제 :
SELECT AVG(job_lvl), MAX(job_lvl), MIN(job_lvl), SUM(job_lvl) FROM employee
WHERE emp_id LIKE '%PS%';
② MIN및 MAX함수 사용
모든 데이터 유형에 대해 MIN 및 MAX를 사용할 수 있습니다.
예제)
오라클 디비 예제 : SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
☞설명)
모든 데이터 유형에 대해 MAX 및 MIN함수를 사용할 수 있다.
슬라이드 예제는 최근에 입사한 사원과 가장 오래전에 입사한 사원을 표시한다.
MSSQL 디비 예제 :
SELECT MIN(hire_date), MAX(hire_date) FROM employee;
오라클 디비 예제 :
SELECT MIN(last_name), MAX(last_name)
FROM employees;
모든 사원 이름을 영문자순으로 나열했을 때 맨 처음 및 맨 마지막에 해당하는 사원이름을 표시한다.
참고 : AVG,SUM,VARIANCE 및 STDDEV함수는 숫자 데이터 유형에만 사용할 수 있다.
MSSQL 디비 예제 :
SELECT MIN(price), MAX(price)
FROM titles;
③-1 COUNT함수 사용
COUNT( * )는 테이블의 행 수를 반환한다.
오라클 디비 예제 : SELECT COUNT( * )
FROM employees
WHERE department_id = 50;
예제는 부서 50의 사원 수를 표시합니다.
MSSQL 디비 예제 :
SELECT COUNT( * ) FROM employee
WHERE pub_id=0877;
SELECT COUNT( * ) FROM titles
WHERE type='popular_comp'
COUNT함수
COUNT함수에는 다음 세 가지 형식이 있습니다.
․ COUNT( * )
․ COUNT(expr)
․ COUNT(DISTINCT expr)
COUNT( * )는 열에 널 값이 있는 행 및 중복 행을 포함하여 테이블에서 SELECT문의 조건을 만족하는 행 수를 반환한다.
SELECT문에 WHERE절이 포함된 경우 COUNT( * )는 WHERE절의 조건을 만족하는 행수를 반환한다.
반대로 COUNT(expr)은 expr로 식별되는 열에서 널이 아닌 행수를 반환한다.
COUNT(DISTINCT expr)은 expr로 식별되는 열에서 중복되지 않는 널이 아닌 값의 수를 반환한다.
③-2 COUNT함수 사용
․ COUNT(expr)은 expr에 대해 널이 아닌 값을 가진 행수를 반환한다.
․ EMPLOYEES테이블에서 널 값을 제외한 부서 값의 수를 표시한다.
오라클 디비 예제 :
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;
COUNT함수
예제는 부서 80에서 커미션을 받은 사원의 수를 표시합니다.
MSSQL 디비 예제 :
SELECT count(price) FROM titles
WHERE type='popular_comp'
오라클 디비 예제 :
SELECT COUNT(department_id)
FROM employees;
EMPLOYEES 테이블에서 부서 값의 수를 표시합니다.
MSSQL 디비 예제 :
SELECT count(pub_id) FROM employee
④ DISTINCT키워드 사용
․ COUNT(DISTINCT expr)은 expr에 대해 중복되지 않는 널이 아닌 값의 수를 반환합니다.
․ EMPLOYEES테이블에서 중복되지 않는 부서 값이 수를 표시합니다.
오라클 디비 예제 :
SELECT COUNT(DISTINCT department_id)
FROM employees
MSSQL 디비 예제 :
SELECT COUNT(DISTINCT pub_id) FROM employee
4-2. 그룹 함수 및 널 값
그룹 함수는 해당 열의 널 값을 무시한다.
오라클 디비 예제 :
SELECT AVG(commission_pct)
FROM employees;
그룹 함수 및 널 값
모든 그룹 함수는 해당 열의 널 값을 무시한다.
예제에서 평균은 테이블의 COMMISSION_PCT 열에 유효한 값이 저장된 행만으로 계산되며, 모든 사원이 받는 커미션
총액을 커미션을 받는 사원 수(4)로 나눈 값입니다.
MSSQL 디비 예제 :
SELECT AVG(price) FROM titles ---> SELECT SUM(price) / COUNT(price) FROM titles -->COUNT(*)이 아님
① 그룹 함수에 NVL함수 사용 (MSSQL에서는 ISNULL함수를 사용)
NVL함수는 그룹 함숨가 널 값을 포함하도록 강제로 지정한다.
오라클 디비 예제 :
SELECT AVG(NVL(commission_pct,0))
FROM employees;
NVL함수는 그룹 함수가 널 값을 포함하도록 강제로 지정한다.
예제에서 평균은 COMMISSION_PCT열의 널 값 저장 여부에 관계없이 테이블의 모든 행을 기반으로 계산되며, 모든 사원
이 받는 커미션 총액을 회사의 전체 사원 수(20)로 나눈 값이다.
MSSQL 디비 예제 :
SELECT AVG( ISNULL(price,0) ) FROM titles --> SELECT SUM(price)/ COUNT( * ) FROM titles
4-3. 데이터 그룹 생성
지금까지 모든 그룹 함수는 테이블을 하나의 대형 정보 그룹으로 취급한다.
테이블 정보를 더 작은 그룹으로 나누어야 할 경우 GROUP BY절을 사용하면 된다.
데이터 그룹 생성 : GROUP BY절 구문
SELECT column, group_function(cloumn)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
GROUP BY 절을 사용하여 테이블 행을 더 작은 그룹으로 나눈다.
GROUP BY 절을 사용하여 테이블 행을 그룹으로 나눈 후 그룹 함수를 사용하여 각 그룹에 대한 요약 정보를 반환할 수있다.
group_by_expression 행 그룹화 기준을 결정하는 값을 가진 열을 지정 합니다.
지침
․ SELECT절에 그룹 함수를 포함시킨 경우 GROUP BY절에 개별 열을 지정하지 않으면 개별 결과를 선택할 수 없으며,
GROUP BY절에 열 목록을 포함시키지 않으면 오류 메시지가 나타난다.
․ WHERE절을 사용하면 그룹으로 나누기 전에 행을 제외시킬 수 있다.
․ GROUP BY 절에 열을 포함시켜야 한다.
․ GROUP BY 절에는 열 별칭을 사용할 수 없다.
예제) GROUP BY절 사용
오라클 디비 예제 :
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
GROUP BY절을 사용할 때 SELECT목록의 열 중 그룹함수가 아닌 열은 모두 GROUP BY절에 포함되어야 한다.
예제는 각 무서의 부서 번호 및 평균 급여를 표시한다.
GROUP BY절을 포함하는 SELECT문은 다음 방식으로 평가된다.
․ SELECT 절은 검색할 열을 지정합니다.
- EMPLOYEES테이블의 부서 번호 열
- GROUP BY절 에서 지정한 그룹 내의 모든 급여에 대한 평균
․ FROM절은 데이터베이스가 액세스할 테이블(EMPLOYEES테이블)을 지정한다.
․ WHERE절은 검색할 행을 지정하는데 예제에는 WHERE절이 없으므로 기본적으로 모든 행을 검색한다.
․ GROUP BY절은 행 그룹화 방식을 지정한다.
행은 부서번호에 따라 그룹화 되므로 급여 열에 적용되는 AVG함수는 각 무서의 평균급여를 계산한다.
MSSQL 디비 예제 :
SELECT pub_id, AVG(job_lvl) FROM employee
GROUP BY pub_id
예제) GROUP BY절 사용
GROUP BY열을 SELECT목록에 포함시키지 않아도 된다.
오라클 디비 예제 : SELECT AVG(salary)
FROM employees
GROUP BY department_id;
GROUP BY열을 SELECT절에 포함시키지 않아도 된다.
예를 들어, SELECT문은 해당 부서번호는 표시하지 않고 각 부서의 평균급여만 표시한다.
그러나 부서번호가 없으면 그 결과는 의미가 없다.
MSSQL 디비 예제 :
SELECT AVG(job_lvl) FROM employee
GROUP BY pub_id
예제)
ORDER BY절에 그룹함수를 사용할 수 있다.
오라클 디비 예제 : SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary);
MSSQL 디비 예제 :
SELECT pub_id, AVG(job_lvl) FROM employee
GROUP BY pub_id
ORDER BY AVG(job_lvl);
① 여러 열을 기준으로 그룹화
그룹 내 그룹에 대한 결과를 확인해야 할 경우가 있다.
각 부서 내에서 각 업무에 대해 지급되는 급여 총액을 표시하는 보고서를 보여주려 한다면 EMPLOYEES테이블은 먼저 부
서 번호에 따라 그룹화된 후 그룹 내에서 다시 업무에 따라 그룹화하면 된다.
예를 들어, 부서 50에서 일하는 네 명의 사무원(stock clerk)이 하나의 그룹으로 묶이고 이 그룹 내 모든 사무원에 대해
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
WHERE AVG(salary) > 8000
*
ERROR at line 3 :
ORA-00934 : group function is not allowed here
하나의 결과(총급여)가 생성된다.
예제) 여러 열에 GROUP BY절 사용
오라클 디비 예제 : SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;
☞설명)
하나 이상의 GROUP BY열을 나열하여 그룹 및 하위 그룹에 대한 요약 결과를 반환할 수 있으며, GROUP BY절에서의 열
순서에 따라 결과의 기본 정렬순서를 결정할 수 있다.
GROUP BY절을 포함하는 SELECT문은 다음과 같이 평가된다.
․ SELECT절은 검색할 열을 지정한다.
- EMPLOYEES테이블의 부서 번호
- EMPLOYEES테이블의 업무ID
- GROUP BY절에서 지정한 그룹 내의 모든 급여 합계
․ FROM절은 데이터베이스가 액세스할 테이블(EMPLOYEES테이블)을 지정한다.
․ GROUP BY절은 행 그룹화 방식을 지정한다.
- 먼저 부서 번호를 기준으로 행을 그룹화한다.
- 그런 다음 해당 부서 번호 그룹 내에서 업무ID를 기준으로 다시 그룹화한다.
따라서 SUM함수는 각 부서 번호 그룹 내에 있는 모든 업무 ID의 급여 열에 적용된다.
MSSQL 디비 예제 :
SELECT pub_id, job_id, SUM(job_lvl) FROM employee
GROUP BY pub_id, job_id
②-1. 그룹 함수를 사용한 잘못된 질의
예제)
SELECT목록의 열 또는 표현식 중 그룹함수가 아닌 것은 GROUP BY절에 포함시켜야 한다.
SELECT department_id, COUNT(last_name)
FROM employees;
SELECT department_id, COUNT(last_name)
*
ERROR at line 1:
ORA-00937 : not a single-group group function
☞설명)
GROUP BY절에 열이 없습니다.
개별항목(DEPARTMENT_ID)및 그룹 함수(COUNT)를 동일한 SELECT문에 함께 사용할 때는 개별항복(DEPARTMENT_ID)을
지정하는 GROUP BY절을 항상 포함시켜야 한다.
GROUP BY절이 없으면 “not a single-group group function" 이라는 오류 메시지가 나타나며 잘못된 열에 별포(*)가 표
시된다.
GROUP BY절을 추가하여 슬라이드에 나타난 오류를 수정할 수 있다.
SELECT department_id, count(last_name)
FROM employees
GROUP BY department_id;
SELECT목록의 열 또는 표현식 중 집계 함수가 아닌 것은 GROUP BY절에 포함시켜야 한다.
②-2. 그룹 함수를 사용한 잘못된 질의
․ WHERE절을 사용하여 그룹을 제한할 수 없다.
․ HAVING절을 사용하여 그룹을 제한할 수 있다.
․ WHERE절에서 그룹함수를 사용할 수 없다.
☞설명)
SELECT문은 WHERE절을 사용하여 평균급여가 $8,000를 넘는 부서의 평균급여를 표시하도록 제한하므로 오류가
발생한다.
HAVING절로 그룹을 제한하여 오류를 수정할 수 있다.
SELECT department_id, AVG(salary)
FROM employees
HAVING AVG(salary) > 8000
4-4. 그룹 결과 제외 : HAVING절
HAVING절을 사용하여 그룹을 제한한다.
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY cloumn];
HAVING절을 사용하여 표시할 그룹을 지정할 수 있으므로 집계정보를 기준으로 그룹 제한을 강화한다.
구문 설명 :
group_condition 반환되는 행 그룹을 지정한 조건이 TRUE인 그룹으로 제한된다.
HAVING절을 사용하면 Oracle server가 다음 단계를 수행한다.
1. 행이 그룹화 한다.
2. 그룹 함수가 그룹에 적용된다.
3. HAVING절의 조건에 일치하는 그룹이 표시된다.
HAVING절이 GROUP BY절 앞에 올 수는 있지만 GROUP BY절을 먼저 두는 것이 더 논리적이므로 이를 권장한다.
그룹이 형성되고 그룹 함수가 계산된 후 SELECT목록의 그룹에 HAVING절이 적용된다.
예제) HAVING절 사용
오라클 디비 예제 :
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
셀렉트해라 department_id와 salary가 가장 큰수를
로부터 employees
묶어라 department_id 를
묶었으면 가장큰수가 10000 보다 큰것만
☞설명)
예제는 최고급여가 $10,000를 넘는 부서의 부서 번호 및 최고 급여를 표시한다.
SELECT목록에 그룹함수를 사용하지 않고도 GROUP BY절을 사용할 수 있다.
그룹함수의 결과를 기반으로 행을 제한할 경우에는 GROUP BY절 및 HAVING절이 모두 있어야 한다.
MSSQL 디비 예제 :
SELECT pub_id, MAX(job_lvl)
FROM employee
GROUP BY pub_id
HAVING MAX(job_lvl) > 160
예제) HAVING절 사용
오라클 디비 예제 :
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING max(salary) > 10000;
☞설명)
예제는 최고급여가 $10,000를 넘는 부서의 부서 번호와 평균 급여를 표시한다.
MSSQL 디비 예제 :
SELECT pub_id, AVG(job_lvl)
FROM employee
GROUP BY pub_id
HAVING MAX(job_lvl) > 160
예제) HAVING절 사용
오라클 디비 예제 : SELECT job_id, SUM(salary) PAYROLL FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
☞설명)
예제는 월급총액이 $13,000를 넘는 각 업무에 대해 업무ID와 월급총액을 표시하되, 영업사원을 제외시킨 후 월급 총액에
따라 목록을 정렬한다.
MSSQL 디비 예제 :
select title_id ,avg(qty), max(qty), min(qty), (qty) from sales group by title_id
select avg(qty), max(qty), min(qty), sum(qty) from sales group by title_id
select title_id , ord_date, avg(qty), max(qty), min(qty), sum(qty) from sales group by title_id , ord_date
order by title_id
select title_id , ord_date, avg(qty), max(qty), min(qty), sum(qty)
from sales
where title_id = 'bu1032' and ord_date = '1994-09-14'
group by title_id , ord_date
또는
select title_id , ord_date, avg(qty), max(qty), min(qty), sum(qty)
from sales where ord_date = '1994-09-14'
group by title_id , ord_date
having title_id = 'bu1032'
또는
select title_id , ord_date, avg(qty), max(qty), min(qty), sum(qty)
from sales group by title_id , ord_date
having title_id = 'bu1032' and ord_date = '1994-09-14'
select * from discounts
select count(*) from discounts
select lowqty from discounts
select count(lowqty) from discounts
select avg(lowqty) from discounts
select sum(lowqty)/count(*) from discounts -- 평균에 null 을 포함
또는
select avg(isnull(lowqty, 0)) from discounts
select count(*) from discounts where stor_id = 8042
select count(title_id) from sales
select count(distinct(title_id)) from sales
select title_id , avg(qty), max(qty), min(qty), sum(qty)
from sales
where avg(qty) >30 ---안됨. 즉, where절엔 그룹함수를 사용할 수 없다
group by title_id
=>
select title_id , avg(qty), max(qty), min(qty), sum(qty)
from sales
group by title_id
having avg(qty)>30
4-5. 그룹 함수 중첩(MSSQL에서는 그룹 함수 중첩을 사용할 수 없다.)
최고 평균 급여를 표시한다.
예제)
오라클 디비 예제 :
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
☞설명)
그룹함수는 두 번까지 중첩될 수 있다. 예제는 최고 평균급여를 표시한다.
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
MSSQL 디비 예제 :
select max(avg(qty))
from sales
group by title_id -- MSSQL에서는 그룹함수의 중첩을 지원하지 않는다.
select top 1 avg(qty) 최대
from sales
group by title_id
order by avg(qty) desc
요약
이 단원에서는 다음과 같은 작업을 수행하는 방법에 대해 배웠습니다.
․ 그룹함수 COUNT,MAX,MIN,AVG사용
․ GROUP BY절을 사용하는 질의 작성
․ HAVING절을 사용하는 질의 작성