[033] Aggregate Function(집계함수), Scalar Function(스칼라함수), Subquery(서브쿼리) / SQL 심화 - ③

Chapter
1. Aggregate Functions(집계함수)
(1) Count
(2) Sum
(3) Avg
(4) Min, Max
(5) Group By
(6) Having
2. Scalar Functions
(1) UPCASE
(2) LCASE
(3) MID
(4) LENGTH
(5) ROUND
(6) NOW
(7) FORMAT
3. SQL Subquery
(1) Scalar Subquery
(2) Inline View
(3) Nested Subquery

1. Aggregate Functions(집계함수)

집계함수란 여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수를 말한다. 다음과 같은 종류가 있다.

• COUNT : 총 갯수를 계산
• SUM : 합계 계산
• AVG : 평균 계산
• MIN : 최솟값 계산
• MAX : 최댓값 계산
• FIRST : 첫번째 결과값을 리턴
• LAST : 마지막 결과값을 리턴

앞에서 살펴본 crime_status테이블과 police_station테이블을 통해 예제를 몇 가지 살펴보자.

(1) Count

다음과 같은 문법을 통해서 함수값을 구할 수 있다.

SELECT COUNT(column)
FROM tablename
WHERE condition;

예제를 살펴보자.

• police_station 테이블의 칼럼의 개수

SELECT COUNT(*)
FROME police_station;

• crime_status에서 경찰서는 총 몇 군데인가

SELECT COUNT(DISTINCT police_station)
FROM crime_status;

• crime_status 테이블에서 crime_type은 총 몇 가지인가

select count(distinct crime_type)
from crime_status;

.
.

(2) Sum

다음과 같은 문법을 통해서 함수값을 구할 수 있다.

SELECT SUM(column)
FROM tablename
WHERE condition;

예제를 살펴보자.

• 범죄의 총 발생 건수

select sum(case_number) 
from crime_status
where status_type='발생';

• 살인의 총 발생 건수

select sum(case_number)
from crime_status
where crime_type='살인' and status_type='발생';

• 중부 경찰서에서 검거된 총 범죄 건수

select sum(case_number)
from crime_status
where police_station='중부' and status_type='검거';

.
.

(3) Avg

숫자 칼럼의 평균을 계산해주는 함수이다. 다음과 같이 사용할 수 있다.

SELECT AVG(column)
FROM tablename
WHERE condition;

예시를 살펴보자.

• 평균 폭력 검거 건수

select avg(case_number)
from crime_status
where crime_type like '폭력' and status_type like '검거';

• 중부경찰서 범죄 평균 발생 건수

select avg(case_number)
from crime_status
where police_station like '중부' and status_type like '발생';

• 살인의 평균 발생 건수

select avg(case_number)
from crime_status
where crime_type like '살인' and status_type like '발생';

• 구로경찰서와 도봉경찰서의 평균 살인 검거 건수

select avg(case_number)
from crime_status
where (police_station like '구로' or police_station like '도봉') and crime_type like '살인' and status_type like '검거';

(4) Min, Max

1) Min

가장 작은 값을 리턴해주는 함수이다. 다음과 같이 사용할 수 있다.

SELECT MIN(column)
FROM tablename
WHERE condition

예제를 살펴보자.

• 강도 발생 건수가 가장 적은 경우 몇 건인가

select min(case_number)
from crime_status
where crime_type like '강도' and status_type like '발생';

• 중부경찰서에서 가장 낮은 검거 건수는?

select min(case_number)
from crime_status
where police_station like '중부' and status_type like '검거';

• 광진경찰서에서 가장 낮은 범죄 검거 건수

select min(case_number)
from crime_status
where police_station like '광진' and status_type like '검거';

• 성북경찰서에서 가장 낮은 범죄 발생 건수

select min(case_number)
from crime_status
where police_station like '성북' and status_type like '발생';

2) Max

가장 큰 값을 리턴해주는 함수이다. 다음과 같이 사용할 수 있다.

SELECT MAX(column)
FROM tablename
WHERE condition

예제를 살펴보자.

• 살인이 가장 많이 검거된 건수

