Oracle analytical functions - Part 2

That is part 2 that I will explain purpose of every analytic function I mentioned it in part 1

AVG

Returns a running average.

COUNT 

Returns a running count of all records or by partition.

FIRST

Returns the row ranked first using DENSE_RANK.

Syntax :-

aggregate_function(column_name) KEEP
(DENSE_RANK FIRST ORDER BY <column_name> [<ASC|DESC> NULLS <FIRST|LAST> ])

FIRST_VALUE

Returns the first value in an ordered set of values. If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS

Syntax :-

FIRST_VALUE(<expression> [IGNORE NULLS]) OVER (<analytic clause>)

LAG

LAG provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.

Syntax :-

LAG(<value expression>, <offset>, <default>)OVER ([<query partition clause>] <order_by_clause>)

 

LAST

Returns the row ranked last using DENSE_RANK

Syntax :-

<aggregate function> KEEP (DENSE_RANK LAST ORDER BY (<expression> <ASC | DESC> NULLS <FIRST | LAST>)

LAST_VALUE

Returns the last value in an ordered set of values. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. This setting is useful for data densification. If you specify IGNORE NULLS, then LAST_VALUE returns the first non-null value in the set, or NULL if all values are null.

Syntax :-

LAST_VALUE (<expression> IGNORE NULLS) OVER (<analytic clause>)

LEAD

LEAD provides access to a row at a given physical offset beyond that position

Syntax :-

LEAD(<expression, offset, default>)[(<query_partition_clause>)] OVER (<order_by_clause>) 

MAX

Returns the maximum value by partition

MIN

Returns the minimum value by partition

RANK

Calculates the rank of a value in a group of values

Syntax :-

RANK(<value>) OVER (<partition_clause> ORDER BY <order_by_clause>)

ROW_NUMBER

Assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order by clause, beginning with 1.

Syntax :-

ROW_NUMBER(<value>) OVER (<partition_clause> ORDER BY <order_by_clause>)

SUM 

Computes the cumulative sample running sum

VARIANCE
Returns the variance of an expression

Thanks