Последние новости
- Защита фотографий Вашим логотипом
- Дорожные знаки для КПК
- Экзамен ПДД для КПК
- Приемы работы с ORACLE SQL
- Oracle SQL - оригинальные решения
- Несложные эффекивные приемы в Oracle SQL UNION, MINUS, INTERSECT
- Работа с SOAP XML в Oracle
- Верменные таблицы в Oracle
- INVDERS мобильная игра Pocket PC
- DBF Studio
| Приемы работы с 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
Надо сделать так, чтобы получилось следующее:
То есть для строк 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
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
Вариант 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
Затем можно построить диапазоны по полученным суммам, и в этих диапазонах брать значение поля 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
|
| След. » |
|---|
Комментарии
2011-04-0815:24:35 with t as(
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)
select id, last_value(kod ignore nulls) over (order by id) from t