oarcle lag()分析函数

lag()是一个分析函数，oracle 8i引入的，8i以后的版本都可用。

用此函数，你可以一次性从表中查询多条数据，而不需要将此表自连接。

它返回表中的当前行的前offset行的指定列值。

语法：

lag ( expression [, offset [, default] ] )

over ( [ query_partition_clause ] order_by_clause )

expression :可以包含oracle内置函数，但不能是任何分析函数。

offset :可选参数，是表中与当前行的物理偏移度，如果省略，默认值为1，

        既当前行的前offset行。

default :可选参数，如果offset参数指向超出了表的范围，就返回这个值，默认值为null。

我们来看下面的实例：

 

 

原文：http://www.techonthenet.com/oracle/functions/lag.php

view plain copy to clipboard print ?

1. SQL> select ename,sal from scott.emp  
2.   2  where sal>1500;  
3. ENAME            SAL  
4. ---------- ---------  
5. ALLEN        1600.00  
6. JONES        2975.00  
7. BLAKE        2850.00  
8. CLARK        2450.00  
9. SCOTT        3000.00  
10. KING         5000.00  
11. FORD         3000.00  
12. --  
13. 1.下面使用默认值null  
14. SQL> select ename,job,sal,  
15.   2         lag(sum(sal),1) over (order by sal)  
16.   3  from scott.emp  
17.   4  where sal>1500  
18.   5  group by ename,job,sal;  
19. ENAME      JOB             SAL LAG(SUM(SAL),1)OVER(ORDERBYSAL  
20. ---------- --------- --------- ------------------------------  
21. ALLEN      SALESMAN    1600.00   
22. CLARK      MANAGER     2450.00                           1600  
23. BLAKE      MANAGER     2850.00                           2450  
24. JONES      MANAGER     2975.00                           2850  
25. FORD       ANALYST     3000.00                           2975  
26. SCOTT      ANALYST     3000.00                           3000  
27. KING       PRESIDENT   5000.00                           3000  
28. --  
29. 2.首行用0替代null  
30. SQL> select ename,job,sal,  
31.   2         lag(sal,1,0) over (order by sal)  
32.   3  from scott.emp  
33.   4  where sal>1500;  
34. ENAME      JOB             SAL LAG(SAL,1,0)OVER(ORDERBYSAL)  
35. ---------- --------- --------- ----------------------------  
36. ALLEN      SALESMAN    1600.00                            0  
37. CLARK      MANAGER     2450.00                         1600  
38. BLAKE      MANAGER     2850.00                         2450  
39. JONES      MANAGER     2975.00                         2850  
40. SCOTT      ANALYST     3000.00                         2975  
41. FORD       ANALYST     3000.00                         3000  
42. KING       PRESIDENT   5000.00                         3000  
43. --  
44. 3.第一行显示其前面第三行(超出了表范围)数据(无用0替代)  
45. 所以下面的前三行都为0  
46. SQL> select ename,job,sal,  
47.   2         lag(sal,3,0) over (order by sal)  
48.   3  from scott.emp  
49.   4  where sal>1500;  
50. ENAME      JOB             SAL LAG(SAL,3,0)OVER(ORDERBYSAL)  
51. ---------- --------- --------- ----------------------------  
52. ALLEN      SALESMAN    1600.00                            0  
53. CLARK      MANAGER     2450.00                            0  
54. BLAKE      MANAGER     2850.00                            0  
55. JONES      MANAGER     2975.00                         1600  
56. SCOTT      ANALYST     3000.00                         2450  
57. FORD       ANALYST     3000.00                         2850  
58. KING       PRESIDENT   5000.00                         2975  
59. --  
60. 4.按job分组，下一而分组的第一个job显示上一个分组的第一个job的值，  
61. 所以下面你看到了4个0:  
62.                      0-1:超出表范围，用0代替  
63.                      0-2:第一个分组(SCOTT-ANALYST)的0值  
64.                      0-3:第二个分组(CLARK-MANAGER )的0值  
65.                      0-4:第三个分组(KING-PRESIDENT)的0值  
66. SQL> select ename,job,sal,  
67.   2         lag(sal,1,0) over (partition by job order by sal)  
68.   3  from scott.emp  
69.   4  where sal>1500;  
70. ENAME      JOB             SAL LAG(SAL,1,0)OVER(PARTITIONBYJO  
71. ---------- --------- --------- ------------------------------  
72. SCOTT      ANALYST     3000.00                              0  
73. FORD       ANALYST     3000.00                           3000  
74. CLARK      MANAGER     2450.00                              0  
75. BLAKE      MANAGER     2850.00                           2450  
76. JONES      MANAGER     2975.00                           2850  
77. KING       PRESIDENT   5000.00                              0  
78. ALLEN      SALESMAN    1600.00                              0  

Oracle/PLSQL: Lag Function

---

In Oracle/PLSQL, the lag function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the lead function.

The syntax for the lag function is:

lag ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )

expression is an expression that can contain other built-in functions, but can not contain any analytic functions.

offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.

default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.

 

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

 

For example:

Let's take a look at an example. If we had an orders table that contained the following data:

ORDER_DATE	PRODUCT_ID	QTY
25/09/2007	1000	20
26/09/2007	2000	15
27/09/2007	1000	8
28/09/2007	2000	12
29/09/2007	2000	2
30/09/2007	1000	4

And we ran the following SQL statement:

select product_id, order_date, 
lag (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders;

It would return the following result:

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
1000	25/09/2007	<NULL>
2000	26/09/2007	25/09/2007
1000	27/09/2007	26/09/2007
2000	28/09/2007	27/09/2007
2000	29/09/2007	28/09/2007
1000	30/09/2007	29/09/2007

Since we used an offset of 1, the query returns the previous order_date.

If we had used an offset of 2 instead, it would have returned the order_date from 2 orders before. If we had used an offset of 3, it would have returned the order_date from 3 orders before....and so on.

 

If we wanted only the orders for a given product_id, we could run the following SQL statement:

select product_id, order_date, 
lag (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders
where product_id = 2000;

It would return the following result:

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
2000	26/09/2007	<NULL>
2000	28/09/2007	26/09/2007
2000	29/09/2007	28/09/2007

In this example, it returned the previous order_date for product_id = 2000 and ignored all other orders.

 

使用LAG和LEAD函数统计 Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。以下是LAG和LEAD的例子： SQL> select  year,region,profit ,lag (profit,1) over  (order by year)     2       as last_year_exp from test;   YEAR REGION      PROFIT LAST_YEAR_EXP ---- ------- ---------- ------------- 2003 West            88 2003 West            88            88 2003 Central        101            88 2003 Central        100           101 2003 East           102           100 2004 West            77           102 2004 East           103            77 2004 West            89           103   SQL> select  year,region,profit ,lead (profit,1) over  (order by year)     2       as next_year_exp from test;   YEAR REGION      PROFIT NEXT_YEAR_EXP ---- ------- ---------- ------------- 2003 West            88            88 2003 West            88           101 2003 Central        101           100 2003 Central        100           102 2003 East           102            77 2004 West            77           103 2004 East           103            89 2004 West            89 Lag函数为Lag(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。 Lead和Lag函数也可以使用分组，以下是使用region分组的例子： SQL> select  year,region,profit ,   2  lag (profit,1,0) over  (PARTITION BY region order by year)     3       as last_year_exp from test;   YEAR REGION      PROFIT LAST_YEAR_EXP ---- ------- ---------- ------------- 2003 Central        101             0 2003 Central        100           101 2003 East           102             0 2004 East           103           102 2003 West            88             0 2003 West            88            88 2004 West            77            88 2004 West            89            77