집계 함수 24.09.09

수호천사임다·2024년 9월 13일

오라클

목록 보기
11/53
  • 집계 함수는 행 그룹을 기반으로 단일 결과(한줄)를 반환
  • 집계 함수는 SELECT 절, HAVING 및 ORDER BY 에서 사용 가능
    • WHERE절에서는 사용 불가
  • 집계 함수는 일반적으로 GROUP BY 절과 사용
    • 부서별, 남자별, 그룹적으로 묶어서 사용
  • 테이블 전체가 하나의 그룹이 되는 경우 GROUP BY 없이 사용
  • GROUP BY 절에서 사용하지 않은 컬럼은 SELECT 절에서 집계함수와 함께 사용 불가
SELECT dept, SUM(sal) FROM emp; -- 에러
-- 일반컬럼인 dept와 SUM() 집게함수는 함께 사용 불가
  • COUNT(*), GROUPNG, CROUPING_ID를 제외한 모든 집계함수는 NULL을 무시
    • AVG() 합계는 NULL이 나온다.
  • COUNT 및 REG_COUNT는 레코드가 없는 경우 0을 반환
    • SUM()은 NULL
-- score
90
null
90

AVG(score) -- 90, null은 무시 (없는 자료)
AVG(NVL(score, 0) -- 60

COUNT(score) -- 2
COUNT(*) -- 3 모든 행수를 구하는 것 

SELECT COUNT(empNO) FROM emp;

SELECT COUNT(empNO) FROM emp;

SELECT COUNT(empNO) FROM emp WHERE city = '미국';

SELECT AVG(empNO) FROM emp;

SELECT AVG(empNO) FROM emp WHERE city = '미국'; -- null

COUNT( * ) - 자료의 행수(개수)

  • COUNT( DISTINCT | ALL ] expr )
  • 중복되는 행과 null 값을 포함하는 행을 포함하여 테이블 행의 수를 리턴
  • COUNT(expr)는 expr에 의해 인식된 열에서 null이 앙닌 행의 수를 리턴
SELECT COUNT(*) FROM emp; -- 전체 행수
SELECT COUNT(empNo) FROM emp; -- 컬럼에 null이 아닌 데이터의 개수
-- * 나 PRIMARY KEY 컬럼, NOT NULL 컬럼을 이용하여 COUNT 하면 전체 행수 
SELECT COUNT(tel) FROM emp; -- null은 카운트 하지 않는다. 

-- 서울 사람 인원수
SELECT COUNT(*)
FROM emp
WHERE city = '서울';

-- 부서의 수
SELECT COUNT(DISTINCT dept)
FROM emp;

-- 서울 사람이면서 남자 인원수
SELECT COUNT(*)
FROM emp
WHERE city = '서울' AND MOD(SUBSTR(rrn,8,1), 2) = 1;

-- 전체인원수, 남자인원수, 여자인원수(1줄로 출력) 
SELECT COUNT(*) 전체, COUNT(DECODE(MOD(SUBSTR(rrn, 8, 1), 2),1 ,1)) 남자,
                    COUNT(DECODE(MOD(SUBSTR(rrn, 8, 1), 2),0, 1)) 여자
FROM emp;

-- 전체인원수, 남자인원수, 여자인원수
-- 구분 인원
SELECT '전체' 구분, COUNT(*) 인원
FROM EMP
    UNION ALL
SELECT '남자', COUNT(*)
FROM emp
WHERE MOD(SUBSTR(rrn, 8 ,1), 2) = 1
    UNION ALL
SELECT '여자', COUNT(*)
FROM emp
WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0;

MAX([ DISTINCT | ALL ] expr) - 최대값

MIN([ DISTINCT | ALL ] expr) - 최소값

SELECT MIN(sal), MAX(sal)
FROM emp;

