Window Functions for Average Price in SQL

If you’ve got a table of historical pricing information (Prices) where each item has a has an ID (not as in row primary key, but as in ISBN style ID) you can get the average price using a PostgreSQL window function.

SELECT DISTINCT ON (“id”) “id”, avg(“price”) OVER (PARTITION BY “id”) FROM “Prices”;

This works by grouping all the items by item ID and then calculating an average value of the prices. Normally this would return the “avg” column with the average price for that item in every row. By adding the distinct requirement you limit it to just one row per item ID.