Untitled

28/02/2024
===========================================================================================
window function
ranking:-
use testdb;
select * from customer;
select cust_id ,row_number() over(order by cust_id) ,rank() over(order by cust_id) ,dense_rank() over(order by cust_id) from customer;


select cust_id ,address ,row_number() over(partition by address order by cust_id) ,
rank() over(partition by address order by cust_id) ,dense_rank() over(partition by address order by cust_id) from customer;

select cust_id ,address ,age,row_number() over(partition by address order by age) ,
rank() over(partition by address order by age) ,dense_rank() over(partition by address order by age) from customer;

update customer set age=34 where cust_id=101;
insert into customer value(112,"ram","pune",45)

===============================================================================================================================
Analytic function:-

select * from customer;

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

select cust_id,first_value(cust_id) over(),last_value(cust_id) over() from customer;
select cust_id,first_value(cust_id) over(order by cust_id),last_value(cust_id) over(order by cust_id) from customer;
select cust_id,address,first_value(cust_id) over(partition by address order by cust_id),last_value(cust_id) over(partition by address order by cust_id) from customer.


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