-- 최소 나이와 최대 나이를 구해라
SELECT MIN(TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD')) 고, 
       MAX(TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD')) 저
FROM emp;

SUM([ DISTINCT | ALL ] expr) - 총합

AVG([ DISTINCT | ALL ] expr) - 평균

SELECT AVG(sal), SUM(sal)
FROM emp;

-- sal 합, 남자 sal 합, 여자 sal 합
SELECT '전체' 구분, SUM(sal) 합
FROM EMP
    UNION ALL
SELECT '남자', SUM(sal) FROM emp WHERE MOD(SUBSTR(rrn, 8 ,1), 2) = 1
    UNION ALL
SELECT '여자', SUM(sal) FROM emp WHERE MOD(SUBSTR(rrn, 8, 1), 2) = 0;

-- sal 전체평균 남자 sal 평균, 여자 sal 평균 한줄로 
SELECT TRUNC(AVG(sal)) 전체, 
    TRUNC(AVG(DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, sal))) 남자,
    TRUNC(AVG(DECODE(MOD(SUBSTR(rrn, 8, 1),2),0, sal))) 여자
FROM emp;

-- 월별 입사 인원수
-- 전체 1월 2월 ... 12월
SELECT COUNT(*) 전체, 
    COUNT(DECODE(TO_CHAR(hireDate, 'MM'), '01', '1')) "1월" ,
    COUNT(DECODE(TO_CHAR(hireDate, 'MM'), '02', '2')) "2월"
FROM emp;

-- 급여(sal)가 가장 높은 사람의 이름, 급여
SELECT name, sal
FROM emp
WHERE sal = MAX(sal); -- 에러 WHERE 절에는 집계함수를 사용할 수 없다. 

SELECT name, sal
FROM emp
WHERE sal =(SELECT MAX(sal) FROM emp);
-- SELECE 안에 또 SELECT 넣는 걸 서브쿼리라 한다. 

-- 평균 급여(sal) 보다 적게 받는 사람의 이름, 급여
SELECT name, sal
FROM emp
WHERE sal  < (SELECT AVG(sal) FROM emp);

VARIANCE([ DISTINCT | ALL ] expr) - 분산

STDDEV([ DISTINCT | ALL ] expr) - 표준편차

Subquery

  • SELECT문, INSERT 문, UPDATE 문, DELETE 문등에서 사용되는 SELECT 문
  • SELECT 문의 SELECT 절, FROM 절, WHERE 절 등에서 서브쿼리를 사용할 수 있다.
  • 서브쿼리는 단독 사용 가능하다.
  • SELECT 절에서 사용되는 경우 한행의 결과를 출력하는 문만 가능
  • WHERE 절에서 사용되는 경우 결과가 하나의 컬럼만 가능만 가능하며
  • IN 이나 ANY 절에서는 여러 행의 결과가 가능하지만 =, >, < 등의 연산에서는 하나의 행만 가능
    • 다른 테이블과 연관된 데이터를 필터링할 때 (IN 또는 ANY 서브쿼리)
  • SELECT 절에 사용하는 서브 쿼리는 하나의 값만 조회되어야 한다. (하나의 칼럼, 하나의 행)
  • 단일 값 반환을 원할 떄
  • 집합 연산을 처리할 때
    • 서브쿼리는 FROM 절에서 사용할 수 있으며, 이는 서브쿼리의 결과를 하나의 테이블로 간주하며 메인 쿼리에서 사용할 수 있다.
    • 복잡한 집합 연산을 처리하거나, 중간 결과를 구해서 그 위에서 추가 연산할 때 사용
  • 특정 조건에 맞는 상위값이나 하위값을 구할 때
    • 상관 서브쿼리는 메인 쿼리의 각 행에 대해 서브쿼리가 반복 실행되며, 이를 통해 복잡한 조건을 처리할 수 있다.
  • 서브쿼리 사용 시 유의사항
    • 단일 값을 반환해야 할 때는 서브쿼리에서 다중 행이 반환되지 않도록 주의해야 한다.
    • 퍼포먼스: 서브쿼리는 때로 성능 문제를 유발할 수 있다. 특히 상관 서브쿼리는 각 행마다 반복적으로 실행되므로, 대규모 데이터셋에서 비효율적이다. 이때는 JOIN으로 대체 하는 것이 성능 향상에 도움이 된다.
SELECT name, (SELECT city FROM emp WHERE city = '서울')
FROM emp; -- 에러 서브 쿼리가 여러줄 

SELECT name, (SELECT AVG(sal) FROM emp)
FROM emp;

SELECT name, sal - (SELECT AVG(sal) FROM emp)
FROM emp;

SELECT name, sal
FROM emp
WHERE sal >= (SELECT sal FROM emp WHERE city = '인천');
-- 에러 , 인천 sal가 하나가 아니다. 누구랑 비교하는지 모르기 때문에 사용불가

SELECT name, sal
FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE city = '인천');
-- IN은 하나만 만족하면 되기때문에 사용 가능 

