Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вторничная задача: Зри в корень. / 12 сообщений из 12, страница 1 из 1
17.12.2019, 00:36
    #39903785
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
Возник академический интерес.

Задача: при построении дерева для строк с пустым значением подставить ближайшее непустое значение родителя.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
create table t(name, parent, value) as
(select 'A', null,  100 from dual
union all select 'B', 'A', 50 from dual
union all select 'Y', 'D', 20 from dual
union all select '1', 'B', 1 from dual
union all select '2', '1', 10 from dual
union all select 'C', 'B', null from dual
union all select 'D', 'A', 30 from dual
union all select 'E', 'C', 11 from dual
union all select 'X', 'C', null from dual);


Наиболее очевидный подход - отрезать последнее значение в sys_connect_by_path или пройтись деревяшкой вверх.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
select t.*,
       level l,
       regexp_substr(rtrim(sys_connect_by_path(value, '#'), '#'), '\d+$') result,
       (select value
          from t t0
         where connect_by_isleaf = 1
        start with t.name = t0.name
        connect by prior parent = name and prior value is null) result0
  from t
start with name = 'A'
connect by prior name = parent;

N P      VALUE          L RESULT        RESULT0
- - ---------- ---------- ---------- ----------
A          100          1 100               100
B A         50          2 50                 50
1 B          1          3 1                   1
2 1         10          4 10                 10
C B                     3 50                 50
E C         11          4 11                 11
X C                     4 50                 50
D A         30          2 30                 30
Y D         20          3 20                 20

9 rows selected.

Можно извернуться и получить скаляром через вложенные агрегаты.
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with tree as
(select t.*,
        level l,
        rownum rn
   from t
 start with name = 'A'
 connect by prior name = parent)
select t1.*,
       -- inner aggregate returns all rows for a given branch up to the root 
       -- outer aggregate returns not null value nearest to the current one
       (select max(max(t2.value) keep (dense_rank first order by t2.rn desc)) keep (dense_rank first order by t2.l desc)
          from tree t2
         where t2.l <= t1.l and t2.rn <= t1.rn
         group by l
        having max(t2.value) keep (dense_rank first order by t2.rn desc) is not null) result
  from tree t1;

Или через pattern matching.
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
with tree as
(select t.*,
        level l,
        rownum rn
   from t
 start with name = 'A'
 connect by prior name = parent)
select * from tree 
match_recognize
(
  order by rn desc
  measures 
    first(name) as name, first(parent) as parent,
    first(value) as value, first(l) as l, first(rn) as rn,
    last(value) result  
  after match skip to next row -- for each row do below
  pattern((up|dummy)+)
  define
     -- mark first row in the match and rows in the same branch towards root as "up"
     -- until parent with not null value is found 
     up as (last(up.l,1) > l and last(up.value,1) is null) or last(up.l,1) is null,
     -- mark rows from other branches towards root as "dummy"
     -- it's used in order to keep matching until parent with not null value is found 
    dummy as last(up.value) is null
) mr
order by rn;

Или модель и проход вверх по ветке с помощью sys_connect_by_path.
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with tree as
(select t.*,
        level l,
        rownum rn,
        sys_connect_by_path(value, '#') path
   from t
 start with name = 'A'
 connect by prior name = parent)
select * from tree
model
dimension by (path)
measures (name, parent, value, l, rn, 0 result)
(
  result[any] = max(value) keep (dense_rank last order by nvl2(value,l,0))[cv(path) like path || '%']  
)
order by rn;


Что интересует - можно ли придумать хитрое окно для аналитики или адресацию для модели без sys_connect_by_path.

PS. rec with интереса не представляет в виду полной тривиальности.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 07:24
    #39903820
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
Кобанчег
Наиболее очевидный подход - отрезать последнее значение в sys_connect_by_path или пройтись деревяшкой вверх.
Для этого не обязательно было начинать с похода вниз. Расскаляренный скалярный подзапрос уже решение.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 07:38
    #39903821
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
Кобанчег
Наиболее очевидный подход
- рекурсивный запрос.
Плохо то, что ты его не вспомнил. Или не знал?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 13:06
    #39904043
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
-2-
Кобанчег
Наиболее очевидный подход - отрезать последнее значение в sys_connect_by_path или пройтись деревяшкой вверх.
Для этого не обязательно было начинать с похода вниз. Расскаляренный скалярный подзапрос уже решение.
Можно поподобнее?

