|
|
-
|
|
Let Us Learn Oracle - Part 45 of N [ Compose and Decompose function of Oracle ]
These functions came into the Oracle world from version 9i
Compose Function
Purpose: The Compose function of Oracle returns a Unicode string
Syntax: Compose(string)
SQL> Select
2 Compose('A' || UniStr('\0300'......
|
|
-
|
|
Let Us Learn Oracle - Part 44 of N [ First and Last function of Oracle ]
Purpose: Both of these acts as analytical and aggregate function. They atc on a set of rows that act as First or Last as per the given sorting specification.
They accepts any numeric or non-numeric datatype that can be impli......
|
|
-
|
|
SELECT MatchID
2 ,MatchGroup
3 ,MatchBetween
4 ,ScheduleDate
5 ,PERCENTILE_Cont(.6) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY MatchGroup) Percentile_Cont
6 FROM MatchTable;
MATCHID ......
|
|
-
|
|
Total number of rows or records.
We have a set with 10 elements. We need to find the Percentile_Disc of the 7th row.Let us first find the Cumulative_distribution
So, Cumulative_Distribution = (7)/(10) = 0.7
So, Percentile_Disc =......
|
|
-
|
|
Let Us Learn Oracle - Part 41 of N [ Cume_Dist function of Oracle ]
It acts both as Aggregate and analytic function.
Analytic Example
Purpose: Within a group of rows, it calculates the cumulative distribution of a value in a group of values.
Syntax: Cume_Dist() Over ([partition_by_clause] ord......
|
|
-
|
|
Select EmpName,Salary,Ratio_to_Report(SALARY) Over() As RatioReportExample
2 From tblEmployee
3 Where DeptId = 1;
EMPNAME SALARY RATIOREPORTEXAMPLE
-------------------------------------------------- ---------- ------------------
Deepak Kum......
|
|
-
|
|
Total number of rows or records.
e.g. We have a set with 10 elements. We need to find the relative rank of the 7th row. Henceforth, applying the values to the above formula, we get
R = 7 , N = 10
So, Percent_Rank = (7-1)/(10-1) = 0.6666666666666667
The range of values return by this function is......
|
|
-
|
|
It helps to further limit the effect of analytical function.
As said, the Last_Value function returns the last value from the order set of values. If the first value is null, then the function returns null.Let us see this function into action.
Example 1: Simple Last_Value demo ......
|
|
-
|
|
It helps to further limit the effect of analytical function.
As said, the First_Value function returns the first value from the order set of values. If the first value is null, then the function returns null.Let us see this function into action.
Example 1: Simple First_Value d......
|
|
-
|
|
Indicates how the data is ordered within the partition.
N.B.~Lag and Lead ......
|
|