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.
·
Rank Function
·
Dense_Rank Function
·
First Function
·
Last Function
·
Lag Function
·
Lead Function
·
Count Function
·
SUM Function
·
Max Function
·
AVG Function
·
First_Value Function
·
LAST_Value Function
·
LISTAGG Function
·
Max Function
·
Min Function
·
MEDIAN Function
Employee_Stronics Table is:
Rank
Function:
Function used to rank
the job of employee with respect to their payment order by their department
number.
Syntax:
analytic_function ([arguments]) OVER (analytic_clause)
Example:
SELECT emp_id,
emp_name,
dept_no,
Salary,
RANK () OVER (PARTITION BY
dept_no ORDER BY
salary) AS Emp_Rank
FROM Employee_Stronics;
Result:
Dense_Rank
Function
This function also work
like Rank functions except that it assigns consecutive ranks.
Syntax:
analytic_function([ arguments ]) OVER (analytic_clause)
Example:
SELECT emp_id,
emp_name,
dept_no,
Salary,
DENSE_RANK () OVER (PARTITION BY dept_no ORDER BY
salary) AS Emp_Rank
FROM Employee_Stronics;
Result:
First
Function:
The function give the first payment of
employee with respect to his department number is called First Function.
Example:
SELECT emp_id,
emp_name,
dept_no,
Salary,
MIN (salary) KEEP (DENSE_RANK FIRST ORDER BY
salary) OVER (PARTITION BY dept_no) AS lowest
FROM Employee_Stronics;
Result:
Last
Function:
The function give the Last payment of
employee with respect to his department number is called First Function.
Example:
SELECT emp_id,
emp_name,
dept_no,
Salary,
MAX
(salary) KEEP (DENSE_RANK LAST ORDER BY
salary) OVER (PARTITION BY dept_no) AS highest
FROM Employee_Stronics;
Result:
Lag
Function:
The LAG function is used for access data from a previous row. That's query returns the salary from the previous row to calculate difference between salary of the current row and that of the previous row. Notice that ORDER BY of the LAG function is used to order the data by salary.
Syntax:
LAG (value_expression [, offset] [, default])
OVER ([query_partition_clause] order_by_clause)
Example:
SELECT emp_id,
emp_name,
dept_no,
Salary,
LAG
(salary, 1, 0) OVER (ORDER BY
salary) AS sal_prev,
Salary - LAG (salary, 1, 0) OVER (ORDER BY
salary) AS sal_diff
FROM Employee_Stronics;
Result:
Lead
function:
The LEAD function is used for return data from the next row. That's query returns the salary from the next row to calculate difference between salary of the current row and the following row.
Syntax:
LEAD (value_expression [, offset] [, default])
OVER ([query_partition_clause] order_by_clause)
Example:
SELECT emp_id,
emp_name,
dept_no,
Salary,
LEAD
(salary, 1, 0) OVER (ORDER BY
salary) AS sal_next,
LEAD (salary, 1, 0) OVER (ORDER BY
salary) - salary AS sal_diff
FROM Employee_Stronics;
Result:
Count
Function:
The count function
shows the total number of employee with the grading of their salary.
Example:
SELECT emp_name, salary,
COUNT (*)
OVER (ORDER BY salary) AS mov_count
FROM Employee_Stronics
ORDER BY salary, emp_name;
Result:
SUM
Function:
The SUM function shows
the total Salary of employee getting from company with respect to emp_id.
Example:
SELECT emp_id, emp_name, salary,
SUM
(salary) OVER (PARTITION BY emp_id) Total_salary
FROM Employee_Stronics;
Result:
AVG
Function:
The function used to Analysis the average
value of employee salary with respect to department number is called AVG
Function.
Example:
SELECT emp_id, emp_name, dept_no,
salary,
AVG (salary) OVER (PARTITION BY dept_no) AS Dept_Avg_salary
FROM Employee_Stronics;
Result:
First_Value
Function:
The function which analyses the record
and give the first value of the record is known as First_Value Function.
Example:
SELECT emp_id, emp_name, salary,
FIRST_VALUE (emp_name)
OVER (ORDER BY emp_name) AS First_Value
FROM Employee_Stronics;
Result:
LAST_Value
Function:
The function which analyses the record
and give the Last value of the record with respect to department number is
known as Last_Value Function.
Example:
SELECT emp_id, emp_name, salary,
LAST_VALUE (emp_name)
OVER (ORDER BY dept_no) AS LAST_VALUE
FROM Employee_Stronics;
Result:
LISTAGG
Function:
The function which analyses the record
and give the Employee name list with respect to department number is known as
LISTAGG Function.
Example:
SELECT dept_no "Dept.",
emp_name "Name”, salary "salary",
LISTAGG (emp_name, '; ') WITHIN GROUP (ORDER BY salary)
OVER (PARTITION BY dept_no) as "Emp_list"
FROM Employee_Stronics;
Result:
MAX
Function:
The function which analyses the record
and give the Maximume Salary of Employee with respect to department number is
known as MAX Function.
Example:
SELECT emp_id, dept_no, emp_name,
salary,
MAX (salary) OVER (PARTITION BY dept_no) AS MAX_Salary
FROM Employee_Stronics;
Result:
MIN
Function:
The function which analyses the record
and give the Minimum Salary of Employee with respect to department number is
known as MAX Function.
Example:
SELECT emp_id, dept_no, emp_name,
salary,
Min (salary) OVER (PARTITION BY dept_no) AS Min_Salary
FROM Employee_Stronics;
Result:
MEDIAN
Function:
The function which analyses the record
and give the MEDIAN Salary of Employee with respect to department number is
known as MEDIAN Function.
Example:
SELECT emp_id, dept_no, salary,
MEDIAN (salary) OVER (PARTITION BY dept_no) "Median by EMP SALARY"
FROM Employee_Stronics;
Result:
Note: Document very important for Database Development.
 Part 1){kind=link}
0 Comments