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

How does each runner compare with the first and last place finisher? What about the nth place finisher? First_value, last_value, and nth_value can make these decisions.

https://app.egghead.io/api/v1/lessons/postgresql-compare-the-outliers-using-first-value-and-last-value/subtitles

[00:00] What if the rows we want to compare to are not immediately above or below our rows? They're not lagging or leading. We can get the first value in a partition. We can ask what the first place runner got in our race. We can partition by our individual division.

[00:18] This will be first place's pace. We can see how much we lost to first place by. We see in division one, 3.08 is the first place pace. Then, we can also look at the last value, although this will be a little bit different.

[00:37] If we just partition by division, that would be the right thing, but we do want to make sure that we're still ordering by pace, ascending. This throws everything topsy-turvy, because this will only be the rows that have already been processed. We actually just want the last value in the partition.

[01:02] This is where we need to use a row statement again. We need to be rows between unbounded preceding and unbounded following, so that we actually consider the entire frame of reference when we're looking for the last value with this type of ordering.

[01:19] This is our last place pace. Then it's very simple for us to compare how much we won or lost by. The same way we've done before, we'll use a sub-query. That's the query we need.

[01:38] Now we can say, "This is how much we lost to first place by. The first place person did not lose to first place by anything. This is how much they beat last place by." We can see the same thing in our frame down here at the bottom for the loser.

Compare the Outliers Using First Value and Last Value

Share this video with your friends

Course

Using Postgres Window Functions