根据表中既有数字,使用SQL语句,对空白行以等差数列形式补充数字。
前段时间遇到一件趣事,一张ID不连续的有序列的表中,存在空白行,这些行由于某种原因数据丢失,现需要根据空白行的上下值,补齐数字。要求以等差数列的形式补充。
我这里简单做了一个测试的表,数据如下:
图中可以看到:
1、 此表ID不连续,但有序;
2、 空白处上下值随着ID增大,有变大也有变小;
3、 数值均为正;
为了便于处理前后的对比,我对该表增加临时列,用来存储处理前数据;
然后,我的对应语句如下:
MERGE INTO TMP_TABLE T
USING (SELECT ID,ROUND((MAX_THE_LEAD - MAX_THE_LAG) * GRONUMB / (GROCNT + 1) +MAX_THE_LAG,3) THE_VALUE_ FROM (SELECT ID,THE_VALUE,THE_LAG,THE_LEAD,
NUMB,GRO,COUNT(1) OVER(PARTITION BY GRO) GROCNT,ROW_NUMBER() OVER(PARTITION BY GRO ORDER BY ID) GRONUMB,
MAX(NVL(THE_LAG, 0/*need modify*/)) OVER(PARTITION BY GRO) MAX_THE_LAG,--修改1
MAX(NVL(THE_LEAD, 0/*need modify*/)) OVER(PARTITION BY GRO) MAX_THE_LEAD--修改1
FROM (SELECT ID,THE_VALUE,THE_LAG,THE_LEAD,NUMB,NUMB - NUMB2 GRO FROM (SELECT ID,THE_VALUE,THE_LAG,THE_LEAD,NUMB,ROWNUM NUMB2 FROM (SELECT T.ID,T.THE_VALUE,T.THE_LAG,T.THE_LEAD,ROWNUM NUMB FROM (SELECT T.ID,T.THE_VALUE,LEAD(T.THE_VALUE) OVER(PARTITION BY 1 ORDER BY T.ID) THE_LEAD,LAG(T.THE_VALUE) OVER(PARTITION BY 1 ORDER BY T.ID) THE_LAG FROM TMP_TABLE TORDER BY T.ID) T) WHERE THE_VALUE IS NULL)))) K
ON (T.ID = K.ID)
WHEN MATCHED THEN
UPDATE SET T.THE_VALUE = K.THE_VALUE_;
COMMIT;
执行结果如下:
回顾一下,处理此表的这个语句有如下前提:
1、 此表ID不连续,但可排序;——若无法排序,则该语句无法正确应用;
2、 数值均为正(大于0);——若不明确,需修改语句;
若要处理列的值有正有负,则需要改动语句中,“修改1”、“修改2”对应的行中的0,修改为比当前列最小值还小的值,可使用”MIN(THE_VALUE) – 1”获取。
(Dzz)