国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

使用分析函數(shù)LAST_VALUE或LAG實現(xiàn)缺失數(shù)據(jù)填充及其區(qū)別

IT那活兒 / 1273人閱讀
使用分析函數(shù)LAST_VALUE或LAG實現(xiàn)缺失數(shù)據(jù)填充及其區(qū)別

點擊上方“IT那活兒”公眾號，關注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！ 


  

本文主要講述實現(xiàn)對指定的空行，按照前面非空或后面非空數(shù)據(jù)進行填充。原來這種實現(xiàn)數(shù)據(jù)填充的方法，主要是用LAST_VALUE+IGNORE NULLS實現(xiàn)，在11G中LAG分析函數(shù)也支持IGNORE NULLS，但是，在性能上，他們是有區(qū)別的。

本文討論2點內(nèi)容：

1. 使用分析函數(shù)LAST_VALUE和11G LAG實現(xiàn)缺失數(shù)據(jù)填充。
2. LAST_VALUE和LAG在實現(xiàn)缺失數(shù)據(jù)填充上的區(qū)別。


使用分析函數(shù)LAST_VALUE和11G LAG實現(xiàn)缺失數(shù)據(jù)填充

經(jīng)常我們在報表中遇到這樣的問題：
例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分析函數(shù)也帶IGNORE NULLS,所以也能實現(xiàn)上面的功能，因為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分析函數(shù)，一次是正常用當前行前面的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.

黃色區(qū)域的數(shù)據(jù)還是按向上查找的填充方式，紅色部分按照向下查找填充的方式。當然，也可以使用LAG或LEAD來實現(xiàn)。

如下：

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行值，F(xiàn)IRST_VALUE是按照排序，找對應窗口的最小ID對應的值（忽略NULL）。所以LAST_VALUE+ORDER BY ID DESC不等價于FIRST_VALUE+ORDER BY ID。見下表格，表示兩者之間的區(qū)別：        
  • 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：繼續(xù)變換下需求，如果按照CATE分區(qū)域，每個區(qū)域內(nèi)按照先從上面查找，找到則用最近的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的填充，但是現(xiàn)在要按CATE分區(qū)，所以應該用ID=7的填充，則前面分析函數(shù)要加上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在實現(xiàn)缺失數(shù)據(jù)填充上的區(qū)別

LAST_VALUE分析可以可以帶WINDOW子句，而LAG分析函數(shù)不可以，這意味著，LAST_VALUE分析函數(shù)更強大，通過前面的例子可以看出，LAST_VALUE實現(xiàn)一般的缺失數(shù)據(jù)填充，不需要NVL的，而LAG還需要NVL,因為它們的含義是完全不同的。比如要實現(xiàn)從之前開始找，再向后找至多2行，然后用最大的ID對缺失數(shù)據(jù)填充。如果使用LAST_VALUE，因為現(xiàn)在不是找到當前行的最后一個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分析函數(shù)來實現(xiàn)，那就比較復雜了。

另外LAG/LEAD分析函數(shù)帶IGNORE NULLS是11G新特性，它的效率遠遠比LAST_VALUE要差。
先構造9999行數(shù)據(jù)如下：
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.
測試缺失數(shù)據(jù)填充，為公平起見，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
統(tǒng)計信息完全一樣，但是LAST_VALUE是0.13s,LAG是22.49s,效率差別太大。經(jīng)過10046跟蹤，發(fā)現(xiàn)LAG分析函數(shù)+IGNORE NULLS大量消耗CPU，F(xiàn)ETCH階段消耗大量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內(nèi)部實現(xiàn)比較差，效率遠遠不如LAST_VALUE的IGNORE NULLS內(nèi)部實現(xiàn)，當然不加IGNORE NULLS的LAG/LEAD效率還是不錯的，對于ORACLE新特性，一定要做足測試，慎用。

本文作者：丁 俊（上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

文章版權歸作者所有，未經(jīng)允許請勿轉載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/129306.html

相關文章

  • 【數(shù)據(jù)科學系統(tǒng)學習】Python # 數(shù)據(jù)分析基本操作[二] pandas

    摘要：中面向行和面向列的操作基本是平衡的。用層次化索引，將其表示為更高維度的數(shù)據(jù)。使用浮點值表示浮點和非浮點數(shù)組中的缺失數(shù)據(jù)。索引的的格式化輸出形式選取數(shù)據(jù)子集在內(nèi)層中進行選取層次化索引在數(shù)據(jù)重塑和基于分組的操作中很重要。 我們在上一篇介紹了 NumPy，本篇介紹 pandas。 pandas入門 Pandas 是基于Numpy構建的，讓以NumPy為中心的應用變的更加簡單。 pandas...

    jayzou 評論0 收藏0

  • TiDB 3.0.0 Beta.1 Release Notes

    摘要：年月日，發(fā)布版，對應的版本為。相比版本，該版本對系統(tǒng)穩(wěn)定性易用性功能優(yōu)化器統(tǒng)計信息以及執(zhí)行引擎做了很多改進。 2019 年 03 月 26 日，TiDB 發(fā)布 3.0.0 Beta.1 版，對應的 TiDB-Ansible 版本為 3.0.0 Beta。相比 3.0.0 Beta 版本，該版本對系統(tǒng)穩(wěn)定性、易用性、功能、優(yōu)化器、統(tǒng)計信息以及執(zhí)行引擎做了很多改進。 TiDB SQL ...

    Worktile 評論0 收藏0

  • 【數(shù)據(jù)科學系統(tǒng)學習】Python # 數(shù)據(jù)分析基本操作[四] 數(shù)據(jù)規(guī)整化和數(shù)據(jù)聚合與分組運算

    摘要：數(shù)據(jù)規(guī)整化清理轉換合并重塑數(shù)據(jù)聚合與分組運算數(shù)據(jù)規(guī)整化清理轉換合并重塑合并數(shù)據(jù)集可根據(jù)一個或多個鍵將不同中的行鏈接起來。函數(shù)根據(jù)樣本分位數(shù)對數(shù)據(jù)進行面元劃分。字典或，給出待分組軸上的值與分組名之間的對應關系。 本篇內(nèi)容為整理《利用Python進行數(shù)據(jù)分析》，博主使用代碼為 Python3，部分內(nèi)容和書本有出入。 在前幾篇中我們介紹了 NumPy、pandas、matplotlib 三個...

    The question 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<