[Database]내장 함수, 집계 함수와 GROUP BY절(MySQL)

*MySQL API

-> 앞서 제시한 데이터 타입, 혹은 기존에 익숙한 프로그래밍 언어와 마찬가지로 MySQL의 자세한 문법이나 사용을 보고 싶으면 API문서를 참고하는 습관을 들이도록 하자. 내장 함수에 있어서도 마찬가지이다.

dev.mysql.com/doc/refman/8.0/en/functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12 Functions and Operators

*더미 테이블

-> SQL에는 보통 테이블이 존재 하지 않지만, 테이블을 표시하고 싶은 경우(예를 들면 산술 연산에 대한 결과등을 테이블 형태로 출력), 참조를 위한 가상의 테이블을 제공해준다. 이것을 더미 테이블이라고 부르며, 보통 dual이라는 이름의 형태로 존재한다.

-- 더미 테이블 dual
select 1 + 1
from dual;

-- 테이블 이름을 아예 참조하지 않아도 무관하다.
select 1 + 1;

 

*알아두면 편리한 내장 함수들

-- 절대값
select abs(-7), abs(0), abs(3);

-- 올림(정수 기준)
select ceil(1.24);

-- 내림(정수 기준)
select floor(1.24);

-- 반올림(대상 수, 기준 자릿수) : 기준 자릿수는 1의 자릿수를 기준으로 왼쪽은 -, 오른쪽은 +로 자릿수가 늘어난다.
-- Ex : 100의 자리에 대하여 반올림
select round(153.1254, -2);

-- Ex : 소숫점 아래 2번째 자리에 대하여 반올림
select round(153.1254, 2);

-- Ex : 1의 자리에 대하여 반올림
select round(153.1254);

-- 모든 문자를 소문자로
select lcase("SElect");

-- 모든 문자를 대문자로
select ucase("SElect");

-- 현재 날짜
select now();

-- 날짜 형식 지정(연, 월, 일, 요일, 시, 분, 초).
select date_format(now(), "%Y.%M.%D %W %H:%I:%S");
select date_format(now(), "%y.%m.%d %w %h:%i:%s");

 

*MySQL Sample Database

-> 아래 데이터베이스는 MySQL에서 기본적으로 제공해주는 연습용 SQL 테이블 데이터이다. 해당 데이터를 이용하여 쿼리를 연습해보도록 하겠다.

www.mysqltutorial.org/mysql-sample-database.aspx/

MySQL Sample Database

 

*집계 함수(Aggregate Function)

-> 집계 함수란, 여러행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수이다. 보통 GROUP BY절과 함께 사용되는 경우가 많다.

-- 검색된 행의 개수를 출력
select count(*)
from student;

-- 학생 성적의 총합
select sum(grade)
from student;

-- 학생 성적의 평균
select avg(grade)
from student;

-- 학생 성적 중 최고 성적
select max(grade)
from student;

-- 학생 성적 중 최저 성적
select min(grade)
from student;

 

*집계 함수(Aggregation Function)와 GROUP BY절

-> 집계 함수란, 여러행 또는 테이블 전체 행으로부터 하나의 결과를 반환하는 함수라고 하였다. 예를 들어 고객 테이블에서 같은 주에 속하는 고객들의 평균 신용 한도를 알아보고 싶다고 가정하자. 이때 다음과 같은 쿼리를 수행하면 문제가 발생한다.

-- 오류 코드
select state, avg(creditLimit)
from customers;

 왜냐하면 avg(creditLimit)에 대한 쿼리의 수행 결과는 전체 테이블에 대하여 수행이 되어 1개의 행을 반환하지만, state 열은 여러개의 결과를 반환하기 때문이다.

-> 이러한 문제를 해결하기 위하여, 집계함수를 전체 테이블에 한 번에 적용하는 것이 아닌 지정한 그룹마다 집계함수를 구하기 위한 방법이 필요하고 이 방법이 GROUP BY절을 활용하는 것이다.

-- 같은 주에 속하는 고객들의 평균 신용 한도
select state, avg(creditLimit)
from customers
group by state
order by state;

 

-- 각 주문별 최고 판매액과 최저 판매액
select orderNumber, max(priceEach), min(priceEach)
from orderdetails
group by orderNumber;

 

*HAVING절

-> GROUP BY절에 조건에 맞는 대상만 그루핑하고 싶은 경우 HAVING절을 사용하면 된다. WHERE절 대신 HAVING절을 사용하는 이유는, WHERE절이 GROUP BY절보다 먼저 수행되기 때문에 GROUP BY절의 조건은 따로 고려해주는 것이다.

-- 100 ~ 150번 고객 중 같은 주에 속하는 고객들의 평균 신용 한도(단, 평균 신용한도가 50000이상인 지역만)
select state, avg(creditLimit) -- 5
from customers -- 실행 순서 1
where customerNumber between 100 and 150 -- 2
group by state -- 3
having avg(creditLimit) > 50000 -- 4
order by avg(creditLimit); -- 6