Интересные приемы работы с ORACLE SQL

мне помог в подготовке материала Доброквашин Михаил , мой коллега

При работе с данными часто попадаются таблицы, в которых описан некий временной процесс, который описывает жизнь некоего объекта.
В какие-то временные моменты с ним происходят различные действия.
Он меняет свой статус. Например движение вагона по железнодорожной сети (меняется статус на груженый, порожний, ремонтный).
Или включение-выключение услуг телефонной связи (финансовая или добровольная блокировка услуги). Часто бывает так, что строки, расположенные по времени между этими событиями, не несут в себе информации о статусе объекта.

Надо распространить имеющуюся информацию на строки с пустыми значениями этого поля.
Дано: набор данных где в одном из полей есть пропуски в значениях. Эти пропуски нужно заполнить предыдущими значениями этого столбца.

Вариант 1

Пусть исходные данные выглядят так:

select 1 as id,'A' as kod from dual
UNION
select 2,'' from dual
UNION
select 3,'' from dual
UNION
select 4,'' from dual
UNION
select 5,'D' from dual
UNION
select 6,'' from dual
UNION
select 7,'' from dual
UNION
select 8,'' from dual
UNION
select 9,'E' from dual

результат 
ID KOD
1 A
2
3
4
5 D
6
7
8
9 E

Надо сделать так, чтобы получилось следующее:
ID KOD
1 A
2 A
3 A
4 A
5 D
6 D
7 D
8 D
9 E


То есть для строк 2,3,4 у которых второе поле пустое, заполнить его предыдущим не пустым значением.
Первое что приходит на ум, это получить некий промежуточный набор данных, в котором были бы отражены интервалы кодов ID и значений обоих столбцов, без промежуточных ?пустых? строк. Используем для этого оконную функцию LEAD(...) OVER .
Затем перемножив исходный набор данных и промежуточный, получить искомый результат.

select a.*
, LEAD(kod) OVER (order by id) as kod_d
, LEAD(id)  OVER (order by id) as id_d
from
(
select 1 as id,'A' as kod from dual
UNION
select 2,'' from dual
UNION
select 3,'' from dual
UNION
select 4,'' from dual
UNION
select 5,'D' from dual
UNION
select 6,'' from dual
UNION
select 7,'' from dual
UNION
select 8,'' from dual
UNION
select 9,'E' from dual )  a
where kod is not null

ID KOD KOD_D ID_D
1 A D 5
5 D E 9
9 E

select a1.id, nvl(a1.kod, a2.kod)
from
(
select 1 as id,'A' as kod from dual
UNION
select 2,'' from dual
UNION
select 3,'' from dual
UNION
select 4,'' from dual
UNION
select 5,'D' from dual
UNION
select 6,'' from dual
UNION
select 7,'' from dual
UNION
select 8,'' from dual
UNION
select 9,'E' from dual
) a1,
(
select a.*
, LEAD(kod) OVER (order by id) as kod_d
, LEAD(id) OVER (order by id) as id_d
from
(
select 1 as id,'A' as kod from dual
UNION
select 2,'' from dual
UNION
select 3,'' from dual
UNION
select 4,'' from dual
UNION
select 5,'D' from dual
UNION
select 6,'' from dual
UNION
select 7,'' from dual
UNION
select 8,'' from dual
UNION
select 9,'E' from dual )  a
where kod is not null
) a2
where a1.id >=a2.id and a1.id <= a2.id_d

ID NVL(A1.KOD,A2.KOD)
1 A
2 A
3 A
4 A
5 D
5 D
6 D
7 D
8 D
9 E

Вариант 2
Однако предыдущий вариант не очень удачен в плане производительности. Фактически там требуется минимум два раза читать данные из исходной таблицы.

Есть другой способ решения данной задачи. Идею предложил Сафронов Виктор. Он основан на использовании другой аналитической функции SUM, которая будет по-нарастающей накапливать сумму по второму столбцу. Для пустых полей, сумма не будет меняться. Для тех полей где стоят буквы – она будет увеличиваться.

Сначала получим нарастающие суммы по полю KOD.

select a.*
, SUM(nvl(ascii(a.kod),0)) OVER (order by id) as id_b
from
(
select 1 as id,'A' as kod from dual
UNION
select 2,'' from dual
UNION
select 3,'' from dual
UNION
select 4,'' from dual
UNION
select 5,'D' from dual
UNION
select 6,'' from dual
UNION
select 7,'' from dual
UNION
select 8,'' from dual
UNION
select 9,'E' from dual )  a

ID KOD ID_B
1 A 65
2 65
3 65
4 65
5 D 133
6 133
7 133
8 133
9 E 202



Затем можно построить диапазоны по полученным суммам, и в этих диапазонах брать значение поля KOD из первой строки диапазона.

select a2.*, FIRST_VALUE(a2.kod) OVER (partition by a2.id_b order by id)
from(
select a.*
, SUM(nvl(ascii(a.kod),0)) OVER (order by id) as id_b
from
(
select 1 as id,'A' as kod from dual
UNION
select 2,'' from dual
UNION
select 3,'' from dual
UNION
select 4,'' from dual
UNION
select 5,'D' from dual
UNION
select 6,'' from dual
UNION
select 7,'' from dual
UNION
select 8,'' from dual
UNION
select 9,'E' from dual)  a
) a2

ID KOD ID_B FIRST_VALUE(A2.KOD)OVER(PARTIT
1 A 65 A
2 65 A
3 65 A
4 65 A
5 D 133 D
6 133 D
7 133 D
8 133 D
9 E 202 E