select max(case_number)
from crime_status
where crime_type like '살인' and status_type like '검거';

• 강남경찰서에서 가장 많이 발생한 범죄 건수

select max(case_number)
from crime_status
where police_station like '강남' and status_type like '발생';

• 영등포경찰서의 가장 높은 범죄 발생 건수

select max(case_number)
from crime_status
where police_station like '영등포' and status_type like '발생';

• 절도 검거가 가장 많은 건수

select max(case_number)
from crime_status
where crime_type like '절도' and status_type like '검거';

.
.

(5) Group By

그룹화하여 데이터를 조회하는 방법이다. 다음과 같이 사용한다.

SELECT col1, col2, ...
FROM tablename
GROUP BY col1, col2, ....
ORDER BY col1, col2, ...;

예제를 살펴보자.

• 경찰서별로 그룹화하여 경찰서 이름을 조회

select police_station
from crime_status
group by police_station
order by police_station;

DISTINCT를 사용하여 그룹화할 수도 있다. 단, 이때는 ORDER BY로 정렬을 시킬 수는 없다.

select distinct police_station
from crime_status;

• 경찰서별로 총 발생 범죄 검수

select police_tation, sum(case_number) 발생건수
from crime_status
where status_type like '발생'
group by police_station
order by 발생건수 desc;

• 경찰서별로 평균 범죄 검거 건수

select police_station, avg(case_number) 평균검거건수
from crime_status
where status_type like '검거'
group by police_station
order by 평균검거건수 desc;

• 경찰서별 평균 범죄 발생건수와 평균 범죄 검거 건수(상위 10개)

select police_station, status_type, avg(case_number)
from crime_status
group by police_station, status_type
limit 10;

.
.

(6) Having

조건에 집계함수가 포함되는 경우에 WHERE 대신 HAVING을 사용한다.

SELECT col1, col2, ...
FROM table
WHERE condition
GROUP BY col1, col2, ...
HAVING condition
ORDER BY col1, col2, ...;

예제를 살펴보자.

• 경찰서별로 발생한 범죄 건수의 합이 400건보다 큰 경우

select police_station, sum(case_number) count
from crime_status
where status_type like '발생'
group by police_station
having count > 4000;

• 경찰서별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우

select police_station, avg(case_number)
from crime_status
where (crime_type like '폭력' or crime_type like '절도')
        and status_type like '발생'
group by police_station
having avg(case_number) >= 2000;

• 경찰서별로 가장 적게 검거한 건수 중 4건보다 큰 경우를 건수가 큰 순으로 정렬

select police_station, min(case_number)
from crime_status
where status_type like '검거'
group by police_station
having min(case_number) > 4
order by min(case_number) desc;

.
.
.
.

2. Scalar Functions

Scalar function이란 입력값을 기준으로 단일 값을 반환하는 함수이다. 다음과 같은 종류가 있다.

• UPCASE : 영문을 대문자로 변환
• LCASE : 영문을 소문자로 변환
• MID : 문자열 부분을 반환
• LENGTH : 문자열의 길이를 반환
• ROUND : 지정한 자리에서 숫자를 반올림(0이 소수점 첫째 자리)
• NOW : 현재 날짜 및 시간을 반환
• FORMAT : 숫자를 천단위 콤마가 있는 형식으로 반환

테이블을 통해서 몇 가지 예제로 함수를 살펴보자.
예제로 살펴볼 함수는 sandwich 테이블과 oil_price테이블이다.

(1) UPCASE

영문을 대문자로 반환하는 함수이다.

• 문장을 대문자로 조회 해보자.

SELECT UPCASE ('This is ucase test.');

• 가격이 $15가 넘는 메뉴를 대문자로 조회

select ucase(menu)
from sandwich
where price >15;

.
.

(2) LCASE

영문을 소문자로 변환한다.

예제를 살펴보자.

• 문장을 소문자로 조회

select lcase('This is lcase test.');

• $5가 안되는 메뉴를 소문자로 조회

select lcase(menu)
from sandwich
where price < 5;

.
.

(3) MID

문자열을 반환하는 함수로 다음과 같이 사용할 수 있다.

