[데분스] Day+8(2). SQL - Aggregate Functions(집계함수), Scalar Functions(스칼라 함수), Subquery(서브쿼리)

1. Aggregate Functions(집계함수)

• Aggregate Functions(집계함수) : 여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수

집계 함수	설명
COUNT()	특정 컬럼의 행 개수를 반환합니다. NULL 값은 무시합니다.
SUM()	특정 컬럼의 총합을 반환합니다. NULL 값은 무시합니다.
AVG()	특정 컬럼의 평균값을 반환합니다. NULL 값은 무시합니다.
MIN()	특정 컬럼의 최솟값을 반환합니다. NULL 값은 무시합니다.
MAX()	특정 컬럼의 최댓값을 반환합니다. NULL 값은 무시합니다.
FIRST()	특정 컬럼의 첫 번째 행의 값을 반환합니다. NULL 값은 무시합니다.
LAST()	특정 컬럼의 마지막 행의 값을 반환합니다. NULL 값은 무시합니다.
GROUP_CONCAT()	특정 컬럼의 문자열을 연결하여 반환합니다. NULL 값은 무시합니다.
STD()	특정 컬럼의 표준 편차를 반환합니다. NULL 값은 무시합니다.
VAR_POP(), VAR_SAMP()	특정 컬럼의 모집단 분산과 표본 분산을 반환합니다. NULL 값은 무시합니다.

---

# COUNT
SELECT COUNT(DISTINCT police_station) FROM crime_status;

# SUM
SELECT SUM(case_number) FROM crime_status
	WHERE crime_type = '살인'
    AND status_type='발생';
    
# AVG
SELECT AVG(case_number) FROM crime_status
    WHERE crime_type LIKE '살인'
    AND status_type LIKE '발생';
    
# MIN
SELECT MIN(Case_number) FROM crime_status
    WHERE police_station LIKE '성북'
    AND status_type LIKE '발생';
    
# MAX
SELECT MAX(case_number) FROM crime_status
    WHERE crime_type = '절도'
    AND status_type = '검거';

---

# GROUP BY (DISTINCT 는 정렬 X)
SELECT police_station, MAX(case_number) FROM crime_status
    WHERE status_type LIKE '검거'
    GROUP BY police_station
    ORDER BY MAX(case_number)
    LIMIT 5;

# HAVING (조건에 '집계함수' 있을 때 GROUP BY절 아래, ORDER BY절 위에 작성)
SELECT police_station, AVG(case_number) 평균 FROM crime_status
    WHERE police_station LIKE '%대문'
    AND status_type LIKE '발생'
    GROUP BY police_station
    HAVING 평균 >= 500;
    # ORDER BY절

---

2. Scalar Functions(스칼라 함수)

• Scalar Functions(스칼라 함수) : 입력값을 기준으로 단일 값을 반환하는 함수

스칼라 함수	설명
UCASE() or UPPER()	문자열에서 모든 알파벳을 대문자로 변환합니다.
LCASE() or LOWER()	문자열에서 모든 알파벳을 소문자로 변환합니다.
MID() or SUBSTRING()	문자열의 특정 부분을 추출합니다.
LENGTH()	문자열의 길이를 반환합니다.
ROUND()	숫자를 가장 가까운 정수로 반올림합니다.
NOW()	현재의 날짜와 시간을 반환합니다.
FORMAT()	숫자를 지정된 형식에 따라 포맷팅합니다.
CONCAT()	두 개 이상의 문자열을 연결합니다.
COALESCE()	주어진 인자 중 첫 번째 NULL이 아닌 값을 반환합니다.
ABS()	숫자의 절대값을 반환합니다.
SQRT()	숫자의 제곱근을 반환합니다.

---

# UCASE
SELECT UCASE(Cafe) FROM sandwich;

# LCASE
SELECT LCASE(Menu) FROM sandwich;

# MID(문자열, 시작위치(1부터 -1), 길이)
SELECT `Rank`, Cafe, Menu, MID(Menu, -3, 3) FROM sandwich  
    WHERE `Rank` = 10;

# LENGTH(공백은 1, NULL은 NULL)
SELECT AVG(LENGTH(Menu)) FROM sandwich;

# ROUND(실수, 소수점표시(기본0=소수점첫째자리))
SELECT ROUND(Price*1000, -2) FROM sandwich;

# NOW
SELECT NOW();

# FORMAT(천 단위 표현할 숫자, 소수점표시)
SELECT FORMAT(Price*1000, 0) FROM sandwich  
    WHERE ROUND(Price*1000, -2) >= 2000;

---

3. Subquery(서브쿼리)

• Subquery(서브쿼리) :
  하나의 SQL문 안에 포함되어 있는 또 다른 SQL문!
  서브쿼리는 메인쿼리 컬럼을 사용할 수 있지만,
  메인쿼리는 서브쿼리 컬럼을 사용할 수 없다!
  ◾ 스칼라 서브쿼리-SELECT 절
  ◾ 인라인 뷰-FROM 절
  ◾ 중첩 서브쿼리(Single Row / Multiple Row / Multiple Column)-WHERE 절

# 스칼라 서브쿼리
SELECT case_number,
(SELECT AVG(case_number) FROM crime_status
	WHERE crime_type LIKE '강도'
    AND status_type LIKE '검거') AVG
FROM crime_status
	WHERE police_station LIKE '은평'
    AND crime_type LIKE '강도'
    AND status_type LIKE '검거';

# 인라인 뷰
SELECT c.police_station, c.crime_type, c.case_number
FROM crime_status c,
	# 경찰서 별 가장 많은 범죄 건수
    (SELECT police_station, max(case_number) COUNT FROM crime_status
    	WHERE status_type LIKE '발생'
    	GROUP BY police_station) m
    WHERE c.police_station = m.police_station
    AND c.case_number = m.count;

# 중첩 서브쿼리
## single row : 단일행 결과값
SELECT name FROM celeb
	WHERE name = (SELECT host FROM snl_show WHERE id = 1);

## multiple row : 한 개 이상의 행 결과값(IN/EXISTS/ANY/ALL 사용)
SELECT host FROM snl_show
	WHERE host IN (SELECT name FROM celeb
    				WHERE JOB_TITLE LIKE '%영화배우%');

## multiple column : 서브쿼리에서 메인쿼리 컬럼(열)을 같이 사용
SELECT name, sex, agency FROM celeb
    WHERE (sex, agency) IN (SELECT sex, agency FROM celeb
    						WHERE name = '강동원');

---