Window functions

By using window functions, for each row from a query, you can perform a calculation using rows related to that row. This is accomplished by using the OVER and WINDOW clauses.

Here is the list of calculations that you can do:

• CUME_DIST(): Cumulative distribution value
• DENSE_RANK(): Rank of the current row within its partition, without gaps
• FIRST_VALUE(): The value of the argument from the first row of the window frame
• LAG(): The argument value from the row lagging the current row within partition
• LAST_VALUE(): Value of argument from the first row of window frame
• LEAD(): Value of argument from row leading current row within partition
• NTH_VALUE(): Argument value from n-th row of window frame
• NTILE(): Bucket number of the current row within its partition
• PERCENT_RANK(): Percentage rank value
• RANK(): Rank of the current row within its partition, with gaps
• ROW_NUMBER(): Number of the current row within its partition

How to do it...

Window functions can be used in various ways. Let's get to...