Продемонстрированный скаляр может определить всех родителей поскольку есть (1) уровень и (2) порядок после постоения дерева.

На всякий случай стоит уточнить для остальных, особой практической пользы он не представляет
и был приведен просто как один из изысков в процессе поиска трюка.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 13:49
    #39904086
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
-2-Расскаляренный скалярный подзапрос уже решение.В том случае если известно что узлы дерева а что нет.
Кроме того, смотреть вверх явно затратнее чем просто идти вниз.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 13:58
    #39904090
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
Кобанчег
смотреть вверх явно затратнее чем просто идти вниз.
Твой запрос и так смотрит вверх для каждой записи. От того, что убрать надзапрос и start with в подзапросе, затратнее не станет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 14:06
    #39904094
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
Если это не очевидно, то предпочтительные решения либо дервяшка + sys_connect_by_path либо rec with в зависимости от деталей/ограничений.
Остальное приведено для полноты картины.

Цель была найти возможность натянуть модель или аналитику на спуск вниз.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 14:29
    #39904109
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
Кобанчег
Цель была найти возможность натянуть модель или аналитику на спуск вниз.


Не получится задать окно - синтакс позволяет только RANGE BETWEEN CURRENT ROW AND N following. A N не получится квантифицировать. Вот если бы было RANGE LIKE ...

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 14:33
    #39904111
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
Или если бы было что-то типа WINDOW_START(expression).

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 19:22
    #39904330
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
SY
Не получится задать окно - синтакс позволяет только RANGE BETWEEN CURRENT ROW AND N following. A N не получится квантифицировать.
По какой колонке или выражению предполагается такой range?

Я в решениях с pattern matching и scalar + nested aggreagates использовал одну и ту же идею.
Для каждого узла делаем: для всех уровней от текущего до корня получаем первый по порядку убывания rn узел и среди полученных берем первый непустой.

Еще моделью это можно продемонстировать так:
Для каждого узла получаем вспомогательный столбец с порядком, потом смотрим последний ненулевой среди первых по порядку.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
with tree as
(select t.*,
        level l,
        rownum rn
   from t
 start with name = 'A'
 connect by prior name = parent)
select * from tree
model
dimension by (rn, l)
measures (name, parent, value, rn ord, 0 tmp, 0 result, 0 i)
rules iterate (9)
(
  tmp[rn <= iteration_number + 1, any] = row_number() over (partition by l order by rn desc),
  result[iteration_number + 1, any] = max(value) keep (dense_rank last order by nvl2(value*decode(tmp,1,1),ord,0))
                                      [rn <= iteration_number + 1, l <= cv(l)],
  i[1, any] = iteration_number
)
order by rn;

Можно и поизящнее сформулировать, но от итераций уйти не удаётся следовательно непрактично.

SY
Вот если бы было RANGE LIKE
А like по какому полю? По path как в модели из стартового поста?

SY
Или если бы было что-то типа WINDOW_START(expression).
Можно развернуть мысль?
В конце концов аналитика явно более ограничена чем модель:
- в аналитике мы можем указываеть rows/range только при сортировке по одному полю
- в модели мы можем выдумывать хитрые условия для срезов для агрегатов по нескольким измерениям (условно agg() [range1, range2, ...])
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 19:31
    #39904333
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
Как конкретный пример, для строки выделенным красным, в окно (или срез) должна попасть она и все выделенные желтым.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
N P      VALUE          L
- - ---------- ----------
A          100          1
B A         50          2
1 B          1          3
2 1         10          4
C B                     3
E C         11          4
X C                     4
D A         30          2
Y D         20          3

В pattern matching это делается тривиально - спустившись на уровень ниже мы рассматриваем только первый по порядку убывания узел на этом уровне.

Еще была мысль предварительно посчитать некоторое выражение.
Вот как здесь для конкретного родителя охватываем всех потомков 3873927 .
Так же предполагалось охватить для конкретного узла всех предков.

Но пока фантазия иссякла.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.02.2020, 20:25
    #39929174
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вторничная задача: Зри в корень.
SY
Вот если бы было RANGE LIKE
Насколько я понял в стандарте уже описано.
R020
Можем в каком-ниубдь Oracle 20 Release 2 появится. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вторничная задача: Зри в корень. / 12 сообщений из 12, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 168ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1881537]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 52ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 49ms
get tp. blocked users:
 2ms
others: 17ms
total:  168ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]