Приемы работы с ORACLE SQL Печать E-mail
Рейтинг: / 6
ХудшаяЛучшая 
автор материала Доброквашин Михаил , мой коллега
 

При работе с данными часто попадаются таблицы, в которых описан некий временной процесс, который описывает жизнь некоего объекта.

В какие-то временные моменты с ним происходят различные действия.

Он меняет свой статус. Например движение вагона по железнодорожной сети (меняется статус на груженый, порожний, ремонтный).

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

Надо распространить имеющуюся информацию на строки с пустыми значениями этого поля.


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

 

 

Вариант 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

Image 

Надо сделать так, чтобы получилось следующее:

Image 

 

То есть для строк 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

 

 Image

 

 

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

 

Image 

 

Затем можно построить диапазоны по полученным суммам, и в этих диапазонах брать значение поля 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

 

данные 

 

 

 


 

Комментарии  

  1. #3 Guzel
    2014-01-3005:25:10 Спасибо! Очень полезно!
  2. #2 Q
    2013-10-0707:39:49 select level from dual connect by level
  3. #1 dest
    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

Добавить комментарий

:D:lol::-);-)8):-|:-*:oops::sad::cry::o:-?:-x:eek::zzz:P:roll::sigh:


Защитный код
Обновить

« Пред.   След. »