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

Откуда:
Сообщений: 2373
Помогите довести до ума запрос.

У меня есть лог, в котором фиксируется изменение данных.
Нужные данные из лога я получаю таким запросом:
select CUSTOMER_ID, OLD_VALUE, NEW_VALUE, MOMENT
from BM_ACTION_LOG
where ACTION_ID = 102
and STAFF_COMMENT = 'changed field `group_id`'

CUSTOMER_IDOLD_VALUENEW_VALUEMOMENT
7853113425530.12.2016 15:26:43
7853125533430.12.2016 15:26:51
7853133421430.12.2016 15:27:04
785312145530.12.2016 15:29:31
785315513430.12.2016 15:29:36


Из этой таблицы мне нужно составить историческую таблицу в стиле id, from_date, to_date.
Я делаю так:
select al.CUSTOMER_ID, al.OLD_VALUE, al.NEW_VALUE, al.MOMENT
, al.NEW_VALUE as GROUP_ID
, al.MOMENT as DATE_BEG
, lead (al.MOMENT) over (partition by al.CUSTOMER_ID order by al.MOMENT) as DATE_END
from BM_ACTION_LOG al
join CUSTOMERS c on (c.CUSTOMER_ID = al.CUSTOMER_ID)
where al.ACTION_ID = 102
and al.STAFF_COMMENT = 'changed field `group_id`'
and al.CUSTOMER_ID = 78531
order by al.MOMENT

CUSTOMER_IDOLD_VALUENEW_VALUEMOMENTGROUP_IDDATE_BEGDATE_END
7853113425530.12.2016 15:26:4325530.12.2016 15:26:4330.12.2016 15:26:51
7853125533430.12.2016 15:26:5133430.12.2016 15:26:5130.12.2016 15:27:04
7853133421430.12.2016 15:27:0421430.12.2016 15:27:0430.12.2016 15:29:31
785312145530.12.2016 15:29:315530.12.2016 15:29:3130.12.2016 15:29:36
785315513430.12.2016 15:29:3613430.12.2016 15:29:36

Почти все хорошо, но мне нужно также получить самую первую запись с group_id, которая действует с c.CREATE_DATE до 30.12.2016 15:26:43, то есть должно получиться
CUSTOMER_IDOLD_VALUENEW_VALUEMOMENTGROUP_IDDATE_BEGDATE_END
7853113418.11.2016 15:49:0530.12.2016 15:26:43
7853113425530.12.2016 15:26:4325530.12.2016 15:26:4330.12.2016 15:26:51
7853125533430.12.2016 15:26:5133430.12.2016 15:26:5130.12.2016 15:27:04
7853133421430.12.2016 15:27:0421430.12.2016 15:27:0430.12.2016 15:29:31
785312145530.12.2016 15:29:315530.12.2016 15:29:3130.12.2016 15:29:36
785315513430.12.2016 15:29:3613430.12.2016 15:29:36

Что-то не соображу, как лучше всего сделать "раздвоение" строк для первой записи.
"Раздвоение" в самом начале видимо является неверным:
select al.CUSTOMER_ID, al.OLD_VALUE, al.NEW_VALUE, al.MOMENT
, nvl2(D.X, al.NEW_VALUE, null) as GROUP_ID
, nvl2(D.X, al.MOMENT, c.CREATE_DATE) as DATE_BEG
, nvl2(D.X, lead (al.MOMENT) over (partition by al.CUSTOMER_ID order by al.MOMENT), null) as DATE_END
from (select null as X from DUAL union all select 1 as X from DUAL) D
join CUSTOMERS c on (c.CUSTOMER_ID is not null)
left join BM_ACTION_LOG al on (al.CUSTOMER_ID = c.CUSTOMER_ID and D.X is not null)
where nvl2(D.X, al.ACTION_ID, 102) = 102
and nvl2(D.X, al.STAFF_COMMENT, 'changed field `group_id`') = 'changed field `group_id`'
and c.CUSTOMER_ID = 78531
order by al.MOMENT

поскольку я не могу получить нужное значение GROUP_ID. И кроме того, таблица BM_ACTION_LOG довольно объемная и подобные манипуляции сильно сажают производительность запроса.
________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
1 фев 17, 18:16    [20170794]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
stax..
Guest
Alibek B.,

