Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4991

Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4993
Question 82: More solutions for Q80? – MySQL Question of the Day

Skip to content

By plogi in mysql questions

Hello again!
Still about question 80… We have now 3 solutions to read values from the previous row: Subquery, user variables and with a helper-table + join. There is another feature (at least) which could be used, maybe a more procedural way? (hint, hint)
[Just for fun]

Tags: , ,

Comment Feed

One Response

  1. plogi11. July 2010 @ 12:56:36

    Yes, another way of solving the problem is using a stored proecedure. There you can use the traditional approach of reading the rows in order and store the previous values in variables.

    drop procedure if exists proc_q80;
    delimiter //
    CREATE PROCEDURE proc_q80 ()
      declare v_cnt bigint;
      declare v_prev_ts timestamp;
      declare v_ts timestamp;
      declare v_diff bigint;
      declare v_s_diff bigint;
      declare v_done smallint;
      declare c_vendlog CURSOR FOR SELECT sold_ts FROM vending_log order by sold_ts;
      OPEN c_vendlog;
      set v_prev_ts = null;
      set v_done = 0;
      set v_cnt = 0;
      set v_s_diff = 0;
        FETCH c_vendlog INTO v_ts;
        IF v_done = 0 THEN
           set v_diff = unix_timestamp(v_ts) - unix_timestamp(v_prev_ts);
           IF v_diff is not null THEN
              set v_cnt = v_cnt + 1;
              set v_s_diff = v_s_diff + v_diff;
           END IF;
           set v_prev_ts = v_ts;
        END IF;
      UNTIL v_done = 1 END REPEAT;
      close c_vendlog;
      SELECT v_s_diff / v_cnt as avg_diff;
    delimiter ;

You must be logged in to post a comment.