Language/SQL

SQL 집계 함수

_빌런 2023. 9. 17. 15:53 
-- Aggregate Funtions: 집계 함수
-- https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

집계 함수(Aggregate function)란 입력이 여러 개의 행(row)이고, 출력이 하나인 결과를 말한다.

대표적인 집계 함수에는 합계(SUM), 최솟값(MIN), 최댓값(MAX) 등이 있다.

여러 개의 행(row)이 있을 때, 특정 조건에  만족하는 결과가 하나 나온다.

다른 MySQL의 집계 함수에 대해서 궁금하면 사이트를 더 참고하자. 

 └ https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

 

CREATE DATABASE book_shop;

CREATE TABLE books 
	(
		book_id INT AUTO_INCREMENT,
		title VARCHAR(100),
		author_fname VARCHAR(100),
		author_lname VARCHAR(100),
		released_year INT,
		stock_quantity INT,
		pages INT,
		PRIMARY KEY(book_id)
	);
 
INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343),
('10% Happier', 'Dan', 'Harris', 2014, 29, 256),
('fake_book', 'Freida', 'Harris', 2001, 287, 428),
('Lincoln In The Bardo', 'George', 'Saunders', 2017, 1000, 367);

이번 글에서 사용하는 테이블은 이전 글에서 사용한 테이블과 동일하다.

만약 데이터베이스나 테이블 데이터가 없다면 위의 코드를 사용하자.

 

SELECT COUNT(column_field) FROM table_name;

조건에 맞는 개수를 세는 COUNT 함수다.

집계 함수는 '여러 개의 행'이 입력이고, '출력이 하나'라고 했다.

아무런 조건 없이 기본 테이블에서 COUNT를 쓴다면 언제나 결과가 같다.

왜냐하면 column1이든 column2든 행 개수는 동일하기 때문이다.

 

-- Using COUNT
SELECT COUNT(*) FROM books;
SELECT title, COUNT(*) FROM books; -- ERROR

SELECT COUNT(author_fname) FROM books;
SELECT COUNT(DISTINCT author_fname) FROM books;

SELECT title FROM books WHERE title LIKE '%the%';
SELECT COUNT(title) FROM books WHERE title LIKE '%the%';

첫 번째 예시는 books의 열(row) 개수를 구한다.

두 번째 예시는 title과 COUNT(*)을 출력하려고 하면 error가 발생한다.

왜냐하면 COUNT(*)에서 이미 모든 데이터를 하나로 묶었다. 그렇기에 title로 열의 개수를 세려고 하면 오류가 생긴다.

세 번째 예시는 author_fname의 열 개수를 구한다. 이는 첫 번째 예시와 값이 같다.

author_fname에는 같은 이름이 여러 개 있을 수 있다. 네 번째 예시처럼 DISTINCT를 이용하면 중복을 없앨 수 있다.

여섯 번째 예시처럼 COUNT에서 와일드카드를 이용하여 개수를 셀 수도 있다.

 

SELECT (groupby_column or aggregate_function) FROM table_name GROUP BY column_field;

여러 개의 행을 하나로 묶는 GROUP BY 함수다.

다른 일반적인 함수와는 쓰임이 조금 달라, 자세하게 살펴보려고 한다. 아래의 표 예시를 보자.

 

title author_name pages
Le petit prince Saint-Exupery 200
The old man and the sea Ernest Hemingway 200
Demain Hermann Hesse 400
The great gatsby F. Scott Fitzgerald 400
Unterm rad Hermann Hesse 300

위와 같은 테이블이 있다고 가정해보자.

그럼 title로 묶든, author_name으로 묶든, pages로 묶든 할 수 있다. 순서대로 묶어보자.

 

Le petit prince Saint-Exupery 200
The old man and the sea Ernest Hemingway 200
Demain Hermann Hesse 400
The great gatsby F. Scott Fitzgerald 400
Unterm rad Hermann Hesse 300

GROUP BY title을 했을 때의 테이블 상황이다.

