MySQL 기초부터 훑어보기2(NUMBER, NULL, FEATURE연산 외)

 

 

https://dev.mysql.com/

 

 

 

안녕하십니까 다제입니다. 

오늘부터는 sql 문법에 대해서 공부를 진행하였습니다. 

 

 

오늘은 ③숫자함수, ④null처리, ⑤이상치처리 ⑥feature연산, ⑦조건문, ⑧고유값처리, ⑨문자열처리에 대해서 다루어보겠습니다. 

 

 

---

 

 

1. 여러 숫자 함수들 

 

1) row count 하기 

# 각 feature row확인 
SELECT COUNT(height) FROM member;
# 단, COUNT는 NULL을 제외하고 숫자를 센다. 

# 모든 row 확인
SELECT COUNT(*) FROM member;

 

2) feature에서 최대값 구하기 

SELECT MAX(weight) FROM main.member;

 

3) feature에서 최소값 구하기 

SELECT MIN(weight) FROM main.member;

 

4) feature의 평균 구하기

SELECT AVG(weight) FROM main.member;

 -. 만약 null값이 있다면 제외하고 평균값을 구하게 된다. 

 

5)  feature의 합계 구하기

SELECT SUM(weight) FROM main.member;

 

6) feature의 표준편차 구하기 

SELECT STD(weight) FROM main.member;

 

7) feature의 절대값 구하기

SELECT ABS(weight) FROM main.member;

 

8) feature의 제곱근 구하기

SELECT SQRT(weight) FROM main.member;

 

9) feature의 올림 구하기

SELECT CEIL(weight) FROM main.member;

 

10) feature의 내림 구하기

SELECT FLOOR(weight) FROM main.member;

 

11) feature의 반올림 구하기

SELECT ROUND(weight) FROM main.member;

* 기타 *

  -. 위에 열거된 함수들은 집계함수와 산술함수로 나눌 수 있다. 

  -. 집계합수 : 특정 feature의 여러 row의 값들을 동시에 고려해서 실행되는 함수 

  -. 산술함수 : 특정 feature의 각 row의 값마다 실행되는 함수 

 

 

 

---

 

 

2. NULL 다루는 방법 

1) 특정 feature로 NULL출력하기

SELECT * FROM member WHERE weight IS NULL;

 

2) 특정 feature로 NULL 제외 후 출력하기

SELECT * FROM member WHERE weight IS NOT NULL;

 

3) NULL을 치환하는 방법 - 다른 직군 사람들과 소통하기 위해서 

SELECT 
    COALESCE(height, "공백"),
    COALESCE(weight, "공백"),
    COALESCE(email, "공백"),
FROM main.member ;

 -. 위 코드는 COALESCE 다음에 내가 NULL값을 바꿔주고자 하는 feature의 이름을 기재 

 -. 해당 feature에 값이 있다면 그 값을 리턴해주고 값이 없다면 두번째 인자에 기재된 값으로 리턴해주는 코드 

 

 

 

---

 

 

3. 이상치 제거 

1) 특정 feature의 이상치 제거 후 평균으로 확인

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

 

 

 

 

---

 

 

 

4. feature끼리 연산

1) feature끼리 연산이 가능하며, 만약 연산에 이용되는 feature에 NULL이 있다면 NULL로 결과값이 출력이 된다. 

2) feature의 이름은 feature 옆에 별도로 기재하여 명명할 수 있다.

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

 

3) feature 합치기 (CONCAT)

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

 

 

5. feature 조건문

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

 -. pandas에서는 if를 사용하여 기재했었는데, 여기에서는 CASE END라는 개념을 사용해서 조건문 및 feature을 새롭게 만들더라구요 ( 엄청 신기했습니다. )

 -. 약간 파이썬, pandas, 머신러닝 학습 후 SQL을 학습하니 과거로 여행을 온 듯한 느낌을 받았습니다. 

 -. 코드 작성하는게 루비랑 비슷한 점이 보이더라구요.

 

 

 

---

 

 

 

6. 고유값만 보기 

1) feature에 대한 고유값 출력하기 

SELECT DISTINCT(gender) FROM main.member;

 -. pandas에서는 unique를 통해서 출력하였는데, SQL에서는 DISTINCT를 사용합니다. 

 

2) 주소에 대한 고유값 확인하기 

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

 -. SUBSTRING은 문자열을 추출하는 함수로써 address feature의 각 row에 있는 첫번째문자열부터 2글자 뽑아오라는 의미입니다. 

 -. 보통 주소는 전부 다르기 때문에 DISTINCT를 하여도 다 출력이 되기 때문에 큰 범위로 그룹화해주는 것라고 생각하시면 됩니다. 

 -. pandas에서는 이런걸 보지 못하였는데요. 다시 찾아봐도 SUBSTRING이라는 함수는 보이지 않았고, df['col'] = df['col'].str[:9]처럼 슬라이싱 하거나, split을 하여서 사용을 하네요 

 

3) 고유값 COUNT하기 

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

 

 

 

 

---

 

 

 

 

7. 문자열 관련된 함수들

7-1) LEHGTH - 길이 측정

SELECT LENGTH(address) FROM main.member;

 -. pandas와 동일함

 

7-2) UPPER, LOWER - 대,소문자 변경 

SELECT UPPER(address) FROM main.member;
SELECT LOWER(address) FROM main.member;

 -. pandas와 동일함 

 

7-3) LPAD, RPAD - 앞뒤 원하는 문자열로 채우주는 함수 

SELECT LPAD(gender, 8, "!") FROM main.member;
# 결과값 : !!!!male
SELECT RPAD(gender, 8, "!") FROM main.member;
# 결과값 : male!!!!

-. pandas에서 문자열을 채워주는 함수들은 아래 코드 및 출처 참조 

df['email_pad']    = df['email'].str.pad(width=20, side='left', fillchar='_') # 지정길이 패딩
df['email_center'] = df['email'].str.center(width=20, fillchar='_') # 중앙놓고 지정길이 패딩
df['email_ljust']  = df['email'].str.ljust(width=20, fillchar='_')  # 왼쪽놓고 지정길이 패딩
df['email_rjust']  = df['email'].str.rjust(width=20, fillchar='_')  # 오른쪽놓고 지정길이 패딩
df['email_zfill']  = df['email'].str.zfill(width=20) 

# 출처 :https://m.blog.naver.com/PostView.nhn?blogId=wideeyed&logNo=221603778414&proxyReferer=https:%2F%2Fwww.google.com%2F

 

 

7-4) 공백제거 함수 

# 왼쪽 공백 제거 
SELECT LTRIM(address) FROM main.member;

# 오른쪽 공백 제거
SELECT RTRIM(address) FROM main.member;

# 양쪽 공백 제거 
SELECT TRIM(address) FROM main.member;

 -. pandas에서는 strip이 이에 해당됨