[Codeit/MySQL] #4 데이터 분석 입문

집계 함수

~ 컬럼의 여러 row들을 대상으로 원하는 특징 값을 구해준다 (동시에 실행)

 

COUNT( ) -> row의 개수 구하기

SELECT COUNT(*) FROM copang_main.member;

~ 모든 row의 개수를 구함

SELECT COUNT(email) FROM copang_main.member;

~ null을 제외하고 해당 특성을 가진 row의 개수를 구함

 

MAX( ) -> 해당 속성 중 가장 큰 값 찾기

SELECT MAX(height) FROM copang_main.member;

MIN( ) -> 해당 속성 중 가장 작은 값 찾기

SELECT MIN(weight) FROM copang_main.member;

AVG( ) -> 해당 속성값의 평균 구하기

SELECT AVG(weight) FROM copang_main.member;

*null 값은 자동으로 제외된다

 

SUM( ) -> 해당 속성값들의 합계 구하기

SELECT SUM(age) FROM copang_main.member;

STD( ) -> 해당 속성값들의 표준편차 구하기

SELECT STD(age) FROM copang_main.member;

 

산술 함수

~ 컬럼의 각 row마다 단순한 산술 연산을 해준다 (각각 실행)

ABS( ) -> 절대값을 구한다

SQRT( ) -> 제곱근을 구한다

 

CEIL( ) -> 소수점에서 올린다

FLOOR( ) -> 소수점에서 내린다

ROUND( ) -> 반올림 한다

SELECT ROUND(height) FROM copang_main.member;

 

NULL을 다루는 방법

NULL: 값이 없음, 0이나 공백(‘ ‘)도 아님

선택 입력사항인 경우 null값이 많아진다

 

IS NULL -> ‘특정’ 컬럼에 null이 들어있는 row 조회하기

SELECT * FROM copang_main.member WHERE address IS NULL;

IS NOT NULL -> ‘특정’ 컬럼에 null이 들어있는 row를 제외하고 조회하기

SELECT * FROM copang_main.member WHERE address IS NOT NULL;

OR을 활용해서 ‘하나의 컬럼이라도’ null이 들어있는 row 조회하기

SELECT * FROM copang_main.member 
	WHERE height IS NULL
		OR weight IS NULL
        OR address IS NULL;

* COALESCE(A, ‘B’) -> A컬럼의 NULL을 B로 치환해서 띄울 수 있다

 

이상한 값을 제외하고 싶다면?

age컬럼에 음수가 있거나 지나치게 큰 값이 있는 경우..

SELECT AVG(age) FROM copang_main.member WHERE age BETWEEN 5 AND 100;

~이런 식으로 row를 추리고 평균을 구할 수 있다

 

address컬럼에 말도 안되는 주소들만 조회해보자

SELECT * FROM copang_main.member WHERE address NOT LIKE '%호';

이런 식으로 배운 조건을 다양하게 활용해서 이상한 값들을 제외하거나 조회하자!

..보통 회원가입 과정에서 이상한 데이터가 차단되긴 한다

 

컬럼끼리 계산하기

키와 몸무게를 사용해서 BMI를 구해보자 (BMI: 몸무게(kg) 나누기 키(m)의 제곱)

SELECT email, height, weight, weight / ((height/100) * (height/100)) FROM copang_main.member;

~ 컬럼끼리 산술 계산이 가능하다! 사칙연산, 나머지 연산(+ - * / %)

 

컬럼에 alias 붙이기, 결합하기

추가된 컬럼의 수식이 그대로 나와서 보기 안 좋다..

AS -> 컬럼의 별명(alias)을 붙인다

SELECT email,
	weight / ((height/100) * (height/100)) AS BMI
FROM copang_main.member;

*물론 추가된 컬럼이 아닌 기존 컬럼에도 별명을 붙일 수 있다

*alias에 띄어쓰기가 포함되어 있다면 작은따옴표(‘ ‘)로 묶어줘야 한다

 

AS 없이 스페이스를 하나 띄우고 써도 적용된다 (그래도 쓰는게 좋다)

SELECT email,
	weight / ((height/100) * (height/100)) BMI
FROM copang_main.member;

CONCAT -> 컬럼들을 묶을 수 있다

SELECT email,
	CONCAT (height, 'cm', ', ', weight, 'kg') AS '키와 몸무게',
	weight / ((height/100) * (height/100)) AS BMI
FROM copang_main.member;

 

컬럼의 값 변환해서 보기

 