select mid(string, start_position, lenth);

→ string: 원본 문자열
→ start: 문자열 반환 시작 위치. (첫글자는 1, 마지막 글자는 -1)
→ length: 반환할 문자열 길이

예제를 살펴보자.

• 1번 위치에서 4글자를 조회

select mid('This is mid test', 1, 4);

• -4번 위치(뒤에서 4번째 위치)에서 4글자 조회

select mid('This is mid test', -4, 4);

• 11위 카페이름 중 두번째 단어만 조회 (6번 위치에서 4글자)

select mid(cafe, 6,4)
from sandwich
where ranking = 11;

.
.

(4) LENGTH

문자열의 길이를 반환한다.

select length(string);

• 다음 문장의 길이를 조회해보자.

select length('this is len test');

몇 가지 살펴볼 점이 있다.

• 문자가 없는 경우 길이는 0을 반환한다.

select length('');

• 공백이 경우에는 문자이므로 길이가 1이다.

select length(' ');

• NULL의 경우는 길이가 없으므로 NULL이다.

select length(NULL);

• sandwich테이블에서 TOP3의 주소 길이를 검색

select length(address), address
from sandwich
where rangking <=3;

.
.

(5) ROUND

지정한 자리에서 숫자를 반올림하는 함수이다.

select round(number , 반올림할 소수점 위치);

예제를 통해 살펴보자.

• 반올림할 위치를 지정하지 않으면 소수점 자리 0에서 반올림한다.

select round(315.625);

• 소수점 첫번째 위치는 0이다.

select round(315.625, 0).

• 일단위 위치는 -1이다.

select round(315.625, -1);

• sandwich 테이블에서 소수점 자리는 반올림해서 1달러 단위까지만 표시(최하위 3개만 표시)

select ranking, price, round(price)
from sandwich
order by ranking desc
limit 3;

.
.

(6) NOW

현재 날짜 및 시간을 반환한다.

select now();

(7) FORMAT

숫자를 천단위 콤마가 있는 형식으로 반환

select format(number, 표시할 소수점 위치);

예제

• 소수점을 표시하지 않을 경우 0

select format(12345.6789, 0);

• 소수점 두자리까지 표시할 경우 2

select format(12345.6789, 2);

• oil_price 테이블에서 가격이 백원단위에서 반올림했을 때 2000원 이상인 경우 천원단위에 콤마를 넣어서 조회

select format(가격, 0) 
from oil_price
where round(가격, -3) >= 2000;
# format을 하면 string형식이 되므로 round로 수를 비교해야 한다.

.
.
.
.

3. SQL Subquery

하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다.
서브쿼리는 괄호로 묶어서 사용하는데, 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.
서브쿼리는 메인쿼리의 칼럼을 사용할 수 있지만, 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.
단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하며, 서브쿼리내에서는 order by를 사용할 수 없다.

세 가지 종류로 나눌 수 있는데, 종류에 따라 사용되는 위치가 다르다.

• Scalar Subquery : SELECT절에서 사용
• Inline View : FROM절에서 사용
• Nested Subquery(중첩 서브쿼리) : WHERE절에서 사용

(1) Scalar Subquery

SELECT절에서 사용하는 쿼리로, 결과는 하나의 컬럼이어여 한다.

SELECT col1, (SELELCT col2 FROM tabl2 WHERE condition)
FROM table1
WHERE condition;

예제를 살펴보자.

• 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회

# 서울은평경찰서의 강도 검거 건수
SELECT case_number
FROM crime_status
WHERE police_station LIKe '은평'
       AND crime_type LIKE '강도' 
       AND status_type LIKE '검거';

# 서울시 경찰서 전체의 평균 강도 검거 건수
SELECT avg(case_number)
FROM crime_status
WHERE crime_type like '강도' 
  AND status_type LIKE '검거';

나눠보면 이렇게 값을 구할 수 있는데, 여기서 두 번째 쿼리를 서브쿼리로 select문에 집어넣으면 값을 한 번에 출력할 수 있다.

# scalar subquery : 경찰서 전체의 평균 강도 검거 건수
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 '검거';

