mySQL 데이터 분석

2022-02-07

집계함수 (Aggregate Function)

어떤 컬럼의 값들을 대상으로 원하는 특징값을 구해주는 함수.
특정 컬럼의 여러 row의 값들을 동시에 고려해서 실행되는 함수.
그루핑을 통해 생성된 각 그룹의 수치적인 특성을 구하는 함수.

COUNT: null의 갯수는 제외하고 조회한다.

1 2	SELECT COUNT(email) FROM main.member; SELECT height FROM main.member;

*: 전체 row 수를 조회해준다.
1
SELECT COUNT(*) FROM main.member;
MAX: 최댓값을 구한다.
1
SELECT MAX(height) FROM main.member;
MIN: 최솟값을 구한다.
1
SELECT MIN(height) FROM main.member;
AVG: 평균값을 구한다. NULL은 제외하여 평군을 구함.
1
SELECT AVG(height) FROM main.member;
SUM: 합계 함수
STD: 표준편차 함수

산술함수 (Mathematical Function)

특정 컬럼의 각 row의 값마다 실행되는 함수

ABS: 절대값을 구하는 함수
SQRT: 제곱근을 구하는 함수
CEIL: 올림 함수
FLOOR: 내림 함수
ROUND: 반올림 함수
참고

NULL을 다루는 방법

NULL이 있는 데이터만 확인하고 싶은 경우

1	SELECT * FROM main.member WHERE address IS NULL;

NULL이 없는 데이터만 확인하고 싶은 경우

1	SELECT * FROM main.member WHERE address IS NOT NULL;

여러 속성중 특정 값 하나라도 NULL이 있는 데이터를 보고 싶은 경우

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

NULL을 다른 단어로 변경하고 싶은 경우
- 값이 있으면 그 값 그대로, NULL이면 두번째 인자가 출력된다.
  1
  2
  3
  4
  5
  SELECT
  COALESCE(height, '###'),
  COALESCE(weight, '---'),
  COALESCE(address, '@@@'),
  FROM main.member;

NULL에 대해 알아야 하는 사실

IS NULL과 =NULL은 다르다.
- NULL은 어떠한 값이 아니기 때문에 등호를 사용하여 비교할 수 있는 대상이 아니다. 그래서 TRUE일 수가 없다.
NULL은 어떤 연산을 해도 NULL이다.
컬럼끼리 계산할 때 하나라도 NULL이 있으면 결과는 NULL이다.

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

제대로 필터링하지 않으면 20, 30대 타겟인 데이터베이스에 음수, 300 등의 이상한 숫자가 생길 수 있다.
1
SELECT AVG(age) FROM main.member WHERE age BETWEEN 5 AND 100;

이상한 주소를 입력한 회원들이 있을 수 있다.

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

컬럼에 alias 붙이기

띄어쓰기(스페이스)가 포함된 alias에는 따옴표를 붙여주어야 한다. 그렇지 않으면 스페이스를 기준으로 구문 해석이 이루어지는 SQL 특성상 에러가 발생한다.

as

AS를 붙여 별칭을 붙인다.

AS를 생략하여 스페이스로 구분할 수 있지만, 가독성을 위해 as를 사용하는 것이 좋다.

SELECT
  email 이메일,
  height AS 키,
  weight AS 몸무게,
  weight / ((height/100) * (height/100)) AS BMI
FROM main.member;

concat

concatenate의 줄임말로 연결하다의 의미이다.

괄호 안에 있는 것들을 이어서 하나의 컬럼으로 만들어준다.

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

컬럼의 값 변환해서 보기

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 main.member
ORDER BY obesity_check ASC;

CASE 함수의 종류

단순 CASE 함수

CASE 문 바로 뒤에 컬럼 이름을 쓰고, 그 컬럼의 값과 어떤 값이 같은지(=)를 비교하는 CASE 함수.
ELSE 컬럼이름: 해당 컬럼에 있던 모든 값을 그대로 보여달라는 의미.

