group by를 특정 값을 기준으로 누적하고 싶을 때... GROUP_CONCAT

DB로 로그를 쌓고 있다.

사용자별로 어떤 페이지에 방문했는지 기록을 한다고 가정하자.

스키마는 단순하게 아래와 같이 컬럼으로 되어 있다고 치자.

time, user, page

특정 사용자가 시간 순서대로 어떤 페이지에 방문을 했는지 보고 싶다.

이해를 돕기 위해 샘플 데이터를 해보면,

raw데이터는 아래와 같이 행으로 되어 있다.

time, user, page

07:33,A,login

07:34,A,mypage

07:40,A,changeForm

07:50,A,completeForm

07:55,A,logout

08:22,B,login

08:25,B,searchForm

08:26,B,readArticle

보고 싶은 데이터의 형태는

A, login-> mypage-> changeForm-> completeForm-> logout

B, login-> searchForm-> readArticle

의 두 행이 된다.

처음에는 어렵게 생각했었는데 집계 함수를 생각해내면 된다.

하지만 표준SQL에는 이러한 집계 함수가 없고 벤더마다 이름이 다른 것 같다.

SQLite3의 경우 group_concat 함수를 쓰면된다. 이 함수는 인자가 하나 혹은 두 개를 넣을 수 있는데, 두 개 짜리에는 사용자에게 구분자를 넣을 수 있게 해준다. 후자를 사용해서 쿼리를 짜보면 아래와 같다.

SELECT user, group_concat(page) AS pageList FROM log GROUP BY user;

데이터가 시간 순서대로 들어가 있지 않을 수도 있다. 이런 경우에는 먼저 정렬을 해주고 그루핑을 해주면 된다.

SELECT user, group_concat(page) AS pageList FROM (

  SELECT user, page FROM log ORDER BY time

) GROUP BY user;

이러한 group_concat과 같은 기능을 해주는 것을 벤더별로 정리하면 다음과 같다.

SQLite3:  GROUP_CONCAT  

MY-SQL: GROUP_CONCAT (이 함수는 1024글자의 제한이 있다. set group_concat_max_len=10000 와 같은 명령으로 제한을 바꿀 수 있다.)

MS-SQL: STUFF (SQL Server 2008 부터 지원) / GROUP_CONCAT 함수를 쓸 수 있게 커스텀 함수도 있다.

Oracle: wmsys.WM_CONCAT (저장된 혹은 정렬된 순서로 합쳐지지 않는 문제가 있다고 한다 listagg() 등을 써야 한다.)

유사한 질문이 있는 stackoverflow : http://stackoverflow.com/questions/15154644/group-by-to-combine-concat-a-column

오라클의 WM_CONCAT 함수가 합치는 순서가 엉망이라는 이야기를 들어서 SQLite3에서 잘 되는 지 확인을 해보니 정렬이 잘 되는 것 같다.

create table idtable (seq number, id number, val varchar2(20));

insert into idtable (seq, id, val) values (2, 10, '2');

insert into idtable (seq, id, val) values (1, 10, '1');

insert into idtable (seq, id, val) values (5, 10, '5');

insert into idtable (seq, id, val) values (3, 10, '3');

insert into idtable (seq, id, val) values (10, 20, '10');

insert into idtable (seq, id, val) values (26, 20, '26');

insert into idtable (seq, id, val) values (17, 20, '17');

insert into idtable (seq, id, val) values (28, 20, '28');

sqlite> select * from idtable;

2|10|2

1|10|1

5|10|5

3|10|3

10|20|10

26|20|26

17|20|17

28|20|28

sqlite> select id, GROUP_CONCAT(val) from idtable group by id;

10|2,1,5,3

20|10,26,17,28

sqlite> select id, GROUP_CONCAT(val) from (

   ...>  select id, val from idtable order by seq

   ...> ) group by id;

10|1,2,3,5

20|10,17,26,28