I have a table:

fname|o_details| cost
eva  |coat|125
eva  |coat|225
eva  |shirt|60
eva  |slipper|20
farida|coat|100
farida|shirt|50
farida|shoes|80
farida|skirt|30
henry|shoes|80

I am trying to understand difference between:

sum(cost) over(partition by fname order by fname desc) as part_by_fname,
sum(cost) over(partition by fname order by fname,o_details desc) as part_by_both

I had a understanding that order by in over clause just change the order and we apply the window aggregation function based on the partitions. But to my surprise I am getting the output as attached. .

Please explain the logic behind these two over clauses and what makes them different

CodePudding user response：

From 9.22. Window Functions:

When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. An aggregate used with ORDER BY and the default window frame definition produces a “running sum” type of behavior, which may or may not be what's wanted. To obtain aggregation over the whole partition, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.

When you ORDER BY fname, all rows within the partition have the "same" position in that order. You also PARTITION BY fname, so ORDER BY fname has no effect. That follows that the window frame is the same for all rows in the partition and therefore the function's results.

Then, when you ORDER BY o_details, it has an effect. The position of the rows in the partition is not the same for all of them anymore. And as the frame is relative to the position of the row in that order, it's different for almost each row and so are the function's results. I wrote almost, because this does not completely apply to the two fnames of 'eva' with the same o_details of 'coat'. They share one position. So for these two rows the function's results are again the same for the a fore mentioned reasons.