资讯专栏INFORMATION COLUMN

上云采购季！| 爆款云服务器低至4.6元/月，首单享0.9折

免费试用
首页/文章专栏/使用分析函数LAST_VALUE或LAG实现缺失数据填充及其区别

使用分析函数LAST_VALUE或LAG实现缺失数据填充及其区别

IT那活儿 / 978人阅读
使用分析函数LAST_VALUE或LAG实现缺失数据填充及其区别

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！ 


  

本文主要讲述实现对指定的空行，按照前面非空或后面非空数据进行填充。原来这种实现数据填充的方法，主要是用LAST_VALUE+IGNORE NULLS实现，在11G中LAG分析函数也支持IGNORE NULLS，但是，在性能上，他们是有区别的。

本文讨论2点内容：

1. 使用分析函数LAST_VALUE和11G LAG实现缺失数据填充。
2. LAST_VALUE和LAG在实现缺失数据填充上的区别。


使用分析函数LAST_VALUE和11G LAG实现缺失数据填充

经常我们在报表中遇到这样的问题：
例1: 对每行VAL为空的，向上找最近的不为空的VAL，然后填充到当前为止。
dingjun123@ORADB> SELECT * FROM t;
        ID VAL CATE
---------- ---------- ----------
         1 VAL1 CATE0
         2            CATE0
         3            CATE0
         4            CATE0
         5            CATE0
         6 VAL6 CATE1
         7            CATE1
         8            CATE1
         9            CATE1
9 rows selected.

在10g中有LAST_VALUE+IGNORE NULLS很好解决，如下：

dingjun123@ORADB> SELECT ID,
  2  last_value(val IGNORE NULLS) over(ORDER BY ID) val,
  3  cate
  4  FROM t;
        ID VAL CATE
---------- ---------- ----------
         1 VAL1 CATE0
         2 VAL1 CATE0
         3 VAL1 CATE0
         4 VAL1 CATE0
         5 VAL1 CATE0
         6 VAL6 CATE1
         7 VAL6 CATE1
         8 VAL6 CATE1
         9 VAL6 CATE1
9 rows selected.

上面的SQL含义是ID排序直到当前行（默认是RANGE窗口），忽略VAL为空的值，因为是LAST_VALUE，所以找最近的不为空的VAL值来填充到当前行。在11G中，LAG分析函数也带IGNORE NULLS,所以也能实现上面的功能，因为LAG是找当前行前面1行的值，所以需要加个NVL，LAST_VALUE不需要，它是直接找到当前行，否则有值的可能为空，如下：

dingjun123@ORADB> SELECT ID,
  2  nvl(val,lag(val IGNORE NULLS) over(ORDER BY ID)) val,
  3  cate
  4  FROM t;
        ID VAL CATE
---------- ---------- ----------
         1 VAL1 CATE0
         2 VAL1 CATE0
         3 VAL1 CATE0
         4 VAL1 CATE0
         5 VAL1 CATE0
         6 VAL6 CATE1
         7 VAL6 CATE1
         8 VAL6 CATE1
         9 VAL6 CATE1
9 rows selected.
当然，具体需求总是复杂的，如果变换一下：
例2：如果前面找不到值填充（也就是前面的全是NULL），那么就向后查找最近的一条不为空的值填充。
如下：
dingjun123@ORADB> select id,val,cate from t;
        ID VAL CATE
---------- ---------- ----------
         1            CATE0
         2            CATE0
         3  VAL3 CATE0
         4            CATE0
         5            CATE0
         6  VAL6 CATE1
         7            CATE1
         8            CATE1
         9            CATE1
9 rows selected.

对于ID=1和ID=2的行，因为前面找不到VAL的值，所以用ID=3的来填充。很显然，这里需要用到2次LAST_VALUE分析函数，一次是正常用当前行前面的VAL来填充，如果填充不了，就用按ID倒叙排列的最近一行来填充。如下：

