Imagine you get the logs of a vending-machine.
You want to know the average time between vending-events.
The log table looks like this:
DROP TABLE IF EXISTS vending_log;
CREATE TABLE `vending_log` (
`sold_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`product_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`sold_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- this fills test-data
set @a:='2010-01-01 12:00:00';
insert into vending_log
select @a:=adddate(@a,interval 1 + rand()*2500 second),
floor(rand()*100)
from information_schema.tables v1,
information_schema.tables v2 limit 1000;
How do you calculate the average time between the vending-events?
[Just for fun]
Mysql does not have OLAP extensions or windowing functions at this point. So it is not that easy to refer to the previous row.
Below is one way of solving the problem: With a subquery to retrieve the previous row. It works quite fine and is easy to read, but it is slow.
The next one is fast, but relies on user variables.
Here is still a variation on the subquery solution…
Instead of selecting the max(), you can also just select 1 timestamp and limit by 1.
I’m not fond of “limit 1″ for anything else than testing/debugging, but in this case it is faster than the “select max()” solution.
Aaaaand still a subquery solution: