Link: https://drive.google.com/file/d/1q9kcihrPaM4WP1ferWjDeSBuur91v4jF/view?usp=sharing
Download and open in Notepad++;
Window Function: Window Function applies aggregate, ranking and analytics functions over a particular window ( set of rows)
--When we Perform calculation of data using various aggregated function such as Max, Min and AVG,
we get single output row using these functions.
--
--Window Function performs an aggregate operation for each row and return result in details.
Windows Function Synatx:
Select column_name1, Column_Name2,...Column_Name(n),
fun() OVER( [<PARTITION BY Clause>)]
[ORDER BY Clause> ]
[ROW or RANGE Clause> ])
from table_name;
fun() -> aggregate functions or Ranking functions or Analytic Functions
window_function ([ALL]expression) over ( [PARTION_BY partition_list] [ORDER BY order_list])
->List of Window functions
• Over() --> The Over () is replacement of Group by. You can't use group by if using over().
The Over Clause is used to determine which rows from the query applied to the function.
An Over Clause is used with window functions to define that window.
Within the Over() Clause we can use PARTITION BY or ORDER BY.
• PARTITION BY : The PARTITION BY Clause is used to divide the result set from the query into data subsets .
->Aggregate functions
• Avg()
• Sum()
• Count()
• Min()
• Max()
-> Ranking Functions
• Rank()
• Dense_Rank()
• Row_Number()
• Ntile(N)
• PERCENT_RANK
-> Value/Analytic Functions
• LAG
• LEAD
• First_Value()
• Last_Value()
--Normal Group BY
SELECT department_id, MAX (salary)
FROM employees
GROUP BY department_id;
/* Formatted on 30/06/2023 11:28:03 (QP5 v5.336) */
--By Subquery mmethod
SELECT e.* , (select max(salary) from employees ) from employees e ;
By Window function:
SELECT e.* , max(salary) over() from employees e ;
/* Formatted on 30/06/2023 11:45:40 (QP5 v5.336) */
Q1.: Find the Maximum Salary , Average Salary and Name of the each employee from employees table.
/* Formatted on 30/06/2023 11:45:40 (QP5 v5.336) */
SELECT employee_id,
department_id,
First_Name,
MAX (salary) OVER () AS Maximum_Salary ,
AVG (salary) OVER () AS Average_Salary
FROM employees
198 50 Donald 24000 6461.83177570093
199 50 Douglas 24000 6461.83177570093
NB: if we are not mentioning partion by in Over() Caluse , then it will retrive the maximum salary for entire employees table.
Q2.: Find the Maximum Salary , Average Salary and Name of the each employee from employees table department wise .
/* Formatted on 30/06/2023 11:45:40 (QP5 v5.336) */
SELECT employee_id,
department_id,
First_Name,
MAX (salary) OVER (Partition By Department_id ) AS Maximum_Salary_Deptwise ,
AVG (salary) OVER (Partition By Department_id ) AS Average_Salary_Deptwise
FROM employees;
-> Rank(): The Rank() window function return a unique Rank Number for each distinct row within the partition
according to a specified column value . The Rank() always work on over() clause with ORDER BY.
Q3. Wite a Query to sort the employees salary on descending order.
/* Formatted on 30/06/2023 11:45:40 (QP5 v5.336) */
SELECT employee_id,
department_id,
First_Name,
salary,
Rank() OVER(order by salary Desc) As Salary_Rank
FROM employees;
100 90 Steven 24000 1
101 90 Neena 17000 2
102 90 Lex 17000 2
Q4. Wite a Query to sort the employees salary on descending order based on departmentwise .
/* Formatted on 30/06/2023 11:45:40 (QP5 v5.336) */
SELECT employee_id,
department_id,
First_Name,
salary,
Rank() OVER(Partition By Department_id order by salary Desc) As Salary_Rank
FROM employees;
200 10 Jennifer 4400 1
201 20 Michael 13000 1
202 20 Pat 6000 2
114 30 Den 11000 1
115 30 Alexander 3100 2
116 30 Shelli 2900 3
NB: Rank() skips positions after equal rankings.
The number of positions skipped depends on how many rows had an identical ranking.
Q5. Wite a Query to sort the employees salary on descending order based on departmentwise using Dense_rank().
/* Formatted on 30/06/2023 11:45:40 (QP5 v5.336) */
SELECT employee_id,
department_id,
First_Name,
salary,
Rank() OVER(Partition By Department_id order by salary Desc) As Salary_Rank,
Dense_Rank() OVER(Partition By Department_id order by salary Desc) As Salary_Dense_Rank
FROM employees;
189 50 Jennifer 3600 11 11
137 50 Renske 3600 11 11
141 50 Trenna 3500 13 12
Q5. Find 3rd Highest Salary from employees table by using Dense_Rank ().
select * from
(SELECT employee_id,
department_id,
first_name,
salary,
DENSE_RANK () OVER (ORDER BY salary DESC) AS RANK
FROM employees ) temp_emp
where temp_emp.RANK = 3 ;
Q6. Find 3rd Highest Salary from employees table by using Dense_Rank () departmentwise.
select * from
(SELECT employee_id,
department_id,
first_name,
salary,
DENSE_RANK () OVER (Partition By Department_id ORDER BY salary DESC) AS RANK
FROM employees ) temp_emp
where temp_emp.RANK = 3 ;
Q7. Find Top 4 salary from employees table.
select * from
(SELECT employee_id,
department_id,
first_name,
salary,
DENSE_RANK () OVER (ORDER BY salary DESC) AS RANK
FROM employees ) temp_emp
where temp_emp.RANK <=4 ;
• Row_Number()-> This is used to get a unique sequential number for each row in the specified data.
SELECT employee_id,
department_id,
First_Name,
salary,
ROW_NUMBER() OVER (order by Salary Desc) As Row_Num
FROM employees;
100 90 Steven 24000 1
101 90 Neena 17000 2
...
...
136 50 Hazel 2200 106
132 50 TJ 2100 107
--Assigning Row_num based on Departmentwise
SELECT employee_id,
department_id,
first_Name,
salary,
ROW_NUMBER() OVER (Partition By Department_id order by Salary Desc) As Row_Num
FROM employees;
200 10 Jennifer 4400 1
201 20 Michael 13000 1
202 20 Pat 6000 2
114 30 Den 11000 1
115 30 Alexander 3100 2
116 30 Shelli 2900 3
• Ntile(N)-> This function is used to distribute the number of rows in the specified(N) number of groups.
SELECT employee_id,
department_id,
first_name,
salary,
NTILE(3)Over(order by salary Desc) As Row_Num
FROM employees ;
-- This will create 3 groups .
PERCENT_RANK as an Aggregate Function:
/*
The PERCENT_RANK aggregate function is predictive, returning the rank as a percentage (between 0 and 1) of the
specified value in a set. As an aggregate function it reduces the number of rows, hence the term "aggregate".
If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated value.
In the following example we see the percent rank of the specified value relative to the salaries for all employees.
Login to Scott/tiger (Scott Schema)
SELECT PERCENT_RANK(2000) WITHIN GROUP (ORDER BY sal) AS percent_rank_sal,
ROUND(PERCENT_RANK(2000) WITHIN GROUP (ORDER BY sal)*100,2) AS percent_rank_sal_pct
FROM emp;
PERCENT_RANK_SAL PERCENT_RANK_SAL_PCT
---------------- --------------------
.571428571 57.14
*/
• LAG() : The LAG function has the ability to fetch data from a previous row.
• LEAD(): The LEAD function fetches the data from subsequent row .
Q10. Display salary of employee along with its previous employee and 2nd Previous employee based on emp_id .
SELECT employee_id,
department_id,
first_name,
salary,
hire_date,
LAG(salary) Over (order by employee_id ) As Prev_employee_salary,
LAG(salary,2) Over (order by employee_id ) As Second_Prev_employee_salary,
LAG(salary,2,0) Over (order by employee_id )As Null_Value_Set_Zero
FROM employees ;
100 90 Steven 24000 17/06/2003 0
101 90 Neena 17000 21/09/2005 24000 0
102 90 Lex 17000 13/01/2001 17000 24000 24000
103 60 Alexander 9000 03/01/2006 17000 17000 17000
Q10. Display salary of employee along with its previous employee and 2nd Previous employee based on hire_date .
SELECT employee_id,
department_id,
first_name,
salary,
hire_date,
LAG(salary) Over (order by hire_date ) As Prev_employee_salary,
LAG(salary,2) Over (order by hire_date ) As Second_Prev_employee_salary,
LAG(salary,2,0) Over (order by hire_date ) As Null_Value_Set_Zero
FROM employees ;
102 90 Lex 17000 13/01/2001 0
203 40 Susan 6500 07/06/2002 17000 0
204 70 Hermann 10000 07/06/2002 6500 17000 17000
205 110 Shelley 12008 07/06/2002 10000 6500 6500
Q11. Display salary of employee along with its next employee and 2nd next employee based on emp_id .
SELECT employee_id,
department_id,
first_name,
salary,
hire_date,
LEAD(salary) Over (order by employee_id ) As Next_employee_salary,
LEAD(salary,2) Over (order by employee_id ) As Second_Next_employee_salary
LEAD(salary,1,0) Over (order by employee_id ) As Zero_if_Next_emp_Sal_Null
FROM employees ;
Q12. Display the list of employees who is getting more or less salary than his previous employee.
SELECT employee_id,
department_id,
first_name,
salary,
hire_date,
LAG(salary,1) Over (order by employee_id ) As Prev_employee_salary,
LAG(salary,2) Over (order by employee_id ) As Second_Prev_employee_salary,
LAG(salary,1,0) Over (order by employee_id ) As Zero_if_Prev_emp_Sal_Null
FROM employees ;
Q12. Display the list of employees whose salary is HIGHER or LOWER than his previous employee.
/* Formatted on 01/07/2023 09:58:58 (QP5 v5.336) */
SELECT employee_id,
department_id,
first_name,
salary,
prev_emp_sal,
CASE
WHEN salary > prev_emp_sal THEN 'HIGHER'
WHEN salary < prev_emp_sal THEN 'LOWER'
ELSE 'EQUAL'
END SALARYCOMP
FROM (SELECT employee_id,
department_id,
first_name,
salary,
hire_date,
LAG (salary, 1, 0) OVER (ORDER BY employee_id) AS prev_emp_sal
FROM employees);
• First_Value() : it will return the first Value of an ordered set.
• Last_Value() : It will return the last value of an ordered set .
SELECT employee_id,
department_id,
salary,
FIRST_VALUE(salary) IGNORE NULLS
OVER (PARTITION BY department_id ORDER BY salary) AS lowest_in_dept
FROM employees;
SELECT employee_id,
department_id,
salary,
LAST_VALUE(salary) IGNORE NULLS
OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_in_dept
FROM employees;
1. How to find duplicate Records from a table.
a) SELECT empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno
FROM EMP_DUP
GROUP BY empno, ename, job,mgr,hiredate,sal,comm,deptno
HAVING COUNT (*) > 1;
b) WITH Q1
AS (SELECT EMPNO,ENAME,DEPTNO,ROW_NUMBER () OVER (PARTITION BY EMPNO, ENAME, DEPTNO ORDER BY EMPNO, ENAME, DEPTNO)rownumber
FROM EMP_DUP)
SELECT * from Q1 where rownumber > 1 ;
c)
SELECT *
FROM EMP_DUP A
WHERE ROWID >
(SELECT MIN (ROWID)
FROM EMP_DUP B
WHERE A.empno = B.empno
AND A.ename = B.ename
AND A.job = B.job
AND A.mgr = B.mgr
AND A.hiredate = B.hiredate
AND A.sal = B.sal
--AND NVL(A.comm,0) = NVL(B.comm,0)
AND A.deptno = B.deptno)
1. How to delete duplicate Records from a table.
METHOD1:
DELETE
FROM EMP_DUP A
WHERE ROWID >
(SELECT MIN (ROWID)
FROM EMP_DUP B
WHERE A.empno = B.empno
AND A.ename = B.ename
AND A.job = B.job
AND A.mgr = B.mgr
AND A.hiredate = B.hiredate
AND A.sal = B.sal
--AND NVL(A.comm,0) = NVL(B.comm,0)
AND A.deptno = B.deptno)
METHOD2:
WITH Q1
AS (SELECT EMPNO,ENAME,DEPTNO,ROW_NUMBER () OVER (PARTITION BY EMPNO, ENAME, DEPTNO ORDER BY EMPNO, ENAME, DEPTNO)rownumber
FROM EMP_DUP)
DELETE from Q1 where rownumber > 1 ; --MySQL Supported not suuported in Oracle
SELECT d.deptno,
d.dname,
empno,
ename,
hiredate,
sal,
SUM (sal) OVER (ORDER BY hiredate) running_total
FROM emp e, dept d
WHERE e.deptno = d.deptno;
DEPTNO DNAME EMPNO ENAME HIREDATE SAL RUNNING_TOTAL
---------- -------------- ---------- ---------- --------- ---------- -------------
20 RESEARCH 7369 SMITH 17-DEC-80 800 800
30 SALES 7499 ALLEN 20-FEB-81 1600 2400
30 SALES 7521 WARD 22-FEB-81 1250 3650
20 RESEARCH 7566 JONES 02-APR-81 2975 6625
30 SALES 7698 BLAKE 01-MAY-81 2850 9475
10 ACCOUNTING 7782 CLARK 09-JUN-81 2450 11925
30 SALES 7844 TURNER 08-SEP-81 1500 13425
30 SALES 7654 MARTIN 28-SEP-81 1250 14675
10 ACCOUNTING 7839 KING 17-NOV-81 5000 19675
20 RESEARCH 7902 FORD 03-DEC-81 3000 23625
30 SALES 7900 JAMES 03-DEC-81 950 23625
10 ACCOUNTING 7934 MILLER 23-JAN-82 1300 24925
20 RESEARCH 7788 SCOTT 19-APR-87 3000 27925
20 RESEARCH 7876 ADAMS 23-MAY-87 1100 29025
14 rows selected.
No comments:
Post a Comment