등호 연산(=)만 가능하다.

CASE 컬럼 이름 
  WHEN 값 THEN 값 
  WHEN 값 THEN 값
  WHEN 값 THEN 값
  ELSE 값
END

검색 CASE 함수

일단 TRUE인 조건을 만나게되면 거기에 있는 THEN 뒤의 값을 돌려주고, CASE 함수는 종료된다.

좀더 다양한 형태의 조건을 사용할 수 있다.

CASE
  WHEN 조건1 THEN 값
  WHEN 조건2 THEN 값 
  WHEN 조건3 THEN 값 
  ELSE 값
END

NULL을 다른 값으로 변환하는 다양한 함수

COALESCE 함수

1	SELECT COALESCE(height, 'N/A') FROM main.member;

1	SELECT COALESCE(height, weight * 2.3, 'N/A') FROM main.member;

IFNULL 함수

첫 번 인자가 NULL인 경우에는, 두 번째 인자를 표시하고 NULL이 아니면 해당 값을 그대로 표현

1	SELECT IFNULL(height, 'N/A') FROM main.member;

IF 함수

가장 첫 번째 인자로 어떤 조건식이 온다. 만약 그 조건식의 결과가 True라면 두 번째 인자를 리턴하고, False라면 세 번째 인자를 리턴한다.

1	SELECT IF(height IS NOT NULL, height, 'N/A') FROM main.member;

CASE 함수

SELECT
  CASE
    WHEN height IS NOT NULL THEN height
    ELSE 'N/A'
  END
FROM main.member;

고유값만 보기

DISTINCT: 고유한 값

1	SELECT DISTINCT(gender) FROM main.member;

SUBSTRING: 문자열의 일부를 추출하는 함수. 인수에는 몇번째 문자열부터 몇글자의 문자열을 추출할 것인가.
1
SELECT DISTINCT(SUBSTRING(address, 1, 2)) FROM main.member;

고유값 개수 구하기

COUNT 함수는 DISTINCT와도 함께 사용할 수 있다.

1	SELECT COUNT(DISTINCT(gender)) FROM main.member;

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

문자열 관련 함수들

LENGTH 함수

문자열의 길이를 구한다.

UPPER, LOWER 함수

UPPER: 문자열을 모두 대문자로 바꿔서 보여준다.
LOWER: 문자열을 모두 소문자로 바꿔서 보여준다.

LPAD, RPAD 함수

문자열의 왼쪽 혹은 오른쪽을 특정 문자열로 채워주는 함수.
숫자이더라도 문자열 함수 안에 인자로 넣어주면 자동으로 문자열로 형변환이 되어 계산됨.
LPAD: LEFT + padding
RPAD: RIGHT + padding
LPAD(age, 10, '0'): age 컬럼의 값을 왼쪽에 문자 9을 붙여서 총 10자리로 만들어라는 의미.
1
SELECT email, LPAD(age, 10, '0') FROM main.member;

TRIM, LTRIM, RTRIM 함수

문자열에 존재하는 공백을 제거하는 함수.
LTRIM: 왼쪽 공백 삭제
RTRIM: 오른쪽 공백 삭제
TRIM: 왼쪽, 오른쪽 양쪽 다 공백 삭제

그루핑해서 보기

GROUP BY를 사용하면 각 그룹에 대해서 각각 실행된다.

각각의 row는 하나의 그룹을 나타낸다.

1	SELECT gender FROM main.member GROUP BY gender;

COUNT 함수를 사용했을 때 그룹별 각각 조회한다.

1	SELECT gender, COUNT(*) FROM main.member GROUP BY gender;

SUBSTRING 함수와 함께 사용할 수 있다. → 지역별로 분리한다.

SELECT
  SUBSTRING(address, 1, 2) as regin,
  COUNT(*)
