[MySQL] 함수, 조건문, 데이터 그룹화

 

1. 자주 쓰이는 수학 함수 (집계함수)

함수	설명	비고
COUNT	행의 수를 나타냄	- NULL값을 포함한 전체 행의 수: COUNT(*) - NULL값을 제외한 전체 행의 수: COUNT(행이름)  - 중복을 제외한 행의 수: COUNT(DISTINCT 행이름) - COUNT 함수는 데이터 검증용으로도 많이 사용됨  : 테이블 내에 NULL 값 및 중복된 값이 있는 경우 COUNT() 함수를 이용해서 쉽게 확인이 가능
SUM	행의 합계를 나타냄	- SUM(행이름) - 예시) SELECT COUNT(*) AS CNT FROM STUD_SCORE;  -------- | CNT | -------- |   8   |  --------
AVG	행의 평균을 나타냄	- AVG(행이름)
MAX	행의 최대값을 나타냄	- MAX(행이름)
MIN	행의 최소값을 나타냄	- MIN(행이름)
STDENV	행의 표준편차를 나타냄	- STDENV(행이름)
VARIANCE	행의 분산을 나타냄	- VARIANCE(행이름)

※ NOTE 1: 집계 함수를 사용하면 NULL 값은 계산에서 무시됨 

---

2. 조건문 

1) Syntax

SELECT <열이름>, 
    CASE WHEN [조건1] THEN [결과값1] 
    	 WHEN [조건2] THEN [결과값2] 
         ELSE [결과값3] 
    END AS <새로운 열 이름>

   (a) [조건1]을 만족하면 [결과값 1]을 보여주고, [조건2]를 만족하면 [결과값2]를 나타내며, 그 이외는 [결과값3]으로 보여준다. 이들의 결과값들은 <새로운 열 이름>으로 나타내진다. 
   (b) CASE WHEN은 GROUP BY와 함께 사용하면 효과적인 데이터 분석을 할 수 있음 

2) 예시

다음 표와 같이 연봉 인상이 직급, 영어 점수와 관련이 되는 경우 각 직원별 인상 연봉을 계산하라. 

(a) 연봉 인상률

JOB	영어 점수 80점 이상	영어 점수 80점 이하
CLERK	7%	6%
OFFICIER	5%	4%
MANAGER	3%	2%

(b) STAFF_SAL 테이블

ID	JOB	CURRENT_SALLARY	ENG_SCORE
2154	OFFICIER	40,000	90
5792	CLERK	32,000	50
6680	MANGAER	120,000	90

(C) MySQL 코드 

SELECT ID, JOB, CURRENT_SAL, ENG_SCORE, 
    CASE WHEN JOB = 'CLERK'    AND ENG_SCORE >= 80 THEN CURRENT_SAL * 1.07
         WHEN JOB = 'CLERK'    AND ENG_SCORE < 80  THEN CURRENT_SAL * 1.06
         WHEN JOB = 'OFFICIER' AND ENG_SCORE >= 80 THEN CURRENT_SAL * 1.05
         WHEN JOB = 'OFFICIER' AND ENG_SCORE < 80  THEN CURRENT_SAL * 1.04
         WHEN JOB = 'MANGAER'  AND ENG_SCORE >= 80 THEN CURRENT_SAL * 1.03
         WHEN JOB = 'MANGAER'  AND ENG_SCORE < 80  THEN CURRENT_SAL * 1.02
    END AS NEXT_SAL
FROM STAFF_SAL;

 

---

3. GROUP BY

1) 그룹화의 장점
   • 데이터를 논리적 집합으로 나누어서 데이터의 특성을 요약 가능 
   • 집단 간 차이를 좀 더 자세히 볼 수 있음 

2) Syntax

SELECT <그룹화할 열이름1>, <그룹화할 열 이름2>, <집계 함수>,... 
FROM <테이블 명>
WHERE <조건절>
GROUP BY <열이름1>, <열이름2>
ORDER BY <정렬할 열 이름1>;

 NOTE 1: GROUP BY는 WHERE 절 뒤에 위치하고 ORDER BY 앞에 항상 위치한다. 
 NOTE 2: 그룹화될 열에 NULL 값을 포함할 경우, NULL 값도 그룹화 된다. 

3) 예시
SCORE_TABLE에서 성별당 영어 성적과 수학 성적의 평균값을 계산하여라. 

(a) SCORE_TABLE 

NAME	ENG	MATH	GNEDER
Jone	28	49	MALE
Kim Yoona	68	98	MALE
Alice	87	87	FEMALE
Kong	30	27	FEMALE

(b) MySQL 코드 

SELECT GENDER, 
       AVG(ENG)  AS ENG_AVERAGE,
       AVG(MATH) AS MATH_AVERAGE
FROM CLASS_SCORE
GROUP BY GENDER;

 

- 결과

GENDER	ENG_AVERAGE	MATH_AVERAGE
MALE	48	73.6
FEMALE	76	82.5

- 집계함수만을 사용하는 경우

SELECT AVG(ENG) AS ENG_AVERAGE,
       AVG(MATH) AS MATH_AVERAGE
FROM CLASS_SCORE

ENG_AVERAGE	MATH_AVERAGE
62	78

 

---

4. GROUP BY/HAVING 

• HAVING은 WHERE의 조건절과 마찬가지로 조건을 주는 역활을 함 
• WHERE와의 차이점:
   (1) WHERE: 데이터가 그룹화 하기 전에 필터링함 (1차로 필터링)    
   (2) HAVING: 데이터가 그룹화를 한 후에 필터링함 (2차로 필터링)
     - WHERE절에서 이미 제외된 행들은 그룹화할 때에도 제외되기 때문에 HAVING절의 고려대상이 아님
• HAVING의 의미:
   (1) 특정한 테이블에서 특정한 조건을 만족하는 데이터를 추출한 후,
   (2) 특정한 조건을 만족한 그룹화된 열 및 집계함수를 나타내라. 

1) Syntax 

SELECT <그룹화할 열이름1>, <집계함수>
FROM <테이블 명>
WHERE <조건절>
GROUP BY <열이름1>
HAVING <집계함수 조건>
ORDER BY <정렬할 열이름>;

 

2) 예시
위의 예시에서 성이 MALE인 학생들의 영어 성적 평균값과 수학 성적 평균값을 나타내어라.

 (a) MySQL 코드 

SELECT GENDER, 
       AVG(ENG)  AS ENG_AVERAGE,
       AVG(MATH) AS MATH_AVERAGE
FROM CLASS_SCORE
GROUP BY GENDER
HAVING GENDER LIKE 'MALE';