Aggregation Function
COUNT
특정 데이터가 몇 개 있는 지 검색할 수 있다.
SELECT COUNT(*) FROM books;
+----------+
| COUNT(*) |
+----------+
| 19 |
+----------+COUNT안에 column을 적어주면 되는데, column에 상관없이 모든 row를 count하겠다면 *을 쓰면 된다.
그러나, 모든 행을 뽑으면서 집계 함수인 COUNT를 쓸 수는 없다. 이건 row의 수가 일치하지 않기 때문이다.
SELECT title, COUNT(title) FROM books;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'book_shop.books.title'; this is incompatible with sql_mode=only_full_group_bytitle column의 row 갯수는 19개이지만 COUNT(title)은 1개이므로 매칭되지 않아 발생하는 에러인 것이다.
COUNT로 특정 row 갯수를 셀 때는 NULL값은 세지 않는다.
SELECT COUNT(author_fname) FROM books;
+---------------------+
| COUNT(author_fname) |
+---------------------+
| 19 |
+---------------------+현재는 author_fname이 19개 이지만, NULL값을 채운 row를 추가해보도록 하자.
DESC books;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| book_id | int | NO | PRI | NULL | auto_increment |
| title | varchar(100) | YES | | NULL | |
| author_fname | varchar(100) | YES | | NULL | |
| author_lname | varchar(100) | YES | | NULL | |
| released_year | int | YES | | NULL | |
| stock_quantity | int | YES | | NULL | |
| pages | int | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
INSERT INTO books() VALUES();
INSERT INTO books() VALUES();
SELECT COUNT(*) FROM books;
+----------+
| COUNT(*) |
+----------+
| 21 |
+----------+이전에 19개 row에 NULL값으로 된 row를 추가하니 21개가 되었다. 그러나 author_fname으로 COUNT를 하면 NULL값은 포함하지 않는다.
SELECT COUNT(author_fname) FROM books;
+---------------------+
| COUNT(author_fname) |
+---------------------+
| 19 |
+---------------------+만약, 고유한 author_fname의 갯수를 알고 싶다면 DISTINCT와 COUNT를 함께 쓸 수 있다.
SELECT COUNT(DISTINCT author_fname) FROM books;
+------------------------------+
| COUNT(DISTINCT author_fname) |
+------------------------------+
| 12 |
+------------------------------+고유한 author_fname을 가진 row는 19개 중에 12개가 있는 것이다.
LIKE와도 같이 사용할 수 있는데, 가령 title 중에 the가 들어가 있는 row의 갯수를 세고 싶다면 다음과 같이 쓸 수 있다.
SELECT title FROM books WHERE title LIKE '%the%';
+-------------------------------------------+
| title |
+-------------------------------------------+
| The Namesake |
| A Hologram for the King: A Novel |
| The Circle |
| The Amazing Adventures of Kavalier & Clay |
| Consider the Lobster |
| Lincoln In The Bardo |
+-------------------------------------------+
SELECT COUNT(title) FROM books WHERE title LIKE '%the%';
+--------------+
| COUNT(title) |
+--------------+
| 6 |
+--------------+6개가 정답인 것이다.
GROUP BY
SQL에서 가장 중요한 명령어인 GROUP BY이다.
SELECT <column>
FROM <table_name>
GROUP BY <column>;GROUP BY 뒤에 있는 column을 기준으로 group들을 만들어낸다. 단, 조심할 것은 SELECT에 GROUP BY에 쓰인 column이외에 다른 column은 쓸 수 없다. 따라서, GROUP BY로 만들고, 보통 각 GROUP에 대한 aggregation function을 사용한다.
가령 다음의 row들이 있다고 하자.
+-----------------------------------------------------+----------------+
| title | author_lname |
+-----------------------------------------------------+----------------+
| The Namesake | Lahiri |
| Norse Mythology | Gaiman |
| American Gods | Gaiman |
| Interpreter of Maladies | Lahiri |
| A Hologram for the King: A Novel | Eggers |
+-----------------------------------------------------+----------------+author_lname을 기준으로 GROUP BY를 시키면 다음과 같이 그룹화된다.
| The Namesake | Lahiri |
| Interpreter of Maladies | Lahiri |
| Norse Mythology | Gaiman |
| American Gods | Gaiman |
| A Hologram for the King: A Novel | Eggers |단 이 결과는 우리 눈으로 보이는 것이 아니라, GROUP BY 실행 시 이면에 보이는 결과로 메모리 상에 존재하는 방식이다.
그래서 각 group마다의 row 갯수를 세고 싶다면 COUNT를 통해서 가능하다.
SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;
+----------------+----------+
| author_lname | COUNT(*) |
+----------------+----------+
| Lahiri | 2 |
| Gaiman | 3 |
| Eggers | 3 |
| Chabon | 1 |
| Smith | 1 |
| Carver | 2 |
| DeLillo | 1 |
| Steinbeck | 1 |
| Foster Wallace | 2 |
| Harris | 2 |
| Saunders | 1 |
| NULL | 2 |
+----------------+----------+Lahiri이름을 가진 작가의 row는 2개라는 것이다.
정렬도 같이 사용할 수 있다.
SELECT author_lname, COUNT(*) AS books_written
FROM books
GROUP BY author_lname
ORDER BY books_written DESC;
+----------------+---------------+
| author_lname | books_written |
+----------------+---------------+
| Gaiman | 3 |
| Eggers | 3 |
| Lahiri | 2 |
| Carver | 2 |
| Foster Wallace | 2 |
| Harris | 2 |
| NULL | 2 |
| Chabon | 1 |
| Smith | 1 |
| DeLillo | 1 |
| Steinbeck | 1 |
| Saunders | 1 |
+----------------+---------------+만약 다음과 같이 GROUP BY에 쓰이지 않은 column을 SELECT에 쓰려고 한다면 error가 발생한다.
SELECT author_lname, title FROM books GROUP BY author_lname;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'book_shop.books.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by이는 GROUP BY에 쓰인 column인 author_lname 이외의 다른 column이 쓰여서 그렇다. 따라서, GROUP BY를 사용할 때는 column에 GROUP BY에 쓰인 column과 집계 함수인 COUNT, MIN, MAX 등을 사용해야한다.
MIN, MAX
row의 최소, 최대 값을 추출할 수 있다.
SELECT MIN(released_year) FROM books;
+--------------------+
| MIN(released_year) |
+--------------------+
| 1945 |
+--------------------+
SELECT MAX(pages) FROM books;
+------------+
| MAX(pages) |
+------------+
| 634 |
+------------+만약 정수가 아니라 알파벳과 같은 string을 입력하면 알파벳 순서로 MIN, MAX가 나온다.
SELECT MIN(author_lname), MAX(author_lname) FROM books;
+-------------------+-------------------+
| MIN(author_lname) | MAX(author_lname) |
+-------------------+-------------------+
| Carver | Steinbeck |
+-------------------+-------------------+그러면, 가장 긴 page를 갖는 책의 title이 무엇인지는 어떻게 알 수 있을까?
SELECT MAX(pages), title FROM books;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'book_shop.books.title'; this is incompatible with sql_mode=only_full_group_by이렇게쓰면 에러가 발생한다. sql은 books table에서 title을 가진 row들을 모두 가져올 것이다. 이에 반해 MAX(pages)는 단 하나의 row이므로 매치가 안되는 것이다.
재미난 방법이 하나 있는데 sub query를 사용하는 것이다. 이는 query문 안에 또 다른 작은 query를 두는 것인데, MIN, MAX와 같은 집계 함수를 사용하여 얻은 결과를 상위 query에 쓸 수 있는 것이다.
SELECT *
FROM books
WHERE pages=(
SELECT MIN(pages)
FROM books);WHERE에 sub query를 두고 pages에 sub query의 결과를 넘겨주는 것이다.
이와 같은 방식을 통해서 가장 많은 page를 가진 책의 이름을 알아낼 수 있다.
SELECT pages, title
FROM books
WHERE pages=(
SELECT MAX(pages)
FROM books
);
+-------+-------------------------------------------+
| pages | title |
+-------+-------------------------------------------+
| 634 | The Amazing Adventures of Kavalier & Clay |
+-------+-------------------------------------------+원하는 결과가 나왔다.
그런데 만약 두 개의 결과가 나온다면 어떨까?
INSERT INTO books(title, pages) VALUES('my life in words', 634);page 수가 634인 책을 추가하고 query를 해보도록 하자.
ELECT pages, title
-> FROM books
-> WHERE pages=(
-> SELECT MAX(pages)
-> FROM books
-> );
+-------+-------------------------------------------+
| pages | title |
+-------+-------------------------------------------+
| 634 | The Amazing Adventures of Kavalier & Clay |
| 634 | my life in words |
+-------+-------------------------------------------+결과가 두 개로 나온다.
sub query로 가장 예전에 출간된 책의 이름을 알아내도록 하자.
SELECT title, released_year
FROM books
WHERE released_year=(
SELECT MIN(released_year)
FROM books);
+-------------+---------------+
| title | released_year |
+-------------+---------------+
| Cannery Row | 1945 |
+-------------+---------------+다중 column GROUP BY
먼저 작가의 마지막 이름을 기준으로 정렬하여, 작가의 첫번째 이름, 마지막 이름을 출력하도록 하자.
SELECT author_fname, author_lname, title FROM books ORDER BY author_lname;
+--------------+----------------+-----------------------------------------------------+
| author_fname | author_lname | title |
+--------------+----------------+-----------------------------------------------------+
| NULL | NULL | my life in words |
| NULL | NULL | NULL |
| NULL | NULL | NULL |
| Raymond | Carver | Where I'm Calling From: Selected Stories |
| Raymond | Carver | What We Talk About When We Talk About Love: Stories |
| Michael | Chabon | The Amazing Adventures of Kavalier & Clay |
| Don | DeLillo | White Noise |
| Dave | Eggers | The Circle |
| Dave | Eggers | A Heartbreaking Work of Staggering Genius |
| Dave | Eggers | A Hologram for the King: A Novel |
| David | Foster Wallace | Consider the Lobster |
| David | Foster Wallace | Oblivion: Stories |
| Neil | Gaiman | Norse Mythology |
| Neil | Gaiman | American Gods |
| Neil | Gaiman | Coraline |
| Dan | Harris | 10% Happier |
| Freida | Harris | fake_book |
| Jhumpa | Lahiri | The Namesake |
| Jhumpa | Lahiri | Interpreter of Maladies |
| George | Saunders | Lincoln In The Bardo |
| Patti | Smith | Just Kids |
| John | Steinbeck | Cannery Row |
+--------------+----------------+-----------------------------------------------------+잘보면 Harris는 Dan Harris와 Freida Harris로 되어있는 것을 볼 수 있다. 즉, 두 명이라는 것이다.
문제는 author_lname 기준으로 그룹화하여 작가가 집필한 책의 갯수를 구하면 다음과 같은 결과가 나온다.
SELECT author_lname, COUNT(*)
FROM books
GROUP BY author_lname;
+----------------+----------+
| author_lname | COUNT(*) |
+----------------+----------+
| Lahiri | 2 |
| Gaiman | 3 |
| Eggers | 3 |
| Chabon | 1 |
| Smith | 1 |
| Carver | 2 |
| DeLillo | 1 |
| Steinbeck | 1 |
| Foster Wallace | 2 |
| Harris | 2 |
| Saunders | 1 |
| NULL | 3 |
+----------------+----------+Harris가 두 권이라고 써 있는 것이 보인다. 그러나 사실 Dan Harris, Freida Harris 두 명이 각각 한 권씩 집필한 것이다.
따라서, author_fname과 author_lname 기준으로 그룹을 만들었어야 하는 것이다.
SELECT author_fname,author_lname, COUNT(*)
FROM books
GROUP BY author_lname, author_fname;
+--------------+----------------+----------+
| author_fname | author_lname | COUNT(*) |
+--------------+----------------+----------+
| Jhumpa | Lahiri | 2 |
| Neil | Gaiman | 3 |
| Dave | Eggers | 3 |
| Michael | Chabon | 1 |
| Patti | Smith | 1 |
| Raymond | Carver | 2 |
| Don | DeLillo | 1 |
| John | Steinbeck | 1 |
| David | Foster Wallace | 2 |
| Dan | Harris | 1 |
| Freida | Harris | 1 |
| George | Saunders | 1 |
| NULL | NULL | 3 |
+--------------+----------------+----------+명확히 구분되어 나타난다.
sql이면에 memory 상에서는 다음과 같이 group이 만들어진 것이다.
| Dave | Eggers | The Circle |
| Dave | Eggers | A Heartbreaking Work of Staggering Genius |
| Dave | Eggers | A Hologram for the King: A Novel |
| Dan | Harris | 10% Happier |
| Freida | Harris | fake_book |Dave Eggers로 된 3개의 row는 하나의 그룹이고, Dan Harris, Freida Harris는 각각의 group이다.
또한, GROUP BY를 쓸 때 author_fname과 author_lname을 합쳐서 하나의 group을 만드는 방법도 있다.
SELECT CONCAT(author_fname, ' ', author_lname) AS author, COUNT(*)
FROM books
GROUP BY author;
+----------------------+----------+
| author | COUNT(*) |
+----------------------+----------+
| Jhumpa Lahiri | 2 |
| Neil Gaiman | 3 |
| Dave Eggers | 3 |
| Michael Chabon | 1 |
| Patti Smith | 1 |
| Raymond Carver | 2 |
| Don DeLillo | 1 |
| John Steinbeck | 1 |
| David Foster Wallace | 2 |
| Dan Harris | 1 |
| Freida Harris | 1 |
| George Saunders | 1 |
| NULL | 3 |
+----------------------+----------+CONCAT(author_fname, ' ', author_lname)로 두 개의 이름 column을 합쳐, author이라는 하나의 이름을 만들어낸 것이다. 이를 GROUP BY를 통해서 그룹화를 시키고 집계함수인 COUNT를 실행하면 fullname으로 해당 작가가 쓴 책들이 몇 권인지 알 수 있다.
SUM과 AVG
SUM은 말 그대로 모든 값들을 하나로 더해주어 결과를 반환하는 기능을 한다.
SELECT SUM(pages) FROM books;
+------------+
| SUM(pages) |
+------------+
| 7257 |
+------------+GROUP BY를 통해서 GROUP을 지정하여 사용할 수도 있다.
가령 작가의 마지막 이름으로 page들의 수를 계산하고 싶다면 다음과 같이 가능하다.
SELECT author_lname, SUM(pages) FROM books GROUP BY author_lname;
+----------------+------------+
| author_lname | SUM(pages) |
+----------------+------------+
| Lahiri | 489 |
| Gaiman | 977 |
| Eggers | 1293 |
| Chabon | 634 |
| Smith | 304 |
| Carver | 702 |
| DeLillo | 320 |
| Steinbeck | 181 |
| Foster Wallace | 672 |
| Harris | 684 |
| Saunders | 367 |
| NULL | 634 |
+----------------+------------+AVG는 해당 column에 해당하는 모든 값의 평균을 구한다.
SELECT AVG(pages) FROM books;
+------------+
| AVG(pages) |
+------------+
| 362.8500 |
+------------+GROUP BY도 같이 사용할 수 있다.
SELECT released_year, AVG(pages) FROM books GROUP BY released_year;
+---------------+------------+
| released_year | AVG(pages) |
+---------------+------------+
| 2003 | 249.5000 |
| 2016 | 304.0000 |
| 2001 | 443.3333 |
| 1996 | 198.0000 |
| 2012 | 352.0000 |
| 2013 | 504.0000 |
| 2000 | 634.0000 |
| 2010 | 304.0000 |
| 1981 | 176.0000 |
| 1989 | 526.0000 |
| 1985 | 320.0000 |
| 1945 | 181.0000 |
| 2004 | 329.0000 |
| 2005 | 343.0000 |
| 2014 | 256.0000 |
| 2017 | 367.0000 |
| NULL | 634.0000 |
+---------------+------------+각 년마다 나온 책들의 평균값을 구하는 방법이다.
