【Oracle】lead 和 lag 函数

lead() 和 lag() 函数

Oracle的两个与偏移量相关的分析函数。

可以获取到向前或向后偏移指定行的某一列元素。

效果类似于自连接,但使用更加方便,效率更高。

  • lead(field, number, default) over(partition by id order by name)
    当前记录后 number 条记录的 field 字段的值,如果没有默认为 default

  • lag(field, number, default) over(partition by id order by name)
    当前记录前 number 条记录的 field 字段的值,如果没有默认为 default

示例一

有 n 条生产线生产 2 中产品(台灯、手机),每条生产线上有 1 个计数器,每生产一个产品,计数器就会 + 1(计数器最大值为 999, 即每到 1000 就会清零重新开始)。☆ 假设获取计数器值的频率很高,排除瞬间增长 >1000个产品的情况。

  • 构造原始数据
    source

  • 统计每次获取计数器时数字的增量。
    使用当前记录的 COUNT 值 减去前一条记录 的 COUNT 值,如果没有,默认等于当前记录,如当前记录的 COUNT 大于 前一条记录的 COUNT,则意味着被置零,需要加上 1000

    	/*此段代码为测试数据构造 start*/
    	WITH temp AS (
    	SELECT '计数器-01' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:00:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 500 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-01' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:10:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 750 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-01' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:20:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 945 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-01' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:30:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 130 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-01' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:40:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 425 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-01' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:50:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 654 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-01' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 22:10:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 32 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-02' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:30:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 412 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-02' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:40:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 521 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-02' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 21:50:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 687 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-02' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 22:00:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 873 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-02' CNAME, '台灯' ENAME, TO_DATE('2019/09/18 22:10:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 952 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-03' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:00:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 43 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-03' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:10:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 241 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-03' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:20:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 434 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-03' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:30:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 644 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-03' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:40:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 832 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-03' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:50:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 5 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-03' CNAME, '手机' ENAME, TO_DATE('2019/09/18 23:00:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 231 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-04' CNAME, '手机' ENAME, TO_DATE('2019/09/18 21:30:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 496 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-04' CNAME, '手机' ENAME, TO_DATE('2019/09/18 21:40:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 634 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-04' CNAME, '手机' ENAME, TO_DATE('2019/09/18 21:50:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 874 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-04' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:00:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 969 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-04' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:10:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 201 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-04' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:20:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 423 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-04' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:30:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 654 COUNT FROM dual
    	UNION ALL 
    	SELECT '计数器-04' CNAME, '手机' ENAME, TO_DATE('2019/09/18 22:40:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 888 COUNT FROM dual
    	)
    	/*此段代码为数据构造 end*/
    
    	SELECT R.CNAME,
    	       R.ENAME,
    	       R.CDATE,
    	       R.CURRENT_COUNT,
    	       R.LAST_COUNT,
    	       DECODE(SIGN(R.CURRENT_COUNT - R.LAST_COUNT),
    	              -1,
    	              R.CURRENT_COUNT - R.LAST_COUNT + 1000,
    	              R.CURRENT_COUNT - R.LAST_COUNT) 增量
    	  FROM (SELECT T.CNAME,
    	               T.ENAME,
    	               T.CDATE,
    	               T.COUNT CURRENT_COUNT,
    	               LAG(T.COUNT, 1, T.COUNT) OVER(PARTITION BY T.CNAME, T.ENAME ORDER BY T.CDATE) LAST_COUNT
    	          FROM TEMP T) R;
    	          
    

    result

示例二

有 n 台检测器,隔一段时间抓取一次状态信息,状态有两种,正常(normal)和异常(error)。获取异常时间段。

  • 构造原始数据
    s

  • 统计当前记录的上一个状态和下一个状态。
    假设统计频率足够高,忽略状态转换之间的之间。
    那么,要获取异常区间。
    自身状态为 error 并且上一个状态为 normal 的时间,为异常开始时间
    自身状态为 error 并且下一个状态为 normal 的时间,为异常结束时间

    /*此段代码为测试数据构造 start*/
    WITH temp AS(
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 21:30:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'normal' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 21:40:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'normal' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 21:50:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'error' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 22:00:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'error' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 22:10:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'error' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 22:20:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'normal' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 22:30:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'normal' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 22:40:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'error' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 22:50:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'error' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 23:00:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'normal' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 23:10:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'error' STATUS FROM dual
    UNION ALL 
    SELECT '检测器-01' CNAME, TO_DATE('2019/09/18 23:20:00', 'yyyy/mm/dd hh24:mi:ss') CDATE, 'normal' STATUS FROM dual
    )
    /*此段代码为测试数据构造 end*/
    SELECT R.CNAME,
           R.CDATE,
           R.STATUS,
           /* 要获取异常区间 */
           /*自身状态为 error 并且上一个状态为 normal 的时间 为异常开始时间 */
           /*自身状态为 error 并且下一个状态为 normal 的时间 为异常结束时间 */
           DECODE(R.STATUS, 'error',DECODE(R.LAST_STATUS, 'normal', R.CDATE, NULL) , NULL ) ERR_START_DATE,
           DECODE(R.STATUS, 'error', DECODE(R.NEXT_STATUS, 'normal', R.CDATE, NULL ), NULL) ERR_END_DATE
            FROM (
    SELECT T.CNAME,
           T.CDATE,
           LAG(T.STATUS, 1, T.STATUS) OVER(PARTITION BY T.CNAME ORDER BY T.CDATE) LAST_STATUS,
           T.STATUS,
           LEAD(T.STATUS, 1, T.STATUS) OVER(PARTITION BY T.CNAME ORDER BY T.CDATE) NEXT_STATUS
      FROM TEMP T ) R;
    
    

    r

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

HolaSecurity

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值