Untitled

27/02/2024
==========================================================
Window function:-
window function applies aggreate functiuon ,ranking,analytic functions over a perticular window (sewt of rows)

over :-
over clause is used with window function to define that window

partition by : 
It is used for divides the row into partition so we can specify which rows to use for compute the window function.

order by :
it is used for we can order row s within each partition.

 
syntax:- select columns,func() over(partition by column order by )
from table_name

Fun():
1:- aggregate  function:-min(),max(),avg(),sum(),count()

2.ranking function:- row_number,rank,dense_rank

3.Analytic Function: lead,lag ,first_value,last_value 



use sp;
create table Employee(emp_id int primary key,emp_name varchar(222),salary int, addr varchar(22),dept varchar(22));
insert into Employee values(101,"aniket",20000,"pune","IT"),
(102,"pranita",30000,"pune","HR"),
(103,"sweta",10000,"nashik","Finance"),
(104,"rohit",25000,"nagpur","IT"),
(105,"roshan",21000,"nanded","IT"),
(106,"subho",20000,"nagpur","HR"),
(107,"nikita",10000,"pune","HR"),
(108,"aniket",30000,"nagpur","HR"),
(109,"pranita",40000,"nashik","Finance"),
(110,"ratna",50000,"mumbai","Finance");

select * from employee;
1 Aggregate function

#aggreate function as window function
select count(*) from Employee;
select emp_id,emp_name ,dept,count(emp_id) over() from Employee;
select emp_id,emp_name ,dept,count(emp_id) over(order by dept ) from Employee;
select emp_id,emp_name ,dept,count(emp_id) over(partition by dept order by dept ) from Employee;


select emp_id,emp_name ,dept,min(emp_id) over(),max(emp_id) over() from Employee;
select emp_id,emp_name ,dept,min(emp_id) over(order by dept ) ,max(emp_id) over(order by dept )from Employee;
select emp_id,emp_name ,dept,min(emp_id) over(partition by dept order by dept ),max(emp_id) over(partition by dept order by dept ) from Employee


================================================================================================================================
2.ranking:

-: row_number: row_number function can provide a unique sequnce number for each row.
starting with 1, according to the ordering of rows within the window partition.

-: rank:-rank() function is used to genearate rank for each row within a result set.
rank function gives you to ranking within your ordered partition.
tier are assign same rank, with the next ranking skipped,so if you have 3 items at rank 4, the next rank listed would be ranked 7.

:-dence_rank: dence_rank gives  you the ranking within your orderd partition, but the rank are consecutive . no rank are skipped if there ranks with multiple items.

exa:-

select emp_id,dept ,row_number() over(order by dept) as rnum,
rank() over(order by dept) as rnk,
dense_rank() over(order by dept) as decn from Employee;