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.
Cocktail Recommentation Engine: Part 3 - The First Results
The following table is a subset of the output results from the cocktail recommendation engine (be warned some rude words in some of the cocktail names). I have tried to drill the list down to just the best matches so bear in mind there are probably 30% more matches thrown out which appear to have statistical significance but I can find no reason for the relationship (beyond sometimes they are next to each other in the large cocktail A-Z list).
Cocktail One - the cocktail someone is on, Cocktail Two - the cocktail I recommend, Probability - the probability that if someone is on cocktail one and visits another cocktail cocktail two will be that cocktail. There had to be at least 10 relationships in total recorded between cocktail one and another cocktail in my db for a cocktail to make this table.
I am hoping that as I get more data this table will grow more accurate and I will have fewer "wtf?" moments looking at the list. I also want to add two more probabilities to the calculation:
Anyway for now enjoy looking at the cocktail relationships and expect more data soon.
April 15, 2007 in general comments, php, SQL | Permalink | Comments (2) | TrackBack (0)