Calculate relative average and Lift for a group of data in PostgreSQL

user_10623444 0
Viewed 35

I have a data table that looks like this:

Shop Region Waste% AvgRegionWaste Lift
a CA 10 15 (30/2) 10/15
b CA 20 15 (30/2) 20/15
c LA 32 37 (74/2) 32/37
d LA 42 37 (74/2) 42/37

I am striving to calculate the last two columns AvgRegionWaste and Lift.

I tried the following approach (as described here):

SELECT avg(waste over (partition by Region order by Shop rows between unbounded preceding and current row) AS rolling_avg
FROM my_table
GROUP BY Shop, Region

But it doesn't seem to do the trick. What am I missing?

window-functions postgresql average
1 Answers

The expression that you need for the column AvgRegionWaste is:

AVG(waste) OVER (PARTITION BY Region)

and the query should be:

SELECT *,
       ROUND(AVG(waste) OVER (PARTITION BY Region), 2) AvgRegionWaste,
       ROUND(Waste / AVG(waste) OVER (PARTITION BY Region), 2) Lift
FROM my_table;

You can adjust the rounding of the returned values as you wish.

See the demo.

user_104988280
Related