Untitled

mail@pastecode.io avatar
unknown
plain_text
2 months ago
1.1 kB
4
Indexable
Never
use test2;
select * from customer;
-- analytic function

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,cust_name , first_value(cust_name) over(order by cust_name) ,last_value(cust_name) over(order by cust_name) from customer;
select cust_id,cust_name , first_value(cust_id) over(order by cust_name) ,last_value(cust_id) over(order by cust_name) from customer;

select cust_id,cust_name,city , first_value(cust_id) over(partition by  city ) ,last_value(cust_id) over(partition by  city ) from customer;


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

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

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

Leave a Comment