TIL 53 | MySQL 데이터분석

CHAEIN·2021년 8월 28일
0

MySQL

목록 보기
3/8
post-custom-banner

집계함수

  • COUNT()
  • MAX()
  • MIN()
  • AVG()
  • STD()

COUNT(컬럼) 함수를 쓰면 해당 컬럼의 row개수를 알 수 있다.

SELECT COUNT(*) FROM member
# 컬럼에 row가 포함되어있으면 count되지 않기 때문에 전체 행을 세고 싶을 때는 와일드카드를 사용한다.

MAX(컬럼), MIN(컬럼), AVG(컬럼) 으로 해당 컬럼의 최소, 최대, 평균 값을 구할 수 있다. AVG함수는 평균을 구할 때 null이 포함된 row는 제외한다.

SELECT MAX(height) FROM member
SELECT MIN(weight) FROM member
SELECT AVG(weight) FROM member

SUM()함수를 이용하면 모든 값의 합을 구할 수 있다.

SELECT SUM(age) FROM member

STD()함수를 이용하면 표준 편차를 구할 수 있다.

SELECT STD(age) FROM member

산술함수

  • ABS() : 절대값
  • SQRT() : 제곱근
  • CEIL() : 올림
  • FLOOR() : 내림
  • ROUND() : 반올림

집계함수와 산술함수의 차이

집계함수는 특정 컬럼의 여러 row의 값들을 동시에 고려해서 실행되는 함수이고 산술함수는 특정컬럼의 각 row마다 실행되는 함수이다.

null 처리

1. COALESCE

Column 값이 null 인 경우를 제외하고 싶다면 COALESCE를 쓸 수 있다. COALESCE 함수는 인수들 중에서 NULL이 아닌 첫번째 값을 반환하는 함수이다. 만약 첫번째 값이 null이라면 그 다음으로 null이 아닌 값을 리턴한다.

SELECT
  COALESCE(height, '####'), #heigt 값이 null일 때 ####이 null 대신 출력
  COALESCE(weight, '---'), #weight 값이 null일 때 ---이 대신 출력
  COALESCE(address, '@@@') #address 값이 null일 때 @@@이 대신 출력
FROM 테이블

#활용 예제
SELECT COALESCE(height, weight*2.3, 'N/A') FROM member
#height가 null 이면 두번째 값이 출력, weight도 null이면 'N/A' 출력

2. IFNULL

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

SELECT IFNULL(height, 'N/A') FROM member

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

SELECT IF(height IS NOT NULL, height, 'N/A') FROM member

4. CASE

SELECT
  CASE
    WHEN height IS NOT NULL THEN height
    ELSE 'N/A'
FROM member

컬럼 산술 계산

+, -, *, /, % 연산자를 이용해 컬럼끼리 산술 계산을 할 수 있다. null 이 포함된 계산식의 결과는 항상 null 이다.

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

alias

AS를 사용하면 컬러명 또는 테이블명에 별칭을 붙일 수 있다.

SELECT weight/((height/100) * (height/100)) AS BMI
FROM member

#AS 를 쓰지 않고 띄어쓰기만 해주어도 alias가 적용된다.
SELECT weight/((height/100) * (height/100)) BMI
FROM member

만약 컬럼에 스페이스가 포함된 alias를 붙이고 싶다면 (ex.'상품 이름') 작은 따옴표나 큰 따옴표를 붙여 alias를 확실히 표현해주어야한다. 또한 SELECT 절에서 설정한 alias를 조건식에 변수처럼 재사용할 수 없다.

CONCAT()

CONCAT()은 여러개의 컬럼 값을 원하는 방식으로 조합하여 표현할 수 있도록 해준다.

SELECT CONCAT(height, 'cm', ', ', weight, 'kg') AS '키와 몸무게'
# ex) 160cm, 50kg 의 형태로 값들이 출력된다.
FROM member

CASE

