그룹 함수

DataBase+SQL

그룹 함수

campanula 2023. 6. 20. 16:34

날짜 형태 (YYYY.MM.DD)로 구하기

TO_CHAR(T_DATE, 'YYYY.MM.DD')
 
SELECT TO_CHAR(SUM(SALARY), '$999,999') AS TOTAL --999999는 자릿수
FROM EMPLOYEES;
 
 TO_CHAR: 변환함수, 날짜, 숫자-> 문자변화
 문자->문자 X

SUM	그룹의 누적 합계를 반환
AVG	그룹의 평균을 반환
MAX	그룹의 최댓값을 반환
MIN	그룹의 최솟값을 반환
COUNT	그룹의 총 갯수를 반환

SELECT TO_CHAR(SUM(SALARY), '$999,999') AS TOTAL --999999는 자릿수
FROM EMPLOYEES;

SELECT AVG(SALARY) FROM EMPLOYEES;
SELECT ROUND(AVG(SALARY),1) FROM EMPLOYEES; --소수점 한자리 표현
SELECT FLOOR(AVG(SALARY)) FROM EMPLOYEES;   --무조건 소수점자리 잘라낸다 FLOOR

SELECT TO_CHAR(MAX(HIRE_DATE), 'YYYY-MM-DD'), TO_CHAR(MIN(HIRE_DATE),'YYYY-MM-DD') FROM EMPLOYEES;

NULL을 저장한 칼럼과 연산=>NULL

BUT, 그룹함수는 다른 연산자와 달리, 해당 칼럼값이 NULL인 것을 제외하고 계산

그러므로, 결과:NULL (X)

그래서 로우(레코드) 개수 구하는 COUNT 함수는 NULL 값에 대해서 세지 X

SELECT COUNT(*), COUNT(EMPLOYEE_ID), COUNT(COMMISSION_PCT) 
--EMPLOYEE_ID: 기본키(NULL 허용X), COMMISSION_PCT(NULL 제외)
FROM EMPLOYEES;

SELECT COUNT(DISTINCT JOB_ID) FROM EMPLOYEES;
--DISTINCT: '중복을 제외한'

칼럼과 그룹 함수를 같이 사용할 때 유의 점

--칼럼과 그룹 함수를 같이 사용할 때 유의할 점
--결과:ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
--00937. 00000 -  "not a single-group group function"
--일반 칼럼과 그룹함수를 같이 쓸 때 오류
--레코드 107개, 작은것 1개 매칭 불가능
SELECT FIRST_NAME, MIN(SALARY) FROM EMPLOYEES;

GROUP BY 절을 사용해 특정 조건으로 세부적인 그룹화

--일반 칼럼명 기준
SELECT 칼럼명 그룹함수(칼럼명)
FROM 테이블명
WHERE 조건문
GROUP BY 칼럼명
------------------------------
SELECT 그룹함수(칼럼)
FROM 테이블;

SELECT DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

특정 그룹으로 묶어 데이터 집계 시 사용
WHERE와 ORDER BY절 사이에 위치
집계(그룹)함수와 함께 사용
SELECT 리스트에서 집계(그룹)함수를 제외한 모든 칼럼과 표현식은 GROUP BY 절에 명시

--부서별 최대 급여와 최소 급여 구하기
SELECT DEPARTMENT_ID, MAX(SALARY) "최대 급여", MIN(SALARY) "최소 급여"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

정렬 문장(ORDER BY)에 레코드가 2개 오면 첫번째 레코드를 기준으로 하고 동일 값이 있을 경우 다음 레코드를 기준으로 정렬

--부서별 DEPARTMENT_ID/ 같은 업무JOB_ID/를 담당하는 사원 수를 구하라
SELECT DEPARTMENT_ID, JOB_ID, COUNT(EMPLOYEE_ID)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID, JOB_ID;

SELECT DEPARTMENT_ID, TO_CHAR(MIN(HIRE_DATE), 'YYYY.MM.DD') "오래 근무한 사원의 입사일"
FROM EMPLOYEES
WHERE DEPARTMENT_ID='30'
GROUP BY DEPARTMENT_ID;
--WHERE 다음에 GROUP BY

HAVING 조건

SELECT 절에 조건을 사용하여 결과를 제한할 때는 WHERE 절을 사용하지만, 그룹의 결과를 제한할 때는 HAVING절 사용

