Untitled

18/03/2024:
======================================================================================================================
window function:-
==============================================================
Window function:-
window function applies aggreate functions ,ranking,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 compute the window function.

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

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


1:- Aggreagate function:- min(),max(),count(),sum(),avg()
2. ranking function :- rownumber, rank , dense_rank()
3. analytical function= lead ,lag, first_value, last_value

----------------------------------------------------------------------------------------------------------------------------------------
1:-Aggreagate function ;-

use testdb;
select * from customer;
select count(*) from customer ;
select customer_id, cust_name ,count(customer_id) over() from customer;
select customer_id, cust_name ,count(customer_id) over(order by customer_id) from customer;
select city,customer_id, cust_name ,count(customer_id) over(partition by city order by customer_id) from customer;

select customer_id, cust_name ,sum(customer_id) over() from customer;
select customer_id, cust_name ,sum(customer_id) over(order by customer_id) from customer;
select city,customer_id, cust_name ,sum(customer_id) over(partition by city order by customer_id) from customer;


select customer_id, cust_name ,max(customer_id) over() from customer;
select customer_id, cust_name ,max(customer_id) over(order by customer_id) from customer;
select city,customer_id, cust_name ,max(customer_id) over(partition by city order by customer_id) 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.

use testdb;
select * from customer;

update customer set cust_name= "Nick Rimando" where customer_id=14;
insert into customer values(13,"Nick Rimando" ,"New York",100 ),
(14,"Brad Davis" ,"New York ",200),
(15,"Graham Zusi" ,"California",200 ),
(16,"Julian Green" ,"London",300);
select customer_id,cust_name, city, row_number() over() as rownum from customer;
select  city, row_number() over(order by city ) as rownum , rank() over(order by city ) as rnk,dense_rank() over(order by city ) drnk from customer;
select  cust_name ,city, 
row_number() over(partition by cust_name order by city ) as rownum , 
rank() over(partition by cust_name order by city  ) as rnk,
dense_rank() over(partition by cust_name order by city  ) drnk from customer;

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

3. analytic function:-
use testdb;
select customer_id from customer;

select customer_id,cust_name ,lead(customer_id) over(),
lag(customer_id) over() from customer;

select customer_id ,cust_name,lead(customer_id) over(order by customer_id),
lag(customer_id) over(order by customer_id) from customer;

select customer_id ,cust_name,lead(customer_id) over(partition by cust_name order by customer_id),
lag(customer_id) over(partition by cust_name order by customer_id) from customer;

select customer_id,cust_name ,first_value(customer_id) over(),last_value(customer_id) over() from customer;
select customer_id ,cust_name,first_value(customer_id) over(order by customer_id),
last_value(customer_id) over(order by customer_id) from customer;

select customer_id ,cust_name,first_value(customer_id) over(partition by cust_name order by customer_id),
last_value(customer_id) over(partition by cust_name order by customer_id) from customer;