dingjun123@ORADB> SELECT ID,
  2  nvl(last_value(val IGNORE NULLS) over(ORDER BY ID),
  3       last_value(val IGNORE NULLS) over(ORDER BY ID DESC)) val,
  4  cate
  5  FROM t
  6  ORDER BY ID;
        ID VAL CATE
---------- ---------- ----------
         1 VAL3 CATE0
         2 VAL3 CATE0
         3 VAL3 CATE0
         4 VAL3 CATE0
         5 VAL3 CATE0
         6 VAL6 CATE1
         7 VAL6 CATE1
         8 VAL6 CATE1
         9 VAL6 CATE1
9 rows selected.

黄色区域的数据还是按向上查找的填充方式，红色部分按照向下查找填充的方式。当然，也可以使用LAG或LEAD来实现。

如下：

dingjun123@ORADB> SELECT ID,
  2  nvl(val,nvl(lag(val IGNORE NULLS) over(ORDER BY ID),lag(val IGNORE NULLS) over(ORDER BY ID DESC))) val,
  3  cate
  4  FROM t
  5  ORDER BY ID;
        ID VAL CATE
---------- ---------- ----------
         1 VAL3 CATE0
         2 VAL3 CATE0
         3 VAL3 CATE0
         4 VAL3 CATE0
         5 VAL3 CATE0
         6 VAL6 CATE1
         7 VAL6 CATE1
         8 VAL6 CATE1
         9 VAL6 CATE1
9 rows selected.

dingjun123@ORADB> SELECT ID,
  2  nvl(val,nvl(lag(val IGNORE NULLS) over(ORDER BY ID),lead(val IGNORE NULLS) over(ORDER BY ID))) val,
  3  cate
  4  FROM t
  5  ORDER BY ID;
        ID VAL CATE
---------- ---------- ----------
         1 VAL3 CATE0
         2 VAL3 CATE0
         3 VAL3 CATE0
         4 VAL3 CATE0
         5 VAL3 CATE0
         6 VAL6 CATE1
         7 VAL6 CATE1
         8 VAL6 CATE1
         9 VAL6 CATE1
9 rows selected.
有没有注意到，使用LAG，排序是ORDER BY ID DESC,使用LEAD，则排序是ORDER BY ID。因为LAG默认是找按照ID排序，找当前行之前的1行，LEAD则是找当前行之后的1行（都是忽略NULL后的结果）对应的值，所以它们这样写是等价的。
但是为什么使用LAST_VALUE的时候，我没有用FIRST_VALUE+ORDER BY ID呢，显然这是不行的。
LAST_VALUE是按照排序，直到找到当前行，返回最大的ID对应的值（忽略NULL），它计算的不是当前行之前的1行值，FIRST_VALUE是按照排序，找对应窗口的最小ID对应的值（忽略NULL）。所以LAST_VALUE+ORDER BY ID DESC不等价于FIRST_VALUE+ORDER BY ID。见下表格，表示两者之间的区别：        
  • LAG/LEAD

  • LAST_VALUE/FIRST_VALUE
--不加WINDOW窗口，不正确dingjun123@ORADB> SELECT ID,
  2  nvl(last_value(val IGNORE NULLS) over(ORDER BY ID),
  3       first_value(val IGNORE NULLS) over(ORDER BY ID)) val,
  4  cate
  5  FROM t
  6  ORDER BY ID;
        ID VAL CATE
---------- ---------- ----------
         1            CATE0
         2            CATE0
         3 VAL3 CATE0
         4 VAL3 CATE0
         5 VAL3 CATE0
         6 VAL6 CATE1
         7 VAL6 CATE1
         8 VAL6 CATE1
         9 VAL6 CATE1
9 rows selected.

--加WINDOW窗口的FIRST_VALUE，正确
dingjun123@ORADB> SELECT ID,
  2  nvl(last_value(val IGNORE NULLS) over(ORDER BY ID),
  3       first_value(val IGNORE NULLS) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND unbounded following)) val,
  4  cate
  5  FROM t
  6  ORDER BY ID;
        ID VAL CATE
---------- ---------- ----------
         1 VAL3 CATE0
         2 VAL3 CATE0
         3 VAL3 CATE0
         4 VAL3 CATE0
         5 VAL3 CATE0
         6 VAL6 CATE1
         7 VAL6 CATE1
         8 VAL6 CATE1
         9 VAL6 CATE1
9 rows selected.
例3：继续变换下需求，如果按照CATE分区域，每个区域内按照先从上面查找，找到则用最近的VAL填充，否则向下查找，用最近的VAL填充。
如下：
dingjun123@ORADB> select id,val,cate from t;
        ID VAL CATE
---------- ---------- ----------
         1            CATE0
         2            CATE0
         3 VAL3 CATE0
         4            CATE0
         5            CATE0
         6            CATE1
         7 VAL7 CATE1
         8            CATE1
         9            CATE1
9 rows selected.
上面的ID=6的按照前面的方法，用ID=3的填充，但是现在要按CATE分区，所以应该用ID=7的填充，则前面分析函数要加上PARTITION BY子句：
dingjun123@ORADB> SELECT ID,
  2  nvl(last_value(val IGNORE NULLS) over(PARTITION BY cate ORDER BY ID),
  3       last_value(val IGNORE NULLS) over( PARTITION BY cate ORDER BY ID DESC)) val,
  4  cate
  5  FROM t
  6  ORDER BY ID;
        ID VAL CATE
---------- ---------- ----------
         1 VAL3 CATE0
         2 VAL3 CATE0
         3 VAL3 CATE0
         4 VAL3 CATE0
         5 VAL3 CATE0
         6 VAL7 CATE1
         7 VAL7 CATE1
         8 VAL7 CATE1
         9 VAL7 CATE1
9 rows selected.

SELECT ID,
nvl(val,nvl(lag(val IGNORE NULLS) over(PARTITION BY cate ORDER BY ID),
               lead(val IGNORE NULLS) over(PARTITION BY cate ORDER BY ID))) val,
               cate
FROM t
ORDER BY ID;
--结果一样，省略

LAST_VALUE和LAG在实现缺失数据填充上的区别

LAST_VALUE分析可以可以带WINDOW子句，而LAG分析函数不可以，这意味着，LAST_VALUE分析函数更强大，通过前面的例子可以看出，LAST_VALUE实现一般的缺失数据填充，不需要NVL的，而LAG还需要NVL,因为它们的含义是完全不同的。比如要实现从之前开始找，再向后找至多2行，然后用最大的ID对缺失数据填充。如果使用LAST_VALUE，因为现在不是找到当前行的最后一个ID对应的值了，所以，必须加NVL,否则有值也会被转掉：
dingjun123@ORADB> SELECT ID,val,
  2  nvl(val,last_value(val IGNORE NULLS) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND 2 following)) new_val,
  3  cate
  4  FROM t;
        ID VAL NEW_VAL CATE
---------- ---------- ------------------------------------------- ----------
         1 VAL1 VAL1 CATE0
         2            VAL1 CATE0
         3            VAL1 CATE0
         4            VAL6 CATE0
         5            VAL6 CATE0
         6 VAL6 VAL6 CATE1
         7            VAL6 CATE1
         8            VAL6 CATE1
         9            VAL6 CATE1
9 rows selected.

如果上面的需求使用LAG分析函数来实现，那就比较复杂了。

另外LAG/LEAD分析函数带IGNORE NULLS是11G新特性，它的效率远远比LAST_VALUE要差。
先构造9999行数据如下：
dingjun123@ORADB> DROP TABLE t;
Table dropped.

