18. 집계 함수 / RANK 함수

1. 집계 함수

• SUM, AVG, COUNT, MIN, MAX
• 보통 GROUP BY 절과 같이 사용
  ⇒ SELECT 절에서 일반항목과 집계함수가 같이 사용될 때에는 무조건 GROYP BY를 사용해야 한다.
• (GROUP BY절 사용해야 하는 경우)
  . SELECT절에서 일반항목(칼럼, 수식)과 집계 함수가 같이 사용된 경우
  . GROUP BY 칼럼명 1 [, 칼럼명 2,....] -- 칼럼명 1을 만들고 그 속에서 칼럼명 2를 가지고 그룹을 만듦
  . SELECT절에서 사용된 일반항목(칼럼, 수식)은 반드시 GROUP BY 절에 기술해야 한다.!
  . 집계 함수에 부여된 조건은 HAVING절에 기술
     ⇒ SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY 순서
  . GROUP BY 절은 WHERE절 다음에 기술 (WHERE 절은 생략 가능)
  . HAVING 절은 GROUP BY절 다음에 기술. ORDER BY 절은 맨 마지막에 기술

1) COUNT(*|칼럼명)

⇒ 아우터 조인할 때는 반드시 칼럼명을 사용한다

• 행의 수를 반환

예제) 사원 테이블에서 전체 사원수를 조회하시오

SELECT COUNT(*), COUNT(EMP_NAME) 
  FROM EMPLOYEES;
-- 일반함수가 사용되지않았고 집계함수만 사용되었기때문에 그룹바이를 사용하지 않음
-- 전체를 그룹으로 생각

 

예제) 사원테이블에서 부서별 사원수를 조회하시오

SELECT DEPARTMENT_ID AS 부서코드,
       COUNT(*) AS 사원수
  FROM EMPLOYEES
 GROUP BY DEPARTMENT_ID
 ORDER BY 1;

 

예제) 사원테이블에서 급여가 3000 이상인 부서별 사원수를 조회하시오

SELECT DEPARTMENT_ID AS 부서코드,
       COUNT(*) AS 사원수
  FROM EMPLOYEES
 WHERE SALARY >= 3000
 GROUP BY DEPARTMENT_ID
 ORDER BY 1;

 

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

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

 

예제) 상품테이블에서 분류별 상품수가 20개 이상인 자료를 조회하시오

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

 

2) SUM(칼럼명)

• '칼럼명'에 저장된 자료의 합계를 반환

예제) 사원 테이블에서 각 부서별 급여 합계를 구하시오 ( Alias는 부서 코드, 부서명, 급여 합계 )

SELECT A.DEPARTMENT_ID AS 부서코드,
       B.DEPARTMENT_NAME AS 부서명,
       COUNT(*) AS 사원수,
       NVL(SUM(A.SALARY), 0) AS 급여합계
  FROM EMPLOYEES A, DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID -- 내부조인조건
 GROUP BY A.DEPARTMENT_ID, B.DEPARTMENT_NAME  --집계함수가 쓰이지 않은 일반항목을 모두 적어줌 
 ORDER BY 3 DESC; 
 -- 컬럼의 순서번호와 ASC오름차순 과 DESC내림차순으로 설정.
 -- 급여의 합계 내림차순으로

 

예제) 2005년 2~5월 사이 발생한 제품별 매입 집계를 구하시오
       ( Alias는 상품코드, 매입수량 합계, 매입금액 합계 )
       단, 상품코드 순으로 출력하시오

SELECT BUY_PROD AS 상품코드,
       SUM(BUY_QTY) AS 매입수량합계,
       SUM(BUY_COST*BUY_QTY) AS 매입금액합계,
       COUNT(*) AS 매입건수
  FROM BUYPROD
 WHERE BUY_DATE BETWEEN TO_DATE('20050201') AND TO_DATE('20050531') 
 GROUP BY BUY_PROD   -- SELECT 절에서 집계함수를 제외한 일반수식이나 항목이 사용되었으면 GROUP BY 사용
 ORDER BY 1;
  -- SELECT 절에 없더라도 그룹을 묶어야한다면 같은 테이블에 있는 일반항목을 사용할수 있다. 2중 그룹핑 
  -- GROUP BY BUY_PROD, BUY_DATE

 

