이번 퀴즈로 배워보는 SQL 시간에는 접속자별로 연속으로 같은 사이트 방문시 하나의 행으로 집계해 출력하는 문제를 MySQL로 풀어본다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.
문제
접속자별로 웹사이트 방문 기록을 저장하는 MySQL 테이블이 있습니다. 웹사이트 접속로그 테이블(web_log)은 접속자(id), 접속사이트(site), 접속시간(tm) 항목으로 구성돼 있습니다.
<표 1>의 원본 테이블로부터 접속자별 접속기록을 <표 2>의 결과테이블 형태로 도출하는 SQL을 작성하세요.
- [리스트 1] 웹사이트 접속 로그 테이블
-
CREATE TABLE web_log AS SELECT '111' id, 'www.gurubee.net' site, '11:10' tm UNION ALL SELECT '111', 'www.gurubee.net', '11:11' UNION ALL SELECT '111', 'www.imaso.co.kr', '11:12' UNION ALL SELECT '111', 'www.imaso.co.kr', '11:13' UNION ALL SELECT '111', 'www.gurubee.net', '11:14' UNION ALL SELECT '222', 'www.gurubee.net', '11:11' UNION ALL SELECT '222', 'www.imaso.co.kr', '11:12' UNION ALL SELECT '222', 'www.imaso.co.kr', '11:13' UNION ALL SELECT '222', 'www.imaso.co.kr', '11:14' ; SELECT * FROM web_log ORDER BY id, tm, site ;
- [표 1] 원본 테이블
-
+-----+-----------------+-------+ | id | site | tm | +-----+-----------------+-------+ | 111 | www.gurubee.net | 11:10 | | 111 | www.gurubee.net | 11:11 | | 111 | www.imaso.co.kr | 11:12 | | 111 | www.imaso.co.kr | 11:13 | | 111 | www.gurubee.net | 11:14 | | 222 | www.gurubee.net | 11:11 | | 222 | www.imaso.co.kr | 11:12 | | 222 | www.imaso.co.kr | 11:13 | | 222 | www.imaso.co.kr | 11:14 | +-----+-----------------+-------+
- [표 2] 결과 테이블
-
+-----+------+-----------------+-------+-----+ | id | grp | site | tm | cnt | +-----+------+-----------------+-------+-----+ | 111 | 1 | www.gurubee.net | 11:10 | 2 | | 111 | 2 | www.imaso.co.kr | 11:12 | 2 | | 111 | 3 | www.gurubee.net | 11:14 | 1 | | 222 | 1 | www.gurubee.net | 11:11 | 1 | | 222 | 2 | www.imaso.co.kr | 11:12 | 3 | +-----+------+-----------------+-------+-----+
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 2] 정답 리스트
SELECT id
, grp
, site
, MIN(tm) tm
, COUNT(*) cnt
FROM (SELECT id, site, tm
, @grp := CASE WHEN id != @id THEN 1
WHEN site != @site THEN @grp + 1
ELSE @grp
END AS grp
, @id := id
, @site := site
FROM web_log a
, (SELECT @id := ''
, @site := ''
, @grp := 0
) b
ORDER BY id, tm
) c
GROUP BY id, grp, site
;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 문제는 사용자별 접속기록을 집계해 보여주는 문제입니다. 또한 오라클(Oracle) 관련 문제가 아닌 MySQL 문제입니다만, 먼저 오라클 쿼리로 문제를 풀어본 뒤에 MySQL 문제를 풀어봅시다.
- [리스트 3] GROUP BY id, site
-
SELECT id, site , MIN(tm) tm , COUNT(*) cnt FROM web_log GROUP BY id, site ORDER BY id, tm ;
- [표 3] GROUP BY id, site
-
+-----+-----------------+-------+-----+ | id | site | tm | cnt | +-----+-----------------+-------+-----+ | 111 | www.gurubee.net | 11:10 | 3 | | 111 | www.imaso.co.kr | 11:12 | 2 | | 222 | www.gurubee.net | 11:11 | 1 | | 222 | www.imaso.co.kr | 11:12 | 3 | +-----+-----------------+-------+-----+
<리스트 3>의 쿼리를 수행해 <표 3>의 결과를 얻었습니다. <표 3>은 사용자와 사이트를 기준으로 GROUP BY한 결과입니다. 그러나 ID 111의 결과가 3행이 아닌 2행으로 집계됐습니다. 즉 ID, 사이트 외에 추가적인 기준항목이 필요하다는 뜻입니다.
- [리스트 4] 직전행 비교(LAG)
-
SELECT id, site, tm , LAG(site) OVER(PARTITION BY id ORDER BY tm) lag_site , DECODE(LAG(site) OVER(PARTITION BY id ORDER BY tm) , site, 0, 1) x FROM web_log ;
- [표 4] 직전행 비교(LAG)
-
ID SITE TM LAG_SITE X ------ ----------------- ---------- ----------------- ---------- 111 www.gurubee.net 11:10 1 111 www.gurubee.net 11:11 www.gurubee.net 0 111 www.imaso.co.kr 11:12 www.gurubee.net 1 111 www.imaso.co.kr 11:13 www.imaso.co.kr 0 111 www.gurubee.net 11:14 www.imaso.co.kr 1 222 www.gurubee.net 11:11 1 222 www.imaso.co.kr 11:12 www.gurubee.net 1 222 www.imaso.co.kr 11:13 www.imaso.co.kr 0 222 www.imaso.co.kr 11:14 www.imaso.co.kr 0
<리스트 4>의 쿼리를 수행해 <표 4>의 결과를 얻었습니다. 직전행의 사이트를 구하기 위해 분석함수인 LAG를 사용했습니다. Decode를 이용해 직전행의 사이트와 현재행의 사이트를 비교해, 같으면 0을 다르면 1을 표현하도록 했습니다.
<표 4>의 x 값이 1인 행이 새로운 그룹의 시작이 되는 행임을 알 수 있습니다.
- [리스트 5] 그룹핑 기준 생성
-
SELECT id, site, tm , SUM(x) OVER(PARTITION BY id ORDER BY tm) grp FROM (SELECT id, site, tm , DECODE(site , LAG(site) OVER(PARTITION BY id ORDER BY tm) , 0, 1) x FROM web_log ) ;
- [표 5] 그룹핑 기준 생성
-
ID SITE TM GRP ------ ----------------- ---------- ---------- 111 www.gurubee.net 11:10 1 111 www.gurubee.net 11:11 1 111 www.imaso.co.kr 11:12 2 111 www.imaso.co.kr 11:13 2 111 www.gurubee.net 11:14 3 222 www.gurubee.net 11:11 1 222 www.imaso.co.kr 11:12 2 222 www.imaso.co.kr 11:13 2 222 www.imaso.co.kr 11:14 2
<리스트 5>의 쿼리를 수행해 <표 5>의 결과를 얻었습니다. 앞서 구한 x 값을 순차적으로 누적 합산해 grp 값을 구했습니다. 이 grp 값이 바로 그룹의 기준이 되는 값입니다.
- [리스트 6] 최종 집계
-
SELECT id, grp, site , MIN(tm) tm , COUNT(*) cnt FROM (SELECT id, site, tm , SUM(x) OVER(PARTITION BY id ORDER BY tm) grp FROM (SELECT id, site, tm , DECODE(site , LAG(site) OVER(PARTITION BY id ORDER BY tm) , 0, 1) x FROM web_log ) ) GROUP BY id, grp, site ORDER BY id, grp, site ;
- [표 6] 최종 집계 결과
-
ID GRP SITE TM CNT ------ ---------- ----------------- ---------- ---------- 111 1 www.gurubee.net 11:10 2 111 2 www.imaso.co.kr 11:12 2 111 3 www.gurubee.net 11:14 1 222 1 www.gurubee.net 11:11 1 222 2 www.imaso.co.kr 11:12 3
<리스트 6>의 쿼리를 수행해 <표 6>의 결과를 얻었습니다. 앞에서 구한 grp 값과 id, 사이트 값을 기준으로 Group By해 최종적으로 원하는 결과를 구했습니다.
오라클의 분석함수를 이용해 직전행 값을 구한 뒤, 누적합계를 구해서 그룹 기준항목을 만들어 내고, 그 기준으로 집계해서 원하는 결과를 구했습니다.
그렇다면 MySQL에서는 어떻게 접근해야 할까요? MySQL에는 분석함수를 사용할 수 없습니다. 그렇다면 어떻게 직전행 값을 구하고 누적합계를 구할까요?
MySQL에는 분석함수를 사용하지 못하지만 오라클에서 사용할 수 없었던 변수 사용이 가능합니다. 변수를 선언하고 컬럼 값을 저장해 사용한다면 직전행 값 참조나 누적합계를 구할 수 있습니다.
- [리스트 7] 변수 사용
-
SELECT id, site, tm , @grp := CASE WHEN id != @id THEN 1 WHEN site != @site THEN @grp + 1 ELSE @grp END AS grp , @id := id , @site := site FROM web_log a , (SELECT @id := '' -- 직전 id , @site := '' -- 직전 사이트 , @grp := 0 -- 그룹기준 ) b ORDER BY id, tm ;
- [표 7] 변수 사용 결과
-
+-----+-----------------+-------+------+-------------+-----------------+ | id | site | tm | grp | @id := id | @site := site | +-----+-----------------+-------+------+-------------+-----------------+ | 111 | www.gurubee.net | 11:10 | 1 | 111 | www.gurubee.net | | 111 | www.gurubee.net | 11:11 | 1 | 111 | www.gurubee.net | | 111 | www.imaso.co.kr | 11:12 | 2 | 111 | www.imaso.co.kr | | 111 | www.imaso.co.kr | 11:13 | 2 | 111 | www.imaso.co.kr | | 111 | www.gurubee.net | 11:14 | 3 | 111 | www.gurubee.net | | 222 | www.gurubee.net | 11:11 | 1 | 222 | www.gurubee.net | | 222 | www.imaso.co.kr | 11:12 | 2 | 222 | www.imaso.co.kr | | 222 | www.imaso.co.kr | 11:13 | 2 | 222 | www.imaso.co.kr | | 222 | www.imaso.co.kr | 11:14 | 2 | 222 | www.imaso.co.kr | +-----+-----------------+-------+------+-------------+-----------------+
<리스트 7>의 쿼리를 수행해 <표 7>의 결과를 얻었습니다. FROM 절에 인라인 뷰를 사용해 사용하게 될 변수를 선언했습니다. @id, @site는 직전행의 id와 사이트를 저장할 변수입니다.
@grp는 그룹의 기준이 되는 값을 만들어낼 변수입니다. 쿼리의 실행 순서는 a와 b를 조인조건 없이 조인하고, 정렬을 수행한 뒤에 Select 절에 있는 항목들이 조회되는 순입니다.
Case문을 통해 그룹 기준항목인 @grp의 값을 지정하고 grp로 별칭을 줍니다. 매 행마다 수행되는 Case문을 차례대로 풀어보면, 첫 번째 행에서의 최초 @id는 값이 ‘ ’으로 id 값인 ‘111’과 다르므로 1이 됩니다. Case문이 수행된 다음에는 @id, @site에 각각 현재행의 값을 저장합니다.
두 번째 행에서는 @id와 id 값이 ‘111’로 같고, @site와 사이트의 값이 ‘www.gurubee.net’으로 동일하므로 ELSE 절을 이용하게 돼 @grp 값이 그대로 유지됩니다. 고로 값은 1이 됩니다.
세 번째 행에서는 id 값은 같지만 사이트 값은 다르기 때문에 두 번째 WHEN 절을 이용하게 돼 @grp 값은 1 증가된 2가 됩니다. 이렇게 차례대로 진행해 나가면서 그룹의 기준항목인 grp 항목이 완성이 됩니다. 이제 원하는 기준항목을 구했으니 최종적인 Group BY를 하면 됩니다.
- [리스트 8] 최종 집계
-
SELECT id , grp , site , MIN(tm) tm , COUNT(*) cnt FROM (SELECT id, site, tm , @grp := CASE WHEN id != @id THEN 1 WHEN site != @site THEN @grp + 1 ELSE @grp END AS grp , @id := id , @site := site FROM web_log a , (SELECT @id := '' -- 직전 id , @site := '' -- 직전 사이트 , @grp := 0 -- 그룹기준 ) b ORDER BY id, tm ) c GROUP BY id, grp, site ;
GROUP BY를 통해 <리스트 8>의 정답 쿼리가 완성됐습니다.
이번 시간의 내용을 정리해 봅시다. 이번 퀴즈의 핵심은 앞단의 사이트와 뒷단의 사이트가 같음에도 불구하고 다르게 집계돼야 한다는 것입니다.
이를 위해 오라클에서는 분석 함수를 이용해 직전값을 가져와 비교하고, 그것을 토대로 누적합계를 구해서 그룹의 기준을 구했습니다. 그러나 MySQL에서는 전혀 다른 방법으로 변수를 선언해서 절차적으로 문제를 풀었습니다.
문제를 푸는 원리만 잘 파악한다면 DBMS는 문제가 되지 않을 것입니다. 각 DBMS의 특성을 알고 그에 맞게 응용력을 발휘하길 바랍니다.
- 강좌 URL : http://www.gurubee.net/lecture/2864
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.