Untitled

13/02/2024
=====================================================================================================================================
window function:-
window function applies aggregate function,ranking and analytic functions over a perticular window(set 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 comput the window function.

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

syntax:-
select columns fun() over(partition by ,order by) from table name

types of window function
fun():-
1 aggreate function:-count(),min(),max(),avg(),sum()
2.ranking function: row_number(),rank(),dense_rank()
3.analytic function: lead() ,lag()


use third;
-- create table Employee(id int ,name varchar(222),salary int ,age int ,city varchar(222));
-- insert into Employee values (1,"jiya",20000,23,"pune"),
-- (2,"tanish",30000,45,"pune"),
-- (3,"jay",40000,34,"nagpur"),
-- (4,"ram",20000,12,"nagpur"),
-- (5,"maya",40000,23,"nashik"),
-- (6,"neha",50000,23,"nashik"),
-- (7,"sweeti",40000,56,"nanded"),
-- (8,"rahul",20000,44,"pune"),
-- (9,"yash",30000,33,"pune")

1 Window function using aggregate function.

select * from Employee;
select city,count(id) from Employee group by city;

select id,city,count(id) over() as total from Employee;
select id,city,count(id) over(order by id) as total from Employee;
select id,city,count(id) over(partition by city Order by city) as total from Employee;
select id,city,sum(id) over(partition by city Order by city) as sum from Employee;
select id,city,avg(id) over(partition by city Order by city) as avg from Employee;

select id,city,count(id) over(partition by city Order by city) as total ,
sum(id) over(partition by city Order by city) as sum ,
avg(id) over(partition by city Order by city) as avg from Employee;

2. window function using ranking

select id , name ,salary , row_number() over(order by salary) as "row_num" ,
rank() over(order by salary) as "rank",
dense_rank() over(order by salary) as "dence_rank" from employee;

select id , name ,salary , row_number() over(partition by salary order by salary) as "row_num" ,
rank() over(partition by salary order by salary) as "rank",
dense_rank() over(partition by salary order by salary) as "dence_rank" from employee;

select id , name ,salary , city,row_number() over(partition by city order by salary) as "row_num" ,
rank() over(partition by city order by salary) as "rank",
dense_rank() over(partition by city order by salary) as "dence_rank" from employee;


========================================================================================================================================