мож моделькой
прономеровать с 1 для індекса
0 новая строка
напр
  1  with t(id,rn,o,n,z,po) as(
  2  select 1,1,10,20, date '2017-01-01', date '2017-01-03' from dual union all
  3  select 1,2,20,30, date '2017-01-01', date '2017-01-03' from dual union all
  4  select 1,3,30,40, date '2017-01-03', null from dual union all
  5  select 2,1,11,21, date '2017-02-01', date '2017-02-13' from dual union all
  6  select 2,2,21,31, date '2017-02-13', null from dual
  7  )
  8  select * from t
  9  model
 10       partition by (id)
 11       dimension by (rn dim)
 12       measures(rn,o old,n new,z,po)
 13       rules
 14       (z[0]=date '2016-11-18',
 15        po[0]=z[1],
 16        new[0]=old[1]
 17        )
 18* order by id,dim
SQL> /

        ID        DIM         RN        OLD        NEW Z        PO
---------- ---------- ---------- ---------- ---------- -------- --------
         1          0                               10 18.11.16 01.01.17
         1          1          1         10         20 01.01.17 03.01.17
         1          2          2         20         30 01.01.17 03.01.17
         1          3          3         30         40 03.01.17
         2          0                               11 18.11.16 01.02.17
         2          1          1         11         21 01.02.17 13.02.17
         2          2          2         21         31 13.02.17

7 rows selected.
1 фев 17, 19:26    [20170996]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4704
Alibek B.
"раздвоение" строк для первой записи
select *
from (select t.*, decode(row_number() over (partition by customer_id order by moment), 1, sys.odcinumberlist(0, 1), sys.odcinumberlist(0)) c from t), table(c)

Для 12с более эффективно решалось бы через pattern matching.
1 фев 17, 19:54    [20171060]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
stax..
Guest
dbms_photoshop
Alibek B.
"раздвоение" строк для первой записи
select *
from (select t.*, decode(row_number() over (partition by customer_id order by moment), 1, sys.odcinumberlist(0, 1), sys.odcinumberlist(0)) c from t), table(c)

Для 12с более эффективно решалось бы через pattern matching.


в 11-ке вроде тож прокатіт
  1* select * from dual,table(decode(1,1, sys.odcinumberlist(0, 1), sys.odcinumberlist(0)))
SQL> /

D COLUMN_VALUE
- ------------
X            0
X            1


или имелось ввиду что-то другое?

.....
stax
1 фев 17, 21:40    [20171251]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
stax.., точно, про модель я не подумал.
Спасибо, самое то.

dbms_photoshop, да, дублировать строку надо было на row_number=1.
Но вариант с моделью мне нравится больше.
1 фев 17, 22:18    [20171378]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
Составил такой запрос:
select CUSTOMER_ID, VERSION, NEW_VALUE as GROUP_ID, DATE_BEG, DATE_END
from BM_ACTION_LOG
where ACTION_ID = 102
and STAFF_COMMENT = 'changed field `group_id`'
model
  partition by (CUSTOMER_ID)
  dimension by ((row_number() over (partition by CUSTOMER_ID order by MOMENT)) VERSION)
  measures(OLD_VALUE, NEW_VALUE, MOMENT DATE_BEG, lead (MOMENT) over (partition by CUSTOMER_ID order by MOMENT) DATE_END)
  rules
  (
    DATE_BEG[0]=null,
    DATE_END[0]=DATE_BEG[1],
    NEW_VALUE[0]=OLD_VALUE[1]
  )
order by CUSTOMER_ID, VERSION

CUSTOMER_IDVERSIONGROUP_IDDATE_BEGDATE_END
78531013430.12.2016 15:26:43
78531125530.12.2016 15:26:4330.12.2016 15:26:51
78531233430.12.2016 15:26:5130.12.2016 15:27:04
78531321430.12.2016 15:27:0430.12.2016 15:29:31
7853145530.12.2016 15:29:3130.12.2016 15:29:36
78531513430.12.2016 15:29:36

