특정 그룹을 지정하고, 그룹 내 최댓값, 최솟값, 평균값, 개수, 소계를 구하는 집계 함수와 그룹에 속한 필드의 순서와 특정 위치의 값을 구하는 그룹함수를 실습한다.
15.1 MAX()
그룹으로 만든 ‘필드명’의 최댓값을 구한다. 그룹은 GROUP BY 구문이나 PARTITION BY 구문에 ‘그룹필드명’을 지정해 만든다.
•
MAX(필드명)
•
MAX(필드명) OVER ( PARTITION BY 그룹필드명 )
“GROUP BY CLASS_CD” 구문은 테이블 전체에 CLASS_CD 필드 그룹을 적용하지만 “(PARTITION BY CLASS_CD)” 구문은 해당 레코드의 행에서만 CLASS_CD 필드 그룹을 적용한다.예제1. 성적 테이블에서 반코드로 그룹을 만들고 국어, 영어, 수학 점수의 최댓값을 구한다.
SELECT CLASS_CD,
MAX(KOR) AS KOR_최대값,
MAX(ENG) AS ENG_최대값,
MAX(MAT) AS MAT_최대값
FROM TB_GRADE
GROUP BY CLASS_CD
ORDER BY CLASS_CD;
SQL
복사
예제2. 성적 테이블에서 반코드 그룹을 만들고 국어 점수 최댓값과 국어 점수와의 차이를 구한다.
SELECT CLASS_CD,
STUDENT_NO,
STUDENT_NM,
KOR,
MAX(KOR) OVER (PARTITION BY CLASS_CD) AS 최대값, -- 해당 레코드의 행에서만 필드 그룹을 적용
MAX(KOR) OVER (PARTITION BY CLASS_CD) - KOR AS 차이
FROM TB_GRADE
ORDER BY CLASS_CD,STUDENT_NO;
SQL
복사
15.2 MIN()
그룹으로 만든 ‘필드명’의 최솟값을 구한다. 그룹은 GROUP BY 구문이나 PARTITION BY 구문에 ‘그룹필드명’을 지정해 만든다.
•
MIN(필드명)
•
MIN(필드명) OVER (PARTITION BY 그룹필드명)
예제1. 성적 테이블에서 반코드 그룹을 만들고 국어, 영어, 수학 점수의 최솟값을 구한다.
SELECT CLASS_CD,
MIN(KOR) AS KOR_최소값,
MIN(ENG) AS ENG_최소값,
MIN(MAT) AS MAT_최소값
FROM TB_GRADE
GROUP BY CLASS_CD
ORDER BY CLASS_CD;
SQL
복사
예제2. 성적 테이블에서 반코드 그룹을 만들고 국어 점수와 국어 점수 최솟값의 차이를 구한다.
SELECT CLASS_CD,
STUDENT_NO,
STUDENT_NM,
KOR,
MIN(KOR) OVER (PARTITION BY CLASS_CD) AS 최소값,
MIN(KOR) OVER (PARTITION BY CLASS_CD) - KOR AS 차이
FROM TB_GRADE
ORDER BY CLASS_CD, STUDENT_NO;
SQL
복사
15.3 SUM()
SUM() 함수는 그룹으로 만든 ‘필드명’의 합계를 구한다. 그룹은 마찬가지로 GROUP BY나 PARTITION BY 구문에 ‘그룹필드명’을 지정해 만든다.
•
SUM(필드명)
•
SUM(필드명) OVER (PARTITION BY 그룹필드명)
예제1. 성적 테이블에서 반코드 그룹을 만들고 국어, 영어, 수학 점수의 합계를 구한다.
SELECT CLASS_CD,
SUM(KOR) AS KOR_합계값,
SUM(ENG) AS ENG_합계값,
SUM(MAT) AS MAT_합계값
FROM TB_GRADE
GROUP BY CLASS_CD
ORDER BY CLASS_CD;
SQL
복사
15.4 AVG()
AVG() 함수는 그룹으로 만든 ‘필드명’의 평균을 구한다. 그룹은 GROUP BY나 PARTITION BY구문에 ‘그룹필드명’을 지정해 만든다.
•
AVG(필드명)
•
AVG(필드명) OVER ( PARTITION BY 그룹필드명 )
예제1. 성적 테이블에서 반코드 그룹을 만들고 국어, 영어, 수학 점수의 평균값을 구한다.
SELECT CLASS_CD,
ROUND(AVG(KOR),2) AS KOR_평균값,
ROUND(AVG(ENG),2) AS ENG_평균값,
ROUND(AVG(MAT),2) AS MAT_평균값
FROM TB_GRADE
GROUP BY CLASS_CD
ORDER BY CLASS_CD;
SQL
복사
15.5 COUNT()
COUNT() 함수는 그룹으로 만든 ‘필드명’의 레코드 개수를 구한다. 그룹은 GROUP BY 구문이나 PARTITION BY 구문에 ‘그룹필드명’을 지정해 만든다.
•
COUNT(필드명)
•
COUNT(필드명) OVER (PARTITION BY 그룹필드명)
예제1. 포인트 테이블에서 고객코드로 그룹을 만들고 일련번호(SEQ_NO)의 레코드 개수를 구해 적립수를 보인다.
SELECT CUSTOMER_CD,
COUNT(SEQ_NO) AS 적립수
FROM TB_POINT
GROUP BY CUSTOMER_CD
ORDER BY CUSTOMER_CD;
SQL
복사
15.6 ROLLUP()
ROLLUP() 함수는 그룹으로 만든 ‘필드명1’,’필드명2’, ...의 그룹을 만들어 소계와 총계를 구한다.
예를들어, “GROUP BY ROLLUP(CLASS_CD)” 구문은 CLASS_CD 필드 그룹을 만들어 소계와 총계를 구하고, “GROUP BY ROLLUP(YEAR, CLASS_CD)” 구문은 YEAR, CLASS_CD 필드 그룹을 만들어 소계와 총계를 구한다.
•
ROLLUP(필드명1, 필드명2, .... 필드명n)
예제1. 포인트 테이블에서 고객코드 그룹을 만들고 포인트의 합과 총합을 구한다. (총합은 ROLLUP을 이용해서 구할 수 있음)
SELECT NVL(CUSTOMER_CD,'총합') AS CUSTOMER_CD,
SUM(POINT) AS 포인트합
FROM TB_POINT
GROUP BY ROLLUP(CUSTOMER_CD)
ORDER BY CUSTOMER_CD;
SQL
복사
예제2. 판매 테이블에서 판매일과 상품명을 그룹으로 만들어 소계와 총합을 구한다. 매출금액은 단가에 판매 개수를 곱한 값으로 한다.
SELECT NVL(SALES_DT,'총합') AS SALES_DT,
CASE WHEN SALES_DT IS NOT NULL AND PRODUCT_NM IS NULL THEN '<소계>'
ELSE PRODUCT_NM
END AS PRODUCT_NM,
SUM(PRICE*SALES_COUNT) AS 매출금액
FROM TB_SALES
GROUP BY ROLLUP(SALES_DT,PRODUCT_NM)
ORDER BY SALES_DT;
SQL
복사
15.7 ROW_NUMBER()
ROW_NUMBER() 함수는 ‘그룹필드명’으로 그룹을 만들고 ‘정렬필드명’으로 정렬해 중복 없는 순번을 구한다. 이 함수로 쿼리 작업에서 고유번호를 생성할 수 있다.
•
ROW_NUMBER() OVER (PARTITION BY 그룹필드명 ORDER BY 정렬필드명)
예제1. 7월 성적 테이블에서 국어 점수를 내림차순 정렬해 중복 없는 순위를 구한다.
SELECT TEST_CD,
KOR,
ROW_NUMBER() OVER (ORDER BY KOR DESC) AS 순위 -- 그룹은 없고 단순 정렬
FROM TB_GRADE_07
ORDER BY KOR DESC;
SQL
복사
즉, ROW_NUMBER()는 중복 없는 순위를 매길 때 유용하게 쓰인다.
15.8 RANK()
RANK() 함수는 ‘그룹필드명’으로 그룹을 만들고 ‘정렬필드명’으로 정렬해 같은 값이면 중복 순위를 부여하고 그 이후는 해당 순위만큼 이동한 순위를 구한다. ( < - > ROW_NUMBER() )
•
RANK() OVER (PARTITION BY 그룹필드명 ORDER BY 정렬필드명)
예제1. 7월 성적 테이블에서 국어 점수를 내림차순 정렬해 같은 값이면 중복한 순위를 부여하고, 다음 순위는 해당 순위만큼 이동한 순위를 구한다.
SELECT TEST_CD,
KOR,
RANK() OVER ( ORDER BY KOR DESC ) AS 순위
FROM TB_GRADE_07
ORDER BY KOR DESC;
SQL
복사
15.9 DENSE_RANK()
DENSE_RANK() 함수는 ‘그룹필드명’으로 그룹을 만들고 ‘정렬필드명’으로 정렬해 같은 값이면 중복 순위를 부여하고 그 이후는 순차적인 순위를 구한다. ( RANK()와 비슷하지만 중복 순위 다음의 순위가 그 다음 숫자라는 차이가 있음)
•
DENSE_RANK() OVER (PARTITION BY 그룹필드명 ORDER BY 정렬필드명)
예제1. 7월 성적 테이블에서 국어 점수를 내림차순 정렬하고, 같은 값이면 중복 순위를 부여한 후 다음 순위는 순차적으로 구한다.
SELECT TEST_CD,
KOR,
DENSE_RANK() OVER (ORDER BY KOR DESC) AS 순위
FROM TB_GRADE_07
ORDER BY KOR DESC;
SQL
복사
15.10 RANK() WITHIN
RANK() ... WITHIN 구문은 GROUP() 함수에서 ‘정렬필드명’으로 정렬한 값을 기준으로 ‘값’이 어디에 위치할 수 있는지를 구한다. 순위 값은 RANK() 함수로 순위를 부여하는 방식과 동일하게 적용한다.
•
RANK(값) WITHIN GROUP(정렬필드명 [ASC | DESC] )
예를들어, “RANK(15) WITHIN GROUP(ORDER BY SQNO)” 라고 하면 SQNO값으로 오름차순 정렬한 상태에서 15의 위치를 구한다.
예제1. 7월 성적 테이블에서 국어 점수를 중복된 순위로 내림차순 정렬해 96과 90에 해당하는 순위의 위치를 구한다. (RANK(), RANK() WITHIN )
SELECT RANK(96) WITHIN GROUP(ORDER BY KOR DESC) AS "96_위치",
RANK(90) WITHIN GROUP(ORDER BY KOR DESC) AS "90_위치"
FROM TB_GRADE_07;
SQL
복사
물론, DENSE_RANK() 를 사용해서 다른 위치를 반환할 수도 있다.
SELECT DENSE_RANK(96) WITHIN GROUP(ORDER BY KOR DESC) AS "96_위치",
DENSE_RANK(90) WITHIN GROUP(ORDER BY KOR DESC) AS "90_위치"
FROM TB_GRADE_07;
SQL
복사
15.11 FIRST_VALUE()
FIRST_VALUE() 함수는 ‘그룹필드명’으로 그룹을 만들고 ‘정렬필드명’으로 정렬한 ‘필드명’의 첫 번째 위치의 값을 구한다.
•
FIRST_VALUE(필드명) OVER (PARTITION BY 그룹필드명 ORDER BY 정렬필드명)
예제1. 성적 테이블에서 반코드로 그룹을 만들고 국어 점수를 내림차순 정렬해 국어 점수의 첫 번째 값을 구한다. (즉, 그룹별 국어 점수의 최댓값)
SELECT CLASS_CD,
STUDENT_NO,
STUDENT_NM,
KOR,
FIRST_VALUE(KOR) OVER (PARTITION BY CLASS_CD ORDER BY KOR DESC) AS "반별 최대점수"
FROM TB_GRADE
ORDER BY CLASS_CD, KOR DESC;
SQL
복사
FIRST_VALUE() 함수와 관련된 기능으로 ORDER BY 정렬 기준의 마지막 데이터를 구하는 LAST_VALUE() 함수가 있다. SELECT CLASS_CD,
STUDENT_NO,
STUDENT_NM,
KOR,
LAST_VALUE(KOR) OVER (PARTITION BY CLASS_CD) AS "반별 최소점수"
FROM TB_GRADE
ORDER BY CLASS_CD, KOR DESC;
SQL
복사