Dance and Pivot SQL Style

Aug 1, 2010

Found myself looking at Open Flash Charts for a reporting system. I wanted to create page views broken down by day. A simplified schema is as follows:

I wanted to create a table like the following:

My first though was just do a simple GROUP BY and pivot the table through code.

1
2
3
SELECT page, DATE(created_at), COUNT(*) AS hits 
FROM page_views
GROUP BY DATE(created_at), page ORDER BY page;

I figured I could alternatively use temp tables but, I had an itch to find out if there was another way using pure SQL and no temp tables. During my search I ran across all sorts of queries some obscure head scratchers. After much frustration I started reading the MySQL for any reprieve particularly the section on functions and operators. After a small meal and some strange query results I ran into a presentation created by Giuseppe Maxia aka the “Data Charmer”. I reamed through his presentation and found what I was looking for in the form of a 2 query crosstab. I reformulated his query to suit my needs and came up with the following:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT page_name AS name,
COUNT(CASE WHEN DATE(created_at) = '?' THEN id ELSE null END) AS mon,
COUNT(CASE WHEN DATE(created_at) = '?' THEN id ELSE null END) AS tues,
COUNT(CASE WHEN DATE(created_at) = '?' THEN id ELSE null END) AS wed,
COUNT(CASE WHEN DATE(created_at) = '?' THEN id ELSE null END) AS thurs,
COUNT(CASE WHEN DATE(created_at) = '?' THEN id ELSE null END) AS fri,
COUNT(CASE WHEN DATE(created_at) = '?' THEN id ELSE null END) AS sat,
COUNT(CASE WHEN DATE(created_at) = '?' THEN id ELSE null END) AS sun,
COUNT(CASE WHEN DATE(created_at) 
  IN ('?','?','?','?','?','?','?')
  THEN id ELSE null END) AS total
FROM page_views GROUP BY name

What are your thoughts? Any similar implementations how did you handle it?

tags: [ sql ]