Работает весьма шустро, даже быстрее, чем я рассчитывал (около 150мс, при этом в таблице порядка 5кк записей).
Единственное хотел бы уточнить — не следует ли сразу обработать null-значения для дат (nvl(DATE_BEG,DATE'1000-01-01') as DATE_BEG, nvl(DATE_END,DATE'9999-01-01') as DATE_END), чтобы в последующих соединениях не использовать nvl, или нет особой разницы?
То есть:
select ...
from events
join
(
select CUSTOMER_ID, VERSION, NEW_VALUE as GROUP_ID, DATE_BEG, DATE_END
from BM_ACTION_LOG
...
) groups on (groups.customer_id = events.customer_id and nvl(groups.date_beg,date'1000-01-01') <= events.moment and nvl(groups.date_end,date'9999-01-01') > events.moment)
...

или
select ...
from events
join
(
select CUSTOMER_ID, VERSION, NEW_VALUE as GROUP_ID, nvl(DATE_BEG,DATE'1000-01-01') DATE_BEG, nvl(DATE_END,DATE'9999-01-01') DATE_END
from BM_ACTION_LOG
...
) groups on (groups.customer_id = events.customer_id and groups.date_beg <= events.moment and groups.date_end > events.moment)
...
1 фев 17, 23:05    [20171502]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
Посоветуйте еще с одним моментом.
Есть таблица CUSTOMERS, в которой есть поля CUSTOMER_ID и GROUP_ID.
Историю изменений GROUP_ID я могу получить из журнала, но это не совсем достоверная информация — в некоторых случаях изменение группы не фиксируется в журнале, кроме того в журнале не всегда фиксируется номер группы при создании записи в таблице CUSTOMERS.
Кроме того, история изменений существенна только в случае определенной группы (группа "Корзина", куда "удаляются" записи), в остальных случаях (когда запись не "удалена") можно использовать текущую группу.
Если такое решать «в лоб», то получается примерно такой запрос:
with GH as
(
select CUSTOMER_ID, VERSION, NEW_VALUE as GROUP_ID, DATE_BEG, DATE_END
from BM_ACTION_LOG
where ACTION_ID = 102
and STAFF_COMMENT = 'changed field `group_id`'
model
  partition by (CUSTOMER_ID)
  dimension by ((row_number() over (partition by CUSTOMER_ID order by MOMENT)) VERSION)
  measures(OLD_VALUE, NEW_VALUE, MOMENT DATE_BEG, lead (MOMENT) over (partition by CUSTOMER_ID order by MOMENT) DATE_END)
  rules
  (
    DATE_BEG[0]=null,
    DATE_END[0]=DATE_BEG[1],
    NEW_VALUE[0]=OLD_VALUE[1]
  )
)
select C.CUSTOMER_ID
, C.GROUP_ID as CURRENT_GROUP_ID
, GH.GROUP_ID as HISTORY_GROUP_ID
, nvl(nullif(C.GROUP_ID,54),GH.GROUP_ID) as GROUP_ID
from CUSTOMERS C
left join GH
  on
  (
    GH.CUSTOMER_ID = C.CUSTOMER_ID
    and coalesce(GH.DATE_BEG, C.CREATE_DATE, DATE'1000-01-01') <= sysdate
    and coalesce(GH.DATE_END, C.STOP_DATE, DATE'9999-01-01') > sysdate
  )

А можно ли в случае CUSTOMERS.GROUP_ID=54 вообще не делать джойны с подзапросом?
Для этого можно в модели добавить условие «and CUSTOMER_ID in (select CUSTOMER_ID from CUSTOMERS where GROUP_ID=54)».
Это нормальный способ или есть что-то более правильное?
2 фев 17, 10:10    [20172303]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
Не осилил я модель, чтобы составить ее в одном запросе (без подзапросов).
Воспользовался советом dbms_photoshop, там для меня проще.
Составил такой запрос:
with HISTORY as
(
  select ...
  , nvl2(L.ACTION_LOG_ID,
         decode(row_number() over (partition by L.CUSTOMER_ID order by L.MOMENT),
         1, sys.ODCINumberList(0,1),
         sys.ODCINumberList(1)), null) as DUPS
  from ...
)
select *
from HISTORY
left join TABLE(HISTORY.DUPS) DUPS on (0=0)

