CASE expressions are a huge timesaving SQL feature that I have just recently fallen in love with. The specific example was trying to find how the activity of a class of users has declined over a period of twelve months. Without CASE this would have required writing and executing 12 different queries and then joining them into a table either by joining all those queries together or by pasting them into (in this case) excel. That's hard enough but what about 24hrs in a day for day parting analysis across 100000 keywords (far too big for excel <2007 that one ;) )
CASE selects each of those different queries I described into a column using a clause something like:
CASE month WHEN 2006M10 THEN revenue ELSE 0 END
For a table something like:
| month | userid | revenue |
| 2006M10 | alex | $500 |
| 2006M10 | beth | $402 |
| 2006M11 | alex | $220 |
| 2006M11 | beth | $170 |
| 2006M12 | alex | $52 |
| 2006M12 | beth | $50 |
| 2007M01 | beth | $1000 |
The select may look like:
SELECT
sum( CASE month WHEN 2006M10 THEN revenue ELSE 0 END) as oct06,
sum( CASE month WHEN 2006M11 THEN revenue ELSE 0 END) as nov06,
sum( CASE month WHEN 2006M12 THEN revenue ELSE 0 END) as dec06,
FROM
tbl_userrevenue
WHERE
userid IN ('alex', 'beth')
and the output would look like:
| oct06 | nov06 | dec06 |
| $902 | $390 | $102 |
Which in my mind is a pretty cool tool for analyzing anything where you want to look at discrete time intervals or users classes or almost anything else individually.
Comments