1. Common window functions
In the previous lesson, we looked at using aggregations which we had reviewed earlier, but there are also specific functions which were created expressly for using with windows functions. This lesson covers four of the more commonly used windowing functions, FIRST_VALUE, LAST_VALUE, LEAD and LAG.
2. FIRST_VALUE() and LAST_VALUE()
FIRST_VALUE and LAST_VALUE return the first and last values in each window, respectively.
Thus, each one of them returns one value for the entire window. Because you are trying to find a specific record in a window, the position of the records in the window must be specified using the ORDER BY Command.
The first value from each window shown here is 28,000, 140,000, and 367,000.
3. FIRST_VALUE() and LAST_VALUE() in T-SQL
Here is the code used to create windows as shown on the previous slide and using them to select the first and last values of CurrentQuota for each window. The window is sorted based upon the values in the ModifiedDate field. The ORDER BY command is required in the OVER statement after the column being partitioned because the order values appear in the window and that determines the first or last value. In this query, the data is sorted by the ModifiedDate using ORDER BY.
4. Results
In this result slide, the values of StartQuota and EndQuota are the same across all the rows in a window. Now let's look at how to query the adjacent rows.
5. Getting the next value with LEAD()
Using LEAD, you can compare the value of the current row to the value of the next row in the window. For the last row of the window, the LEAD value will always be NULL, as there is no next value in the window.
Take a look at the values in this example. The window is represented by a green box which includes all of the rows for SalesYear 2011 because the window is partitioned by SalesYear. The value for the NextQuota column in each row comes from the CurrentQuota value in the next row. Because LEAD only evaluates within the window, the last row of NextQuota in each window is NULL as there is no next value.
Because LEAD always gets the next value, it is important to use ORDER BY to correctly determine the order of all rows in the window.
6. LEAD() in T-SQL
Here is the T-SQL code using LEAD. Notice LEAD is outside the window and the CurrentQuota column is passed to it. The window follows with the OVER keyword and is partitioned by the column SalesYear. LEAD requires the ORDER BY clause to order the values in the window, which is ordered by the ModifiedDate. Notice that each window ends with a NULL.
7. Getting the previous value with LAG()
Similarly, you can use LAG to compare the value of the current row to the value of the previous row in the window. For the first row of the window, the LAG value will always be NULL, as there is no previous value in the window.
Take a look at the values in this example. The window is represented by a green box which includes all of the rows for SalesYear 2011 because the window is partitioned by SalesYear. The value for the PreviousQuota column in each row comes from the CurrentQuota value in the previous row. Because LAG only evaluates within the window, the first row of PreviousQuota in each window is NULL as there is no previous value.
Because LAG always gets the previous value, it is important to use ORDER BY to correctly determine the order of all rows in the window.
8. LAG() in T-SQL
Here is the T-SQL code using LAG. LAG is also outside the window and the CurrentQuota column is passed to it. The window follows with the OVER keyword and is partitioned by the column SalesYear. Similar to LEAD, LAG also requires the ORDER BY clause to order the values in the window, which is ordered by the ModifiedDate. Notice that each window starts with a NULL.
9. Let's practice !
Go ahead and practice using these common window functions.