Смысл запроса в следующем: если для записи найдена достоверная история в журнале операций, то в ODCINumberList возвращается массив из двух элементов, для первой записи в журнале, чтобы ее раздвоить, и массив из одного элемента для остальных записей в журнале. Если же достоверных данных в журнале не найдено, то возвращается пустой массив.
Затем делается left join с этим массивом, для первой записи из журнала строка должна раздвоиться, для остальных случаев строка остается (так как left join).
Однако при выполнении этого запроса я получаю пустой результат.
Если же left join я комментирую, то получаю результат из одной строки.
Не могу понять, куда пропадает строка. Или это особенности TABLE?
2 фев 17, 17:33    [20174633]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
Кстати, если сделать так:
  , nvl2(L.ACTION_LOG_ID,
         decode(row_number() over (partition by L.CUSTOMER_ID order by L.MOMENT),
         1, sys.ODCINumberList(0,1),
         sys.ODCINumberList(1)), sys.ODCINumberList(null)) as DUPS

то строка не пропадает и запрос работает, как я хочу.

Нашел такую тему: http://www.sql.ru/forum/777843/ocherednoy-bag-s-left-join
Прочитал, но не совсем понял, баг это или особенности table().
2 фев 17, 17:36    [20174643]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4704
Alibek B.
Прочитал, но не совсем понял, баг это или особенности table().
В коллекциях для этой задачи нет особой необходимости, то было больше для баловства (если интересны детали - прочитай "unnesting collections" в pdf здесь: 20168262).
Можешь соединяться с (... union all ...).
stax..
или имелось ввиду что-то другое?
Имелось в виду, что если имеется 12с, то строку можно добавить с помощью pattern matching.
Правда, подозреваю, это можно сделать только если больше одной строки на customer_id.
Так что не очень решение. Нет 12с под рукой, чтоб попробовать.
2 фев 17, 18:09    [20174757]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
stax..
Guest
dbms_photoshop,

я етот pattern matching не совсем понял, да и практики у меня сейчас нет
ищу работу

.....
stax
2 фев 17, 21:27    [20175240]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
dbms_photoshop
то было больше для баловства

Видимо да, для больших объемов это не подходит.
Если у меня запрос с моделью выполнялся 160мс, то запрос с коллекциями выполнялся более 8 минут.
2 фев 17, 21:51    [20175322]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4704
dbms_photoshop
stax..
или имелось ввиду что-то другое?
Имелось в виду, что если имеется 12с, то строку можно добавить с помощью pattern matching.
Правда, подозреваю, это можно сделать только если больше одной строки на customer_id.
Так что не очень решение. Нет 12с под рукой, чтоб попробовать.
Решение при указанных ограничениях.
SQL> select part, id
  2  from
  3  (select rownum part from dual connect by rownum <= 3) t
  4  cross apply (select rownum id from dual connect by level <= t.part) p
  5  match_recognize
  6  ( partition by part
  7    order by id desc
  8    all rows per match with unmatched rows
  9    after match skip to next row
 10    pattern (x0 x*)
 11    define x0 as x0.id in (1, 2)
 12  ) mr;

      PART         ID
---------- ----------
         1          1
         2          2
         2          1
         2          1
         3          3
         3          2
         3          1
         3          1

8 rows selected.

Alibek B.
dbms_photoshop
то было больше для баловства

Видимо да, для больших объемов это не подходит.
Если у меня запрос с моделью выполнялся 160мс, то запрос с коллекциями выполнялся более 8 минут.
Да, объемы прямо огромные, если модель выполняется за 160мс.

Ну ты же, я надеюсь в состоянии посмотреть план и куда уходит время.
2 фев 17, 22:49    [20175478]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4704
stax..
dbms_photoshop,

я етот pattern matching не совсем понял, да и практики у меня сейчас нет
ищу работу

.....
stax
Мне кажется я достаточно понятно рассказываю про него тут - The Power of Oracle SQL.
2 фев 17, 22:50    [20175485]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
dbms_photoshop
Да, объемы прямо огромные, если модель выполняется за 160мс.

Планы и трассировку я не смотрел, но мне чисто умозрительно кажется, что запрос с подзапросом, где внутри с помощью decode составляется коллекция, а снаружи по этой коллекции строится таблица, не может быть быстрым.
Такой вариант, кстати, гораздо проще и понятнее:
...
from HISTORY
left join
(
  select 1 as V, 0 as N from DUAL
  union all select 1, 1 from DUAL
) DUPS on (DUPS.V = HISTORY.VERSION)
...

