Analytical

Analytic functions compute a value for each row based on a group of related rows — for example running totals, moving averages and comparisons with preceding or following rows.

Function	Description
CUME_DIST	Calculates the cumulative distribution of a value in a group of values. That is, CUME_DIST computes the relative position of a specified value in a group of values. For a row _r_, assuming ascending ordering, the CUME_DIST of _r_ is the number of rows with values lower than or equal to the value of _r_, divided by the number of rows evaluated in the partition or query result set. CUME_DIST is similar to the PERCENT_RANK function.
FIRST_VALUE	Returns the first value in an ordered set of values.
LAG	Accesses data from a previous row in the same result set without the use of a self-join. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
LAST_VALUE	Returns the last value in an ordered set of values.
LEAD	Accesses data from a subsequent row in the same result set without the use of a self-join. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.
PERCENT_RANK	Calculates the relative rank of a row within a group of rows. Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition. PERCENT_RANK is similar to the CUME_DIST function.
PERCENTILE_CONT	Calculates a percentile based on a continuous distribution of the column value. The result is interpolated and might not be equal to any of the specific values in the column.
PERCENTILE_DISC	Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset. For a given percentile value _P_, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to _P_. For example, PERCENTILE_DISC (0.5) will compute the 50th percentile (that is, the median) of an expression. PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values; the result is equal to a specific value in the column.