집계함수

서현서현·2022년 2월 18일
0

DB, SQL

목록 보기
14/27
post-thumbnail

💫 집계함수

  • 주어진 자료를 특정컬럼을 기준으로 그룹으로 구분하고 각 그룹내에서 합계(SUM), 평균(AVG), 최대값(MAX), 최소값(MIN), 자료의수(COUNT)를 반환하는 함수
  • SELECT절에 집계함수와 일반컬럼(또는수식)이 함께 사용된경우 반드시 GROUP절을 기술해야함
  • SELECT절에 집계함수만 사용한 경우 GROUP BY절 생략
(사용형식)
SELECT 컬럼list
FROM 테이블명
[WHERE 조건]
[GROUP BY 컬럼[,컬럼,..]]
[HAVING 조건]
[ORDER BY 컬럼|컬럼인덱스 [ASC|DESC],...];

- ORDER BY 절에 기술하는 컬럼은 SELECT절에 사용된 컬럼 
	또는 해당 테이블에서 그룹으로 나누기 위해 필요한 컬럼
- 집계함수가 사용되면 다중행결과 반환
- HAVING 조건 : 집계함수에 조건이 부여된 경우 기술

EX1) 사원테이블에 저장된 사원수를 조회

SELECT COUNT(*) FROM HR.EMPLOYEES

EX2) 사원테이블에서 부서별 사원수를 조회

  SELECT DEPARTMENT_ID,
         COUNT(*)
    FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

EX3) 사원테이블에서 부서별 사원수가 10명 이상인 부서 조회

	SELECT DEPARTMENT_ID,
         COUNT(*)
    FROM HR.EMPLOYEES
GROUP BY  DEPARTMENT_ID
  HAVING COUNT(*)>10
ORDER BY 1;

HAVING 말고 WHERE 쓰면 오류!

💫 1) SUM

  • 각 그룹에서 특정 컬럼의 합계를 구하여 반환.
(사용형식)
SUM(expr)

(EX1) 사원테이블에서 각 부서별 급여 합계를 구하시오.

SELECT DEPARTMENT_ID AS 부서,
SUM(SALARY) AS "급여 합계"
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

(EX2) 매입테이블에서 2005년 2월 상품별 매입수량 합계와 매입금액 합계를 구하시오

SELECT BUY_PROD AS 상품코드,
SUM(BUY_QTY) AS "매입수량 합계",
SUM(BUY_QTY*BUY_COST) AS "매입금액 합계"
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050201') AND LAST_DAY('20050201')
GROUP BY BUY_PROD
ORDER BY 1;

(EX3) 2005년도 매입자료에서 월별 상품별 매입수량합계와 매입금액합계를 조회하시오.

SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월,
BUY_PROD AS 상품코드,
SUM(BUY_QTY) AS "매입수량 합계",
SUM(BUY_QTY*BUY_COST) AS "매입금액 합계"
FROM BUYPROD
WHERE EXTRACT(YEAR FROM BUY_DATE) = 2005
GROUP BY EXTRACT(MONTH FROM BUY_DATE), BUY_PROD
ORDER BY 1;

(EX4) 회원테이블에서 성별 마일리지 합계를 구하시오.

SELECT CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1' OR
SUBSTR(MEM_REGNO2,1,1)='3' THEN '남성' ELSE '여성' END AS 구분,
SUM(MEM_MILEAGE) AS "마일리지 합계"
FROM MEMBER
GROUP BY CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1' OR
SUBSTR(MEM_REGNO2,1,1)='3' THEN '남성' ELSE '여성' END

(EX5) 매출테이블에서 2005년 4월 회원별 구매수량합계를 구하시오.

SELECT CART_MEMBER AS 회원,
SUM(CART_QTY) AS 구매수량합계
FROM CART
WHERE SUBSTR(CART_NO,1,6)='200504'
GROUP BY CART_MEMBER

(EX6) 매출테이블에서 2005년 4월 회원별 구매수량합계를 구하고 구매수량 기준 상위 5명의 정보를 출력하시오

서브쿼리 사용!

SELECT A.CART_MEMBER AS 회원번호,
A.SAMT AS 구매수량합계
FROM (SELECT CART_MEMBER,
SUM(CART_QTY) AS SAMT
FROM CART
WHERE CART_NO LIKE '200504%'
GROUP BY CART_MEMBER
ORDER BY 2 DESC) A
WHERE ROWNUM<=5;

(EX7) 2005년 1~3월 상품별 매입금액합계 중 매입금액이 500만원 이상인 상품만 조회하시오.

