This post originated from an RSS feed registered with Python Buzz by Aaron Brady. Original Post: Best SQL Crime Ever Feed Title: insom.me.uk Feed URL: http://feeds2.feedburner.com/insommeuk Feed Description: Posts related to using Python. Some tricks and tips, observations, hacks, and the Brand New Things. Latest Python Buzz Posts Latest Python Buzz Posts by Aaron Brady Latest Posts From insom.me.uk

Given a table like this:

mysql> select * from delta;
+------------+-------+
| end_time   | level |
+------------+-------+
| 1245937320 |     5 | 
| 1245937565 |     1 | 
| 1245954231 |     5 | 
| 1245954844 |     2 | 
| 1245956014 |     4 | 
| 1245963214 |     5 |

Doing this:

select u from ( select @ac:=0 i, NULL u union select * from ( select 0,
concat('http://chart.apis.google.com/chart?cht=pc&chd=t:',
group_concat(100*(q/@ac)),'&chl=',group_concat(l
separator '|'),'&chs=300x200') u from ( select q, l, @ac:=(@ac + q) from (
select sum(d)q, l from( select @s:=((select min(end_time) from delta)) d,
(select min(end_time) from delta) st, 0 l union select (end_time-@s) d,
(@s:=(end_time)), level from delta order by st)x group by l having (l is not
null and l > 0)) e ) r) m) pp order by u desc limit 1

Gives you this:

http://chart.apis.google.com/chart?cht=pc&chd=
t:0.3070,2.9300,2.5503,94.2127&chl=1|2|4|5&chs=300x200

Which is this:

Or rather, it’s a pie chart of how much time was spent at each level.

MySQL variables are fun, dangerous, and messy.