Postgresql – Calculating the percentage change from the previous row

postgresqlwindow functions

I just entered the world of window functions. I have a similar problem to solve as in this question.

I have this table:

The last column (%Grown) is given by:

-(1 - price of row 2 / price of row 1) * 100

All the information I have found so far was related to sums, avg for all rows. I don't know how to bound two rows so I can calculate the percentage.

How do I translate this into SQL?

Best Answer

The best way I think would be to use the LAG() or LEAD() functions:

SELECT *, 
       - 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown
FROM table_name AS t
ORDER BY t.Id ;

With LEAD(Price) OVER (ORDER BY t.Id) you have access to the next Price when the rows are ordered by Id. It's not clear what the order should be. Based on the data in the question, you may want (ORDER BY Price DESC).

Related Solutions

Sql-server – Running total to the previous row

The answer is to use 1 PRECEDING, not CURRENT ROW -1. So, in your query, use:

    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID 
                            ORDER BY SalesOrderDetailID
                            ROWS BETWEEN UNBOUNDED PRECEDING 
                                     AND 1 PRECEDING) 
    AS  PreviousRunningTotal

Also note that on your other calculation:

    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
                            ORDER BY SalesOrderDetailID) ...

SQL-Server uses the default ^* RANGE UNBOUNDED PRECEDING AND CURRENT ROW. I think that there is an efficiency difference and ROWS UNBOUNDED PRECEDING AND CURRENT ROW is to be preferred (after testing of course and if it gives the results you want).

Much more details you can find in the blog article by @Aaron Bertrand, including performance tests: Best approaches for running totals – updated for SQL Server 2012

^{* this is of course the default range when an ORDER BY is present inside the OVER clause - otherwise, without ORDER BY the default is the whole partition.}

Postgresql – Finding earliest connected value over two columns

It's a recursive problem by nature. SQL introduced recursive CTEs for that purpose quite some time ago. Since Redshift does not seem to support this, your only remaining option to solve this within the database is a user defined function (UDF). It seems like the only server-side language supported by Redshift is plpythonu.

Here is an old answer with a PL/pgSQL function and an rCTE implementing the same solution side-by-side. But it's using the default procedural language of Postgres: PL/pgSQL:

Loop in function does not work as expected

No idea why Redshift chose not to support PL/pgSQL.

Best Answer

Related Solutions

Sql-server – Running total to the previous row

Postgresql – Finding earliest connected value over two columns

Related Question