SQLite | 데이터 조회 | 데이터를 그룹화하여 합계와 평균을 계산 (GROUP BY, HAVING)
데이터의 개수를 구하는 함수와 컬럼마다 값을 계산하는 함수 등을 사용할 때, 모든 데이터를 나열하는 것이 아니라 특정 컬럼 값을 사용하여 그룹화를 하여 그룹 단위로 집계 등을 할 수 있다. 여기에서는 그룹화 할 때 사용하는 GROUP BY 절의 사용과 그룹화 한 후 조회을 수행하는데 사용하는 HAVING 절에 대해 설명한다.
GROUP BY 절을 사용하여 그룹화
먼저 GROUP BY 절을 사용하여 그룹화하는 방법이다. 형식은 다음과 같다.
SELECT 컬럼명, ... FROM 테이블명
GROUP BY 컬럼명1, 컬럼명2, ...;
GROUP BY 절 다음에 지정된 컬럼의 값이 같으면 같은 그룹이 된다. 여러 컬럼을 지정하는 경우에는 값의 조합이 같은 것이 그룹이 된다.
조금 이해하기 어려울지도 모르니 실제 예제를 보도록 하자.
예를 들어, 다음과 같이 테이블을 만든다.
create table user (name text, gender text, address text);
INSERT 문을 사용하여 테이블에 데이터를 추가한다.
insert into user values ('devkuma', 'man', 'Seoul');
insert into user values ('kimkc', 'man', 'Busan');
insert into user values ('arikuma', 'woman', 'Seoul');
insert into user values ('happykuma', 'woman', 'Suwan');
insert into user values ('raccoon', 'man', 'Busan');
insert into user values ('mykuma', 'woman', 'Daejeon');
insert into user values ('yourkuma', 'man', 'Seoul');
sqlite> insert into user values ('devkuma', 'man', 'Seoul');
sqlite> insert into user values ('kimkc', 'man', 'Busan');
sqlite> insert into user values ('arikuma', 'woman', 'Seoul');
sqlite> insert into user values ('happykuma', 'woman', 'Suwan');
sqlite> insert into user values ('raccoon', 'man', 'Busan');
sqlite> insert into user values ('mykuma', 'woman', 'Daejeon');
sqlite> insert into user values ('yourkuma', 'man', 'Seoul');
sqlite>
이 테이블에서 그대로 데이터를 받아오면 다음과 같다.
select * from user;
sqlite> select * from user;
name gender address
---------- ---------- ----------
devkuma man Seoul
kimkc man Busan
arikuma woman Seoul
happykuma woman Suwan
raccoon man Busan
mykuma woman Daejeon
yourkuma man Seoul
sqlite>
우선 count 함수를 사용하여 전체 테이블의 데이터의 수을 받아온다. count 함수는 “count 함수"를 참조하도록 한다.
select count(*) from user;
sqlite> select count(*) from user;
count(*)
----------
7
sqlite>
다음은 GROUP BY 절을 사용하여 gender 컬럼으로 그룹화하고 그룹별로 포함되는 데이터의 수를 구한다.
select gender, count(*) from user group by gender;
sqlite> select gender, count(*) from user group by gender;
gender count(*)
---------- ----------
man 4
woman 3
sqlite>
gender 컬럼에 있는 값마다 데이터의 수를 받아온 것을 볼 수 있다. 마찬가지로 지금 address 컬럼으로 그룹화하고 데이터의 수를 받아온다.
select address, count(*) from user group by address;
sqlite> select address, count(*) from user group by address;
address count(*)
---------- ----------
Busan 2
Daejeon 1
Seoul 3
Suwan 1
sqlite>
address 컬럼에 있는 값마다 데이터의 수를 받아온 것을 볼 수 있다. 마지막으로 여러 컬럼으로 그룹화를 해보겠다. gender 컬럼과 address 컬럼을 그룹화하여 데이터의 수를 받아온다.
select gender, address, count(*) from user group by gender, address;
sqlite> select gender, address, count(*) from user group by gender, address;
gender address count(*)
---------- ---------- ----------
man Busan 2
man Seoul 2
woman Daejeon 1
woman Seoul 1
woman Suwan 1
sqlite>
지정한 여러 컬럼 값의 조합마다 그룹화 된 데이터의 수를 받아온 것을 볼 수 있다.
여기에서는 count 함수만 사용하였지만, 다른 함수를 동일하게 사용하여 그룹마다 값의 합계를 내거나, 평균을 계산할 수도 있다.
HAVING절을 사용하여 그룹 후의 데이터에 조건을 설정
그룹화한 경우에도 WHERE 절을 사용하여 대상이 되는 데이터의 조건 설정을 할 수 있다.
SELECT 컬럼명, ... FROM 테이블명 WHERE 조건식
GROUP BY 컬럼명, ...;
이 경우 먼저 WHERE 절에 작성된 조건식으로 데이터를 조회한 이후에 그룹화된다.
이에 반에 그룹화를 하고나서 그룹을 조회하는데 사용되는 것이 HAVING 절이다. 형식은 다음과 같다.
SELECT 컬럼명, ... FROM 테이블명
GROUP BY 컬럼명, ... HAVING 조건식;
그룹화를 한 결과에 조건식이 적용되므로, HAVING 절에 조건식으로 작성할 수 있는 것은 그룹화에 지정된 컬럼명이나 함수 등을 사용하여 그룹 단위로 집계한 결과만 가능하다.
그러면 실제로 해보도록 하자. 앞에서 사용한 그대로 테이블을 사용한다. 다음과 같이 데이터가 현재 저장되어 있다.
select * from user;
sqlite> select * from user;
name gender address
---------- ---------- ----------
devkuma man Seoul
kimkc man Busan
arikuma woman Seoul
happykuma woman Suwan
raccoon man Busan
mykuma woman Daejeon
yourkuma man Seoul
sqlite>
조건을 설정하지 않고 address 컬럼으로 그룹화하고 데이터의 수를 구한다.
select address, count(*) from user group by address;
sqlite> select address, count(*) from user group by address;
address count(*)
---------- ----------
Busan 2
Daejeon 1
Seoul 3
Suwan 1
sqlite>
그러고 HAVING 절을 사용하여 count(*)의 개수가 2개 이상의 데이터만 조회하도록 하려고 한다.
select address, count(*) from user group by address having count(*) >= 2;
sqlite> select address, count(*) from user group by address having count(*) >= 2;
address count(*)
---------- ----------
Busan 2
Seoul 3
sqlite>
그룹화된 후에 받아온 데이터에서 조건식을 맞는 데이터만이 조회되었다. 이와 같이 HAVING 절을 사용하면 그룹화한 후에 데이터에 대한 조건식을 설정할 수 있다.