APPS-ORACLE.RU

Заполнение пропусков в выборке.

Пробелы в столбце VAL заполняем по следующему принципу: берем следующее значение VAL и отнимаем от него текущее значение X

Исходные данные

with t as (
 select 1 as id, 1 as gr, null as val, 2    as x from dual
  union all
 select 2 as id, 1 as gr, null as val, 3    as x from dual
  union all
 select 3 as id, 1 as gr, 10   as val, null as x from dual
  union all
 select 4 as id, 2 as gr, null as val, 7    as x from dual
 union all
 select 5 as id, 2 as gr, null as val, 2    as x from dual
union all
 select 6 as id, 2 as gr, null as val, 1    as x from dual
union all
 select 7 as id, 2 as gr, 5    as val, null as x from dual
union all
 select 8 as id, 3 as gr, 85   as val, null as x from dual
union all
 select 7 as id, 4 as gr, null as val, 1    as x from dual
union all
 select 9 as id, 4 as gr, null as val, 3    as x from dual
union all
 select 15 as id, 4 as gr, 9   as val, null as x from dual
)
select * from t;

        ID         GR        VAL          X
---------- ---------- ---------- ----------
         1          1                     2
         2          1                     3
         3          1         10
         4          2                     7
         5          2                     2
         6          2                     1
         7          2          5
         8          3         85
         7          4                     1
         9          4                     3
        15          4          9

11 rows selected

Решение

select gr, id,
 case when val is not null then val
  else first_value(val) over(partition by gr order by id desc nulls first)
       - sum(x) over(partition by gr order by id desc nulls first
         rows between unbounded preceding AND CURRENT ROW)
 end val,
 x
from t
order by gr, id desc

        GR         ID        VAL          X
---------- ---------- ---------- ----------
         1          3         10
         1          2          7          3
         1          1          5          2
         2          7          5
         2          6          4          1
         2          5          2          2
         2          4         -5          7
         3          8         85
         4         15          9
         4          9          6          3
         4          7          5          1

11 rows selected

Похожие записи:

• LAG IGNORE NULLS
• Регулярные выражения. Удаление последней буквы или цифры в строке.
• SQL Склонение количества ошибок
• Удаление управляющих символов из строки
• Список параметров процедуры SQL запросом