가령 로그 필드에 날짜(created_at)와 IP(client_ip)가 있는데, 전체 로그 수 기준으로 정렬하면서 각 호스트(row)와 일자(column)별로 통계 테이블을 출력해서 보고 싶다고 하자.
아래는 PostgreSQL용으로 만들어진 쿼리다. (다른 DB에서도 CASE는 보통 지원하니까 쉽게 될 것이다.)
SELECT w.host, SUM(w.d1) AS d1, SUM(w.d2) AS d2, SUM(w.d3) AS d3, SUM(w.d4) AS d4, SUM(w.d5) AS d5
FROM
(
SELECT client_ip AS host,
CASE date(created_at) WHEN '2010-05-30' THEN count(*) ELSE 0 END AS d1,
CASE date(created_at) WHEN '2010-05-29' THEN count(*) ELSE 0 END AS d2,
CASE date(created_at) WHEN '2010-05-28' THEN count(*) ELSE 0 END AS d3,
CASE date(created_at) WHEN '2010-05-27' THEN count(*) ELSE 0 END AS d4,
CASE date(created_at) WHEN '2010-05-26' THEN count(*) ELSE 0 END AS d5
FROM ml_log_web
WHERE created_at >= date(current_date - interval '5 day') and created_at <= date(current_date - interval '1 day') + interval '23 hour 59 min 59 sec'
GROUP BY client_ip, date(created_at)
) w
GROUP BY w.host
ORDER BY SUM(w.d1) + SUM(w.d2) + SUM(w.d3) + SUM(w.d4) + SUM(w.d5) DESC;
이렇게 CASE로 일단 열을 분류한 다음에 빈 공간은 SUM해서 없애버리면 된다.
EXPLAIN 해보면 인덱스 타고 Hash Aggregate한 다음 Sort 하는 것 볼 수 있음..
덧글