Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

function

CUME_DIST as Analytical Function in Oracle SQL – PLSQL

by

The CUME_DIST function in Oracle SQL / PLSQL is used to get the cumulative distribution of a value in a group of values.

CUME_DIST function in Oracle SQL / PLSQL returns a value > 0 and <=1. The CUME_DIST function can be used in as an Aggregate and Analytical Function. Syntax for the CUME_DIST function as an Analytical Function in Oracle SQL / PLSQL is: SELECT column(s),
CUME_DIST() OVER (PARTITION BY column ORDER BY column(s))
FROM table_name;

Example:
Using CUME_DIST as ANALYTICAL function
Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query as:

SELECT department
       ,salary
       ,CUME_DIST () OVER (PARTITION BY department ORDER BY salary) CUME_DISTT
FROM employee;

We will get the following output:

Department Salary CUME_DISTT
IT 20000 0.5
IT 28000 1
Sales 10000 0.333333333333333
Sales 32000 0.666666666666667
Sales 40000 1
Support 30000 1

The above SQL query returns the cumulative distribution of employees within the employee table grouped by departments

CUME_DIST as Aggregate Function in Oracle SQL – PLSQL

by

The CUME_DIST function in Oracle SQL / PLSQL is used to get the cumulative distribution of a value in a group of values.

CUME_DIST function in Oracle SQL / PLSQL returns a value > 0 and <=1.

The CUME_DIST function can be used in as an Aggregate and Analytical Function.

Syntax for the CUME_DIST function as an Aggregate Function in Oracle SQL / PLSQL is:
SELECT column(s),
CUME_DIST(value1, value2 , . , valueN)
WITHIN GROUP (ORDER BY column1, column2, . . , columnN)
FROM table_name
GROUP BY column(s);

Note:

  • The Number of values / expressions in the CUME_DIST and the ORDER BY clause shoud be the same.
  • The values / expressions in CUME_DIST and ORDER BY clause are matched based on positions and hence the data types must be compatible between both

Example 1:

Using CUME_DIST as AGGREGATE function
Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query as:

 SELECT CUME_DIST (18000) WITHIN GROUP (ORDER BY salary) CUME_DISTT
FROM employee;

We will get the following output:

CUME_DISTT
0.285714285714286

The above SQL query returns the cumulative distribution of an employee having salary of 18000 within the employee table.

Example 2:
Using CUME_DIST as AGGREGATE function with GROUP BY clause

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query as:

SELECT department
       ,CUME_DIST (18000) WITHIN GROUP (ORDER BY salary) CUME_DISTT
FROM employee
GROUP BY department;

We will get the following output:

Department CUME_DISTT
IT 0.333333333333333
Sales 0.5
Support 0.5

The above SQL query returns the cumulative distribution of an employee having salary of 18000 within the employee table grouped by departments

USER Function in Oracle SQL – PLSQL

by

The USER function in Oracle SQL / PLSQL is used to retrieve the user id of the current Oracle Session.

Syntax for the USER function in Oracle SQL / PLSQL is:

SELECT USER
FROM table_name;

Example:

Using USER function to retrieve the user name for the current session

SELECT USER
FROM dual;

Will return the name of the user logged in the current Oracle Session e.g. “System” or “Scott”.

PERCENT_RANK Function in Oracle SQL – PLSQL

by

Oracle SQL / PLSQL PERCENT_RANK function is used to calculate the rank of a hypothetical row.

  • As an aggregate function a rank is calculated as r minus 1 divided by the number of rows in the aggregate group.
  • As an analytical function rank is calculated as r minus 1 divided by the number of rows in the aggregate group minus 1.
  • A Percent_Rank returns a value between 0 and 1 inclusive.

The SQL PERCENT_RANK function can be used in as an Aggregate and Analytical Function.

Oracle SQL / PLSQL PERCENT_RANK Function Syntax as an Aggregate Function is:

SELECT PERCENT_RANK(expression1, expression2 . . , expressionN)
WITHIN GROUP (ORDER BY column1, column2, . . , columnN)
FROM table_name;

Oracle SQL / PLSQL PERCENT_RANK Function Syntax as an Analytical Function is:

SELECT PERCENT_RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;

Example 1: Using Oracle SQL / PLSQL PERCENT_RANK Function as AGGREGATE function

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query Oracle SQL PERCENT_RANK Function with SELECT Statement as:

SELECT PERCENT_RANK(15000) WITHIN GROUP (ORDER BY salary)
FROM employee;

