SQL & PL/SQL

Hi,

I'm trying to calculate the 'area under the curve' using the trapezium rule for a set of data points. i.e. I have a set of values, sampled at irregular intervals.

create table measurements (
  measurement_value integer,
  measurement_time timestamp
);

insert into measurements (measurement_value,measurement_time) values (50,TIMESTAMP'2011-01-01 00:00:00');
insert into measurements (measurement_value,measurement_time) values (40,TIMESTAMP'2011-01-01 01:00:00');
insert into measurements (measurement_value,measurement_time) values (40,TIMESTAMP'2011-01-01 03:00:00');
insert into measurements (measurement_value,measurement_time) values (50,TIMESTAMP'2011-01-01 03:30:00');
insert into measurements (measurement_value,measurement_time) values (40,TIMESTAMP'2011-01-01 05:00:00');
insert into measurements (measurement_value,measurement_time) values (40,TIMESTAMP'2011-01-01 06:00:00');
insert into measurements (measurement_value,measurement_time) values (60,TIMESTAMP'2011-01-01 07:00:00');

I'd like to calculate the area of the 'curve' where the value was below a threshold of, e.g. 45.

I can use the trapezium rule and assume a straight line between subsequent measurement, i.e. for the first 2 data points, the value drops from 50 to 40 linearly over the hour, and so the area for those measurements is (30min * 5)/2. For the 2nd and 3rd data points, the area is (120min * 5), for the 3rd and 4th, the area is (15min * 5)/2. And so on. The values are not necessarily round numbers, almost any value is possible.

I've started by using 'lead' to obtain the subsequent measurement value and time, but I'm getting stuck dealing with the end points and non-trivial interpolation. Does anyone have any pointers to get me going?

Thanks,

Dan Scott