--- In firebird-support@yahoogroups.com, "Francisco G" <frangonve@...> wrote:

>
> Hello, I have a question about computing values in a table sorted on Date/Time field:
>
> Table will have easily more than 10 million rows.
>
> Input data will look like this:
>
> Date/Time Price
> Record 1: Date/Time1 15.01
> Record 2: Date/Time2 15.45
> Record 3: Date/Time3 15.64
> Record 4: Date/Time4 16.04
> Record 5: Date/Time5 15.04
> Record 6: Date/Time6 15.64
> Record 7: Date/Time7 15.23
>
> I need to calculate a new column holding the average of the previous 3 rows prices and another column holding the median (50% percentile) of the previous 3 rows prices:
>
>
>
> Date/Time Price 3-Average 3-Median
> Record 1: Date/Time1 15.01 Null Null
> Record 2: Date/Time2 15.45 Null Null
> Record 3: Date/Time3 15.64 Null Null
> Record 4: Date/Time4 16.04 15.37 15.45
> Record 5: Date/Time5 15.04 15.71 15.64
> Record 6: Date/Time6 15.64 15.24 15.04
> Record 7: Date/Time7 15.23 15.24 15.04
>
> Please, can you give me a hint?
>
> Cheers
>
> Francisco
>

Hello Francisco

To make this calculation, I would use a stored procedure.

The query in the stored procedure returns row by row in the for loop.

There you can store the last and the second last values in local variables, calculate the avarage and the median, suspend (to return date/time, price, avarage, median), and copy the last to the second last and the current to the last to be prepared for the next row.

Good luck
Chris