ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вторничная задача: Зри в корень.
12 сообщений из 12, страница 1 из 1
Вторничная задача: Зри в корень.
    #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, 00:36
| Ответить | Цитировать | Написать  
Вторничная задача: Зри в корень.
    #39903820
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Кобанчег
Наиболее очевидный подход - отрезать последнее значение в sys_connect_by_path или пройтись деревяшкой вверх.
Для этого не обязательно было начинать с похода вниз. Расскаляренный скалярный подзапрос уже решение.
...
Рейтинг: 0 / 0
17.12.2019, 07:24
| Ответить | Цитировать | Написать  
Вторничная задача: Зри в корень.
    #39903821
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Кобанчег
Наиболее очевидный подход
- рекурсивный запрос.
Плохо то, что ты его не вспомнил. Или не знал?
...
Рейтинг: 0 / 0
17.12.2019, 07:38
| Ответить | Цитировать | Написать  
Вторничная задача: Зри в корень.
    #39904043
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
-2-
Кобанчег
Наиболее очевидный подход - отрезать последнее значение в sys_connect_by_path или пройтись деревяшкой вверх.
Для этого не обязательно было начинать с похода вниз. Расскаляренный скалярный подзапрос уже решение.
Можно поподобнее?

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

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

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


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

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

SY.
...
Рейтинг: 0 / 0
17.12.2019, 14:33
| Ответить | Цитировать | Написать  
Вторничная задача: Зри в корень.
    #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:22
| Ответить | Цитировать | Написать  
Вторничная задача: Зри в корень.
    #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
17.12.2019, 19:31
| Ответить | Цитировать | Написать  
Вторничная задача: Зри в корень.
    #39929174
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
SY
Вот если бы было RANGE LIKE
Насколько я понял в стандарте уже описано.
R020
Можем в каком-ниубдь Oracle 20 Release 2 появится. :)
...
Рейтинг: 0 / 0
20.02.2020, 20:25
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вторничная задача: Зри в корень.
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39904090&tid=1881537]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 41ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 18ms
total:  155ms
созд. / загр.: 155ms
x
x
Закрыть


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