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]
Question 82: More solutions for Q80?
11. July 2010
By plogi in mysql questions
One Response
You must be logged in to post a comment.
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 () BEGIN 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; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; OPEN c_vendlog; set v_prev_ts = null; set v_done = 0; set v_cnt = 0; set v_s_diff = 0; REPEAT 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; END // delimiter ;