SELECT BUY_PROD AS 상품코드,
SUM(BUY_QTY*BUY_COST) AS 매입금액합계
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050331')
GROUP BY BUY_PROD
HAVING SUM(BUY_QTY*BUY_COST) >= 5000000
ORDER BY 1;

💫 등수부여(WINDOW 함수) - SELECT 절에서만 사용

  • RANK() OVER, DENSE_RANK() OVER, ROW_NUMBER() OVER 제공
  • 그룹별 등수부여는 RANK() OVER 함수에서 PARTITION BY 절을 사용하여 구현
  • 그룹별 최소 최대값은 KEEP() 함수와 DENSE_RANK() 함수를 사용하여 구현

💫 1) RANK() OVER

  • 일반적인 등수 출력(동일 값에 동일 등수를 부여하고 다음 등수는 같은값의 갯수만큼
    건너 뛴 값 부여(ex : 1,2,2,2,5,6...)
(사용형식)
RANK() OVER(ORDER BY 컬럼명 [ASC|DESC][,컬럼명 [ASC|DESC],...])

(EX1) 사원테이블에서 급여순으로 순위를 부여하시오

Alias는 사원번호, 사원명, 부서코드, 급여, 순위

SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여,
RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM HR.EMPLOYEES;

(EX2) 사원테이블에서 급여순으로 순위를 부여하되 같은 급여이면 입사일이

빠른 사원부터 순위 부여

Alias는 사원번호, 사원명, 부서코드, 급여, 순위
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
HIRE_DATE AS 입사일,
SALARY AS 급여,
RANK() OVER(ORDER BY SALARY DESC) AS 순위1,
RANK() OVER(ORDER BY SALARY DESC, HIRE_DATE ASC) AS 순위2
FROM HR.EMPLOYEES;

(EX3) 2005년 상품별 매입수량순으로 순위를 부여하시오

Alias는 상품번호, 상품명, 매입수량 ,순위

SELECT A.BUY_PROD AS 상품번호,
B.PROD_NAME AS 상품명,
SUM(BUY_QTY) AS 매입수량,
RANK() OVER(ORDER BY SUM(BUY_QTY) DESC) AS 순위
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD = B.PROD_ID --조인조건
AND EXTRACT(YEAR FROM BUY_DATE) = 2005
GROUP BY A.BUY_PROD, B.PROD_NAME;

그룹별 순위 : PARTITION BY 절 사용 << 하나의 그룹에 여러개가 들어가있으면 안씀

💫 2) PARTITION BY

  • 'PARTITION BY 컬럼명'은 'GROUP BY 컬럼명' 역할 수행
(사용형식)
RANK() OVER (PARTITION BY 컬럼명[,컬럼명,...] ORDER BY 컬럼명 [ASC|DESC][,컬럼명 [ASC|DESC],...])

(EX1) 사원테이블에서 각 부서별 사원들의 급여순으로 순위부여

Alias는 사원번호, 사원명, 부서코드, 급여, 순위

SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여,
RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS 순위
FROM HR.EMPLOYEES;

💫 3) 그룹별 최소값, 최대값 구하기

  • KEEP() 함수와 FIRST, LAST 식별자 사용
  • DENSE_RANK 함수만 사용 가능
(사용형식)
MIN(컬럼명)|MAX(컬럼명) KEEP(DENSE_RANK FIRST|LAST ORDER BY 컬럼명 [ASC|DESC])
OVER(PARTITION BY 컬럼명)

(EX1) 사원테이블에서 사원들의 급여를 조회하되 사원들이 속한 부서의

최대급여와 최소급여를 조회하시오.

Alias는 사원번호, 사원명, 부서코드, 급여, 최소급여, 최대급여

SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여,
MIN(SALARY) KEEP(DENSE_RANK FIRST ORDER BY SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서최소급여,
MAX(SALARY) KEEP(DENSE_RANK LAST ORDER BY SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서최대급여
FROM HR.EMPLOYEES
ORDER BY 3, 4;

💫 DENSE_RANK() OVER

  • 순위를 부여하는 방법이 RANK() OVER함수와 다르며 그 이외의 특징은 동일함
  • 같은 값에 같은 순위를 부여하지만 차 순위는 순차적인 순위 부여 (ex : 1,2,2,2,3,4,5...)

💫 ROW_NUMBER() OVER

  • 순위를 부여하는 방법이 RANK() ONER함수나 DENSE_RANK()와 다르며 그 이외의 특징은 동일
  • 같은 값에도 차례대로 증가하는 순위부여 (ex : 값 : 10,20,20,20,30,40,50...이면
    순위 : 1,2,3,4,5,6,7,... )

💫 2) AVG

  • 그룹별 평균값을 반환
(사용형식)
AVG(expr)

(EX1) 상품테이블에서 분류별 평균매입가를 조회하시오

  SELECT PROD_LGU AS 분류코드, 
         AVG(PROD_COST) AS "평균 매입가"
    FROM PROD
GROUP BY PROD_LGU
ORDER BY 1;

소수점은 자르거나 반올림

ROUND( AVG(PROD_COST)) AS "평균 매입가"

(EX2) 사원테이블에서 부서별 평균 근속년수를 조회하시오

SELECT DEPARTMENT_ID AS 부서코드, 
        AVG(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE)) AS "평균 근속년수"
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

