Clever Geek Handbook

How to do iterative fold in sql

Hi I want to perform a rolling sum (valx) based on the previous value of the gland sum row (valy) and the current value of the valx row

valy = previousrow_valy + valx

animal valx valy 
cat    1    1 
cat    3    4 
cat    2    6 
dog    4    4 
dog    6    10 
dog    7    17 
dog    8    25
+2


source to share


1 answer


This is where you define the order.

Assuming you want to order somecolumn

.

In Oracle

and PostgreSQL 8.4

:

SELECT  animal, valx, SUM(valx) OVER (PARTITION BY animal ORDER BY somecolumn) AS valy
FROM    mytable

In MySQL

:



SELECT  animal, valx,
        @valy := CASE WHEN @animal = animal THEN @valy + valx ELSE valx END AS valy,
        @animal := animal
FROM    (
        SELECT  @valy = 0,
                @animal := NULL
        ) vars,
        mytable
ORDER BY
        animal, somecolumn

This SQL Server

is one of the rare cases where creation is CURSOR

more efficient.

All databases (including SQL Server

) support the syntax ANSI

:

SELECT  animal, valx,
        (
        SELECT  SUM(valx)
        FROM    mytable mi
        WHERE   mi.animal = mo.animal
                AND mi.somecolumn <= mo.somecolumn
        ) AS valy
FROM    mytable mo

however, this is inefficient if mytable

there are many records in, the execution time grows likeO(n^2)

+1


source






More articles:


All Articles