CASE는 조건에 따라 값을 지정하여 CASE 컬럼에 출력한다. CASE 컬럼 또한 alias를 이용해 별칭을 지어줄 수 있다.

CASE 함수에는 크게 두가지가 있다.

1.단순 CASE 함수

CASE 문 바로 뒤에 컬럼 이름을 쓰고, 그 컬럼의 값과 어떤 값이 같은지(=)를 비교하는 CASE 함수를 단순 CASE 함수라고 한다.

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

#ex
SELECT age,
CASE age
  WHEN 29 THEN '스물 아홉 살'
  WHEN 30 THEN '서른 살'
  ELSE age
END

2.검색 CASE 함수

단순 CASE 함수에서는 등호 연산(=) 밖에 할 수 없다. 하지만 검색 CASE 함수에서는 사용자가 직접 원하는 대로 조건을 설정할 수 있기 때문에 좀더 다양한 형태의 조건을 걸 수 있다.

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

# ex)
SELECT 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.4
        AND weight/((height/100) * (height/100)) < 25
        THEN '정상'
    ELSE '저체중'
END) as obesity_check

FROM member

DISTINCT

중복값을 제외한 고유한 값만 출력한다. 만약 값으로 주어진 문자열의 일부만 보고싶다면 (ex. 주소의 경우 서울 마포구 ~~ 에서 도시만 확인하고 싶다면) Substring함수를 이용해 일부 문자만 고유값으로 추출할 수 있다.

SELECT DISTINCT(gender) FROM member
#address 컬럼값의 첫번째 글자부터 두글자만 추출해 고유한 갑산 출력하는 조건식
SELECT DISTINCT(SUBSTRING(address,1,2)) FROM member

추가적인 문자열 함수들

  • LENGTH(컬럼) : 문자열의 길이를 반환한다.
  • UPPER(컬럼) / LOWER(컬럼) : 대문자, 소문자로 바꾸어 보여주는 함수
  • LPAD(컬럼, 개수, 들어갈 문자) / RPAD(컬럼, 개수, 들어갈 문자) : 문자열의 왼쪽 또는 오른쪽을 특정 문자열로 채워주는 함수
  • LTRIM(컬럼) / RTRIM(컬럼) / TRIM(모두) : 왼쪽공백, 오른쪽 공백, 양쪽 공백을 제거하는 함수

GROUP BY

GROUP BY는 특정 컬럼을 그룹화하여 조회한다.

SELECT
  gender,
  COUNT(*),
  AVG(height),
  MIN(weight),
FROM member
GROUP BY gender

위에 예제는 아래와 같은 형태로 출력된다.

genderCOUNT(*)AVG(height)MIN(weight)
m15176.5556
f9173.748.2

SUBSTRING 함수를 이용해 특정 문자열을 그룹화하여 사용할 수도 있다.

SELECT
  SUBSTRING(address, 1, 2) as region,
  COUNT(*)
FROM member
GROUP BY SUBSTRING(address, 1, 2);
regionCOUNT(*)
경기6
제주4
....

그루핑 기준으로 복수의 컬럼을 사용할 수도 있다.

SELECT
  SUBSTRING(address, 1, 2) as region,
  gender,
  COUNT(*)
FROM member
GROUP BY SUBSTRING(address, 1, 2),
          gender
regiongenderCOUNT(*)
경기m2
경기f4
제주m2
제주f2
......

GROUP BY를 사용할 때는 주의해야할 두가지가 있다. 첫째, GROUP BY 뒤에 쓸 컬럼 이름들만 SELECT할 수 있다. 둘쨰, 대신 SELECT 절 뒤에서 집계 함수에 그 외의 컬럼 이름을 인자로 넣는 것은 허용된다. 위의 예제에서 age를 조회할 순 없지만 MAX(age)와 같이 집계함수를 이용하면 조회가 가능하다는 뜻이다.