FROM main.member
GROUP BY SUBSTRING(address, 1, 2);

여러개의 컬럼을 그루핑할 수도 있다. → 같은 지역이라도 성별로 분리할 수도 있음.

SELECT
  SUBSTRING(address, 1, 2) as regin,
  COUNT(*)
FROM main.member
GROUP BY
  SUBSTRING(address, 1, 2),
  gender;

주요 그룹들만 보고 싶을 때 HAVING을 사용한다. → 서울 지역 남성을 보고 싶을 때

where: 테이블에서 맨 처음 row들을 조회할 때 조건을 설정하기 위한 구문
having: 이미 조회된 row들을 그룹핑했을 때 생성된 그룹들중에 다시 필터링을 할 때 사용하는 구문
1
2
3
4
5
6
7
8
9
10
SELECT
SUBSTRING(address, 1, 2) as regin,
COUNT(*)
FROM main.member
GROUP BY
SUBSTRING(address, 1, 2),
gender
HAVING
region = '서울'
AND GENDER = 'm';

NULL값도 노출하기 때문에 값이 있는 region값만 정렬하고 싶으면 IS NOT NULL을 사용한다.

SELECT
  SUBSTRING(address, 1, 2) as regin,
  COUNT(*)
FROM main.member
GROUP BY
  SUBSTRING(address, 1, 2),
  gender
HAVING region IS NOT NULL
ORDER BY
  region ASC,
  gender DESC;

GROUP BY를 쓸 때 지켜야 하는 규칙

SELECT절에는 GROUP BY 뒤에서 사용한 컬럼 또는 COUNT, MAX 같은 집계 함수만 사용할 수 있다.
반대로, GROUP BY 뒤에 쓰지 않은 컬럼 이름을 SELECT절 뒤에 사용할 수 없다.

오류 예시

SELECT SUBSTRING(address, 1, 2) AS region, gender, age, COUNT (*) FROM main.member
GROUP BY SUBSTRING(address, 1, 2), gender
HAVING region IS NOT NULL
ORDER BY region ASC, gender DESC;

정상 예시

SELECT SUBSTRING(address, 1, 2) AS region, gender, AVG(age), COUNT (*) FROM main.member
GROUP BY SUBSTRING(address, 1, 2), gender
HAVING region IS NOT NULL
ORDER BY region ASC, gender DESC;

WITH ROLLUP

세부 그룹들을 좀 더 큰 단위의 그룹으로 중간중간에 합쳐준다.

SELECT SUBSTRING(address, 1, 2) as region, gender, COUNT(*)
FROM main.member
GROUP BY SUBSTRING(address, 1, 2), gender WITH ROLLUP
HAVING region IS NOT NULL
ORDER BY region ASC, gender DESC;

GROUP BY 뒤 기준 순서에 따라 WITH ROLLUP의 결과도 달라진다.
NULL임을 나타내기 위해 쓰인 NULL vs. 부분 총계를 나타내기 위해 쓰인 NULL

SELECT문의 실행 순서

앞에 나와야 하는 순서

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

해석 및 실행되는 순서

FROM: 어느 테이블을 대상으로 할 것인지를 먼저 결정한다.
WHERE: 해당 테이블에서 특정 조건(들)을 만족하는 row들만 선별한다.
GROUP BY: row들을 그루핑 기준대로 그루핑된다. 하나의 그룹은 하나의 row로 표현한다.
HAVING: 그루핑 작업 후 생성된 여러 그룹들 중에서, 특정 조건(들)을 만족하는 그룹들만 선별한다.
SELECT: 모든 컬럼 또는 특정 컬럼들을 조회한다.
ORDER BY: 각 row를 특정 기준에 따라서 정렬한다.
LIMIT: 이전 단계까지 조회된 row들 중 일부 row들만을 추린다.

REFERENCE
코드잇 온라인 강의 개발자를 위한 SQL 데이터베이스