응용 SQL | 집계성 SQL

적자생존

 

Chapter 02 응용 SQL

1. 집계성 SQL ⭐⭐⭐

(1) 다중 행 연산자 - 2022.02 기출

- 다중 행 연산자는 서브 쿼리 결과가 여러 개 튜플 반환하는 다중 행 서브쿼리에서 사용되는 연산자

- IN, ANY, ALL, EXISTS 사용

- 단일 행 비교 연산자 (> , < , =, <>) 와 결합하여 사용 가능 

 

• 다중 행 연산자 종류 

1.  IN : 리턴되는 값 중 조건에 해당하는 값이 있으면 참 

2. ANY : 서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상 만족 시 참 

3. ALL : 값을 서브쿼리에 의해 리턴되는 모든 값과 조건 값을 비교하여 모든 값을 만족해야 참

4. EXISTS : 메인 쿼리 비교 조건이 서브 쿼리 결과 중 만족하는 값이 하나라도 존재하면 참

 

* ANY와 SOME은 같은 개념

 

 

[1] IN 연산자

 

[ DEPT 테이블 ]

DEPT_ID	DEPT_NAME	MRG_ID
10	개발팀	101
20	운영팀	106

 

[ EMP 테이블 ]

EMP_ID	EMP_NAME	DEPT_ID
101	김철수	10
102	홍길동	10
103	장보고	10
104	이순신	10
105	유관순	10
106	박영희	20
107	허준	20
108	정약용	20
109	스티브	20

 

[ 쿼리 ]

SELECT EMP_ID,
       EMP_NAME,
       DEPT_ID
 FROM EMP
WHERE EMP_ID IN
        (SELECT MGR_ID
           FROM DEPT);

• DEPT 테이블의 MGR_ID 칼럼에 해당하는 EMP 테이블 EMP_ID 행에서 EMP_ID, EMP_NAME, DEPT_ID 만 조회
• SELECT 절에서 EMP_ID, EMP_NAME, DEPT_ID 컬럼 조회 
• FROM 절에서 EMP 테이블 검색 
• WHERE 절에서 EMP_ID 컬럼에 대한 IN 조건을 다중 행 서브 쿼리로 정의
• 서브 쿼리에서 DEPT 테이블의 MGR_ID 값 조회
• 결론적으로 부서별 관리자의 ID, 이름, 부서 ID 정보 조회

[ 조회 결과 ]

EMP_ID	EMP_NAME	DEPT_ID
101	김철수	10
106	박영희	20

 

 

 

[2] ANY 연산자 

 

[ EMP 테이블 ]

EMP_ID	EMP_NAME	SALARY	JOB_TITLE
101	김철수	4500000	팀장
102	홍길동	4000000	과장
103	장보고	3500000	과장
104	이순신	3000000	사원
105	유관순	2500000	사원
106	박영희	5000000	팀장
107	허준	4000000	과장
108	정약용	3500000	과장
109	스티브	3000000	사원

 

[ 쿼리 ]

SELECT EMP_ID,
       EMP_NAME,
       SALARY,
       JOB_TITLE
 FROM  EMP A
WHERE SALARY > ANY
         ( SELECT SALARY
             FROM EMP
            WHERE JOB_TITLE='과장');

• EMP 테이블에서 연봉이 과장 직급과 비교했을 때, 한 명이라도 이상일 경우 EMP_ID, EMP_NAME, SALARY, JOB_TITLE 조회
• SELECT 절에서 EMP_ID, EMP_NAME, SALARY, JOB_TITLE 컬럼 조회
• FROM  절에서 EMP 테이블 검색
• WHERE 절에서 SALARY 컬럼에 대해 ANY 연산자를 활용한 다중 행 서브 쿼리(Multi Row Subquery)로 정의
• 서브쿼리에서 EMP 테이블 중 직급 컬럼이 과장인 직원의 SALARY 값 조회 
• 결론적으로 직책이 과장인 직원들보다 많은 급여를 받는 직원 정보 조회

[ 조회 결과 ]

EMP_ID	EMP_NAME	SALARY	JOB_TITLE
106	박영희	5000000	팀장
101	김철수	4500000	팀장
102	홍길동	4000000	과장
107	허준	4000000	과장

 

*** 과장 직급 중 가장 낮은 연봉이 3500000이므로 최솟값보다 크면 모두 포함 > 과장 직급도 포함

 

[3] ALL 연산자 

 

[ EMP 테이블 ]

A. EMP_ID	A.EMP_NAME	A.SALARY	JOB_TITLE
101	김철수	4500000	팀장
102	홍길동	4000000	과장
103	장보고	3500000	과장
104	이순신	3000000	사원
105	유관순	2500000	사원
106	박영희	5000000	팀장
107	허준	4000000	과장
108	정약용	3500000	과장
109	스티브	3000000	사원

 

[ 쿼리 ]

SELECT EMP_ID,
       EMP_NAME,
       SALARY,
       JOB_TITLE
  FROM EMP A
 WHERE SALARY > ALL
         (SELECT SALARY
            FROM EMP
           WHERE JOB_TITLE ='과장');

 

[ 조회 결과 ]

EMP_ID	EMP_NAME	SALARY	JOB_TITLE
101	김철수	4500000	팀장
106	박영희	5000000	팀장

• EMP 테이블에서 연봉 칼럼 데이터 값이 모두 직급이 과장인 사람의 연봉보다 높은 튜플 데이터 조회 
• SELECT 절에서 EMP_ID, EMP_NAME, SALARY, JOB_TITLE 조회
• FROM 절에서 EMP 테이블 검색
• WHERE 절에서 SALARY 컬럼에 대해 ALL 연산자 활용한 다중 행 서브쿼리 정의
• 서브쿼리에서 JOB_TITLE 컬럼이 과장인 직원 SALARY 값 조회 > 모든 서브쿼리 조회값보다 연봉이 큰 튜플 조회
• 결론적으로 직책이 과장인 직원 모두보다 더 많은 급여를 받는 직원 조회 

 