소수 첫째자리에서 반올림

ROUND(AVG(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE)),1) AS "평균 근속년수"

(EX3) 사원테이블에서 부서별 평균급여를 조회하시오

SELECT  DEPARTMENT_ID AS 부서, 
        ROUND(AVG(SALARY)) AS 평균급여
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

(EX4) 회원테이블에서 연령대별 평균마일리지값을 구하시오

SELECT TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)||'대' AS 연령대, 
        ROUND(AVG(MEM_MILEAGE))  AS "평균 마일리지"
FROM MEMBER
GROUP BY TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)
ORDER BY 1;

💫 3) COUNT

  • 그룹별 자료의 수(행의 수)를 반환
COUNT(컬럼명|*)
- '컬럼명|*' : 외부조인(OUTER JOIN)을 제외하고 '컬럼명'이나 '*'는 차이가 없음
- 외부조인인 경우 '*'을 사용하면 자료가 없어도 '1'을 출력함(NULL값을 갖는 행이 존재하기 때문)
따라서 외부조인에서 COUNT함수 사용시 '컬럼명'을 기본키 컬럼으로 기술하는것이 안전하다

(EX1) 사원테이블에서 부서별 인원수를 조회하시오

SELECT DEPARTMENT_ID AS 부서코드, 
        COUNT(*) AS 인원수1,
        COUNT(EMP_NAME) AS 인원수2,
        COUNT(DEPARTMENT_ID) AS 인원수3
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

인원수 3만 다르게 나옴

한명만 부서코드가 NULL이기 때문에 갯수가 다르게 나오는것...

킴벌리만 NULL

마찬가지로COMMISSION_PCT도 NULL값이 많기때문에 이걸 기준으로 하면 더 엉망으로 나옴

(EX2) HR계정 회사의 모든 부서별 인원수를 조회하시오

  • 사원테이블에서 사용하는 부서
SELECT DISTINCT DEPARTMENT_ID
FROM HR.EMPLOYEES
ORDER BY 1;

  • 부서테이블에 존재하는 부서
SELECT DISTINCT DEPARTMENT_ID
FROM HR.DEPARTMENTS
ORDER BY 1;

부서테이블에서 DEPARTMENT_ID는 기본키이기때문에 중복되거나 NULL값을 가질수가 없다

사원테이블에서는 외래키이기 때문에 NULL이 가능했던것!

  • HR계정 회사의 모든 부서별 인원수를 조회하시오
SELECT DISTINCT DEPARTMENT_ID AS 부서코드,
        COUNT (*) AS 인원수1,
        COUNT (A.EMP_NAME) AS 인원수2
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID(+)=B.DEPARTMENT_ID
GROUP BY B.DEPARTMENT_ID
ORDER BY 1;

대상이 되어지는 테이블의 기본키를 써주ㅓ...외부조인...

근데 왜 안돌아가

(EX3) 사원테이블에서 부서별 인원수가 5인이상인 부서 조회

		SELECT DEPARTMENT_ID AS 부서코드,
					 COUNT(*) AS 인원수
			FROM HR.EMPLOYEES
	GROUP BY DEPARTMENT_ID
		HAVING COUNT(*)>=5
	ORDER BY 1;

그룹함수에 조건이 부여될때는 HAVING절을 사용한다

(EX4) 상품테이블에서 분류별 상품수를 조회하시오

SELECT PROD_LGU AS 분류코드, 
        COUNT(*) AS 상품수
FROM PROD
GROUP BY PROD_LGU
ORDER BY 1;

(EX5) 매입테이블에서 월별 매입건수를 조회하시오

SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월,
        COUNT(BUY_QTY) AS 매입건수
FROM BUYPROD
GROUP BY EXTRACT(MONTH FROM BUY_DATE)
ORDER BY 1;

(EX6) 매출테이블에서 월별매출건수가 10이상인 매출정보만 조회하시오