• oil_price에서 셀프주유소의 평균가격과, sk에너지의 가장 비싼 가격 조회

.
.

(2) Inline View

FROM절에서 사용하는 서브쿼리로, 메인쿼리에서는 인라인 뷰에서 조회한 컬럼만 사용가능 하다.

SELECT a.column, b.column
FROM table1 a, (SELECT column1, column2, ... FROM table2) b
WHERE condition;

예제를 통해서 살펴보자

• 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회

먼저 경찰서별로 가장 많이 발생한 범죄 건수를 조회해보자.

SELECT police_station, max(case_number) count
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station;

데이터를 이용해서 해당 범죄 유형까지 조회하고 싶다면, 서브쿼리를 이용해서 이 범죄 건수가 같은 범죄 건수의 유형을 찾아내면 한 번에 조회할 수 있다.

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;

• oil_price에서 상표별로 가장 비싼 가격과 상호

SELECT o.상호, o.가격, o.상표
FROM oil_price o,
     (SELECT MAX(가격) 최고가, 상표
      FROM oil_price
      GROUP BY 상표) m
WHERE m.최고가 = o.가격
   AND m.상표 = o.상표;

.
.

(3) Nested Subquery

WHERE절에서 사용하는 서브쿼리이며, 다음과 같이 나뉜다.
→ Single Row : 하나의 열을 검색하는 서브쿼리
→ Multiple Row : 하나 이상의 열을 검색하는 서브쿼리
→ Multiple Column : 하나 이상의 행을 검색하는 서브쿼리

1) Single Row Subquery

서브쿼리가 비교연산자와 사용되는 경우, 서브쿼리의 검색 결과는 한 개의 결과값을 가져야 한다.

SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_name
		        FROM table_name
		        WHERE condition)
ORDER BY column_name;

• snl에 출연한 연예인 중 id가 1인 연예인의 이름

select name 
from celeb
where name = (select host from snl_show where id=1);

2) Multiple ROW Subquery

① IN과 사용될 때

SELECT column_names
FROM table_name
WHERE column_name IN (SELECT column_name
		         FROM table_name
		         WHERE condition)
ORDER BY column_names;

• SNL에 출연한 영화배우 조회

SELECT host
FROM snl_show
WHERE host IN (SELECT name
	         FROM celeb
	         WHERE job_title LIKE '%영화배우%');

② EXISTS와 사용될 때
서브쿼리 결과에 값이 있으면 반환된다.

SELECT col_names
FROM table_name
WHERE EXISTS (SELECT column_name
	        FROM table_name
	        WHERE condition)
ORDER BY column_names;

• 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회

SELECT name
FROM police_station p
WHERE EXISTS (SELECT police_station
	        FROM crime_status c
	        WHERE p.name=c.reference AND case_number > 2000);

③ ANY와 사용
서브쿼리 결과 중에서 최소한 하나라도 만족하면 반환된다.

SELECT column_names
FROM table_name
WHERE column_name =ANY (SELECT column_name
			FROM table_name
			WHERE condition)
ORDER BY column_names;

• SNL에 출연한 적이 있는 연예인 이름 조회

SELECT name
FROM celeb
WHERE name = ANY (SELECT host FROM snl_show);

④ ALL과 사용
서브쿼리 결과를 모두 만족하면 반환한다.

SELECT column_names
FROM table_name
WHERE column_name = ALL (SELECT column_name
			 FROM table_name
			 WHERE condition)
ORDER BY column_names;

• SNL에 출연한 적이 있는 연예인 중 id가 1인 연예인

SELECT name
FROM celeb
WHERE name = ALL ( SELECT host
		    FROM snl_show
		    WHER id=1);

2) Multiple Column Subquery - 연관 서브쿼리

서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우이다.

SELECT column_names
FROM tablename a
WHERE (a.column1, a.column2, ... ) IN (SELECT b.column1, b.column2, ...
				FROM tablename b
				WHERE a.column_name = b.column_name)
ORDER BY column_names;

• 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회

SELECT name, sex, agency
FROM celeb
WHERE (sex, agency) IN (SELECT sex,agency FROM celeb WHERE name='강동원);