Sometimes you want a group average beside every observation, not a shorter table containing only the averages. A window calculation can preserve the individual rows while adding group context.
For a fictional table called readings, imagine values 4 and 8 in group A and 15 in group B. The expression avg(value) OVER (PARTITION BY group_name) gives 6 beside both A rows and 15 beside B. The individual values remain visible.
The PostgreSQL window-function tutorial explains this distinction from ordinary grouping, which can collapse rows into a group result. This example deliberately omits window ordering to calculate over the whole partition.
- Confirm the rows included after filtering.
- Choose the field that defines each partition.
- Calculate the expected average for one small group by hand.
- Check that the output still contains the intended individual records.
The repeated average is context. Summing that column would count the group result repeatedly; it is not a new set of independent group measurements.
If you later add an ordering clause, inspect the window frame rather than assuming the calculation is unchanged. Preserve the exact query with the result so another reader can tell whether a number describes an entire group or a moving portion of it.
Editorial illustration from this site’s image library; not documentary evidence of the example or object discussed.