문제) 2013년도 지역별 대출잔액을 조회하시오 ( 대출잔액이 많은 지역부터 출력 )

SELECT REGION AS 지역,
       SUM(LOAN_JAN_AMT) AS 대출잔액
  FROM KOR_LOAN_STATUS
 WHERE PERIOD LIKE '2013%'
 GROUP BY REGION
 ORDER BY 2 DESC;

 

문제) 2013년도 지역별 구분별 대출잔액을 조회하시오 ( 대출잔액이 많은 지역부터 출력 )

SELECT REGION AS 지역,
       GUBUN AS 구분,
       SUM(LOAN_JAN_AMT) AS 대출잔액
  FROM KOR_LOAN_STATUS
 WHERE PERIOD LIKE '2013%'
 GROUP BY REGION, GUBUN
 ORDER BY 3 DESC;

 

문제) 매입자료를 이용하여 2005년도 거래처별 매입 집계를 구하시오

-- 거래처또는 거래처이름을 기준으로 그룹을 한다 코드보다 보기쉽게하기위해

SELECT C.BUYER_ID AS 거래처코드,
       C.BUYER_NAME AS 거래처명,
       SUM(A.BUY_QTY) AS 매입수량집계,
       SUM(A.BUY_QTY * B.PROD_COST) AS 매입급액집계
  FROM BUYPROD A, PROD B, BUYER C
 WHERE A.BUY_PROD = B.PROD_ID
   AND B.PROD_BUYER = C.BUYER_ID
   AND A.BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20051231')
 GROUP BY C.BUYER_ID, C.BUYER_NAME;

--필요한것은 BUYPROD 와 BUYER 테이블인데 중가에 공통된것이 PROD에 있다
-- BUYER 테이블에서 코드를 찾기위해
-- BUYPROD 테이블과 PROD 에 공통된 상품코드를 찾고 그것에 대항하는 거래처코드를 찾고
-- 그 거래처 코드와 바이어아이디가 같는것을 찾음

 

문제) 회원 테이블에서 남녀회원별 인원수 및 마일리지 합계를 조회하시오

SELECT DECODE(SUBSTR(MEM_REGNO2, 1,1), 1, '남성회원', 2, '여성회원') AS 성별,
       COUNT(SUBSTR(MEM_REGNO2,1,1)) AS 인원수,
       SUM(MEM_MILEAGE) AS 마일리지합계
  FROM MEMBER
 GROUP BY SUBSTR(MEM_REGNO2, 1,1);
 
 -- CASE WHEN SUBSTR(MEM_REGNO2, 1,1) = '1' THEN '남성회원 ELSE '여성회원' END AS 성별

 

문제) 상품 테이블에서 분류별 상품의 수를 조회하시오

SELECT PROD_LGU AS "상품 분류",
       COUNT(*) AS "상품의 수"
  FROM PROD
 GROUP BY PROD_LGU ;

 

3) AVG (칼럼명)

• '칼럼명'의 자료에 대한 평균값을 반환 ⇒ 표현식도 올 수 있다.

예제) 회원 테이블에서 평균 마일리지를 조회하시오 ⇒ 값이 1개 이기때문에 GROUP BY절을 사용하지 않는다

SELECT ROUND(AVG(MEM_MILEAGE)) AS "평균 마일리지"
  FROM MEMBER;

 

예제) 회원테이블에서 남녀회원별 평균 마일리지를 조회하시오

SELECT CASE WHEN SUBSTR(MEM_REGNO2,1,1) = '1' THEN '남성회원' 
       ELSE '여성회원' END AS 성별,
       ROUND(AVG(MEM_MILEAGE)) AS "평균 마일리지"
  FROM MEMBER
 GROUP BY SUBSTR(MEM_REGNO2,1,1);

 

예제) 회원 테이블에서 평균 마일리지를 구하고 평균 마일리지보다 많은 마일리지를 보유한 회원을 조회하시오

