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 valueDENSE_RANK()
: Rank of the current row within its partition, without gapsFIRST_VALUE()
: The value of the argument from the first row of the window frameLAG()
: The argument value from the row lagging the current row within partitionLAST_VALUE()
: Value of argument from the first row of window frameLEAD()
: Value of argument from row leading current row within partitionNTH_VALUE()
: Argument value from n-th row of window frameNTILE()
: Bucket number of the current row within its partitionPERCENT_RANK()
: Percentage rank valueRANK()
: Rank of the current row within its partition, with gapsROW_NUMBER()
: Number of the current row within its partition