Analytical Functions in Oracle 11g database. (Database Developing) Part 2

Note: Document very important for Database Development.

Analytical Functions:

             The function which are used to “Over Clause” is known as Analytical function. As shown in Examples.These function are used to analyses the records.Some analytical functions are following.

·        Variance Function

·        Var_samp Function

·        Var_Pop Function

·        STDDEV

·        STDDEV_Samp

·        STDDEV_Pop

·        Row_Number Function

·        Ratio_To_Report Function

·        Percent_Rank Function

·        Percentile_Count Function

·        NTILE Function

·        N^th Value Function

·        CUME_DIST Function

Variance Function:

       The function return the Variance of Salary of Employee with respect to department number is known as Variance Function.

Formula:

   SUM ((expr - (SUM (expr) / COUNT (expr))) ²) / COUNT (expr)

Example:

SELECT emp_name, dept_no, salary, VARIANCE (salary)

      OVER (ORDER BY dept_no) "Variance"

   FROM Employee_Stronics;

  Result:

         

Var_samp Function:

       The function return the Variance of Salary/Population of Employee with respect to department number with ignoring the null values is known as Var_samp Function.

Var_Pop Function:

       The function return the Variance of Salary/Population of Employee with respect to department number without ignoring the null values is known as Var_Pop Function.

Example:

SELECT emp_name, dept_no, salary,

   VAR_POP (SUM (salary))

      OVER (ORDER BY dept_no) "Var_Pop",

   VAR_SAMP (SUM (salary))

      OVER (ORDER BY dept_no) "Var_samp"

  FROM Employee_Stronics

   GROUP BY emp_name, dept_no, salary;

  

Result:

     

STDDEV Function:

The function return the square root of Variance of salary/Population of employee with respect to department number is known as STDDEV Function.

Formula:

                          (Variance)^(1/2)

Example:

  SELECT dept_no, emp_name, salary,

   STDDEV (salary) OVER (ORDER BY dept_no) "StdDev"

   FROM Employee_Stronics;

  Result:

STDDEV_SAMP Function:

       The function return the square root of Var_samp of salary/Population of employee with respect to department number is known as STDDEV_Samp Function.

Formula:

               (Var_samp)^(1/2)

 

Example:

SELECT dept_no, emp_name, salary,

   STDDEV_SAMP (salary) OVER (PARTITION BY dept_no) AS Emp_sdev

   FROM Employee_Stronics;

  Result:

                

STDDEV_Pop Function:

       The function return the square root of Var_Pop of salary/Population of employee with respect to department number is known as STDDEV_Pop Function.

Formula:

                 (Var_Pop)(1/2)

Example: 

SELECT dept_no, emp_name, salary,

   STDDEV_POP (salary) OVER (PARTITION BY dept_no) AS Pop_sdev

   FROM Employee_Stronics;

  

Result:

               

Row_Number Function:

       The function return the number of employee with respect to their department number is known as Row_Number Function.

Example:

SELECT emp_name, dept_no, salary, SUM (salary) Amount,

    ROW_NUMBER () OVER (PARTITION BY dept_no

    ORDER BY SUM (salary) DESC) Row_Number

     FROM Employee_Stronics

     GROUP BY emp_name, dept_no, salary;  

Result:

        

Ratio_To_Report Function:

       The function return the Ratio of employee Salary with respect to their department number is known as Ratio_To_Report Function.

Example:

SELECT emp_name, dept_no, salary, RATIO_TO_REPORT (salary)

 OVER (PARTITION BY dept_no) AS Ratio_To_Report_Salary

   FROM Employee_Stronics;

Result:

     

Percent_Rank Function:

       The function return the Rank of employee with respect to their department number in ascending order is known as Percent_Rank Function. First value in record always taken as null as shown in Example.

Example:

   SELECT emp_name, dept_no, salary, PERCENT_RANK ()

       OVER (PARTITION BY dept_no ORDER BY salary ASC) AS Percent_Rank

  FROM Employee_Stronics;

Result:

       

Percentile_Count Function:

       The function return the Percentile value of employee record with respect to their department number in ascending order is known as Percentile_Count Function. If percentile value not exit then show the median of record as show in following Example.

Example:

   SELECT emp_name, dept_no, salary,

       PERCENTILE_CONT (0.8) WITHIN GROUP (ORDER BY salary DESC)

         OVER (PARTITION BY dept_no) "Percentile_Count"

  FROM Employee_Stronics;

Result:

      

NTILE Function:

       The function divide the number of records into bucket and if number of record increase then it use the following formula.

We have 7 record in table and bucket are 4. So we will generate (7/4=3). 3 new bucket with the numbering 1, 2, 3. As shown in example.

Example:

SELECT emp_name, dept_no, salary, NTILE (4) OVER (ORDER BY salary ASC) AS quartile

  FROM Employee_Stronics;

Result:

        

N^th_Value Function:

       The function return the nth value of the salary of employee with respect to his department number and Employee id in descending order.

Example:

SELECT emp_id, dept_no, MIN (salary),

    NTH_VALUE (MIN (salary), 1) OVER (PARTITION BY dept_no ORDER BY emp_id DESC) N^th_Salary

  FROM Employee_Stronics

  GROUP BY emp_id, dept_no;

Result:

      

CUME_DIST Function:

       The function return the Cumulative distribution of the salary of employee with respect to his department number and in descending order.

Example:

SELECT emp_id, dept_no, MIN (salary),

    NTH_VALUE (MIN (salary), 1) OVER (PARTITION BY dept_no ORDER BY emp_id DESC) N^th_Salary

  FROM Employee_Stronics

  GROUP BY emp_id, dept_no;

Result:

     

Note: Document very important for Database Development.