Anyone know of a better way to calculate the derivative of two series in SQL? I.e., given a table with incrementing counters does a better method of finding the rate of change in those counters exist? The next best solution I can think of has me using postgresql's scripting language. Example table and my current solution:

Table: `r` id | rx_bytes | tx_bytes | stamp ----+------------+------------+---------------------------- 1 | 4409175828 | 1953740054 | 2007-05-10 13:52:05.768994 2 | 4409317532 | 1953876624 | 2007-05-10 13:55:04.676586 3 | 4409586227 | 1954221384 | 2007-05-10 14:00:01.921733 4 | 4411577594 | 1954689574 | 2007-05-10 14:05:05.606004 5 | 4412308594 | 1955036986 | 2007-05-10 14:10:02.103433 6 | 4412563005 | 1955363345 | 2007-05-10 14:15:02.930761 7 | 4412821894 | 1955604425 | 2007-05-10 14:20:09.826853 8 | 4413091666 | 1955914285 | 2007-05-10 14:25:06.545572 9 | 4413338941 | 1956166222 | 2007-05-10 14:30:04.06891 10 | 4413602704 | 1956457986 | 2007-05-10 14:35:05.334229

select ( r2.rx_bytes - r1.rx_bytes ) / ( extract(epoch from r2.stamp) - extract(epoch from r1.stamp) ) from r as r1, r as r2 where r2.id = r1.id + 1;

Implemented in plpgsql, the refcursor would mean using FETCH syntax, so I chose to always return junk (0.0 or something else) for the first row. Also, I need to benchmark this on a large-ish table:

-- create language plpgsql; create or replace function first_derivative( _a bigint, _b bigint ) returns numeric as \$ph\$ declare srow record; old_a bigint; old_b bigint; ret numeric; BEGIN -- setup temp table if we don't have it available -- XXX refactor temp table name to variable if not exists ( select 1 from pg_class where relname = 'first_derivative_temptable' ) then create temporary table first_derivative_temptable (a bigint, b bigint); insert into first_derivative_temptable values ( _a , _b ); return 0.0; end if; -- get old values for srow in select * from first_derivative_temptable LOOP old_a = srow.a; old_b = srow.b; end loop; -- save derivative ret = ( _a - old_a ) / ( _b - old_b ); -- insert values into temp table update first_derivative_temptable set a = _a , b = _b; return ret; END; \$ph\$ language plpgsql; -- tests select first_derivative( rx_bytes, cast( extract(epoch from stamp) as bigint) ) from raq1 where rx_bytes is not null order by stamp limit 5; select * from first_derivative_temptable;