Sometimes people use mysql instead of postgresql. So, here's a delta function
for mysql that will provide the basis for a derivative(x,y) function. Since
it uses a temporary table, different sessions won't collide; however when
used two or more times in the same query, it uses a unique ID hack to keep track.
I'll benchmark this and post some updates tomorrow.
-- return delta of argument
-- pass in a unique id when used 2+ times in a query
drop function delta;
delimiter //
create function delta (n bigint, uniq int)
returns float deterministic
begin
declare last_n bigint;
create temporary table if not exists tmp_delta (
id int not null,
last bigint not null
);
-- first datum always returns 0 or garbage
select last into last_n from tmp_delta where id = uniq ;
if last_n is null then
insert into tmp_delta set id = uniq, last = n;
set last_n = n;
end if;
update tmp_delta set last = n where id = uniq;
return n - last_n;
end //
delimiter ;
select (delta(rx, 1), delta(tx, 2) from router where interface = 1 order by stamp ;