SELECT MEM_ID AS 회원번호,
       MEM_NAME AS 회원명,
       MEM_MILEAGE AS 마일리지
  FROM MEMBER
 WHERE MEM_MILEAGE >= (SELECT ROUND(AVG(MEM_MILEAGE)) FROM MEMBER) ; 
 -- 평균값을 구한것을 WHERE 절에 입력해줘야한다.

 

예제) 회원테이블에서 평균 마일리지를 구하고 평균마일리지보다
        많은 마일리지를 보유한 회원을 조회하되 평균 마일리지도 출력하시오

SELECT MEM_ID AS 회원번호,
       MEM_NAME AS 회원명,
       MEM_MILEAGE AS 마일리지,
       (SELECT ROUND(AVG(MEM_MILEAGE)) FROM MEMBER) AS 평균마일리지
  FROM MEMBER
 WHERE MEM_MILEAGE >= (SELECT ROUND(AVG(MEM_MILEAGE)) FROM MEMBER) ;   
 -- 효율적이지 못한 방법

 

예제) 회원테이블에서 평균 마일리지를 구하고 평균마일리지보다
        많은 마일리지를 보유한 회원을 조회하되 평균 마일리지도 출력하시오

 (IN-LINE SUBQUERY 사용) 
 SELECT MEM_ID AS 회원번호,
       MEM_NAME AS 회원명,
       MEM_MILEAGE AS 마일리지,
       A.MAVG AS 평균마일리지
  FROM MEMBER, (SELECT ROUND(AVG(MEM_MILEAGE)) AS MAVG 
                  FROM MEMBER) A
 WHERE MEM_MILEAGE >= A.MAVG ; --조인조건 
 
 -- FROM 절부터 시행이되기때문에 테이블을 가져다놓고 찾기 시작한다.
 -- 한번 값을 구해놓고 참조 하는것
 
 -- SELECT 문에 AVG가 오게된다면 GROUP BY 절에 나머지 항목들이 분류기준으로 필수로 와야하는데
 -- 각각은 그룹으로 묶을수가 없는 요소이기때문에 오류가 온다
 -- 그럴땐 이것의 결과가 나타나있는 컬럼의 이름을 넣어준다
 -- FROM 절은 무조건 다 테이블 여러개오면 이름을 붙여준다.
 -- 집계함수가 없기때문에 GROUP BY절 사용해서는 안된다.

 

예제) 매입 테이블에서 제품별 평균 매입수량을 조회하시오.

SELECT BUY_PROD AS 제품코드, 
       AVG(BUY_QTY) AS 평균매입수량
  FROM BUYPROD
 GROUP BY BUY_PROD 
 ORDER BY 1;
     
SELECT B.PROD_NAME AS 제품명,
       AVG(A.BUY_QTY) AS 평균매입수량
  FROM BUYPROD A , PROD B
 WHERE A.BUY_PROD = B.PROD_ID
 GROUP BY B.PROD_NAME    
 ORDER BY 1;

 

예제) 매입테이블에서 제품별 평균매입수량을 조회하되 평균 매입수량이 15개 이상인 조회 하시오.

SELECT BUY_PROD AS 제품코드, 
       AVG(BUY_QTY) AS 평균매입수량
  FROM BUYPROD
 GROUP BY BUY_PROD
HAVING AVG(BUY_QTY) >= 15
 ORDER BY 1;
 
SELECT B.PROD_NAME AS 제품명, 
       A.BUY_PROD AS 제품코드, 
       AVG(A.BUY_QTY) AS 평균매입수량
  FROM BUYPROD A , PROD B
 WHERE A.BUY_PROD = B.PROD_ID
 GROUP BY B.PROD_NAME, A.BUY_PROD
HAVING AVG(A.BUY_QTY) >= 15
 ORDER BY 2;

 

예제) 사원 테이블에서 각 부서별 평균 급여를 계산하시오

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

SELECT B.DEPARTMENT_NAME AS 부서명,
       A.DEPARTMENT_ID AS 부서코드,
       ROUND(AVG(A.SALARY),1) AS 평균급여
  FROM EMPLOYEES A, DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
 GROUP BY A.DEPARTMENT_ID, B.DEPARTMENT_NAME
 ORDER BY 1;

 

