I have a data table that looks like this:

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?

CodePudding user response：

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.