-- 서울 사람이 아닌 사람들 중에 평균(sal) 보다 많이 받는 사람(name, sal, city)
SELECT name, sal, city
FROM emp
WHERE city <> '서울' AND  sal > (SELECT AVG(sal) FROM emp WHERE city = '서울');

-- name, sal, 최대급여와의차이
SELECT name, sal, (SELECT MAX(sal) FROM emp) - sal 차이
FROM emp ;

-- 여자들 sal를 가장 많이 받는 사람(name, rrn, sal)
SELECT name, rrn, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8,1),2) = 0 AND
    sal = (SELECT MAX(sal) FROM emp WHERE MOD(SUBSTR(rrn, 8, 1),2) = 0);

-- sal + bonus가 가장 많은 사람(name, sal, bonus, sal + bonus)
SELECT name, sal, bonus, sal + bonus pay
FROM emp
WHERE sal + bonus = (SELECT MAX(sal + bonus) FROM emp);

-- 부서별 인원수가 가장 많은 부서명과 인원수 
SELECT MAX( COUNT(*))
FROM emp
GROUP BY dept
HAVING COUNT(*) = (SELECT MAX( COUNT(*)) FROM emp GROUP BY dept);

 -- 입사 인원수가 가장 많은 년도 및 인원수 출력
SELECT TO_CHAR(hireDate, 'YYYY'), COUNT(*) 
FROM emp
GROUP BY TO_CHAR(hireDate, 'YYYY');

HAVING은 SELECT에서 사용가능한건만 HAVING에 올 수 있다. 

 -- 입사 인원수가 가장 많은 년도 및 인원수 출력
SELECT TO_CHAR(hireDate, 'YYYY'), COUNT(*) 
FROM emp
GROUP BY TO_CHAR(hireDate, 'YYYY');

-- 입사년도별 인원의 최대값
SELECT MAX(COUNT(*)) 
FROM emp
GROUP BY TO_CHAR(hireDate, 'YYYY')
-- GROUP BY EXTRACT(YEAR FROM hireDate); 

HAVING COUNT(*) = 
(SELECT TO_CHAR(hireDate, 'YYYY'), COUNT(*) 
FROM emp
GROUP BY TO_CHAR(hireDate, 'YYYY'));
-- TO_CHAR 날짜, 숫자를 문자 데이터로 바꾸는 것
-- 단일행에서만 사용이 가능하다. 
-- 집계함수는 일반 컬럼과 사용할 수 없다. 
-- 나온 결과에 값이 들어가면 HAVING이 들어간다.

-- 다른 방법 
WITH tb AS(
    SELECT TO_CHAR(hireDate, 'YYYY') 년도, COUNT(*) 인원수
    FROM emp
    GROUP BY TO_CHAR(hireDate, 'YYYY')
) -- 테이블

SELECT 년도, 인원수
FROM tb
WHERE 인원수 = ( SELECT MAX(인원수) FROM tb );

