Oracle-分析函数之取上下行数据lag()和lead()

亦凉 2022-05-28 11:25 183阅读 0赞

# [https://www.2cto.com/database/201611/562166.html][https_www.2cto.com_database_201611_562166.html] #

数据库技术 [https://www.itkc8.com][https_www.itkc8.com]

# 概述 #

ORACLE函数系列：

[Oracle][]常见函数大全

Oracle-分析函数之连续求和sum(…) over(…)

Oracle-分析函数之排序值rank()和dense\_rank()

Oracle-分析函数之排序后顺序号row\_number()

Oracle-分析函数之取上下行数据lag()和lead()

--------------------

这两个函数是偏移量函数，可以查出一个字段的上一个值或者下一个值，配合over来使用。

lead函数，这个函数是向上偏移.  
lag函数是向下偏移一位.

# 语法 #

【语法】

[?][Link 1]

<table style="width:613px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:baseline;"> 1 </td> 
 <td style="vertical-align:baseline;width:581px;"> <code>lag(EXPR,&lt;offset&gt;,&lt;</code><code>default</code><code>&gt;)&lt;/</code><code>default</code><code>&gt;&lt;/offset&gt;</code> </td> 
 </tr> 
 </tbody> 
</table>

[?][Link 1]

<table style="width:613px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:baseline;"> 1 </td> 
 <td style="vertical-align:baseline;width:581px;"> <code>LEAD(EXPR,&lt;offset&gt;,&lt;</code><code>default</code><code>&gt;)&lt;/</code><code>default</code><code>&gt;&lt;/offset&gt;</code> </td> 
 </tr> 
 </tbody> 
</table>

【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）

lead () 下一个值 lag() 上一个值

【参数】

EXPR是从其他行返回的表达式 OFFSET是缺省为1 的正数，表示相对行数。希望检索的当前行分区的偏移量 DEFAULT是在OFFSET表示的数目超出了分组的范围时返回的值。

exp\_str 是要做对比的字段  
offset 是exp\_str字段的偏移量 比如说 offset 为2 则 拿exp\_str的第一行和第三行对比，第二行和第四行，依次类推，offset的默认值为1！

【说明】Oracle分析函数

--------------------

# 栗子 #

[?][Link 1]

<table style="width:613px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:baseline;"> 1 2 3 4 5 6 </td> 
 <td style="vertical-align:baseline;width:581px;"> <code>create</code> <code>table</code> <code>LEAD_TABLE</code> <code>(</code> <code>&nbsp;</code><code>CASEID VARCHAR2(10),</code> <code>&nbsp;</code><code>STEPID VARCHAR2(10),</code> <code>&nbsp;</code><code>ACTIONDATE </code><code>DATE</code> <code>)</code> </td> 
 </tr> 
 </tbody> 
</table>

[?][Link 1]

<table style="width:684px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:baseline;"> 1 2 3 4 5 6 7 8 9 10 11 12 </td> 
 <td style="vertical-align:baseline;width:644px;"> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case1'</code><code>,</code><code>'Step1'</code><code>,to_date(</code><code>'20161101'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case1'</code><code>,</code><code>'Step2'</code><code>,to_date(</code><code>'20161102'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case1'</code><code>,</code><code>'Step3'</code><code>,to_date(</code><code>'20161103'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case1'</code><code>,</code><code>'Step4'</code><code>,to_date(</code><code>'20161104'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case1'</code><code>,</code><code>'Step5'</code><code>,to_date(</code><code>'20161105'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case1'</code><code>,</code><code>'Step4'</code><code>,to_date(</code><code>'20161106'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case1'</code><code>,</code><code>'Step6'</code><code>,to_date(</code><code>'20161107'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case1'</code><code>,</code><code>'Step1'</code><code>,to_date(</code><code>'20161201'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case2'</code><code>,</code><code>'Step2'</code><code>,to_date(</code><code>'20161202'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> <code>insert</code> <code>into</code> <code>LEAD_TABLE </code><code>values</code><code>(</code><code>'Case2'</code><code>,</code><code>'Step3'</code><code>,to_date(</code><code>'20161203'</code><code>,</code><code>'yyyy-mm-dd'</code><code>));</code> &nbsp; <code>commit</code><code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

数据规格：  
![这里写图片描述][2016110509500587.png]

## 输出当前 和 之前 之后的date 和 step ##

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:baseline;"> 1 2 3 4 5 6 7 8 9 </td> 
 <td style="vertical-align:baseline;width:698px;"> <code>select</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>a.caseid&nbsp; ,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>a.stepid </code><code>as</code> <code>currentStepID,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>a.actiondate&nbsp; </code><code>as</code> <code>currentActionDate,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lead(stepid)&nbsp; over(partition </code><code>by</code> <code>a.caseid </code><code>order</code> <code>by</code> <code>a.stepid)&nbsp; nextStep,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lead(actiondate)&nbsp; over(partition </code><code>by</code> <code>a.caseid </code><code>order</code> <code>by</code> <code>a.stepid)&nbsp; nextActionDate,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lag(stepid) over(partition </code><code>by</code> <code>a.caseid </code><code>order</code> <code>by</code> <code>a.stepid)&nbsp; preStep,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lag(actiondate)&nbsp; over(partition </code><code>by</code> <code>a.caseid </code><code>order</code> <code>by</code> <code>a.stepid)&nbsp; preActionDate</code> <code>&nbsp;&nbsp;</code><code>from</code> <code>lead_table&nbsp; a ;</code> </td> 
 </tr> 
 </tbody> 
</table>

![这里写图片描述][2016110509500588.png]

## 进一步统计一下两者的相差天数 ##

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:baseline;"> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td style="vertical-align:baseline;"> <code>select</code> <code>caseid,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>stepid,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>actiondate,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>nextactiondate,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>nextactiondate - actiondate datebetween</code> <code>&nbsp;&nbsp;</code><code>from</code> <code>(</code><code>select</code> <code>caseid,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>stepid,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>actiondate,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lead(stepid) over(partition </code><code>by</code> <code>caseid </code><code>order</code> <code>by</code> <code>actiondate) nextstepid,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lead(actiondate) over(partition </code><code>by</code> <code>caseid </code><code>order</code> <code>by</code> <code>actiondate) nextactiondate,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lag(stepid) over(partition </code><code>by</code> <code>caseid </code><code>order</code> <code>by</code> <code>actiondate) prestepid,</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lag(actiondate) over(partition </code><code>by</code> <code>caseid </code><code>order</code> <code>by</code> <code>actiondate) preactiondate</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>from</code> <code>lead_table) ;</code> </td> 
 </tr> 
 </tbody> 
</table>

![这里写图片描述][2016110509500589.png]

数据库技术 [https://www.itkc8.com][https_www.itkc8.com]

[https_www.2cto.com_database_201611_562166.html]: https://www.2cto.com/database/201611/562166.html
[https_www.itkc8.com]: https://www.itkc8.com/
[Oracle]: https://www.2cto.com/database/Oracle/
[Link 1]: https://www.2cto.com/database/201611/562166.html#
[2016110509500587.png]: https://www.2cto.com/uploadfile/Collfiles/20161105/2016110509500587.png
[2016110509500588.png]: https://www.2cto.com/uploadfile/Collfiles/20161105/2016110509500588.png
[2016110509500589.png]: https://www.2cto.com/uploadfile/Collfiles/20161105/2016110509500589.png