8

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

I have this table:

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?

1
  • 1
    What column should be used for the ordering of rows? Aug 27, 2015 at 13:26

2 Answers 2

11

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).

2
  • Thank you for your answer. Actually, the table is a query and I'm trying to calculate the price variation from one purchase date to another. (say you buy some bricks in 01.01.2015 with 5$, on 05.01.2015 with 10$ and on 10.01.2015 with 10$) I think it may work with a function as well, but I'm so stuck...
    – ralu
    Aug 28, 2015 at 12:45
  • It doesn't matter if it's a base table or a query result. Just replace table_name with your query (SELECT ....) and play with the (ORDER BY ...) inside the OVER. Aug 28, 2015 at 13:00
-1

You can use Common Table expression,

WITH Data as (select  ROW_NUMBER() OVER (ORDER BY applicable_date) as RowId, date, qty, price, [value] from tbl
)
SELECT 
CurrentRow.date, CurrentRow.qty, CurrentRow.price, CurrentRow.[value], 'Your calculation here for Growth'
FROM Data as CurrentRow
LEFT OUTER JOIN Data as NextRow ON NextRow.RowId = CurrentRow.RowId + 1
WHERE NextSlice.RowId IS NOT NULL

I Hope it helps.

5
  • [value] is an invalid identifier ([ or ] or not allowed in an SQL identifier)
    – user1822
    Aug 27, 2015 at 12:15
  • in postgres you can use reserved words with " "
    – ralu
    Aug 27, 2015 at 12:46
  • Thank you, Mouliin. However, I am lost. What's NextSlice?
    – ralu
    Aug 27, 2015 at 12:49
  • Sorry, It should be NextRow and CurrentRow. I only used [value] because it was in the original table structure.
    – mouliin
    Aug 27, 2015 at 13:20
  • There was a Value in the question, not a [Value]
    – user1822
    Aug 27, 2015 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.