мне помог в подготовке материала Доброквашин Михаил , мой коллега
При работе с данными часто попадаются таблицы, в которых описан некий временной процесс, который описывает жизнь некоего объекта.
В какие-то временные моменты с ним происходят различные действия.
Он меняет свой статус. Например движение вагона по железнодорожной сети (меняется статус на груженый, порожний, ремонтный).
Или включение-выключение услуг телефонной связи (финансовая или добровольная блокировка услуги). Часто бывает так, что строки, расположенные по времени между этими событиями, не несут в себе информации о статусе объекта.
Интересные приемы работы с ORACLE SQL
Категория: Блог
Опубликовано 23.05.2017
Надо распространить имеющуюся информацию на строки с пустыми значениями этого поля.
Дано: набор данных где в одном из полей есть пропуски в значениях. Эти пропуски нужно заполнить предыдущими значениями этого столбца.
Вариант 1
Пусть исходные данные выглядят так:
результат
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 .
Затем перемножив исходный набор данных и промежуточный, получить искомый результат.
ID KOD KOD_D ID_D
1 A D 5
5 D E 9
9 E
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.
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 из первой строки диапазона.
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
Дано: набор данных где в одном из полей есть пропуски в значениях. Эти пропуски нужно заполнить предыдущими значениями этого столбца.
Вариант 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