[4] EXISTS 연산자

 

[ EMP 테이블 ]

A.EMP_ID	A.EMP_NAME	A.SALARY	JOB_TITLE
101	김철수	4500000	팀장
102	홍길동	4000000	과장
103	장보고	3500000	과장
104	이순신	3000000	사원
105	유관순	2500000	사원
106	박영희	5000000	팀장
107	허준	4000000	과장
108	정약용	3500000	과장
109	스티브	3000000	사원

 

[ 쿼리 ]

SELECT A.EMP_ID,
       A.EMP_NAME,
       A.SALARY
 FROM EMP A
WHERE EXISTS
        (SELECT 1
           FROM EMP B
          WHERE A.SALARY = B.SALARY
           AND B.EMP_NAME='홍길동');

• EMP 테이블에서 홍길동과 같은 연봉을 받는 직원들 조회 
• SELECT 절에서 EMP_ID, EMP_NAME, SALARY 컬럼 조회 
• FROM 절에서 EMP 테이블 별칭 A로하여 검색
• WHERE 절에서 EXISTS 연산자 활용한 다중 행 서브쿼리 정의
• 서브 쿼리에서 메인 쿼리와의 관계를 SALARY 컬럼 등치조건으로 정의
• 결론적으로 홍길동과 동일 급여 받는 직원들을 조회 (홍길동 포함)

[ 조회 결과 ]

A.EMP_ID	A.EMP_NAME	A.SALARY
102	홍길동	4000000
107	허준	4000000

 

 

---

 

(2) 집계 함수 (Aggregate Function)

- 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수 

 

[1] 집계 함수 구문

SELECT 컬럼1, 컬럼2, ..., 집계함수
   FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, ...
[HAVING 조건식 (집계함수 포함)]

- WHERE 조건으로 지정된 데이터 집합으로부터 그룹화된 집합에 대한 조건 선택 시 HAVING 사용

- GROUP BY 구문 뒤에 구분 컬럼 기재해서 테이블 그룹화 

- HAVING 구문은 그룹화된 집합에 대한 조건 지정 시 사용 > 상수나 집약 함수, 집약 키 사용 가능

 

• GROUP BY 구문

- SQL은 WHERE 구문 활용 조건별 대상 ROW 선택

- 복수 ROW 대상 데이터 분석 시, 그룹핑 대상 선별 필요 > GROUP BY 

- NULL 값 ROW 제외하고 산출
- SELECT에서 사용하는 ALIAS 사용 불가 
- WHERE 구문에 포함 X
- WHERE 구문이 GROUP BY보다 먼저 실행, 그룹핑 대상이 되는 단일 행들을 사전에 선별하는 역할

 

- GROUP BY 구문은 실제 구체적 데이터 분석값 보고자 하는 컬럼 단위 선정 시 사용되는 기준 

- 조정을 통해 사용자가 원하는 분석 데이터 조회 가능

 

• HAVING 구문

- WHERE 구문에서 사용할 수 없는 집계 함수 구문 적용 > 복수 행 계산 결과 조건별 적용

- 일반적으로 GROUP BY 뒤에 기재, GROUP BY 구문 기준 항목이나 소그룹 집계 함수 활용 조건 적용에 사용

* HAVING 구문은 GROUP BY 및 집계 함수에 대한 WHERE 구문

 

 

[2] 집계 함수의 종류

 

- 집계 특성 상 숫자 유형 계산에 사용

→ MAX/MIN 또는 COUNT 등 문자열 유형 최대/최소나 건수 계산에도 사용

 

※ 집계 함수 계산 주의점 

NULL은 없는 데이터로 판단
▷ 이름 칼럼 값이 NULL, NULL, '홍길동' 있을 경우 COUNT(이름) 결괏값은 1

 

• COUNT : 복수 행 줄 수 반환
• SUM : 복수 행 해당 컬럼 값 합계 게산
• AVG : 복수 행 해당 컬럼 값 평균 계산
• MAX : 복수 행 해당 컬럼 값 중 최댓값
• MIN : 복수 행 해당 컬럼 값 중 최솟값 
• STDDEV : 복수 행 해당 컬럼 값 표준편차 계산
• VARIANCE : 복수 행 해당 컬럼 값 분산 계산 

 

[ 학생 테이블 ]

학생명	국어	영어
유리	100	80
철수	50	70
영식	80	90
미선	70	100

 

 

[ 쿼리 예시 ]

 

#1

SELECT COUNT(*)
  FROM STUDENT
 WHERE 국어>=80;

• 학생 테이블에서 국어 점수가 80점 이상인 학생 수 계산 > 유리와 영식 2명

COUNT(*)

2

 

 

#2 

SELECT SUM(국어), AVG(영어)
  FROM STUDENT;

• 학생 테이블에서 국어 점수 합, 영어 점수 평균 계산 > 300, 85

SUM(국어)	AVG(영어)
300	85

 

#3

SELECT MAX(국어), MIN(국어)
  FROM STUDENT;

• 학생 테이블에서 국어 점수 최고점과 최솟값 조회 > 100, 50

MAX(국어)	MIN(국어)
100	50

 

#4 

SELECT STDDEV(국어), VARIANCE(국어)
  FROM STUDENT;

• 학생 테이블에서 국어 점수 표준편차와 분산 계산 > 18.03, 325

STDDEV(국어)	VARIANCE(국어)
18.03	325