Oracle中LAG()和LEAD()等分析统计函数的用法

项目中有个需求：一条工单被N个部门审批，其中第一个部门理论审批时长=这个工单的规定审批时长*30%;后面部门的理论审批时长=（这个工单的规定审批时长-这个工单的规定审批时长*30%（即第一个部门审批的时长））/剩下审批的部门个数。注：后面的审批步骤中，一个部门可能同时审批多次。

如果实际审批时长>理论审批时长，那么这条工单就判为超时工单。

这里就涉及到一个问题，怎样算每个部门的实际处理时长？

第一个部门的实际处理时长=处理工单时间-接收工单时间。

以后部门的实际处理时长=处理工单时间-上一个审批的处理时间。

这里，我们取上一个审批的处理时间时就要用到Oracle的Lag()函数。

LAG()函数

 

--首先，根据流水号SD分组，同一个工单分在同一组中。

--然后，按GD_CLDATE排序，升序。

--最后，利用LAG(GD_CLDATE,1),取得上一个审批的处理时间。 

--1:表示上一条，数据可改变；GD_CLDATE表示要取的字段。

LAG(GD_CLDATE,1) OVER(PARTITION BY SD ORDER BY GD_CLDATE) AS LASTESTHANDLETIME

例如：

SELECT ID,SD,GD_USERGRADE,DEPARTMENTNAME,GD_CLDATE,LAG(GD_CLDATE,1) OVER(PARTITION BY SD ORDER BY GD_CLDATE) AS LASTESTHANDLETIME FROM VM_OVERTIMEORDERDETAIL;

 

 

我们可以看到，取出来有些GD_CLDATE为空，表明它们是第一个处理的部门，我们可以为这个空，赋一个默认值sysdate

 

LAG(GD_CLDATE,1,SYSDATE) OVER(PARTITION BY SD ORDER BY GD_CLDATE) AS LASTESTHANDLETIME

SELECT ID,SD,DEPARTMENTNAME,GD_CLDATE,LAG(GD_CLDATE,1,SYSDATE) OVER(PARTITION BY SD ORDER BY GD_CLDATE) AS LASTESTHANDLETIME FROM VM_OVERTIMEORDERDETAIL

 

 

我们可以根据LASTESTHANDLETIME是不是当前时间判断或计算它们的实际处理时长。

至于一条工单一个部门处理了多次，我们只需要分别它们每次的实际处理时长后再相加便可以得到该部门的实际处理总时长。

LEAD()函数

使用方法和LAG()函数一样，只不过它用于取后面N条的某一记录。

LEAD(GD_CLDATE,1,SYSDATE) OVER(PARTITION BY SD ORDER BY GD_CLDATE) AS LASTESTHANDLETIME

SELECT ID,SD,DEPARTMENTNAME,GD_CLDATE,LEAD(GD_CLDATE,1,SYSDATE) OVER(PARTITION BY SD ORDER BY GD_CLDATE) AS LASTESTHANDLETIME FROM VM_OVERTIMEORDERDETAIL