Search code examples
postgresqlaveragewindow-functions

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

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?

Solution

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