We will get ‘0.166666666666667’ as the PERCENT_RANK of 15000 because as per the data in the ‘employee’ table if we sort the ‘salary’ column from lowest to highest salary then 15000 will come at the 2nd position in that list and the total number of rows will be 6.

Hence the percent_rank for 15000 is calculated as:

(Position of the row-1/total rows) i.e. (2-1/6) = 1/6 = 0.166666666666667

Example 2: Using Oracle SQL / PLSQL PERCENT_RANK Function as ANALYTICAL function

As mentioned earlier, the syntax for the Oracle SQL PERCENT_RANK function as an Analytical Function is:

SELECT PERCENT_RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10

If we write our query Oracle SQL PERCENT_RANK Function with SELECT Statement as:

SELECT employee_name
       ,salary
       ,department
       ,PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) Percent_Ranking
FROM employee;

We will get the following output:

Employee_Name Salary Department Percent_Ranking
Emp B 20000 IT 0
Emp C 28000 IT 1
Emp A 10000 Sales 0
Emp E 32000 Sales 0.5
Emp F 40000 Sales 1
Emp D 30000 Support 0

Here we can see that Oracle SQL PERCENT_RANK function is being used as an analytical function.

It returns the position or rank of records in group of records partitioned by a criteria e.g. in our case we have partitioned the records by ‘department’ and then within that partition we are ranking the records relative to each other.

First row is always given a percent rank of ‘0’ by Oracle SQL PERCENT_RANK Function.

The PERCENT_RANK is this case is calculated as:

(Position Of the rows-1/total rows -1)

e.g. for Emp E percent rank is calculated using

(Position Of the rows-1/total rows -1) as (2-1/3-1) = (1/2) = 0.5

And for Emp F percent rank is calculated using

(Position Of the rows-1/total rows -1) as (3-1/3-1) = 2/2 = 1

DENSE_RANK Function in Oracle SQL – PLSQL

by

The Dense_Rank function in Oracle SQL / PLSQL is used to return the rank or position of a value in a group of values. It’s very similar to RANK function but RANK function can cause non-consecutive rankings if the tested values are same.

The DENSE_RANK function can be used in as an Aggregate and Analytical Function.

Syntax for the DENSE_RANK function as an Aggregate Function in Oracle SQL / PLSQL is:

SELECT DENSE_RANK(expression1, expression2, . . , expressionN)
WITHIN GROUP (ORDER BY column1, column2, . . , columnN)
FROM table_name;

Syntax for the DENSE_RANK function as an Analytical Function in Oracle SQL / PLSQL is:

SELECT DENSE_RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;

The number of expressions the DENSE_RANK function and ORDER BY clause must be the same and also the data types should be compatible.

Example 1:

Using DENSE_RANK as AGGREGATE function

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

If we write our query as:

SELECT DENSE_RANK(15000) WITHIN GROUP (ORDER BY salary)
FROM employee;

Will return ‘2’ as the rank of 15000 because as per the data in the ‘employee’ table if we sort the ‘salary’ column from lowest to highest salary then 15000 will come at the 2nd position in that list.

Example 2:

Using DENSE_RANK as ANALYTICAL function

Syntax for the DENSE_RANK function as an Analytical Function in Oracle SQL / PLSQL is:

SELECT DENSE_RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

If we write our query as:

SELECT employee_name
       ,salary
       ,department
       ,DENSE_RANK() OVER (PARTITION BY department ORDER BY salary) Dense_Ranking
FROM employee;
Employee_Name Salary Department Dense_Ranking
Emp B 20000 IT 1
Emp C 28000 IT 2
Emp A 10000 Sales 1
Emp E 32000 Sales 2
Emp D 30000 Support 1

Here we can see that DENSE_RANK function is being used as an analytical function and it returns the position or rank of records in group of records partitioned by a criteria e.g. in our case we have partitioned the records by ‘department’ and then within that partition we are ranking the records relative to each other.

‘Emp B’ is having a rank of 1 and ‘Emp C’ is having a rank 2 in ‘IT’ partition because we have sorted the list by ‘Salary’ and ‘Emp B’s salary’ is less than ‘Emp C’s salary’ within the ‘IT’ partition.

Similarly with in ‘Sales’ partition ‘Emp A’ is having less salary than ‘Emp E’ that’s why ‘Emp A’ is having rank 1 and ‘Emp E’ having rank 2.