Subtract current row from previous row, keeping previous row value as constant -- SQL

user_9189645 0
Viewed 404

Table product has average revenue by product

product avg_rev
A 500
B 400
C 250
D 100

Table product_diff, is where I need to calculate, diff_a as another column where formula a-a, a-b, a-c, a-d carries over.

product avg_rev diff_a
A 500 0
B 400 100
C 250 250
D 100 400

My query so far: select product, avg_rev, lag(avg_rev,1) over (order by product) as previous_value from product;

I can take difference between previous row minus current row by saying previous_value-avg_rev but what I need is for previous row to be constant, minus current row as carried over all the way.

hiveql lag hive sql
2 Answers

You need the difference of avg_rev of the 1st product with the current row's avg_rev:

SELECT product, 
       avg_rev, 
       FIRST_VALUE(avg_rev) OVER (ORDER BY product) - avg_rev AS diff_a 
FROM product;

See the demo.
Results:

product avg_rev diff_a
A 500 0
B 400 100
C 250 250
D 100 400
user_104988280

If you specifically want one of the products, I would use conditional logic:

select product, avg_rev,
       avg_rev - max(case when product = 'A' then avg_rev end) over () as diff_a
from product;
user_11440350
Related