и выполняется быстрее: при первом запуске около 15с, при последующих менее 300мс.

Но тем не менее, для данной задачи мне самым оптимальным кажется использование модели.
Буду изучать документацию и примеры и делать через нее.
3 фев 17, 09:05    [20176009]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4704
Alibek B.
трассировку я не смотрел
А её и не надо смотреть.
Для анализа производительности в 99% случаев достаточно dbms_xplan.display_cursor с включенными runtime execution statistics или dbms_sqltune.report_sql_monitor.
Alibek B.
мне чисто умозрительно кажется
Alibek B.
мне самым оптимальным кажется
Понятно. Дальнейший диалог смысла не имеет.
3 фев 17, 12:20    [20176891]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
Вроде бы удалось создать нужную модель, на всех тестовых данных результаты такие, какие мне нужны.
+

select *
--, CUSTOMER_ID
--, VERSION
--, GROUP_ID
--, nvl(DATE_BEG,DATE'1000-01-01') as DATE_BEG
--, nvl(DATE_END,DATE'9999-01-01') as DATE_END
from CUSTOMERS C
left join
(
  select S.CUSTOMER_ID, L.MOMENT
  from BM_ACTION_LOG L
  join BM_ENVELOP E on (E.ENVELOP_ID = L.TARGET_ID)
  join SERVICES S on (S.SERVICE_ID = E.SERVICE_ID)
  where L.ACTION_ID = 50300
  and L.SUB_ACTION_ID = 5030001
) A on (A.CUSTOMER_ID = C.CUSTOMER_ID)
left join BM_ACTION_LOG L
  on
  (
    L.CUSTOMER_ID = C.CUSTOMER_ID
    and C.GROUP_ID in (54,374,254,255)
    and L.ACTION_ID = 102
    and L.STAFF_COMMENT = 'changed field `group_id`'
  )
where C.CUSTOMER_ID in (2829, 78531, 683, 296, 16448, 942, 929, 7338, 780)
model partition by (C.CUSTOMER_ID)
  dimension by
  (
    nvl2(L.ACTION_LOG_ID,row_number() over (partition by L.CUSTOMER_ID order by L.MOMENT),0) VERSION
  )
  measures
  (
      C.GROUP_ID as "_GROUP_ID"
    , C.CREATE_DATE as "_DATE_CREATE"
    , A.MOMENT as "_DATE_START"
    , C.STOP_DATE as "_DATE_STOP"
    , L.OLD_VALUE as "_OLD"
    , L.NEW_VALUE as GROUP_ID
    , L.MOMENT DATE_BEG
    , lead (MOMENT) over (partition by L.CUSTOMER_ID order by L.MOMENT) DATE_END
  )
  rules
  (
    "_GROUP_ID"[0]=nvl("_GROUP_ID"[0],"_GROUP_ID"[1]),
    "_DATE_CREATE"[0]=nvl("_DATE_CREATE"[0],"_DATE_CREATE"[1]),
    "_DATE_START"[0]=nvl("_DATE_START"[0],"_DATE_START"[1]),
    "_DATE_STOP"[0]=nvl("_DATE_STOP"[0],"_DATE_STOP"[1]),
    DATE_BEG[0]=coalesce("_DATE_START"[0],"_DATE_CREATE"[0]),
    DATE_END[0]=DATE_BEG[1],
    GROUP_ID[0]=nvl("_OLD"[1],"_GROUP_ID"[0])
  )


1. Информация из журнала операций используется только для некоторых групп, для остальных групп всегда используется текущее значение группы.
2. Если для записи нет данных в журнале операций, то чтобы избежать добавления новой строки с индексом 0 в модели, в dimension я использую nvl2; для таких строк индекс всегда равен нулю, для остальных случаев он начинается с 1.
3. Поскольку при отсутствии данных в журнале операций элементов с индексами от 1 не существует, в rules также приходится использовать nvl.

Вообщем все работает, но мне не нравится обилие nvl.
Видимо из-за этого запрос выполняется около минуты.
Не посоветуете, можно ли его ускорить?
Нынешний план выглядит следующим образом:

