mysql 5.0/5.1 questions for learning purposes

• Home
• About
• Legal
• Contact Us

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;

3 Responses

1. plogi — 9. July 2010 @ 22:26:52 —

   
   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.

   select avg(vv1.diff) from
     (select *,unix_timestamp(v1.sold_ts) -
               unix_timestamp((select max(sold_ts)
                               from vending_log
                               where sold_ts < v1 .sold_ts)) as diff
      from vending_log v1) vv1;
   

   The next one is fast, but relies on user variables.

   set @a:=null;
   set @b:=null;
   select avg(vv1.diff) from
     (select @b:=@a,@a:=sold_ts, unix_timestamp(sold_ts) - unix_timestamp(@b) as diff
      from vending_log order by sold_ts) vv1;
   

   
   
2. plogi — 15. July 2010 @ 11:39:47 —

   
   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.
   

   select avg(vv1.diff) from
     (select *,unix_timestamp(v1.sold_ts) -
               unix_timestamp((select sold_ts
                               from vending_log
                               where sold_ts < v1 .sold_ts order by sold_ts desc limit 1)) as diff
      from vending_log v1) vv1;
   

   
   
3. plogi — 15. July 2010 @ 17:25:06 —

   
   Aaaaand still a subquery solution:

   select avg(unix_timestamp(v1.sold_ts)-unix_timestamp(v2.sold_ts)) as avg_diff
   from vending_log v1, vending_log v2 where v2.sold_ts =
   (select sold_ts from vending_log
     where sold_ts < v1.sold_ts
     order by 1 desc limit 1 );