title은 중복된 데이터가 없기 때문에, 5개의 행이 그대로 유지된다.

 

Saint-Exupery Le petit prince 200
Ernest Hemingway The old man and the sea 200
Hermann Hesse Demain
Unterm rad		 400
300
F. Scott Fitzgerald The great gatsby 400

GROUP BY author_name을 했을 때의 테이블 상황이다. 

author_name는 헤르만 헤세가 두 권의 책을 적어 행이 4개가 됐다.

 

200 Le petit prince
The old man and the sea		 Saint-Exupery
Ernest Hemingway
400 Demain
The great gatsby		 Hermann Hesse
F. Scott Fitzgerald
300 Unterm rad Hermann Hesse

GROUP BY pages를 했을 때의 테이블 상황이다.

200 페이지 책과 400 페이지 책은 각각 2권씩 있어, 행이 3개가 됐다.

 

이렇게 GROUP BY으로 행을 묶어주면, SELECT로 선택할 때 제약이 생긴다.

GROUP BY에서 사용한 column이나 집계 함수만 사용이 가능하다.

예를 들어 pages로 묶은 테이블에서 title이나 author_name에 접근한다고 해보자.

300 페이지는 상관없지만, 200 페이지나 400 페이지에는 여러 데이터가 있다.

즉 어떤 title이나 어떤 author_name에 접근할지 DBSM 입장에서는 모른다는 것이다.

 

-- Using GROUP BY
-- Defination: GROUP BY summarizes or aggregates idnetical data into single rows
SELECT author_lname FROM books GROUP BY author_lname;
SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;
SELECT author_lname, COUNT(*) AS count FROM books GROUP BY author_lname;
SELECT author_lname, COUNT(*) AS count FROM books GROUP BY author_lname ORDER BY count;

SELECT released_year, COUNT(*) FROM books GROUP BY released_year;

첫 번째 예시는 author_lname으로 테이블을 묶고, author_lname 목록을 반환한다.

이 코드는 사실상 author_lname을 선택한 것과 다를 바가 없다. 하지만 테이블 내부에서는 그룹으로 나뉜 상태다.

두 번째 예시는 author_lname으로 테이블을 묶고, author_lname과 각 행 개수를 센다.

세 번째 예시는 두 번째 예시와 같지만, count의 field를 AS로 지정해준다.

네 번째 예시는 세 번째 예시에서 count 개수 오름차순으로 정렬한 뒤 반환한다.

다섯 번째 예시는 released_year로 테이블을 묶고, released_year와 그에 해당하는 각 행 개수를 센다.

 

-- Using multiple GROUP BY
SELECT author_fname, author_lname, COUNT(*)
FROM books
GROUP BY author_lname, author_fname;

SELECT CONCAT(author_fname, ' ', author_lname) AS author, COUNT(*)
FROM books
GROUP BY author;

GROUP BY에는 한 가지 이상의 값으로 묶는 것이 가능하다.

일차적으로 묶은 데이터 내부에서, 따로 데이터를 더 묶는 것이다.

ORDER BY처럼 AS로 선언한 별칭에 대해서 GROUP BY하는 것도 가능하다.

 

SELECT MIN(column_field) FROM table_name;
SELECT MAX(column_field) FROM table_name;

column_field에서 가장 작은 값(MIN) 혹은 가장 큰 값(MAX)을 반환하는 함수다.

 

-- Using MIN, MAX
SELECT MIN(released_year) FROM books;
SELECT MAX(pages) FROM books;
SELECT MIN(author_lname) FROM books;
SELECT MAX(author_lname) FROM books;

숫자 데이터는 당연히 대소 비교를 한 뒤, 값을 반환한다.

문자 데이터라면 글자 순으로 값을 반환한다. a에 가까울수록 작은 값이고, z에 가까울수록 큰 값이다.

 

-- Using MIN/MAX with GROUP BY
SELECT released_year, MAX(pages)
FROM books
GROUP BY released_year;

SELECT author_fname, author_lname, MIN(released_year)
FROM books
GROUP BY author_fname, author_lname;

MIN / MAX와 GROUP BY를 같이 사용할 수 있다.

COUNT 대신 MIN이나 MAX 함수를 사용하는 방법과 같다.

 

SELECT column_field
FROM table_name
WHERE condition = (SELECT column_field FROM table_name);

'서브 쿼리(sub query)'라는 기능이 있다.

쿼리(query) 안에서 쿼리(query)를 수행하는 기능으로, WHERE 값으로 또 다른 쿼리를 작성한다.

 

예를 들어서, condition에서 pages가 가장 큰 값을 찾고 싶어한다.

이때 pages의 최댓값을 모른다면? SELECT pages FROM books ORDER BY DESC로 값을 찾아야만 한다.

이때 WHERE 값으로 SELECT MAX(pages) FROM books를 넣어준다면, 바로 찾을 수 있다.

 

-- Using Sub Query
SELECT title, pages FROM books ORDER BY pages DESC LIMIT 1;
SELECT * FROM books
WHERE pages = (SELECT MAX(pages) FROM books);

SELECT title, released_year FROM books
WHERE released_year = (SELECT MIN(released_year) FROM books);

첫 번째 예시와 두 번째 예시는 모두 실행 결과가 같다.

첫 번째 예시는 pages를 역순 정렬한 다음, 위에서 한 행(row)만 잘라내어 title과 pages를 반환한다.

두 번째 예시는 pages에서 가장 큰 값을 찾아, 해당 값과 일치하는 pages 값만 반환한다.

세 번째 예시는 released_year에서 최솟값을 찾아 해당 값과 일치하는 title과 released_year를 반환한다.

 

SELECT SUM(column_field) FROM table_name;

MIN, MAX, COUNT 함수와 쓰임은 동일하다.

SUM은 합계를 구하는 함수다.

 

-- Using SUM
SELECT SUM(pages) FROM books;

SELECT author_fname, author_lname, SUM(pages)
FROM books
GROUP BY author_fname, author_lname;

첫 번째 예시는 books 테이블에서 pages 값을 모두 더하여 반환한다.

두 번째 예시는 author_fname, author_lname을 기준으로 묶어, 저자마다 pages 합계를 반환한다.

 

SELECT AVG(column_field) FROM table_name;

평균(average)의 약어인 AVG로, 주어진 데이터들의 평균을 구하는 함수다.

숫자 데이터의 평균은 구할 수 있지만, 문자 데이터의 평균은 구할 수 없다.

 

-- Using AVG
SELECT AVG(released_year) FROM books;
SELECT AVG(pages) FROM books;

SELECT released_year, AVG(stock_quantity), COUNT(*)
FROM books
GROUP BY released_year
ORDER BY released_year;

첫 번째 예시는 released_year의 평균을 반환한다.

두 번째 예시는 pages의 평균을 반환한다.

세 번째 예시는 released_year를 기준으로 데이터를 묶고, 오름차순 정렬을 한다.

그리고 released_year, 재고(stock_quantoty)의 평균, 행 개수를 반환한다.

 

-- Example
-- Q1
SELECT COUNT(*) FROM books;

-- Q2
SELECT released_year, COUNT(*) FROM books GROUP BY released_year;

-- Q3
SELECT SUM(stock_quantity) FROM books;

-- Q4
SELECT CONCAT(author_fname, ' ', author_lname) AS author, AVG(released_year)
FROM books GROUP BY author;

-- Q5
SELECT author_fname, author_lname, pages
FROM books
WHERE pages = (SELECT MAX(pages) FROM books);

-- Q6
SELECT
    released_year AS 'year',
    COUNT(*) AS '# books',
    AVG(pages) AS 'avg pages'
FROM books
GROUP BY released_year
ORDER BY released_year;

집계 함수를 사용한 다양한 활용 예제이다.

참고하여 집계 함수를 익숙하게 사용해보자.