24 July, 2011

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

1 comment:

  1. Awesome post. You have listed most of the important functions with their relative meaning that are primarily used by the programmers. This post is really helpful to me as it acts as a quick guide to know and understand all these popular functions. Thanks for sharing.
    sap project management

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...