Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Подскажите, как правильно обновить все записи, кроме последней?  [new]
Alibek B.
Member

Откуда:
Сообщений: 3734
Есть сущность и дополнительные атрибуты этой сущности, которые являются историческими:
create table items
(
  item_id number not null,
  name varchar2 not null,
  ...
);

create table attributes
(
  attr_id number not null,
  item_id number not null,
  attribute varchar2 not null.
  value varchar2,
  date_beg date not null,
  date_end date
);


Вообще атрибут должен быть уникальным (то есть в таблице атрибутов не должно быть значений с повторяющимися item_id+attribute); если добавляется уже существующий атрибут, то действующий атрибут должен закрыться (date_end=sysdate) и в таблицу атрибутов должна добавиться новая запись:
attr_iditem_idattributevaluedate_begdate_end
11a1v12015-01-012015-01-10
21a1v22015-01-10

Но в информационной системе есть небольшая недоработка и в некоторых случаях предыдущий атрибут не закрывается:
attr_iditem_idattributevaluedate_begdate_end
11a1v12015-01-01
21a1v22015-01-10


Мне нужно найти такие дубли и закрыть их, оставив самую свежую запись.
Дубли выбираю так:
select ...
from items i
join attributes a on (a.item_id = i.item_id and a.date_beg <= sysdate and nvl(a.date_end,date'9999-01-01') > sysdate)
join attributes d on (d.item_id = i.item_id and d.date_beg <= sysdate and nvl(d.date_end,date'9999-01-01') > sysdate and d.attr_id != a.attr_id and d.attribute = a.attribute)


Как лучше будет закрыть все старые записи?
Добавить к вышеуказанному запросу группировку по item_id, получить max(date_beg), обернуть в подзапрос и сделать update attributes set date_beg = sysdate where attr_id not in (...)?
Или лучше прямо в этом запроса аналитические функции задействовать (partition by)?

________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
30 сен 15, 15:28    [18215328]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
stax..
Guest
Alibek B.,
merge

но я б не морочил голову и делал через pl/sql

и в свете "небольшая недоработка "
проверте нет ли пересечений и в исторических данных

ps
сдедущий date_end часто делают меньше предыдущего date_beg

....
stax
30 сен 15, 15:41    [18215454]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
stax..
Guest
stax..
Alibek B.,
следущий date_end часто делают меньше предыдущего date_beg

....
stax


Предыдущий date_end часто делают меньше следующего date_beg
30 сен 15, 15:43    [18215474]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
JDS
Member

Откуда:
Сообщений: 690
Если нет записей с равными date_start, можно попробовать примерно так, но внимательно проверить прежде, а лучше забэкапиться ):
UPDATE attributes t
   SET date_end = (SELECT MAX(date_start) - 1
                     FROM attributes 
                    WHERE item_id = t.item_id
                      AND date_end IS NULL)
 WHERE date_end IS NULL
   AND EXISTS (SELECT 1
                 FROM attributes
                WHERE item_id = t.item_id
                  AND date_start > t.date_start)
30 сен 15, 15:45    [18215493]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
stax..
Guest
JDS,
ему надо поменять не на мах, а на предыдущую
с null может быть не одна запись

.....
stax
30 сен 15, 16:01    [18215590]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
Alibek B.
Member

Откуда:
Сообщений: 3734
stax..
merge

В каком смысле?
merge into attributes
using (select item_id, max(date_beg) as max_date ...) rs on (attributes.item_id = rs.item_id and attributes.date_beg < rs.max_date)
when matched then update set date_end = ...
Или имеется ввиду другое?

stax..
проверте нет ли пересечений и в исторических данных

Есть, 500 штук таких записей набралось.
Потому и думаю запускать по расписанию скрипт, который старые атрибуты будет закрывать.

JDS
Если нет записей с равными date_start, можно попробовать примерно так, но внимательно проверить прежде, а лучше забэкапиться ):

Таких записей в текущих данных нет.
Но теоретически такое возможно.
Я пока сам не решил, как именно буду такие ситуации решать.
Склоняюсь к использованию аналитических функций
max(a.attr_id) keep (dense_rank first order by a.date_beg) over (partition by r.item_id) as first_id
и update для attr_id != first_id.
30 сен 15, 16:04    [18215603]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
123йй
Member

Откуда:
Сообщений: 1639
Alibek B.,

lead ?
30 сен 15, 16:07    [18215622]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
JDS
Member

Откуда:
Сообщений: 690
stax..
ему надо поменять не на мах, а на предыдущую
с null может быть не одна запись

Логично, тогда что-то вроде такого изврата ):
UPDATE attributes t
   SET date_end = (SELECT MAX(date_start)  KEEP(DENSE RANK LAST ORDER BY date_start DESC) - 1 -- хотя и MAX по идее, должно достать минимальную ближайшую дату )))
                     FROM attributes 
                    WHERE item_id = t.item_id
                      AND date_end IS NULL
                      AND date_start > t.date_start)
 WHERE date_end IS NULL
   AND EXISTS (SELECT 1
                 FROM attributes
                WHERE item_id = t.item_id
                  AND date_start > t.date_start)

Но проверять лучше на тестовых данных конечно )
30 сен 15, 16:07    [18215626]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
JDS
Member

Откуда:
Сообщений: 690
Да desc лишнее конечно, ТС сам уже все написал )
UPDATE attributes t
   SET date_end = (SELECT MAX(date_start)  KEEP(DENSE_RANK FIRST ORDER BY date_start) - 1
                     FROM attributes 
                    WHERE item_id = t.item_id
                      AND date_end IS NULL
                      AND date_start > t.date_start)
 WHERE date_end IS NULL
   AND EXISTS (SELECT 1
                 FROM attributes
                WHERE item_id = t.item_id
                  AND date_start > t.date_start)
30 сен 15, 16:16    [18215700]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
Alibek B.
Member

Откуда:
Сообщений: 3734
123йй
lead ?

Спасибо, видимо да.
Оно гораздо короче, чем мой вариант.
30 сен 15, 16:41    [18215914]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
Alibek B.
Member

Откуда:
Сообщений: 3734
Нет, lead все же не подходит, слишком усложняется запрос.

Просьба проверить, все ли в запросе правильно?
with
  items as
  (
    select null as item_id, null as login from dual where 0=1
    union all select 38731, 'item1' from dual
  ),
  attributes as
  (
    select 0 as attr_id, 0 as item_id, '' as attribute, '' as value, DATE'1000-01-01' as date_beg, DATE'9999-01-01' as date_end from dual where 0=1
    union all select 283945, 38731, 'RBN-Context_Name',     'local',                              to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 283946, 38731, 'RBN-Sub_Profile_Name', 'PPPOE',                              to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 283944, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 191270, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2014-05-04 14:25:02', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 183709, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2014-03-28 10:57:51', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 183711, 38731, 'Service-Type',         'Framed-User',                        to_date('2014-03-28 10:57:51', 'YYYY-MM-DD HH24:MI:SS'), null from dual
  )
select i.item_id, a.attribute
, a.attr_id as cur_id, a.date_beg as cur_date, a.value as cur_value
, d.attr_id as dup_id, d.date_beg as dup_date, d.value as dup_value
, max(a.date_beg) keep (dense_rank first order by a.date_beg desc) over (partition by a.item_id, a.attribute) as first_date
, max(a.attr_id) keep (dense_rank first order by a.date_beg desc) over (partition by a.item_id, a.attribute) as first_id
from items i
join attributes a on (a.item_id = i.item_id and a.date_beg <= sysdate and nvl(a.date_end,DATE'9999-01-01') > sysdate)
join attributes d on (d.item_id = i.item_id and d.date_beg <= sysdate and nvl(d.date_end,DATE'9999-01-01') > sysdate and d.attr_id != a.attr_id and d.attribute = a.attribute)
order by CUR_ID, DUP_ID

Я ошибок не вижу, но возможно у меня глаз замылился.
Меня смущает, что на три дубля получается 6 строк.

Финальный запрос для update будет примерно таким:
+

update attributes
set date_beg = trunc(sysdate)
where attr_id in
(
select distinct CUR_ID
from (
with
  items as
  (
    select null as item_id, null as login from dual where 0=1
    union all select 38731, 'item1' from dual
  ),
  attributes as
  (
    select 0 as attr_id, 0 as item_id, '' as attribute, '' as value, DATE'1000-01-01' as date_beg, DATE'9999-01-01' as date_end from dual where 0=1
    union all select 283945, 38731, 'RBN-Context_Name',     'local',                              to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 283946, 38731, 'RBN-Sub_Profile_Name', 'PPPOE',                              to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 283944, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 191270, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2014-05-04 14:25:02', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 183709, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2014-03-28 10:57:51', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 183711, 38731, 'Service-Type',         'Framed-User',                        to_date('2014-03-28 10:57:51', 'YYYY-MM-DD HH24:MI:SS'), null from dual
  )
select i.item_id, a.attribute
, a.attr_id as cur_id, a.date_beg as cur_date, a.value as cur_value
, d.attr_id as dup_id, d.date_beg as dup_date, d.value as dup_value
, max(a.date_beg) keep (dense_rank first order by a.date_beg desc) over (partition by a.item_id, a.attribute) as first_date
, max(a.attr_id) keep (dense_rank first order by a.date_beg desc) over (partition by a.item_id, a.attribute) as first_id
from items i
join attributes a on (a.item_id = i.item_id and a.date_beg <= sysdate and nvl(a.date_end,DATE'9999-01-01') > sysdate)
join attributes d on (d.item_id = i.item_id and d.date_beg <= sysdate and nvl(d.date_end,DATE'9999-01-01') > sysdate and d.attr_id != a.attr_id and d.attribute = a.attribute)
order by CUR_ID, DUP_ID
)
where FIRST_ID != CUR_ID
)

30 сен 15, 17:42    [18216456]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
stax..
Guest
Alibek B.,

set date_end = trunc(sysdate)

SQL> select * from t order by ITEM_ID,date_beg
  2  /

   ATTR_ID    ITEM_ID ATTRIBUTE  VALUE      DATE_BEG DATE_END
---------- ---------- ---------- ---------- -------- --------
         0          1 a          10         01.01.14 01.01.15
         1          1 a          10         01.01.15
         2          1 a          11         11.01.15
         4          1 a          12         12.01.15
         3          2 a          20         20.01.15


....
stax
30 сен 15, 18:21    [18216689]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
stax..
Guest
Alibek B.
stax..
merge

В каком смысле?
merge into attributes
using (select item_id, max(date_beg) as max_date ...) rs on (attributes.item_id = rs.item_id and attributes.date_beg < rs.max_date)
when matched then update set date_end = ...
Или имеется ввиду другое?

stax..
проверте нет ли пересечений и в исторических данных

Есть, 500 штук таких записей набралось.
Потому и думаю запускать по расписанию скрипт, который старые атрибуты будет закрывать.

JDS
Если нет записей с равными date_start, можно попробовать примерно так, но внимательно проверить прежде, а лучше забэкапиться ):

Таких записей в текущих данных нет.
Но теоретически такое возможно.
Я пока сам не решил, как именно буду такие ситуации решать.
Склоняюсь к использованию аналитических функций
max(a.attr_id) keep (dense_rank first order by a.date_beg) over (partition by r.item_id) as first_id
и update для attr_id != first_id.


Alibek B.,
по чесному я Ваш скрипт проверки не совсем понял

я БЫ, проверял с помощью lag/lead date_beg=date_end
и от етого плясал
задачка я так понимаю практическая, запускается раз в день
я б не мудрувал и делал через pl/sql

если принципально одним оператором, я б кодировал через merge

......
stax
30 сен 15, 18:29    [18216729]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
Alibek B.
Member

Откуда:
Сообщений: 3734
Сделал через MERGE:
merge into RADREPLY data using (
select S.SERVICE_ID, R.ATTRIBUTE
, R.RADREPLY_ID as CUR_ID, R.DATE_BEG as CUR_DATE, R.VALUE as CUR_VALUE
, D.RADREPLY_ID as DUP_ID, D.DATE_BEG as DUP_DATE, D.VALUE as DUP_VALUE
, max(R.DATE_BEG) keep (dense_rank first order by R.DATE_BEG desc) over (partition by R.SERVICE_ID, R.ATTRIBUTE) as LAST_DATE
, max(R.RADREPLY_ID) keep (dense_rank first order by R.DATE_BEG desc) over (partition by R.SERVICE_ID, R.ATTRIBUTE) as LAST_ID
from SERVICES S
join RADREPLY R on (R.SERVICE_ID = S.SERVICE_ID and R.DATE_BEG <= sysdate and nvl(R.DATE_END,DATE'9999-01-01') > sysdate)
join RADREPLY D on (D.SERVICE_ID = S.SERVICE_ID and D.DATE_BEG <= sysdate and nvl(D.DATE_END,DATE'9999-01-01') > sysdate and D.RADREPLY_ID != R.RADREPLY_ID and D.ATTRIBUTE = R.ATTRIBUTE)
where S.STATUS != -20
and not exists (select TARIFFEL_ID from BM_TARIFFEL where TARIFF_ID = S.TARIFF_ID and TARIFFEL_TYPE_ID = 908 and DATE_BEG <= sysdate and nvl(DATE_END, DATE'9999-01-01') > sysdate)
) rs on (rs.CUR_ID = data.RADREPLY_ID and rs.CUR_ID != rs.LAST_ID)
when matched then update set data.DATE_END = rs.LAST_DATE


Но этот скрипт вылетает с ошибкой, когда дублей больше 2 (потому что когда дублей 3, подзапрос возвращает 6 строк и однозначного соответствия в условии слияния не получается).
Временно сделал так:
merge into RADREPLY data using (
select distinct S.SERVICE_ID, R.ATTRIBUTE
, R.RADREPLY_ID as CUR_ID, R.DATE_BEG as CUR_DATE, R.VALUE as CUR_VALUE
--, D.RADREPLY_ID as DUP_ID, D.DATE_BEG as DUP_DATE, D.VALUE as DUP_VALUE
, max(R.DATE_BEG) keep (dense_rank first order by R.DATE_BEG desc) over (partition by R.SERVICE_ID, R.ATTRIBUTE) as LAST_DATE
, max(R.RADREPLY_ID) keep (dense_rank first order by R.DATE_BEG desc) over (partition by R.SERVICE_ID, R.ATTRIBUTE) as LAST_ID
from SERVICES S
join RADREPLY R on (R.SERVICE_ID = S.SERVICE_ID and R.DATE_BEG <= sysdate and nvl(R.DATE_END,DATE'9999-01-01') > sysdate)
join RADREPLY D on (D.SERVICE_ID = S.SERVICE_ID and D.DATE_BEG <= sysdate and nvl(D.DATE_END,DATE'9999-01-01') > sysdate and D.RADREPLY_ID != R.RADREPLY_ID and D.ATTRIBUTE = R.ATTRIBUTE)
where S.STATUS != -20
and not exists (select TARIFFEL_ID from BM_TARIFFEL where TARIFF_ID = S.TARIFF_ID and TARIFFEL_TYPE_ID = 908 and DATE_BEG <= sysdate and nvl(DATE_END, DATE'9999-01-01') > sysdate)
) rs on (rs.CUR_ID = data.RADREPLY_ID and rs.CUR_ID != rs.LAST_ID)
when matched then update set data.DATE_END = rs.LAST_DATE

(то есть убрал из вывода D и применил distinct)

А можно ли в принципе исключить декартово перемножение в подзапросе?
1 окт 15, 09:43    [18218408]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
JDS
Member

Откуда:
Сообщений: 690
Господа! Туплю наверно, не понимая конечной цели, но почему не просто: )
WITH attributes AS (SELECT 0 as attr_id, 0 AS item_id, '' AS attribute, '' AS value, DATE '1000-01-01' AS date_beg, DATE '9999-01-01' AS date_end FROM dual WHERE 0=1 UNION ALL
                    SELECT 283945, 38731, 'RBN-Context_Name',     'local',                              TO_DATE('2015-08-01', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL 
                    SELECT 283946, 38731, 'RBN-Sub_Profile_Name', 'PPPOE',                              TO_DATE('2015-08-01', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL 
                    SELECT 283944, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', TO_DATE('2015-08-01', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL 
                    SELECT 191270, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', TO_DATE('2014-05-04', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL
                    SELECT 183709, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', TO_DATE('2014-03-28', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL 
                    SELECT 183711, 38731, 'Service-Type',         'Framed-User',                        TO_DATE('2014-03-28', 'YYYY-MM-DD'), NULL FROM DUAL)
SELECT attr_id, item_id, attribute, VALUE, date_beg, date_end,
      (SELECT MAX(date_beg) KEEP(DENSE_RANK FIRST ORDER BY date_beg) - 1
         FROM attributes t
        WHERE t.item_id = tr.item_id
          AND t.attribute = tr.attribute
          AND t.value = tr.value
          AND t.date_beg > tr.date_beg) AS new_date_end
  FROM attributes tr
 WHERE date_end IS NULL
   AND EXISTS (SELECT 1
                 FROM attributes
                WHERE item_id = tr.item_id
                  AND attribute = tr.attribute
                  AND value = tr.value
                  AND date_beg >= tr.date_beg)
1 окт 15, 10:31    [18218665]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
JDS
Member

Откуда:
Сообщений: 690
А. Ну сами записи для обновления:
WITH attributes AS (SELECT 0 as attr_id, 0 AS item_id, '' AS attribute, '' AS value, DATE '1000-01-01' AS date_beg, DATE '9999-01-01' AS date_end FROM dual WHERE 0=1 UNION ALL
                    SELECT 283945, 38731, 'RBN-Context_Name',     'local',                              TO_DATE('2015-08-01', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL 
                    SELECT 283946, 38731, 'RBN-Sub_Profile_Name', 'PPPOE',                              TO_DATE('2015-08-01', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL 
                    SELECT 283944, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', TO_DATE('2015-08-01', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL 
                    SELECT 191270, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', TO_DATE('2014-05-04', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL
                    SELECT 183709, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', TO_DATE('2014-03-28', 'YYYY-MM-DD'), NULL FROM DUAL UNION ALL 
                    SELECT 183711, 38731, 'Service-Type',         'Framed-User',                        TO_DATE('2014-03-28', 'YYYY-MM-DD'), NULL FROM DUAL)
SELECT attr_id, item_id, attribute, VALUE, date_beg, date_end,
      (SELECT MAX(date_beg) KEEP(DENSE_RANK FIRST ORDER BY date_beg) - 1
         FROM attributes t
        WHERE t.item_id = tr.item_id
          AND attr_id != tr.attr_id
          AND t.attribute = tr.attribute
          AND t.value = tr.value
          AND t.date_beg > tr.date_beg) AS new_date_end
  FROM attributes tr
 WHERE date_end IS NULL
   AND EXISTS (SELECT 1
                 FROM attributes
                WHERE item_id = tr.item_id
                  AND attribute = tr.attribute
                  >>>AND attr_id != tr.attr_id<<
                  AND value = tr.value
                  AND date_beg >= tr.date_beg)
1 окт 15, 10:34    [18218689]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
JDS
Member

Откуда:
Сообщений: 690
Ну и сам апдейт )
UPDATE attributes 
  SET date_end =  (SELECT MAX(date_beg) KEEP(DENSE_RANK FIRST ORDER BY date_beg) - 1
                     FROM attributes t
                    WHERE t.item_id = tr.item_id
                      AND attr_id != tr.attr_id
                      AND t.attribute = tr.attribute
                      AND t.value = tr.value
                      AND t.date_beg > tr.date_beg)
 WHERE date_end IS NULL
   AND EXISTS (SELECT 1
                 FROM attributes
                WHERE item_id = tr.item_id
                  AND attribute = tr.attribute
                  AND attr_id != tr.attr_id
                  AND value = tr.value
                  AND date_beg >= tr.date_beg)
1 окт 15, 10:36    [18218712]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
JDS
Member

Откуда:
Сообщений: 690
Подозрение, что value лишнее )
+
UPDATE attributes 
  SET date_end =  (SELECT MAX(date_beg) KEEP(DENSE_RANK FIRST ORDER BY date_beg) - 1
                     FROM attributes t
                    WHERE t.item_id = tr.item_id
                      AND attr_id != tr.attr_id
                      AND t.attribute = tr.attribute
                      AND t.date_beg > tr.date_beg)
 WHERE date_end IS NULL
   AND EXISTS (SELECT 1
                 FROM attributes
                WHERE item_id = tr.item_id
                  AND attribute = tr.attribute
                  AND attr_id != tr.attr_id
                  AND date_beg >= tr.date_beg)
1 окт 15, 10:39    [18218729]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
123йй
Member

Откуда:
Сообщений: 1639
Alibek B.
Нет, lead все же не подходит, слишком усложняется запрос.

разве ?
lead(attributes.date_beg) over(partition by item_id,attribute order by attr_id)-1
1 окт 15, 10:42    [18218762]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
Alibek B.
Member

Откуда:
Сообщений: 3734
JDS, спасибо за подсказки, но мне кажется, что мой вариант читается проще.
Если мне доведется через полгода править этот запрос, я весь день буду разбираться, почему там два почти одинаковых подзапроса и что они делают.

123йй
разве ?

Боюсь мне эта подсказка непонятна.
Насколько я понял, lead позволяет получить следующую/предыдущую запись в группе, то есть удобен для построения цепочек.
А мне нужно получить/исключить первый элемент в группе.
То есть lead для этого тоже подойдет (нужно искать записи, у которых lead пустой или напротив непустой), однако у меня запрос получился сложнее, чем с dense_rank.
1 окт 15, 11:10    [18219026]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
MonteCarlos
Member

Откуда:
Сообщений: 547
Так разве не проще?
+
слегка расширил данные
create table attributes
as 
with attr as
  (
    select 0 as attr_id, 0 as item_id, '' as attribute, '' as value, DATE'1000-01-01' as date_beg, DATE'9999-01-01' as date_end from dual where 0=1
    union all select 283945, 38731, 'RBN-Context_Name',     'local',                              to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 283946, 38731, 'RBN-Sub_Profile_Name', 'PPPOE',                              to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 283944, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2015-08-01 00:03:03', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 191270, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2014-05-04 14:25:02', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 183709, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2014-03-28 10:57:51', 'YYYY-MM-DD HH24:MI:SS'), null from dual
    union all select 183710, 38731, 'Cisco-AVPair',         'subscriber:accounting-list=PPP_ACC', to_date('2014-03-28 10:57:51', 'YYYY-MM-DD HH24:MI:SS'), to_date('2014-05-04 14:25:02', 'YYYY-MM-DD HH24:MI:SS') from dual
    union all select 183711, 38731, 'Service-Type',         'Framed-User',                        to_date('2014-03-28 10:57:51', 'YYYY-MM-DD HH24:MI:SS'), null from dual
  )
select * from attr

merge into attributes a 
using (select rowid as r_id, 
              date_end,
              lead(date_beg) over (partition by item_id, attribute order by date_beg, attr_id) as date_end_new       
       from attributes
) b
on (a.rowid = b.r_id)
when matched then 
  update 
  set a.date_end = b.date_end_new
  where b.date_end is null and b.date_end_new is not null
2 окт 15, 04:54    [18224065]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
stax..
Guest
Alibek B.
Если мне доведется через полгода править этот запрос


доп условие если date_end заполнено то оно правильно,
иначе убрать where

есть недостаток
последняя строка всетаки меняется но null на null
но зато очень просто и если через год ...
(если очень надо усложніть добавить условие rowid<> (поледний ровід )
  1  create table attributes
  2  as
  3  select  1 attr_id, 1item_id, 10 attribute,1 value,date '2015-01-01' date_beg,date '2015-01-02' date_end from du
  4      union all select  2, 1, 10,2,date '2015-01-02',null from dual
  5      union all select  3, 1, 10,3,date '2015-01-03',date '2015-01-04' from dual
  6      union all select  4, 1, 10,4,date '2015-01-04',null from dual
  7      union all select  5, 1, 10,5,date '2015-01-07',null from dual
  8      union all select  6, 1, 10,6,date '2015-01-08',null from dual
  9      union all select  7, 1, 10,7,date '2015-01-09',null from dual
 10      union all select  8, 1, 20,8,date '2015-01-03',date '2015-01-04' from dual
 11      union all select  9, 1, 20,9,date '2015-01-04',null from dual
 12      union all select 10, 2, 10,10,date '2015-01-03',date '2015-01-04' from dual
 13      union all select 11, 2, 10,11,date '2015-01-04',null from dual
 14      union all select 12, 2, 10,12,date '2015-01-09',null from dual
 15      union all select 13, 2, 20,10,date '2015-01-03',date '2015-01-04' from dual
 16*     union all select 14, 2, 20,13,date '2015-01-04',null from dual
SQL> /

Table created.

SQL> select * from attributes order by 2,3,5
  2  /

   ATTR_ID    ITEM_ID  ATTRIBUTE      VALUE DATE_BEG DATE_END
---------- ---------- ---------- ---------- -------- --------
         1          1         10          1 01.01.15 02.01.15
         2          1         10          2 02.01.15
         3          1         10          3 03.01.15 04.01.15
         4          1         10          4 04.01.15
         5          1         10          5 07.01.15
         6          1         10          6 08.01.15
         7          1         10          7 09.01.15
         8          1         20          8 03.01.15 04.01.15
         9          1         20          9 04.01.15
        10          2         10         10 03.01.15 04.01.15
        11          2         10         11 04.01.15
        12          2         10         12 09.01.15
        13          2         20         10 03.01.15 04.01.15
        14          2         20         13 04.01.15

14 rows selected.

SQL> update ATTRIBUTES a set DATE_END=
  2  (select min(date_beg) from ATTRIBUTES a1
  3   where a1.item_id=a.ITEM_ID and a1.ATTRIBUTE=a.ATTRIBUTE and a1.DATE_BEG>a.DATE_BEG)
  4  where
  5   a.date_end is null
  6  /

9 rows updated.

SQL> select * from attributes order by 2,3,5
  2  /

   ATTR_ID    ITEM_ID  ATTRIBUTE      VALUE DATE_BEG DATE_END
---------- ---------- ---------- ---------- -------- --------
         1          1         10          1 01.01.15 02.01.15
         2          1         10          2 02.01.15 03.01.15
         3          1         10          3 03.01.15 04.01.15
         4          1         10          4 04.01.15 07.01.15
         5          1         10          5 07.01.15 08.01.15
         6          1         10          6 08.01.15 09.01.15
         7          1         10          7 09.01.15
         8          1         20          8 03.01.15 04.01.15
         9          1         20          9 04.01.15
        10          2         10         10 03.01.15 04.01.15
        11          2         10         11 04.01.15 09.01.15
        12          2         10         12 09.01.15
        13          2         20         10 03.01.15 04.01.15
        14          2         20         13 04.01.15

14 rows selected.

SQL>


.....
stax
2 окт 15, 09:30    [18224432]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как правильно обновить все записи, кроме последней?  [new]
stax..
Guest
Alibek B.,

еще достаточно простой вариант
  1  update
  2  (select
  3    a.*,
  4    (select min(date_beg)
  5     from ATTRIBUTES a1
  6     where a1.item_id=a.ITEM_ID and a1.ATTRIBUTE=a.ATTRIBUTE and a1.DATE_BEG>a.DATE_BEG) d_lead
  7   from ATTRIBUTES a) aa
  8  set aa.DATE_END=aa.d_lead
  9  where
 10*  aa.date_end is null and aa.d_lead is not null
SQL> /


.....
stax
2 окт 15, 09:50    [18224533]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить