Make delicious recipes!

Analytical functions


If a query uses group by, then its not possible to directly select columns other than those specified in the group-by clause. This is because the DB does not know which value to select corresponding to each set formed due to group-by.


Analytic functions are used to select records’ summary by forming partitions within a non-group-by query.

The same analytic functions can be used within group-by expressions to perform analysis over columns which are not present in the group-by clause also.


Example:


Syntax of analytic functions


Function(arg1,..., argn) OVER

( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )


General synatax of <Order by> for analytic functions is:

ORDER BY <sql_expr> [ASC or DESC] NULLS [FIRST or LAST]


General syntax of the <window_clause> is

[ROW or RANGE] BETWEEN <start_expr> AND <end_expr>


<start_expr> can be any one of the following

UNBOUNDED PECEDING

CURRENT ROW

<sql_expr> PRECEDING or FOLLOWING.

<end_expr> can be any one of the following

UNBOUNDED FOLLOWING or

CURRENT ROW or

<sql_expr> PRECEDING or FOLLOWING.




The functions SUM, COUNT, AVG, MIN, MAX are the common analytic functions the result of which does not depend on the order of the records.

Functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE depends on order of records.

Example 2:



Selecting other columns in Group By

The above analytic functions can be used to select other columns in a query having a group-by.

SELECT

ip, port, max(created_date) as created_date ,

max(certificate_id) keep (DENSE_RANK LAST ORDER BY created_date) as certId

FROM PORT

Group By ip, port;


In the above, since cert-id was not there in group by, it cannot be selected in the usual fashion because DB would not know which cert-id to select in a group made by grouping ip-ports. So we explicitly help the DB in making this choice by asking it to order by created_date and then choosing the last cert-id after an order by. The max(created_date) helps when there are more than one last elements after order by.


For example: when there are equal created_dates which form the maximum. Hence max helps the DB to choose max cert-id in such a case).


Difference between Rank and Dense_Rank


Both rank and dense_rank assign numerical values to a set of data by ordering on some criteria. If there is a tie, then same rank is assigned to the entries having the tie. Difference between the two is that Dense_Rank begins assigning immediately from the next numerical value (after the tie), while Rank drops that many numerical values as there were elements in the tie. Example:




Like us on Facebook to remain in touch
with the latest in technology and tutorials!


Got a thought to share or found a
bug in the code?
We'd love to hear from you:

Name:
Email: (Your email is not shared with anybody)
Comment:

Facebook comments:

Site Owner: Sachin Goyal