GOUP BY절 다음에 위치해 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할
HAVING 다음에는 SELECT 리스트에 사용 했던 집계함수를 이용한 조건을 명시

EX) 평균을 구한 후 결과에 조건 부여

HAVING AVG(SALARY)>=5000

집합 연산자: 여러 개의 SELECT 문을 연결해 또 다른 하나의 쿼리를 만드는 역할을 하는 것

UNION ALL	각 쿼리의 결과 집합의 합집합.
UNION	각 쿼리의 결과 집합의 합집합이다. 중복된 행은 한 줄로 출력된다.
INTERSECT	각 쿼리의 결과 집합의 교집합이다. 중복된 행은 한 줄로 출력된다.
MINUS	앞에 있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합니다. 중복된 행은 한줄로 출력

1)UNION:합집합, 두 개의 데이터 집합이 있으면 각 집합 원소를 모두 포함한 결과가 반환, 중복된 것들은 한번만

SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '한국'
UNION
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '일본';

2) UNION ALL: 중복된 항목도 모두 조회

SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '한국'
UNION ALL
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '일본';

3)INTERSECT: 교집합. 데이터 집합에서 공통된 항목만 추출

SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '한국'
INTERSECT 
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '일본';

4)MINUS: 차집합, 한 데이터 집합을 기준으로 다른 데이터 집합과 공통된 항목을 제외한 결과 추출

SELECT GOODS 
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '한국'
MINUS
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY ='일본';

5) 집합 연산자의 제한 사항: 집합 연산자로 연결되는 각 SELECT문의 SELECT 리스트의 개수와 데이터 타입은 일치, 집합 연산자로 SELECT 문을 연결할 때 ORDER BY 절은 맨 마지막 문장에서만 사용

--ORA-01789: 질의 블록은 부정확한 수의 결과 열을 가지고 있습니다.
SELECT GOODS --1개
FROM EXP_GOODS_ASIA
WHERE COUNTRY='한국'
UNION
SELECT SEQ, GOODS --2개
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '일본';

--ORA-01790: 대응하는 식과 같은 데이터 유형이어야 합니다
SELECT SEQ --숫자
FROM EXP_GOODS_ASIA
WHERE COUNTRY ='한국'
UNION
SELECT GOODS --문자
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '일본';

--ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY = '한국'
ORDER BY GOODS
UNION 
SELECT GOODS
FROM EXP_GOODS_ASIA
WHERE COUNTRY='일본';

UNION 응용

-- 부서별 같은 업무(직무)를 담당하는 사원의 급여의 합과 사원수
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID;

-- 부서별 급여의 합과 사원수
SELECT DEPARTMENT_ID, NULL JOB_ID, COUNT(*), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

-- 전체 사원의 급여의 합과 사원수
SELECT NULL DEPARTMENT_ID, NULL JOB_ID, COUNT(*), SUM(SALARY)
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID, JOB_ID;

--집합 연산자로 표현
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
UNION ALL
SELECT DEPARTMENT_ID, NULL JOB_ID, COUNT(*), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
UNION ALL
SELECT NULL DEPARTMENT_ID, NULL JOB_ID, COUNT(*), SUM(SALARY)
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID, JOB_ID;;

ROLLUP
SELECT[조회하고자 하는 부분]
FROM[테이블]
WHERE[테이블에서 GROUP BY 진행전 필터링 조건]
GROUP BY ROLLUP(묶는 기준)->그룹별 결과 뿐만 아니라 총 집계도 해줌
HAVING[GROUP BY 결과 필터링할 조건]

CUBE(exp1, exp2,...): 소계와 전체 합계까지 출력하는 함수, 명시한 표현식 개수에 따라 가능한 모든 조합별로 집계한 결과 반환

SELECT DEPARTMENT_ID, JOB_ID, COUNT(*), SUM(SALARY)
FROM EMPLOYEES
GROUP BY CUBE(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID;

SELECT DEPARTMENT_NAME, COUNT(EMPLOYEE_ID)--DEPARTMENT_NAME이 1번, COUNT(EMPLOYEE_ID) 2번
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
USING(DEPARTMENT_ID)
GROUP BY D.DEPARTMENT_NAME
HAVING COUNT(EMPLOYEE_ID)>=5
ORDER BY 2 DESC;--SELECT 순서