--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 109K| 12M| | 5670 (1)| 00:01:09 |
| 1 | SQL MODEL ORDERED FAST | | 109K| 12M| | 5670 (1)| 00:01:09 |
| 2 | WINDOW SORT | | 109K| 12M| 29M| 5670 (1)| 00:01:09 |
| 3 | NESTED LOOPS OUTER | | 109K| 12M| | 2628 (1)| 00:00:32 |
|* 4 | HASH JOIN RIGHT OUTER | | 10921 | 426K| | 231 (1)| 00:00:03 |
| 5 | VIEW | | 6 | 126 | | 214 (0)| 00:00:03 |
| 6 | NESTED LOOPS | | 6 | 246 | | 214 (0)| 00:00:03 |
| 7 | NESTED LOOPS | | 6 | 186 | | 208 (0)| 00:00:03 |
|* 8 | TABLE ACCESS BY INDEX ROWID| BM_ACTION_LOG | 6 | 132 | | 202 (0)| 00:00:03 |
|* 9 | INDEX RANGE SCAN | BM_ACTLOG_SUB_ACTION_ID_IDX | 4443 | | | 21 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| BM_ENVELOP | 1 | 9 | | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | ENVELOPID_PK | 1 | | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | SERVICES | 1 | 10 | | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | SERVICES_PK | 1 | | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | CUSTOMERS | 10921 | 202K| | 16 (0)| 00:00:01 |
| 15 | VIEW | | 10 | 840 | | 0 (0)| 00:00:01 |
|* 16 | FILTER | | | | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | BM_ACTION_LOG | 1 | 138 | | 194 (0)| 00:00:03 |
|* 18 | INDEX RANGE SCAN | BM_ACTLOG_CUSTOMER_ID_IDX | 360 | | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."CUSTOMER_ID"(+)="C"."CUSTOMER_ID")
8 - filter("L"."ACTION_ID"=50300)
9 - access("L"."SUB_ACTION_ID"=5030001)
11 - access("E"."ENVELOP_ID"="L"."TARGET_ID")
13 - access("S"."SERVICE_ID"="E"."SERVICE_ID")
16 - filter("C"."GROUP_ID"=54 OR "C"."GROUP_ID"=254 OR "C"."GROUP_ID"=255 OR "C"."GROUP_ID"=374)
17 - filter("L"."STAFF_COMMENT"='changed field `group_id`' AND "L"."ACTION_ID"=102)
18 - access("L"."CUSTOMER_ID"="C"."CUSTOMER_ID")
3 фев 17, 12:38    [20176982]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
Подскажите, как в модели ссылаться на текущее и предыдущее значение?
У меня есть модель со следующими правилами:
dimension by
(
  nvl2(L.ACTION_LOG_ID,row_number() over (partition by L.CUSTOMER_ID order by L.MOMENT),0) VERSION
)
...
rules
(
...
  GROUP_ID[0]=nvl("_OLD"[1],"_GROUP_ID"[0]),
...
)

Теперь нужно добавить в модель еще одно поле LAST_ID, которое заполняется по следующему правилу:
1. Если текущий GROUP_ID in (1,2,3), то LAST_ID(i)=LAST_ID(i-1)
2. В ином случае LAST_ID(i)=GROUP_ID(i)

Как мне записать это правило?
Я пробовал так:
  LAST_ID[VERSION]=case when GROUP_ID[VERSION] in (54,374,254,255) then LAST_ID[VERSION-1] else GROUP_ID[VERSION] end,
  LAST_ID[0]=GROUP_ID[0]

но получаю ошибку "ORA-32622: недопустимая ссылка на несколько ячеек".
10 фев 17, 18:25    [20201640]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
Разобрался, справа нужно было использовать функцию CV().
По правде говоря с непривычки понять MODEL сложновато.
Но вроде бы удалось разобраться.
+