-- 생일이 동일한 사람이 2명 이상인 경우
SELECT name, TO_DATE(SUBSTR(rrn,1,6), 'RRMMDD') birth
FROM emp
ORDER BY TO_CHAR(birth, 'MMDD');

-- TO_DATE는 숫자로 된 형식을 날짜로 바꾸는 것이고 
-- TO_CHAR는 날짜나 숫자로 된것을 문자로 바꾸는 것

SELECT SUBSTR(rrn, 3, 4), COUNT(*)
FROM emp
GROUP BY SUBSTR(rrn, 3, 4); 

SELECT SUBSTR(rrn, 3, 4), COUNT(*)
FROM emp
GROUP BY SUBSTR(rrn, 3, 4)
HAVING COUNT(*) >= 2;

SELECT name, TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD') birth
FROM emp
WHERE SUBSTR(rrn, 3, 4) IN (
    SELECT SUBSTR(rrn, 3, 4)
    FROM emp
    GROUP BY SUBSTR(rrn, 3, 4)
    HAVING COUNT(*) >= 2
)
ORDER BY TO_CHAR(birth, 'MMDD');
-- 여러개 있는건 IN으로 비교하자

GROUP BY절에는 컬럼만 SELECT에서 사용이 가능

ROLLUP - 2개의 표현식이 있으면 3레벨까지 표현 (잘 안씀)

  • 1 - 부서별 직위 2 - 부서 합계 3 - 전체 합계

ROLLUP 집계 예

표현식집계
GROUP BY ROLLUP(expr1, epxr2)expt1 + expr2
expr1
전체
GORUP BY expr1, ROLLUP(expr2, expr3)expr1 + (expr2 + epxr3)
expr1 + (expr2)
expr1
GROUP BY ROLLUP(expr1), epxr2expr2 + expr1
expr2

ROLLUP(expr2, epx3)을 하나의 컬럼으로 - 3레벨

  • ROLLUP 실습
    SELECT dept, pos, SUM(sal)
FROM emp
GROUP BY ROLLUP(dept,pos)
ORDER BY dept, pos;
    -- ROLLUP에 표현식이 2개 -> 3레벨 
    -- 부서 직위
    -- 부서 합계
    -- 전체 합계 
    
-- DEPT별 POS의 SAL 소계, DEPT별 소계 출력하여 마지막에 총계는 출력하지 않는다.
SELECT dept, pos, SUM(sal)
FROM emp
GROUP BY dept, ROLLUP(pos) -- pos의 대한 합계, 전체 합계 
ORDER BY dept, pos;

-- 부서별 인원수 및 마지막에 전체 인원수 출력
-- dept 전체인원수 

SELECT dept, COUNT(*)
FROM emp
GROUP BY ROLLUP(dept); -- 부서 합계 , 전체 합계 총 2레벨이 나옴 

-- dept 별 남자와 여자 인원수 및 dept 인원수, 전체 인원수 
-- dept 성별 인원
-- 개발부 여자 x
-- 개발부 남자 x 
SELECT dept, DECODE(MOD(SUBSTR(rrn, 8,1),2),1, '남자', '여자') 성별, COUNT(*)
FROM emp
GROUP BY dept, MOD(SUBSTR(rrn, 8,1),2)
ORDER BY dept;

SELECT dept, DECODE(MOD(SUBSTR(rrn, 8,1),2),1, '남자', 0, '여자') 성별, COUNT(*)
FROM emp
GROUP BY ROLLUP( dept, MOD(SUBSTR(rrn, 8,1),2))
ORDER BY dept;
    image.png

