Get sum of difference of rows in sql-CodePudding

I have a table with given values

| id  | level  | value |
| --- | ------ | ----- |
| 1   | 30000  | 0.05  |
| 2   | 100000 | 0.06  |
| 3   | 120000 | 0.07  |

I want to create an postgres sql query to get a sum in the following logic. I will be providing a value (level) as as parameter to the query (170000).

(100000- 30000)*0.05 (120000-100000)*0.06 (170000-120000)*0.07

(difference of level of row2 and row1) * value of row1 (difference of level of row3 and row2) * value of row2 (difference of level as input and row3) * value of row3

CodePudding user response：

You may use LEAD function to get the next level value, and find the sum according to your logic as the following:

SELECT SUM((nextLevel-level)*value) AS res
FROM
(
  SELECT *,
    LEAD(LEVEL, 1, 170000) OVER (ORDER BY id) nextLevel
  FROM tbl
) T

See a demo.

CodePudding user response：

Use LEAD to see the next row's value. Use COALESCE to substitute the last missing value with 170000.

select sum(subtotal) as total
from
(
  select
    (coalesce(lead(level) over (order by id), 170000) - level) * value as subtotal
  from mytable
) subtotals;