CASE문에 익숙해져야 한다~

1. 일반 case문 -> 단순히 1대1 대응한다

2. 검색 case문 -> 조건에 따른 범위로 대응되어 활용성이 높다

만들어진 BMI 수치에 따라 case를 나누는 속성을 만들고 alias를 붙이고 정렬하자

SELECT 
	email,
	CONCAT (height, 'cm', ', ', weight, 'kg') AS '키와 몸무게',
	weight / ((height/100) * (height/100)) AS BMI,

(CASE
	WHEN weight IS NULL OR height is NULL THEN '비만 여부 알 수 없음'
    WHEN weight / ((height/100) * (height/100)) >= 25 THEN '과체중 또는 비만'
    WHEN weight / ((height/100) * (height/100)) >= 18.5 
    AND weight / ((height/100) * (height/100)) < 25
		THEN '정상'
	ELSE '저체중'
END) AS obesity_check
FROM copang_main.member
ORDER BY obesity_check ASC;

 

고유값만 보기

DISTINCT -> 중복되는 값을 제외하고 속성의 고유값만 보여준다

SELECT DISTINCT(gender) FROM copang_main.member;

SUBSTRING( )을 활용하면 문자열의 특정 부분만 뽑아서 고유값으로 처리할 수 있다

SELECT DISTINCT(SUBSTRING(address, 1, 2)) FROM copang_main.member;

~ 문자열의 1, 2번째 글자로 고유값을 뽑아낸다, 매우 유용하다!

COUNT(DISTINCT(gender)) 이런 식으로 고유값의 개수도 구할 수 있다

 

문자열 관련 함수들

LENGTH -> 문자열의 길이를 구해준다

SELECT LENGTH(address) FROM copang_main.member;

UPPER/LOWER -> 문자열의 대문자를 소문자로/소문자를 대문자로 바꾼다

 

LPAD/RPAD -> 문자열의 왼쪽/오른쪽을 특정 문자열로 채운다

SELECT email, LPAD(age, 10, '0') FROM copang_main.member;

~ age컬럼의 값을 왼쪽에 문자’0’을 붙여서 10자리로 만든다

 

LTRIM/RTRIM -> 문자열의 왼쪽/오른쪽 공백 삭제

TRIM -> 문자열의 왼쪽, 오른쪽 양쪽 다 공백 삭제

 

그루핑해서 보기

그루핑: row들을 그룹으로 나누는 것

GROUP BY -> 동일한 속성값을 기준으로 row들이 그룹화된다

SELECT gender FROM copang_main.member GROUP BY gender;

결과가 DISTINCT와 비슷해 보일 수 있지만 그룹화된 결과이다

각 그룹의 개수와 평균 키를 구해보자!

SELECT gender, COUNT(*), AVG(height) FROM copang_main.member GROUP BY gender;

집계함수들이 그루핑으로 나뉘어져 적용되는 것을 볼 수 있다..

여러 개의 컬럼을 한 번에 그루핑 할 수 있다!

SELECT 
	SUBSTRING(address, 1, 2) AS region,
    gender,
	COUNT(*)
FROM copang_main.member
GROUP BY 
	SUBSTRING(address, 1, 2),
    gender;

그루핑에 따라 조합되는 그룹이 더욱 세분화된다!

*이 경우 SELECT문에는 집계함수와 GROUP BY에 사용할 속성만 쓸 수 있다!!

 

HAVING을 통해 여기서 원하는 그룹만 또 추출할 수 있다 (활용성 높다!)

SELECT 
	SUBSTRING(address, 1, 2) AS region,
    gender,
	COUNT(*)
FROM copang_main.member
GROUP BY 
	SUBSTRING(address, 1, 2),
    gender
HAVING region = '서울';

HAVING region IS NOT NULL;

~ 이런 식으로 NULL 그룹을 제거 / ‘!=’로 특정한 값 제거도 가능

ORDER BY region ASC, gender DESC;

추가로 ORDER BY를 통해 정렬도 해주자!

심화) WITH ROLLUP 을 넣으면 하위 구분의 부분총계row도 추가된다

 

*현재까지 SELECT문의 작성 순서는 다음과 같다*

SELECT>FROM>WHERE>GROUP BY>HAVING>ORDER BY>LIMIT

실제 실행 순서는 다음과 같다

 

본 내용은 Codeit의 '개발자를 위한 SQL 데이터베이스'를 바탕으로 작성되었습니다.