예제) 사원테이블에서 부서별 평균급여를 계산하고 자기 부서의 평균 급여보다
        많은 급여를 받는 사원을 조회하시오 ( Alias는 사원번호, 사원명, 부서 번호, 급여 )

SELECT A.EMPLOYEE_ID AS 사원번호,
       A.EMP_NAME AS 사원명,
       A.DEPARTMENT_ID AS 부서번호,
       A.SALARY AS 급여,
       B.MAVG AS "해당부서 평균급여"
  FROM EMPLOYEES A, (SELECT DEPARTMENT_ID AS DEID,
                            ROUND(AVG(SALARY),1) AS MAVG 
                       FROM EMPLOYEES
                      GROUP BY DEPARTMENT_ID) B
 WHERE A.DEPARTMENT_ID = B.DEID
   AND A.SALARY >= B.MAVG
 ORDER BY 3;

 

4) MIN(칼럼명), MAX(칼럼명)

• '칼럼명'의 값 중 최댓값과 최솟값을 반환

예제) 회원 테이블에서 최대 마일리지와 최소 마일리지를 구하시오

SELECT MAX(MEM_MILEAGE) AS 최대마일리지,
       MIN(MEM_MILEAGE) AS 최소마일리지
  FROM MEMBER;

-- ROWNUM ->> 'TOP' 위에서부터 나오는 행의수만큼 추출
SELECT MEM_ID, MEM_MILEAGE AS M1
  FROM MEMBER
  ORDER BY 2 DESC;
  
-- ROWNUM은 WHERE절에쓰는데 ORDER BY는 WHERE절 아래라서 실행순번에따라 ROWNUM이 의미가없다
-- 서브쿼리를 이용해서 FROM 절에 오더바이한것을 WHERE로 ROWNUM을 사용해야한다.
-- ROWNUM은 불안한 요소가있다.
-- 분석함수중 RANK 함수가 있다.

 

예제) 사원 테이블에서 사원수가 가장 많은 부서를 조회하시오 ( Alias는 부서 번호, 사원수 )

(부서별 사원수)
SELECT DEPARTMENT_ID,
       COUNT(*)
  FROM EMPLOYEES
 GROUP BY DEPARTMENT_ID;
   
      
(사원수가 가장 많은 부서와 인원수)
SELECT A.DID AS 부서번호,
       A.PAMT AS 사원수
  FROM (SELECT DEPARTMENT_ID AS DID,
               COUNT(*) AS PAMT
          FROM EMPLOYEES
         GROUP BY DEPARTMENT_ID
         ORDER BY 2 DESC) A
  WHERE ROWNUM=1; -- 될 수 있으면 ROWNUM을 쓰지 않는다 

(RANK()함수 사용)
SELECT A.DID AS 부서번호,
       A.PAMT AS 사원수
  FROM (SELECT DEPARTMENT_ID AS DID,
               COUNT(*) AS PAMT,
               RANK() OVER(ORDER BY COUNT(*) DESC) AS 순위 
          FROM EMPLOYEES
         GROUP BY DEPARTMENT_ID
         ORDER BY 2 DESC) A
  WHERE 순위=1;
  
  -- ( 정렬하세요 기준이되는컬럼명(수식) 차순) 등수를 비교해서 부여한다.
  -- RANK() OVER(ORDER BY 컬럼명 DESE/ASC )
  
  -- RANK의 종류는 3가지

 

2. RANK 함수

1) RANK( ) 함수

• 동일한 값에 중복 순위 부여한 후 다음 순위 해당 개수만큼 건너뛰고 부여

( 사용 형식 )

SELECT 컬럼명1,
         컬럼명2,
           :
         RANK() OVER(ORDER BY 기준컬럼(또는 수식) DESC|ASC) AS 별칭
    FROM 테이블명

 

예제) 매출 테이블(CART)에 2005년 5월 제품별 판매수량 합계를 구하되 판매수량이 많은 제품 순으로 순위를 부여하시오
       ( Alias 상품코드, 상품명, 판매수량 합계, 순위 )

