Untitled

15/02/2024
=========================================================================================================================
analytic function in Window function :
________________________________________
analytic function:
1.Lead()
2. lag()
3. First_value
4.last_value

use first;
select * from customers;

select customer_id,
lead(customer_id) over(order by  customer_id) as leadvalue ,
lag(customer_id) over(order by  customer_id) as lagvalue
from customers;


select customer_id,country,
lead(customer_id) over(partition by country order by customer_id ) as leadvalue ,
lag(customer_id) over(partition by country order by  customer_id) as lagvalue
from customers;


select customer_id,
lead(customer_id) over() as leadvalue ,
lag(customer_id) over() as lagvalue
from customers;

select age,
lead(age) over(order by  age) as leadvalue ,
lag(age) over(order by  age) as lagvalue
from customers;


select age,
lead(age) over() as leadvalue ,
lag(age) over() as lagvalue
from customers;

 select customer_id,
first_value(customer_id) over(order by customer_id) as fistvalue,
last_value(customer_id) over(order by customer_id) as lastvalue
from customers;

select age,
first_value(age) over(order by age) as fistvalue,
last_value(age) over(order by age) as lastvalue
from customers;

select customer_id,country,
 first_value(customer_id) over(partition by country order by country) as fistvalue,
last_value(customer_id) over(partition by country order by country) as lastvalue
 from customers;


offset the lead and lag value by 2 in the output

select customer_id,
lead(customer_id,2) over(order by  customer_id) as leadvalue ,
lag(customer_id,2) over(order by  customer_id) as lagvalue
from customers;
------------------------------------------------------------------------------------------------------------------------------