【Oracle】lead 和 lag 函数

最新推荐文章于 2024-03-14 10:25:47 发布

HolaSecurity

最新推荐文章于 2024-03-14 10:25:47 发布

阅读量555

点赞数

分类专栏：『Oracle』文章标签： oracle 偏移 lag lead sign

本文链接：https://blog.csdn.net/shaotaiban1097/article/details/100999483

版权

『Oracle』专栏收录该内容

38 篇文章 4 订阅

订阅专栏

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个产品的情况。

构造原始数据

统计每次获取计数器时数字的增量。
使用当前记录的 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)。获取异常时间段。

构造原始数据

统计当前记录的上一个状态和下一个状态。
假设统计频率足够高，忽略状态转换之间的之间。
那么，要获取异常区间。
自身状态为 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;