oracle 分析函数之lead和lag

kgzdlgbjge

　　Lag

和Lead

函数可以在一次查询中取出同一字段的前N行

的数据和后N行

的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。
　　
　　语法：Lead
(expression, offset, default) over(partition-clause order-by-clause)

　　

　　参数说明： 

            offset 偏移，表示查询其后第n条记录， 如果不提供这个参数，就是默认为1；

            default 默认值，如果没有找到。如果没有设置，且找不到，那么就返回Null；


            query_partition_clause  分区语句，对结果集合分区的语句，是可选的，如果没有就是所有的一个分区；

            Order_by_clause 排序语句 必须需要 ,
如果没有order by 子句，就不是“连续”的。

　　
　　建表语句如下：



create table SMALL_CUSTOMERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 10);
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 20);
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 30);
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 5);
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 10);
insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 1);
　　
　　Lead具体用法:


select t.customer_id,
t.sum_orders,
lead(t.sum_orders) over(partition by t.customer_id order by t.sum_orders)
from small_customers t; 

　　
　　用Lead实现行转列：

　　第一步：

　　

　　

select customer_id,
sum_orders,
lead(t.sum_orders, 1) over(partition by customer_id order by t.sum_orders) sum_orders2,
lead(t.sum_orders, 2) over(partition by customer_id order by t.sum_orders) sum_orders3,
rownum rn
from small_customers t
order by rn;

　　

　　
　　第二步,过滤数据：


select *
from (select customer_id,
sum_orders,
lead(t.sum_orders, 1) over(partition by customer_id order by t.sum_orders) sum_orders2,
lead(t.sum_orders, 2) over(partition by customer_id order by t.sum_orders) sum_orders3,
rownum rn
from small_customers t
order by t.sum_orders)
where mod(rn, 5) = 1;

　　
　　第三步,组装数据：


select customer_id, sum_orders || ',' || sum_orders2 || ',' || sum_orders3
from (select customer_id,
sum_orders,
lead(t.sum_orders, 1) over(partition by customer_id order by t.sum_orders) sum_orders2,
lead(t.sum_orders, 2) over(partition by customer_id order by t.sum_orders) sum_orders3,
rownum rn
from small_customers t
order by t.sum_orders)
where mod(rn, 5) = 1;

　　
　　Lag与Lead语法相同，区别是Lag表示查询其前第n条记录


　　



  select t.customer_id,
t.sum_orders,
lag(t.sum_orders) over(partition by t.customer_id order by t.sum_orders)
from small_customers t;