카테고리 없음

[Database Programming] 4. 그룹함수, 데이터 그룹핑, HAVING

코딩펭귄 2024. 2. 19. 15:54

그룹함수

- 데이터의 요약이 필요할 때 사용

- 통계작업 시 많이 사용함

- 개수, 평균, 최소, 최대값, 합계구하기 총 5개 함수 제공

- 함수들의 파라미터 = (알고싶은(PK)) 컬럼

1. COUNT : 개수

- COUNT함수의 파라미터는 PK를 쓰는것이 원칙

ex) 전체 사원의 수 등등
2. MAX : 최댓값

-> 파라미터 : 숫자로 구성된 컬럼

3. MIN() : 최솟값

-> 파라미터 : 숫자로 구성된 컬럼

4. AVG : 평균값

ex) 전체 사원의 평균 연봉 등등

5. SUM : 합계

 

 

데이터 그룹핑

 -- 모든 사원들의 연봉총합, 최대연봉, 최소연봉, 평균연봉, 사원의수를 조회
 SELECT SUM(SALARY)
 	  , MAX(SALARY)
 	  , MIN(SALARY)
 	  , AVG(SALARY)
 	  , COUNT(EMPLOYEE_ID) --COUNT함수의 파라미터는 PK를 쓰는것이 원칙
 	  , MAX(HIRE_DATE) --가장 최근의 입사일자
 	  , MIN(HIRE_DATE) --가장 과거의 입사일자
FROM EMPLOYEES 
;

그룹함수는 혼자 쓰이지 않음. 항상 GROUP BY [분류기준 컬럼] 이 따라다님

-여러 개의 기준으로 분류하려면 분류 기준 COLUMN을 콤마(,)로 나열

         ↳ group by 가 테이블을 쪼개는 방식

 

주요함수, 유용한 문법들

1. TO_CHAR (DATE 타입의 날짜 값 혹은 컬럼, ‘날짜 포멧‘)

- 날짜타입의 데이터를 문자로 변경

2. TO_DATE (문자열타입의 날짜 값 혹은 컬럼, ‘날짜 포멧‘)

- 문자 타입의 날짜 데이터 -> 날짜 타입으로 변경

ABC아이콘 : 문자타입 / 시계아이콘 : 날짜(DATE)타입

 

그룹함수는 일반컬럼과 함께쓸수 없다!

   그룹함수(SUM, MAX ..)와 일반컬럼(DEPARTMENT_ID) 이 함께있으면 집계를 할 수 없다

SELECT DEPARTMENT_ID -- ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
	 , SUM(SALARY)
	 , MAX(SALARY)
	 , MAX(HIRE_DATE)
	 , MIN(HIRE_DATE)
	 , COUNT(EMPLOYEE_ID)
  FROM EMPLOYEES 
;

-> 그룹함수와 일반컬럼을 함께 쓰고싶다면, GROUP_BY로 일반컬럼 명시해주기!

--해결방법
SELECT DEPARTMENT_ID -- ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
	 , SUM(SALARY)
	 , MAX(SALARY)
	 , MAX(HIRE_DATE)
	 , MIN(HIRE_DATE)
	 , COUNT(EMPLOYEE_ID)
  FROM EMPLOYEES 
  GROUP BY DEPARTMENT_ID -- 단일 그룹의 그룹 함수가 아닙니다 에러가 나지 않음
;

- SELECT에만 일반컬럼 있고, GROUP BY에는 없다면 : 에러발생함- GROUP BY에 일반컬럼있고, SELECT에 없다면 : 에러발생하지 않음

 

ORDER BY가 없다면, GROUP BY는 제일 마지막에 온다! (단, 이 둘은 CPU를 너무많이 차지함. 사용지양)  ( 순서 )SELECTGROUP BYORDER BY

 

예시

-- 2004년에 입사한 사원들 중, 
-- '부서 번호별 사원들의 수' 와 평균연봉, 연봉의 총합, 부서번호를 조회한다
SELECT COUNT(EMPLOYEE_ID)
	 , AVG(SALARY)
	 , SUM(SALARY)
	 , DEPARTMENT_ID
  FROM EMPLOYEES 
 WHERE HIRE_DATE >= TO_DATE('2004-01-01', 'YYYY-MM-DD')
   AND HIRE_DATE <= TO_DATE('2004-12-31', 'YYYY-MM-DD')
 GROUP BY DEPARTMENT_ID
;

 

 

HAVING

FROM TABLE을 필터링해주는것 = WHERE

GROUP BY를 필터링해주는것 : HAVING

-- 사원정보에서 동일한 '성'이 두명 이상이 있는 사원들만 조회한다
-- "성"별 사원의 수, "성"
SELECT COUNT(EMPLOYEE_ID)
     , LAST_NAME
  FROM EMPLOYEES
 GROUP BY(LAST_NAME)
 HAVING COUNT(EMPLOYEE_ID) > 1
 ;

- HAVING : GROUP BY가 있을때 '만' 쓸 수 있음 (하지만, HAVING보다는 INLINE VIEW를 더 많이 쓰긴 함)

- 데이터 검증시 가끔 쓰임

 

 

서브쿼리

- SELECT쿼리 안의 SELECT쿼리 (WHERE에서 사용할 때)

 

SELECT col -> col  대신 쓰는 쿼리 : 스칼라 쿼리

   FROM table -> table 대신 쓰는 쿼리 : INLINE VIEW

WHERE col 연산자 값 -> 값 대신에 쓰는 쿼리 = 서브쿼리

 

-> 서브쿼리 활용한 풀이방법

 

-- SYB QUERY 작성 팁 : SUB QUERY를 먼저 작성할것! (우리가 알기위해 필요한 코드를 먼저 작성)
-- 문제 : 부서명 "IT" 부서에서 근무중인 사원들의 정보를 조회 (IT부서의 부서번호를 모르는 상황)
SELECT *
FROM EMPLOYEES 
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID 
				         FROM DEPARTMENTS 
 				 	     WHERE DEPARTMENT_NAME = 'IT')
;
-- 문제 : 도시명이 'Seattle'인 지역에서 근무중인 모든 사원들의 정보를 조회한다
-- 알아야할 정보1. :  도시명이 시에틀인 지역의 지역번호 - 1700
-- 알아야할 정보2. : 지역번호가 1700번인 부서번호 - 10,30,90,100, 110, 120, 130...
-- 알고싶은 정보 : 그 부서에서 근무하는 사원들의 번호

SELECT*
FROM EMPLOYEES 
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID 
						  FROM DEPARTMENTS 
						 WHERE LOCATION_ID = (SELECT LOCATION_ID
											    FROM LOCATIONS 
						 					   WHERE CITY = 'Seattle'))
;