또한, 그룹화로 조회된 데이터에서 특정 값만 보고 싶을 땐 HAVING으로 조건을 부여할 수 있다.

SELECT
  SUBSTRING(address, 1, 2) as region,
  gender,
  COUNT(*)
FROM member
GROUP BY SUBSTRING(address, 1, 2),
          gender
HAVING region = '서울'
      AND gender = 'm';

HAVING을 WHERE와 혼동할 수 있지만 둘의 사용 목적은 엄연히 다르다. WHERE는 row들을 조회할 때 사용하는 조건문이고 HAVING 그룹화된 데이터를 특정 조건에 따라 조회할 때 사용한다.

WITH ROLLUP

WITH ROLLUP은 그룹별 합계를 구할 떄 사용한다. 그룹 기준이 여러개면 가장 상위 그룹을 기준으로 순차적으로 합계를 구한다.

SELECT
  SUBSTRING(address, 1, 2) as region,
  gender,
  COUNT(*)
FROM member
GROUP BY SUBSTRING(address, 1, 2),
          gender
WITH ROLLUP
HAVING region = '서울'
      AND gender = 'm';
regiongendercount(*)
경기m2
경기f4
경기null6
제주m2
제주f2
제주null4

그룹별 상위 기준 region 을 기준으로 그룹별 합계를 구한 row가 추가되었다.

WITH ROLLUP은 그루핑 기준이 여러개일 때 점차적으로 넓은 범위의 부분총계를 나타낸다.

SELECT
  YEAR(birthday) AS b_year,
  YEAR(sign_up_day) AS s_year,
  gender,
  COUNT(*)
FROM member
GROUP BY YEAR(birthday),
        YEAR(sign_up_day),
        gender
WITH ROLLUP
ORDER BY b_year

위와 같이 예를 들었을 때 WITH ROLLUP은 b_year, s_year, gender 세 컬럼이 포함된 로우의 개수, b_year, s_year 두 컬럼이 포함된 개수, b_year이 포함된 로우의 개수를 구하면 점차적으로 넓은 범위의 부분 총계를 보여준다. 이 때문에 WITH ROLLUP으로 의도한 결과를 갖기 위해선 GROUP BY의 우선순위를 잘 설정해주어야한다.

b_years_yeargenderCOUNT(*)
19952017f1
19952017null1
19952017null2
19952018f1
19952018null1
19952019m1
19952019null1
1995nullnull4
19932014m1
19932014null1
19932018m1
19932018null1
1993nullnull2
nullnullnull6

WITH ROLLUP을 사용했을 때 문제는 ROLLUP 한 값이 아니라 원래의 값이 null 인 경우를 구분하지 못한다는 것이다. 그럴 때는 GROUPING 함수를 사용한다.

SELECT
  YEAR(birthday) AS b_year,
  YEAR(sign_up_day) AS s_year,
  gender,
  GROUPING(YEAR(birthday)),
  GROUPING(YEAR(sign_up_day)),
  GROUPING(gender),
  COUNT(*)
FROM member
GROUP BY YEAR(birthday),
        YEAR(sign_up_day),
        gender
WITH ROLLUP
ORDER BY b_year
b_years_yeargenderGROUPING(YEAR(birthday))GROUPING(YEAR(sign_up_day))GROUPING(gender)COUNT(*)
19952017f0001
19952017null0001
19952017null0012
19952018f0001
19952018null0011
19952019m0001
19952019null0011
1995nullnull0114
19932014m0001
19932014null0011
19932018m0001
19932018null0011
1993nullnull0112
nullnullnull1116

GROUPING 함수를 쓰면 각 컬럼별 null값이 실제 null인 경우 0, 부분총계를 나타내기 위한 null인 경우 1로 나타낸다.

profile
CHAEIN
이전 포스트

TIL 52 | MySQL 데이터 조회

다음 포스트

TIL 54 | MySQL 데이터분석_JOIN

post-custom-banner

0개의 댓글