dingjun123@ORADB> CREATE TABLE t AS SELECT LEVEL ID,decode(MOD(LEVEL,5),1,VAL||LEVEL) val,
  2   CATE||(trunc((LEVEL-1)/5)) cate FROM dual CONNECT BY LEVEL<10000;
Table created.


dingjun123@ORADB> select count(*) cnt,count(val) cnt_val from t;
       CNT    CNT_VAL
---------- ----------
      9999 2000
1 row selected.
测试缺失数据填充，为公平起见，LAST_VALUE也加上NVL：
dingjun123@ORADB> SELECT ID,
  2  nvl(val,last_value(val IGNORE NULLS) over(ORDER BY ID)) val,
  3  cate
  4  FROM t;
9999 rows selected.
Elapsed: 00:00:00.13

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
     207607  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       9999  rows processed

dingjun123@ORADB> SELECT ID,
  2  nvl(val,lag(val IGNORE NULLS) over(ORDER BY ID)) val,
  3  cate
  4  FROM t;
9999 rows selected.
Elapsed: 00:00:22.49

Statistics
--------------------------------------------------------
          0  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
     207607  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       9999  rows processed
统计信息完全一样，但是LAST_VALUE是0.13s,LAG是22.49s,效率差别太大。经过10046跟踪，发现LAG分析函数+IGNORE NULLS大量消耗CPU，FETCH阶段消耗大量CPU TIME。
如下：
SELECT ID,
nvl(val,lag(val IGNORE NULLS) over(ORDER BY ID)) val,
cate
FROM t

call     count       cpu elapsed disk query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.02          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      668     21.98      22.08          0         31          0        9999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670     21.98      22.11          0         32          0        9999
看来LAG/LEAD的IGNORE NULLS内部实现比较差，效率远远不如LAST_VALUE的IGNORE NULLS内部实现，当然不加IGNORE NULLS的LAG/LEAD效率还是不错的，对于ORACLE新特性，一定要做足测试，慎用。

本文作者：丁 俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

私有云 云服务器 java异或函数 php变量、类或函数 免费域名空间及其使用方法 用js或jquery实现添加行

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129306.html

相关文章

  • 【数据科学系统学习】Python # 数据分析基本操作[二] pandas

    摘要：中面向行和面向列的操作基本是平衡的。用层次化索引，将其表示为更高维度的数据。使用浮点值表示浮点和非浮点数组中的缺失数据。索引的的格式化输出形式选取数据子集在内层中进行选取层次化索引在数据重塑和基于分组的操作中很重要。 我们在上一篇介绍了 NumPy，本篇介绍 pandas。 pandas入门 Pandas 是基于Numpy构建的，让以NumPy为中心的应用变的更加简单。 pandas...

    jayzou 评论0 收藏0

  • TiDB 3.0.0 Beta.1 Release Notes

    摘要：年月日，发布版，对应的版本为。相比版本，该版本对系统稳定性易用性功能优化器统计信息以及执行引擎做了很多改进。 2019 年 03 月 26 日，TiDB 发布 3.0.0 Beta.1 版，对应的 TiDB-Ansible 版本为 3.0.0 Beta。相比 3.0.0 Beta 版本，该版本对系统稳定性、易用性、功能、优化器、统计信息以及执行引擎做了很多改进。 TiDB SQL ...

    Worktile 评论0 收藏0

  • 【数据科学系统学习】Python # 数据分析基本操作[四] 数据规整化和数据聚合与分组运算

    摘要：数据规整化清理转换合并重塑数据聚合与分组运算数据规整化清理转换合并重塑合并数据集可根据一个或多个键将不同中的行链接起来。函数根据样本分位数对数据进行面元划分。字典或，给出待分组轴上的值与分组名之间的对应关系。 本篇内容为整理《利用Python进行数据分析》，博主使用代码为 Python3，部分内容和书本有出入。 在前几篇中我们介绍了 NumPy、pandas、matplotlib 三个...

    The question 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<