[마케터를 위한 SQL #3] GROUP BY 살펴보기

SQL 의 많은 장점 중 GROUP BY 는 가장 유용하고 자주 사용하게 되는 구문 중 하나이며, 데이터의 양이 많아지고 컬럼이 늘어날수록 필수적으로 사용해야만 하는 구문입니다. GROUP BY 를 엑셀에서의 기능으로 본다면, 피벗(Pivot)과 유사한 역할을 한다고 보시면 되는데, 엑셀에서 피벗은 없어서는 안되는 필수 기능이기에 GROUP BY = PIVOT 으로서 초간단 정의를 마치겠습니다. SQL 을 처음 접하시는 분이라면 이전 글을 확인하시기 바랍니다.

• [마케터를 위한 SQL #1] SELECT Clause 살펴보기
• [마케터를 위한 SQL #2] WHERE Clause 살펴보기

[toc]

데이터베이스 용어 및 개념

매번 포스팅에서 아주 적은 양의 데이터베이스 용어와 개념에 대해서만 설명을 드리고 있습니다. 이번 포스팅에서는 Database 접속에 대한 개념과 이를 통한 SQL 구문 줄여 쓰기, 그리고 예제에 대한 설명 중에 Alias 개념을 전해드리고자 합니다.

데이터베이스 접속

가장 많이 사용되는 DB 중 하나인 MySQL 을 예로 들겠습니다. 논리적으로 하나의 MySQL DBMS 에서는 여러개의 Database 들을 생성할 수 있으며, 다시 하나의 Database 하위에는 다시 여러개의 Table 들을 생성할 수 있습니다. 아래는 필자의 Macbook 에 Homebrew 를 통해 MySQL 을 설치하고, CLI 를 통해 접속을 한 상태를 보여줍니다.

1
2
3
4
5
6
7
8
9
10
11
[/home/hongtebari] mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.16 Homebrew
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

개념을 설명드리기 위한 부분이기 때문에, 직접 해보지 않으셔도 됩니다. 접속 후, 아래 명령어(show databases;)를 통해서 어떤 database 들이 존재하는 지 확인해 보겠습니다.

1
2
3
4
5
6
7
8
9
10
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

show databases 의 실행을 통해 확인된 database 의 리스트는 information_schema, mysql, performance_schema, sys 등 총 4개 입니다. DB 관리자에 의해 새로운 database 가 생성될 수도 있으며, 삭제되거나 이름이 변경될 수 있습니다. 다음으로는, mysql 데이터베이스에 접속하여 어떤 table 들이 생성되어 있는지 확인해 보도록 하겠습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
...
...
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

use mysql; 명령어를 통해 mysql 데이터베이스에 접속을 하였고 show tables; 명령어를 통해 mysql 데이터베이스에 생성되어 있는 table 의 리스트를 확인했습니다. 여기서 제일 마지막 user 테이블의 데이터를 조회해 보면 아래와 같습니다.

1
2
3
4
5
6
7
8
mysql> SELECT User FROM user;
+-----------+
| User      |
+-----------+
| mysql.sys |
| root      |
+-----------+
2 rows in set (0.00 sec)

이전 두번의 포스팅에서 데이터를 조회할 때의 FROM 구문에, 아래와 같이 항상 데이터베이스명.테이블명 의 형태로 구문을 작성했습니다.

1
2
3
4
5
6
7
8
mysql> SELECT User FROM mysql.user;
+-----------+
| User      |
+-----------+
| mysql.sys |
| root      |
+-----------+
2 rows in set (0.00 sec)

하지만 조회하고자 하는 대상 table 이 소속된 Database 에 접속을 한 이후에는 Database명을 명시하지 않아도 Syntax Error 가 발생하지 않게됩니다. 따라서 같은 mysql 데이터베이스에 있는 다른테이블의 데이터를 조회할 때에도 DB명을 명시해주지 않을 수 있습니다. mysql 데이터베이스가 아닌 information_schema 에 접속을 하기 위해서는 역시 use information_schema; 명령어를 사용하시면 됩니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
...
...
| INNODB_SYS_TABLESTATS                 |
+---------------------------------------+
61 rows in set (0.00 sec)

Sample Data

오늘도 아래의 간단한 예제를 가지고 시작해 보겠습니다.

• Database 명 : POPIT
• Table 명 : USER_INFO

[table id=2 /]

엑셀의 피벗과 GROUP BY 비교

아래는 Sample Data 에서 GENDER 를 기준으로 하여, 남자와 여자의 평균 나이를 구하는 피벗 과정의 화면(맥용 Excel) 입니다.

위와 동일한 GROUP BY 의 SQL 구문은 아래와 같습니다.

1
2
3
SELECT    gender, AVG(age) 
FROM      popit.user_info
GROUP BY  gender

위 쿼리는 popit 테이터베이스의 user_info 테이블에서 gender 컬럼에 들어가 있는 값(남자,여자)을 기준으로 GROUP 을 묶어서 평균값을 구하는 쿼리입니다. 기억해 주셔야 할 부분은 GROUP BY 에 명시된 컬럼명을 반드시 SELECT 에서도 명시를 해줘야 한다는 것 입니다. 위에서는 GROUP BY 에 gender 를 명시했기 때문에 SELECT 에서도 gender 를 사용했습니다.

또 한가지 보셔야 할 부분은 지금까지의 예제 쿼리들 중에서 처음으로 Function 을 사용하는 부분이 나왔습니다. AVG 는 내장되어 있는 수 많은 Built-In Function 중 하나 입니다. Hive 에서는 다양한 종류의 Built-In Function 을 제공하는데, 자세한 내용에 대해서는 별도의 포스팅을 통해 살펴보겠습니다. 이번 글 에서는 그 중에서도 가장 많이 사용하게 되는 COUNT, MAX, MIN, AVG, SUM 정도만 살펴보겠습니다.

1
2
3
4
5
6
7
8
9
10
11
USE  popit;
SELECT    gender,
          address,
          AVG(age) avg_age,
          MAX(age) max_age,
          MIN(age) min_age,
          SUM(age) sum_age,
          COUNT(age) count_age
FROM      user_info
GROUP BY  gender, address
;

두번째 예제에서는 GROUP BY 를 두개의 컬럼(gender, address)을 사용했으며, SELECT 에서는 여러개의 Function 을 한꺼번에 사용했습니다. 한가지 기존 예제와 다른 부분은 USE popit; 을 통해 popit 데이터베이스 먼저 접속을 한 후, SELECT 쿼리문 에서는 DB 명을 명시하지 않고 바로 테이블 명을 사용하므로써 전체 쿼리의 길이를 줄인 부분과 AVG, MAX 등 Aggregation Function 을 적용한 각각의 값에 avg_age, max_age 등의 Alias 를 지정해 준 것 입니다. Alias 는 지정을 해주지 않아도 쿼리를 실행하는데에 있어 에러가 발생하지는 않습니다.

HAVING 구문으로 GROUP BY 의 조건 추가

WHERE 조건과는 다르게 GROUP BY 구문을 사용한 Aggregation 결과에 대한 조건을 필터링 하기 위해서는 아래와 같이 HAVING 구문을 GROUP BY 와 함께 사용할 수 있습니다.

1
2
3
4
5
6
SELECT    gender,
          COUNT(id)
FROM      user_info
GROUP BY  gender
HAVING    COUNT(Id) >= 3
;

위 쿼리는 gender 로 GROUP BY 를 했기 때문에, 남자는 3개의 레코드가, 여자는 2개의 레코드가 나오는 결과에서 count 수가 3보다 큰 데이터만을 필터링 하는 쿼리 입니다. WHERE 절에서는 사용할 수 없는 Aggregation Function 을 사용할 수 있으며, GROUP BY 가 적용된 결과 중에서 원하는 데이터만 필터링해서 조회하고자 할 때 사용합니다.

ORDER BY 구문 사용으로 정렬하기

GROUP BY 등의 집계 결과는 내림차순 혹은 오름차순의 정렬이 필요한 경우가 많습니다. 이 때 사용할 수 있는 쿼리가 ORDER BY 구문이며, ORDER BY 컬럼명 DESC 혹은 ORDER BY 컬럼명 ASC 형태로 사용할 수 있으며, ASC 는 Default 값 이므로 생략이 가능합니다. 또한, 컬럼명을 여러개를 콤마(,)로 구분하여 지정해 주는 것으로 여러개의 정렬 순서를 정해 줄수도 있습니다.

1
2
3
4
5
6
7
SELECT    gender,
          COUNT(id)
FROM      user_info
GROUP BY  gender
HAVING    COUNT(Id) >= 3
ORDER BY COUNT(Id) DESC
;

마치며

SQL 에서 가장 많이 사용하게 되는 GROUP BY 구문에 대해서 살펴보았습니다. 다음 글에서는 SQL 사용 이유의 절반이라고 할 수 있는 JOIN 에 대해서 살펴보도록 하겠습니다.

다음 글 보러가기 : 작성 예정