SELECT A.CART_PROD AS 상품코드,
       B.PROD_NAME AS 상품명,
       SUM(A.CART_QTY)AS 판매수량합계,
       RANK() OVER(ORDER BY SUM(A.CART_QTY) DESC) AS 순위
  FROM CART A, PROD B
 WHERE A.CART_PROD = B.PROD_ID
   AND CART_NO LIKE '200505%'
 GROUP BY A.CART_PROD, B.PROD_NAME;

 

2) DENSE_RANK( ) 함수

• 동일한 값에 중복 순위 부여한 후 다음 순위는 중복 순위에 관계없이 순차적인 값을 부여

예제) 매출 테이블(CART)에 2005년 5월 제품별 판매수량 합계를 구하되 판매수량이 많은 제품 순으로 순위를 부여하고
        상위 10위의 상품정보를 조회하시오 (DENSE_RANK 사용)
        ( Alias 상품코드, 상품명, 판매수량 합계, 순위 )

SELECT A.PCODE AS 상품코드,
       A.AMT AS 판매수량,
       A.RK AS 순위
  FROM (SELECT CART_PROD AS PCODE,
               SUM(CART_QTY)AS AMT,
               DENSE_RANK() OVER(ORDER BY SUM(CART_QTY) DESC) AS RK
          FROM CART
         WHERE CART_NO LIKE '200505%'
         GROUP BY CART_PROD) A
 WHERE A.RK <= 10;
         
         
-- 가상테이블 VIEW 라고 한다. 다음 VIEW가 나오면 사라짐 뷰에 이름을 붙여줌
-- 테이블에 이름을 붙여준다 AS사용하지 않는다

SELECT A.PCODE AS 상품코드,
       A.AMT AS 판매수량,
       A.RK AS 순위
  FROM (SELECT CART_PROD AS PCODE,
               SUM(CART_QTY)AS AMT,
               RANK() OVER(ORDER BY SUM(CART_QTY) DESC) AS RK
          FROM CART
         WHERE CART_NO LIKE '200505%'
         GROUP BY CART_PROD) A
 WHERE A.RK <= 10;
--상위 10위

---
SELECT C.PCODE AS 상품코드,
       C.PNAME AS 상품명,
       C.AMT AS 판매수량,
       C.RK AS 순위    
  FROM (SELECT A.CART_PROD AS PCODE,
               B.PROD_NAME AS PNAME,
               SUM(A.CART_QTY)AS AMT,
               RANK() OVER(ORDER BY SUM(A.CART_QTY) DESC) AS RK
          FROM CART A, PROD B
         WHERE A.CART_PROD = B.PROD_ID
           AND CART_NO LIKE '200505%'
         GROUP BY A.CART_PROD, B.PROD_NAME) C
 WHERE C.RK <= 10;
---

 

3) ROW_NUMBER() 함수

• 중복에 관계없이 차례대로 순위 부여

SELECT A.PCODE AS 상품코드,
       A.AMT AS 판매수량,
       A.RK AS 순위
  FROM (SELECT CART_PROD AS PCODE,
               SUM(CART_QTY)AS AMT,
               ROW_NUMBER() OVER(ORDER BY SUM(CART_QTY) DESC) AS RK
          FROM CART
         WHERE CART_NO LIKE '200505%'
         GROUP BY CART_PROD) A
 WHERE A.RK <= 10;
 --상위 10개를 내보내기위래서 ROW_NUMBER 이용
 --ROW_NUMBER는 같은값이 있어도 등수로 그냥 위에서부터 부여한다.

 

4) 그룹별 등수 부여(PARTITION BY)

• RANK, DENSE_RANK, ROW_NUMBER와 함께 사용하여 그룹별 순위 부여

( 사용 형식 )

RANK() OVER(PARTITION BY 그룹분류 기준항목 ORDER BY 등수부여 기준항목 DESC|ASC)

 

예제) 사원 테이블에서 부서별 급여를 많이 받는 사원부터 순위를 부여하여 조회하시오
        ( 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 EMPLOYEES
 ORDER BY 3;

 

 

2. aggregate function.sql

0.00MB

1. aggregate function 2.sql

0.00MB

1. aggregate function 3.sql

0.00MB

1. aggregate function 4.sql

0.00MB