select CUSTOMER_ID
, VERSION
, GROUP_ID
, DATE_BEG
, DATE_END
, LAST_ID
from CUSTOMERS C
left join BM_ACTION_LOG L
on
(
  L.CUSTOMER_ID = C.CUSTOMER_ID
  and L.ACTION_ID = 102
  and L.STAFF_COMMENT = 'changed field `group_id`'
)
model
  partition by (C.CUSTOMER_ID)
  dimension by (nvl2(L.ACTION_LOG_ID,row_number() over (partition by L.CUSTOMER_ID order by L.MOMENT),0) VERSION)
  measures
  (
    to_number(L.NEW_VALUE) as GROUP_ID,
    L.MOMENT as DATE_BEG,
    L.MOMENT as DATE_END,
    0 as LAST_ID,
    nvl(C.CREATE_DATE, DATE'1000-01-01') as "_START",
    nvl(C.STOP_DATE, DATE'9999-01-01') as "_STOP",
    to_number(L.OLD_VALUE) as "_OLD",
    to_number(L.NEW_VALUE) as "_NEW",
    C.GROUP_ID as "_GROUP",
    L.MOMENT as "_MOMENT"
  )
  rules
  (
    "_START"[0] = nvl("_START"[0], "_START"[1]),
    "_STOP"[0] = nvl("_STOP"[0], "_STOP"[1]),
    GROUP_ID[0] = nvl("_OLD"[1], "_GROUP"[0]),
    DATE_BEG[0] = "_START"[0],
    DATE_END[VERSION] = nvl(DATE_BEG[CV()+1], "_STOP"[0]),
    LAST_ID[0] = case when GROUP_ID[0] not in (54,374,254,255) then GROUP_ID[0] end,
    LAST_ID[VERSION] = nvl(case when GROUP_ID[CV()] not in (54,374,254,255) then GROUP_ID[CV()] end, LAST_ID[CV()-1])
  )
order by 1, 2


Удалось его даже оптимизировать и избавиться от лишнего lead, теперь данный запрос выполняется менее 200мс.
11 фев 17, 00:18    [20202285]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
K790
Member

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

всего два вопроса.
1. у Вас OLAP или OLTP?
2. model прямо в промышленную бд будете внедрять? напишите позже про эксплуатацию данного решения.
11 фев 17, 11:10    [20202702]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17275
Встречный вопрос -- почему внедрение MODEL в производственный код вызывает недоумение?
Пока вижу один ответ: тяжко будет поддерживать тем, кто не знаком с технологией

Судя по вопросу OLTP или OLAP -- есть какие-то сведения, что MODEL плохо в смысле конкуренции?
11 фев 17, 11:35    [20202790]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
K790
Member

Откуда:
Сообщений: 553
Вячеслав Любомудров,

Вячеслав, поэтому вопросы и возникли. Хотелось бы узнать насколько модель используется в промышленной эксплуатации. Ее эффективность и применимость.

Все-таки есть сомнения при использовании в OLTP.

Это более риторический вопрос, нежели указать на какие-то изъяны автора.
11 фев 17, 11:50    [20202813]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
Как таковой эксплуатации не планируется.
Модель будет использоваться при составлении ежемесячных отчетов, отчеты экспортируются в Excel и более в БД не используются.
11 фев 17, 11:54    [20202829]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
K790
Member

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

и действительно на 50kk записей выполняется за 150~200мс?


Вячеслав,
автор
MODEL плохо в смысле конкуренции

это же чистый DDL, какая там конкуренция?
11 фев 17, 12:30    [20202885]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить историческую таблицу  [new]
Alibek B.
Member

Откуда:
Сообщений: 2373
K790
и действительно на 50kk записей выполняется за 150~200мс?

Все же не 50кк, а 5кк.
В таблице BM_ACTION_LOG менее 5 млн. записей, но по полям CUSTOMER_ID и ACTION_ID есть индексы, поэтому соединение выполняется быстро. На каждый CUSTOMER_ID в этой таблице записей нужного вида обычно немного (в среднем 2-3, максимум 8). В таблице CUSTOMERS более 10 тысяч записей, конечный запрос (соединение между CUSTOMERS и BM_ACTION_LOG) выполняется менее 200мс и возвращает не более 30к записей.
И MODEL на этом запросе действительно выполняется быстро, около 200мс.

Вообщем с MODEL пришлось некоторое время помучаться, но зато результат мне нравится больше, чем аналитические функции или использование присоединений с ODCINumberList.
11 фев 17, 12:59    [20202942]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить