오랜만이네요.
여러모로 이것저것 바쁘다 보니 1월 말에 이어 2월 말에 이어서 4강을 이어가 봅니다.
워크샵 공부를 하다 보니 SQL 파트를 소홀히 하는 것 같아 다시 시작해봤습니다.
집합함수(Group functions)?
1) 종류 : SUM, AVG, MAX, MIN, COUNT
2) 데이터 형식 : SUM, AVG (오직 숫자), MAX, MIN, COUNT(문자, 숫자, 날짜 데이터 형식)
* LONG, CLOB 데이터 형식에서 MAX,MIN 사용불가!
cf) LONG : 최대 2GB 길이까지 가능한 문자 데이터 형식
CLOB : 최대 [(4GB-1)*DATABASE BLOCK SIZE] BYTE 길이까지 가능한 문자 데이터 형식
* 데이터 블록 사이즈는 2048, 4096, .... 32768 Byte 중 하나이다.
3) 고려해야할 사항 : 집합함수와 ALL, DISTINCT 키워드를 같이 사용할 수 있다. Default는 ALL
4) 집함함수 사용시 데이터가 없으면 (NULL 상태, 값이 0 인 경우가 아니다!!) 필드는 없다고 판단한다.
이때 NVL 함수를 사용한다.
집함함수 사용 예제는?
1) 입사일이 가장 오래된 날짜와 최근 날짜
SELECT MIN(hire_date), MAX(hire_date)
FROM hr.employees;
2) 전체 직원
SELECT COUNT(*)
FROM hr.employees;
3) 현재 직원이 근무하고 있는 부서의 갯수
SELECT COUNT(DISTINCT department_id)
FROM hr.employees;
4) 부서별 평균 임금
SELECT department_id, AVG(salary) AS "AVG_SAL_PER_DEPT" , COUNT(*) AS Persons
FROM hr.employees
GROUP BY department_id ;
5) 에러 상황
예제1)
SELECT department_id,job_id,AVG(salary)
FROM hr.employees
WHERE department_id in (10,20,30,40)
GROUP BY department_id ;
ORA-00979: not a GROUP BY expression (GROUP BY 표현식이 아니다.)
--> SELECT 절에서 집합함수와 같이 쓰인 컬럼중 하나인 JOB_ID가 누락되었기 때문에 생긴 에러이다.
예제2)
SELECT department_id, COUNT(last_name)
FROM hr.employees;
ORA-00937: not a single-group group function (GROUP BY 절이 누락되었다)
--> SELECT 절에서 집함함수와 같이 쓰인 컬럼인 department_id가 GROUP BY절에 기재되어야 한다.
예제3)
SELECT department_id,SUM(salary)
FROM hr.employees
WHERE SUM(salary) > 100000
GROUP BY department_id ;
ORA-00934: group function is not allowed here (GROUP BY절 자리가 아니다)
--> WHERE절에는 집함 함수를 사용할 수 없다.
--> HAVING 절을 사용한다.
SELECT department_id,SUM(salary)
FROM hr.employees
WHERE department_id = 60
GROUP BY department_id
HAVING SUM(salary) > 7000;
집합 함수는 중첩하여 사용가능?
예시)
SELECT job_id
,SUM(CASE department_id when 10 then salary end) "Dept 10"
,SUM(CASE department_id when 20 then salary end) "Dept 20"
,SUM(CASE department_id when 30 then salary end) "Dept 30"
,SUM(CASE department_id when 40 then salary end) "Dept 40"
,SUM(CASE department_id when 50 then salary end) "Dept 50"
,SUM(CASE department_id when 60 then salary end) "Dept 60"
,SUM(CASE department_id when 70 then salary end) "Dept 70"
,SUM(CASE department_id when 80 then salary end) "Dept 80"
,SUM(CASE department_id when 90 then salary end) "Dept 90"
,SUM(CASE department_id when 100 then salary end) "Dept100"
,SUM(CASE department_id when 110 then salary end) "Dept110"
,SUM(CASE when department_id is null then salary end) "NODEPT"
,SUM(salary) "Total"
FROM hr.employees
GROUP BY job_id ;
집합 함수는 꽤 많이 사용하는 함수입니다.
특히 집합 함수와 WHERE절 사용시에 문제점 그리고 이를 해결할 HAVING 절에 대한 구조를 잘 파악해야 합니다.
다음은 여러 테이블의 데이터를 조회하는 JOIN에 대해서 알아보겠습니다.