CUBE 절 - 결합 가능한 모든 값에 대하여 다차원 집계를 생성

  • ex) CUBE 절에서 3(n) 개의 표현식이 주어진 경우 2의 n = 2의 3승 = 8개의 그룹화가 이루어진다.
  • CUBE는 내부적으로 goruping columns의 순서를 바꾸어서 또 한 번의 Query를 추가 수행하고, 총계는 양쪽에 Query에서 모두 생성이 되므로 한 번의 Query에서는 제거되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많다.
  • CUBE의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROUULP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같다.
  • CUBE는 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬할 컬럼을 명시한다
표현식집계
GROUP BY CUBE(expr1, epxr2)expr1 + expr2
expr1
expr2
전체
GROUP BY expr1, CUBE(expr2, expr3)expr1 + (expr2 + expr3)
expr1 + (expr2)
expr1 + (expr3)
expr1
  • CUBE
    -- dept별 pos의 sal 소계, dept별 소계, pos별 소계, 마지막에 총계 출력 
SELECT dept, pos, SUM(sal)
FROM emp
GROUP BY CUBE(dept, pos)
ORDER BY dept, pos;

GROUPING 함수

  • GROUPING 함수는 ROLLUP이나 CUBE와 함께 사용되어 GROUPING 함수에 기술된 컬럼이 ROLLUP이나 CUBE 절에서 사용이 되었는지를 보여 주는 함수이다.
  • GROUPING 함수를 이용할 경우 출력되는 결과값 중 NULL값이 있다면 이 NULL값이 ROLLUP이나 CUBE 연산의 결과로 생성된 것인지, 원래 테이블상에 NULL값으로 저장된 것인지를 확인할 수 있다.
  • GROUPING(컬럼): 컬럼이 ROLLUP 연산에 참여 했으면 0, 참여하지 않았으면 1
  • 0값을 리턴 하는 경우, 해당 인수로 쓰인 값이 ROLLUP이나 CUBE에서 사용되어 졌음을 나타내는 것이고, 1값을 리턴 하는 경우 ROLLUP이나 CUBE에 사용되지 않았음을 나타낸다.
  • GROUPPING 실습
    SELECT dept, pos, GROUPING(dept), GROUPING(pos), TRUNC(AVG(sal))
FROM emp
GROUP BY ROLLUP(dept, pos);

SELECT dept, TRUNC(AVG(sal))
FROM emp
GROUP BY ROLLUP(dept, pos)
HAVING GROUPING(pos) = 1;

GROUP_ID() 함수

  • SELECT 문에서 GROUP BY로 분리되어 복제된 번호로 복제 횟수를 구분하도록 출력
  • 0부터 시작, n번 복제된 경우 n-1 번호 출력
  • GROUP BY의 확장으로 인하여 같은 grouping을 나타내는 값이 중복되어 결과 집합에 포함될 수 있으며, 이 떄 중복된 그룹들을 구별 하기위해 중복되어 나타나는 행에 대해여 GROUP_ID 함수는 0을 반환하고, 나머지 경우에 대하여 1의 값을 반환
  • 이 함수는 질의 결과에서 중복된 groupings를 필터링 하는데 유용
  • 0은 연산 1은 복사
  • GROUP_ID 실습
    SELECT dept, empNo, name, GROUP_ID(),SUM(sal)
FROM emp
GROUP BY ROLLUP (dept, (empNo, name))
ORDER BY dept;

SELECT dept, empNo, name, GROUP_ID(),SUM(sal)
FROM emp
GROUP BY dept, ROLLUP (dept, (empNo, name))
ORDER BY dept, GROUP_ID(), empNo;

SELECT dept, empNo,GROUP_ID(),
    DECODE(GROUP_ID() , 0, NVL(NAME, '합계'), '평균') name,
    DECODE(GROUP_ID(), 0, SUM(sal), ROUND(AVG(sal))) sal
FROM emp
GROUP BY dept, ROLLUP (dept, (empNo, name))
ORDER BY dept, GROUP_ID(), empNo;
profile
수호천사임다
이전 포스트

숫자 함수 24-09-09

다음 포스트

분석 함수 - 24.09.09

0개의 댓글