oracle 分析函数之lead和lag

      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;