CODEWITHSUNDEEP
sqlpostgresql
Lorenzo Ang
Lorenzo Ang

Reputation: 1318

Is there a way to reference a calculated value from the previous row and use that in the next row's calculation?

For example, I have the following query and table:

SELECT generate_series * 5 as num
FROM generate_series(1, 5)

+-----+
| val |
+-----+
|   5 |
|  10 |
|  15 |
|  20 |
|  25 |
+-----+

I want to take the previous row's val however and continously multiply it by 5 such that I end up with the table:

+-----+
| val |
+-----+
|    5|
|   25|
|  125|
|  625|
| 3125|
+-----+

I know I could just go 5 ^ row_number but the problem I need to solve requires that I craft the query to be able to reference the previous row's calculated val

Upvotes: 1

Views: 222

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I'm a little lost on why a custom aggregation function is needed. This is a simple arithmetic progression. Why not just do this?

SELECT gs.*,
       power(5, gs.x)
FROM generate_series(1, 5) gs(x);

Or if you really want to keep your original data:

SELECT gs.x * 5,
       power(5, ROW_NUMBER() OVER (ORDER BY ts.x))
FROM generate_series(1, 5) gs(x);

Upvotes: 0

Lorenzo Ang
Lorenzo Ang

Reputation: 1318

I finally figured out that I needed a custom aggregate function to call from this thread: How to create a custom windowing function for PostgreSQL? (Running Average Example)

End result is this. It takes 2 decimal values, the first one being the one to multiply against while the second one serves as a default.

CREATE OR REPLACE FUNCTION prod(decimal, decimal, decimal) RETURNS decimal 
LANGUAGE SQL AS $$
    SELECT ROUND(COALESCE($1 * $2, $3), 2);
$$;

DO $$ BEGIN
    CREATE AGGREGATE prods ( decimal, decimal ) (
      SFUNC = prod,
      STYPE = decimal
      -- INITCOND = 1.0
    );
    EXCEPTION
        WHEN duplicate_function THEN NULL;
END $$;

Called via:

prods(val, 5) OVER (ORDER BY val)

Upvotes: 1

zip
zip

Reputation: 4061

use lag window function, supposing that lag is your ordering column

select val, val*coalesce(Lag(val,1) OVER ( ORDER BY val),1) as product from tbl

Upvotes: 0

Related Questions