SELECT  SUBSTR(CART_NO,5,2) AS 월, 
        COUNT(CART_QTY) AS 매출건수
FROM CART
WHERE CART_NO LIKE '2005%'
GROUP BY SUBSTR(CART_NO,5,2)
HAVING COUNT(CART_QTY)>=10
ORDER BY 1;

SUBSTR(CART_NO,5,2) : 5번째 글자부터 2글자를 꺼내

🚨 이게 틀린이유: 같은날 한사람이 여러건 샀어도 1건으로 취급하고싶은건데 여러건으로 취급되었기 때문 >> DISTINCT로 중복 해결하자 
SELECT  SUBSTR(A.CNO,5,2)||'월' AS 월, 
        COUNT(*) AS 매출건수
FROM (SELECT DISTINCT CART_NO AS CNO 
                FROM CART) A
WHERE SUBSTR(A.CNO,1,4) = '2005'
GROUP BY SUBSTR(A.CNO,5,2)
HAVING COUNT(*)>=10
ORDER BY 1;

💫 4) MAX, MIN

  • 제시된 컬럼값중 최대(MAX), 최소(MIN)값을 반환
(사용형식)
MAX(expr) / MIN(expr)
🚨 집계함수는 중복으로 사용 안되므로 주의해가면서 풀어보기!

EX1) 사원테이블에서 부서별 최대급여와 최소급여를 조회하시오.

SELECT DEPARTMENT_ID AS 부서코드, 
EMP_NAME AS 사원명,
MAX(SALARY) AS 최대급여, 
MAX(SALARY) AS 최소급여
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID, EMP_NAME
ORDER 1;

왜 또 안돌아가

혀튼 이건 우리가 찾는 형태의 테이블은 아님 . 다르게 짜야함 부서별로 나눠서 이름 쓰고 자기들끼리 급여가 높은지 낮은지 써있음...

SELECT B.EMPLOYEE_ID AS 사원번호,
        B.EMP_NAME AS 사원명,
        A.DEPARTMENT_ID AS 부서코드,
        A.MSAL AS 급여
FROM (SELECT DEPARTMENT_ID,
MAX(SALARY) AS MSAL 
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID) A,
HR.EMPLOYEES B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND B.SALARY = A.MSAL
ORDER BY 3;

EX2) 사원테이블에서 부서별 최대급여와 최소급여를 받는 사원의 사원번호, 사원명, 부서코드, 급여를 조회하시오

	SELECT  B.EMPLOYEE_ID AS 사원번호,
	        B.EMP_NAME AS 사원명,
	        A.DEPARTMENT_ID AS 부서코드,
	        A.MSAL AS 급여
	  FROM (SELECT DEPARTMENT_ID,
	         MAX(SALARY) AS MSAL 
	  FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID) A,
					HR.EMPLOYEES B
   WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
					AND B.SALARY = A.MSAL
ORDER BY 3;

EX3) 2005년 5월 최대구매(금액 기준)를 한 회원의 회원번호, 회원명, 구매금액을 조회하시오

  • 2005년 5월 회원별 구매합계
SELECT A.CART_MEMBER,
        SUM(A.CART_QTY*B.PROD_PRICE) AS ASUM
    FROM CART A, PROD B
   WHERE A.CART_PROD = B.PROD_ID
  AND CART_NO LIKE '200505%'
  GROUP BY A.CART_MEMBER
  • ‘1’번의 결과에서 ‘ASUM’값이 제일 큰 값을 조회
SELECT F.MEM_ID AS 회원번호,
  F.MEM_NAME AS 회원명,
  D.MCASUM AS 구매금액
  
  FROM (SELECT MAX(C.ASUM) AS MCASUM
     FROM(SELECT A.CART_MEMBER AS ACID,
              SUM(A.CART_QTY*B.PROD_PRICE) AS ASUM
            FROM CART A, PROD B
            WHERE A.CART_PROD = B.PROD_ID
             AND CART_NO LIKE '200505%'
            GROUP BY A.CART_MEMBER) C) D,
  (SELECT A.CART_MEMBER AS ACID,
        SUM(A.CART_QTY*B.PROD_PRICE) ASUM
  FROM CART A, PROD B
  WHERE A.CART_PROD = B.PROD_ID
  AND CART_NO LIKE '200505%'
  GROUP BY A.CART_MEMBER) E,
  MEMBER F
  WHERE D.MCASUM = E.ASUM
  AND F.MEM_ID = E.ACID;

profile
서현서현
이전 포스트

NULL 처리함수

다음 포스트

테이블 JOIN

0개의 댓글