Untitled

16/02/2024
=========================================================================================================================
Window Function:-
window function applies aggregate ,ranking,analytic function over particular window(set of rows).
over clause is used with window functions to define that window.


Syntax:-select column_name,func() over(partition by column,order by column) from table_name.

partition by :- partition by divides the row partition so we can specify which rows to use to compute the window function.

Function:
1. agrregate function:- count(),min(),max(),sum(),avg()
2. ranking function : row_number, rank,dense_rank
3. analytic functions: lead,lag,first_value,last_value
----------------------------------------------------------------------------------
1. aggreate Function:
use first;
select * from customer;
select * from employeeinfo;
select count(*) from customer;

select customer_id,cust_name, count(salesman_id) over() as count from customer;
select customer_id,cust_name,salesman_id, count(salesman_id) over(order by salesman_id) as count from customer;
select customer_id,cust_name,salesman_id, count(salesman_id) over(partition by salesman_id order by salesman_id) as count from customer;


select salesman_id,cust_name,
sum(salesman_id) over(partition by cust_name order by salesman_id) as sum ,
avg(salesman_id) over(partition by cust_name order by salesman_id) as avg ,
min(salesman_id) over(partition by cust_name order by salesman_id) as min ,
max(salesman_id) over(partition by cust_name order by salesman_id) as max ,
count(salesman_id) over(partition by cust_name order by salesman_id) as total 
from customer


-----------------------------------------------------------------------------------------------------------------------------------------
2.ranking function :

-: 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 salesman_id,row_number() over(order by salesman_id) as row_num ,
rank() over(order by salesman_id) as rank_num,
dense_rank() over(order by salesman_id) as dense_num from customer.

exa:
select salesman_id,city,row_number() over(partition by city order by salesman_id) as row_num ,
rank() over(partition by city order by salesman_id) as rank_num,
dense_rank() over(partition by city order by salesman_id) as dense_num from customer;


-------------------------------------------------------------------------------------------------------------------------
3. Analytic function:
select customer_id ,lead(customer_id) over(order by customer_id) as 'lead',
lag(customer_id) over(order by customer_id) as 'lag',
first_value(customer_id) over(order by customer_id) as 'first_value',
last_value(customer_id) over(order by customer_id) as 'last_value' from customer

---------------------------------------------------------------------------------------------------------------------------------------