Learn What A Window Function Can Do

Rank Records with Rank and Dense Rank

Perform Weighted Ranking with Percent Rank

Define Windows Using Multiple Factors

Find Percentiles Using ntile

Create Running Totals Using Window Functions

Understand Frame Clauses

Compare Related Rows Using Lag and Lead

Compare the Outliers Using First Value and Last Value

Combine Window Functions to Answer Complex Questions

Using Postgres Window Functions

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.

Course

Using Postgres Window Functions

Understand Frame Clauses

Share this video with your friends