Thursday, May 29, 2008

Grouping MySQL entries in half hour increments

One of my hobbies at work is creating page-long MySQL queries to reduce the number of calls to the database from php. The following query is very mild in size but took a few weeks of brainstorming to develop. It is part of a reporting feature I built into an asterisk phone server. Calls are logged with an entry time, hold time, agent talk time, and whether the call was abandoned before an agent answered.

$query = "SELECT concat(if(hour(enter)>12,concat(hour(enter)-12),
if(minute(ENTER) < 30,'00','30')) as `hour`,
concat(HOUR(ENTER),if(minute(ENTER) < 30,'00','30')) as `group`,
count(call_id) as `calls`,
SEC_TO_TIME(avg( time_to_sec(hold_time ))) as `average hold time`,
SEC_TO_TIME(avg( time_to_sec(agent_time ))) as `average agent time`
FROM `queue_data`
WHERE date(ENTER) = '".$date."'
if(minute(ENTER) < 30,'ABANDON','SUCCESS'),

Fun queries like this can improve performance by reducing queries and using MySQL's processing engine instead of PHP's and make code cleaner. Happy MySQL'ing

No comments: