Cogs and Levers A blog full of technical stuff

Window Function AVG

This article is an extension of the window function, to follow up on each of the window functions.

The AVG function takes the arithmetic mean of the input that it sees.

An example usage over the test data set would be:

SELECT salesperson, sale_date, (quantity * unit_cost),
	   avg(quantity * unit_cost) OVER (
         PARTITION BY salesperson
       ) as month_avg
FROM public.sales
ORDER BY salesperson, sale_date DESC;

This takes the average cost value (quantity * unit_cost), but personalises the value by salesperson. Each salesperson is going to have their average calculated in isolation because of PARTITION BY salesperson.