SQL 실용함수들 살펴보기

-- 5~100세 사이 member들의 나이 평균 
SELECT AVG(age) FROM copang_main.member WHERE age BETWEEN 5 AND 100;

-- 끝자리에 호가 없는행 
SELECT * FROM copang_main.member WHERE address NOT LIKE '%호';

--COMMENT가 NULL이 아닌 ROW들로 평균 별점 구하기
SELECT COUNT(COMMENT) AS 'COUNT(*)', ROUND(AVG(star))
FROM review 
WHERE COMMENT IS NOT NULL;

-- 비만도 계산식
SELECT email, height, weight, weight / ((height/100) * (height/100))
FROM copang_main.member;

-- CONCAT을 사용하여 문장을 만듬, 컬럼표시를 AS로 커스텀
SELECT email, 
       CONCAT(height, 'cm', ', ', weight, 'kg') AS '키와 몸무게', 
       weight / ((height/100) * (height/100)) AS BMI
FROM copang_main.member;

-- CASE WHEN THEN 절로 상황에 맞게 다른값 가져오기
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;

-- 주소중에서 앞의 두글자만 가져옴 (서울, 인천)
-- 여러개의 ROW 중에서 중복되는 값을 없앰
SELECT DISTINCT(SUBSTRING(address, 1, 2)) FROM copang_main.member;

-- 글자 길이 
SELECT *, LENGTH(aaddress) FROM copang_main.member;

-- 대소문자
SELECT email, UPPER(email) FROM copang_main.member; (LOWER)

-- 특정문자 넣기
--  ex) 0000000028 (RPAD)
SELECT email, LPAD(age, 10, '0') FROM copang_main.member; 

-- 공백제거(LTRIM, RTRIM)
SELECT TRIM(word) FROM FROM copang_main.member;

-- GROUP BY 에 있는 컬럼들과 집계함수만 SELECT문에 올 수 있다.
SELECT
    SUBSTRING(address, 1, 2) as region,
    gender,
    COUNT(*)
FROM copang_main.member
GROUP BY
    SUBSTRING(address, 1, 2),
    gender
HAVING region IS NOT NULL
ORDER BY
    region ASC,
    gender DESC;