Understand Frame Clauses
What if you want aggregations that aren’t running totals? What if you want the sum total for the whole frame? Or the minimum value across the frame? Unbounded preceding, unbounded following, and current row help you define your frame of reference.
[00:00] Windows can have different frames of reference. Those are created with a rows clause. You could say "rows between." The default is unbounded preceding and current row. What that says is that all the rows in my window that come before me, plus myself.
[00:23] In this case, since we're creating a sum of the contributions, the sum of 500 and everything before is just 500. In this case, the sum of 30 and 500 is 530. In this row, the sum of all three of these, 555, etc., by the time we'd get to this last row. That's why this creates a running total for us, because that's the default when we use an order by clause.
[00:53] If we remove all of this. Now, that's not what we're going to get. What we're getting here is rows between unbounded preceding, and unbounded following. This will be the same thing.
[01:15] What that says is current row, plus all of the previous rows up here, nothing in all of the following rows. For all of these, you're going to get exactly the sum total of all of them.
[01:29] But if we want to use the same behavior even without the order by clause, now, we just use "and current row," and we get that running total again.
[01:41] Likewise, we could say between current row and unbounded following. That will do the running total in reverse. This one is all of the rows. Then, we keep subtracting until we get down to just the last row in the frame.
Thanks, Michael. More in development right now :)
Great example.
I really like this format. No BS, speedy and clear. Nothing new to me yet, but I'm really hoping that you will create some more advanced Postgres videos in this format. Good work.