ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Поиск комбинаций строк в таблице
15 сообщений из 40, страница 2 из 2
  2  все
Поиск комбинаций строк в таблице
    #39462573
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
rpovarov,

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

По числу отвечающих в этой ветке ты можешь сделать определенные выводы про умение просто подавать информацию и доступно объяснять.
...
Рейтинг: 0 / 0
30.05.2017, 17:31
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39462937
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
dbms_photoshopБыло бы намного понятнее, если бы ты в моем последнем запросе просто подставил данные на которых он возвращает не то, что ты ожидаешь.
Мне не совсем понятно что не так, хотя если твое решение тебя вполне устраивает, на этом можно и зарешить.


Прошу прощения, что не сразу отвечаю. Не успеваю иногда. Пытался разобраться, почему у меня на, казалось бы, одинаковых данных выходят разные результаты. Подготовлю входные данные, которые должны будут покрыть все спорные варианты, и вернусь.
...
Рейтинг: 0 / 0
31.05.2017, 11:03
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39471411
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
Возвращаюсь с новым решением. Основной проблемой было то, что при нескольких экземплярах одного и того же продукта нужно отделить эти экземпляры уникальным ключом так, чтобы в эту выделенную ветку попали также корневой продукт и остальные одиночные продукты (это важно для дальнейшего "опознания" комбинаций). Решил разделить super_tree из предыдущих попыток на два отдельных шага. В результате получается так:
1. Первый проход по дереву, строим product_path и unique_path, считаем количество повторений product_path.
2. Проход по результату из п.1 рекурсивным селектом, ищем повторяющиеся ветки. Как только наткнулись - вешаем на ветку идентификатор branch_id, и обозначаем им все остальные продукты ниже в этой ветке. Теоретически ниже может быть ещё одно разветвление с повтором (в реальных данных такого не встречается), оно игнорируется.
3. Для всех повторяющихся веток размножаем корневые и неповторяющиеся продукты.

Наверняка можно совместить шаг 1 и 2 в один, но я пока не придумал как.

 

Исходная таблица
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
with prods as (
  select 1  as prod_id,  null  as parent_id, 'ROOT1'    as prod_name from dual union
  select 2  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 3  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 4  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 5  as prod_id,  2     as parent_id, 'PROD121'  as prod_name from dual union
  select 6  as prod_id,  3     as parent_id, 'PROD121'  as prod_name from dual union
  select 7  as prod_id,  4     as parent_id, 'PROD121'  as prod_name from dual union
  select 50 as prod_id,  2     as parent_id, 'PROD122'  as prod_name from dual union
  select 60 as prod_id,  3     as parent_id, 'PROD122'  as prod_name from dual union
  select 70 as prod_id,  4     as parent_id, 'PROD123'  as prod_name from dual union
  select 8  as prod_id,  1     as parent_id, 'PROD2'    as prod_name from dual union
  select 9  as prod_id,  8     as parent_id, 'PROD22'   as prod_name from dual union
  select 10 as prod_id,  null  as parent_id, 'ROOT2'    as prod_name from dual union
  select 11 as prod_id,  10    as parent_id, 'PROD22'   as prod_name from dual 
)  
  select 
    prod_id, parent_id, 
    rpad(' ', (level-1)*2, ' ')||p.prod_name prod_name, 
    sys_connect_by_path(p.prod_name, '|') prod_path
  from prods p start with parent_id is null
  connect by prior prod_id = parent_id
  order siblings by prod_id;



После применения super_tree
Код: 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.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
with prods as (
  select 1  as prod_id,  null  as parent_id, 'ROOT1'    as prod_name from dual union
  select 2  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 3  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 4  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 5  as prod_id,  2     as parent_id, 'PROD121'  as prod_name from dual union
  select 6  as prod_id,  3     as parent_id, 'PROD121'  as prod_name from dual union
  select 7  as prod_id,  4     as parent_id, 'PROD121'  as prod_name from dual union
  select 50 as prod_id,  2     as parent_id, 'PROD122'  as prod_name from dual union
  select 60 as prod_id,  3     as parent_id, 'PROD122'  as prod_name from dual union
  select 70 as prod_id,  4     as parent_id, 'PROD123'  as prod_name from dual union
  select 8  as prod_id,  1     as parent_id, 'PROD2'    as prod_name from dual union
  select 9  as prod_id,  8     as parent_id, 'PROD22'   as prod_name from dual union
  select 10 as prod_id,  null  as parent_id, 'ROOT2'    as prod_name from dual union
  select 11 as prod_id,  10    as parent_id, 'PROD22'   as prod_name from dual 
),
product_tree as (
  select 
    prod_id, parent_id, prod_name,
    connect_by_root p.prod_id              as root_id,
    sys_connect_by_path(prod_name, '|')    as prod_path,
    sys_connect_by_path(prod_id, '|')      as unique_path,
    level                                  as lvl,
    count(*) over (partition by sys_connect_by_path(prod_name, '|')) as qty
  from prods p start with parent_id is null
  connect by prior prod_id = parent_id
),
product_tree_branch (prod_id, parent_id, root_id, prod_name, lvl, prod_path, qty, branch) as (
  select 
    prod_id, parent_id, root_id, prod_name, lvl, prod_path, qty, 'x' branch
  from product_tree
  where parent_id is null
  union all
  select 
    t.prod_id, t.parent_id, t.root_id, t.prod_name, t.lvl, t.prod_path, t.qty, 
    case when (p.branch = 'x' and t.qty > 1) then t.unique_path else p.branch end branch
  from product_tree t
  join product_tree_branch p on p.prod_id = t.parent_id
),
super_tree as (
  select br.branch as branch_id, t.* 
  from product_tree_branch t
  join (select distinct root_id, branch from product_tree_branch) br on t.root_id = br.root_id and t.branch in (br.branch, 'x')
)
select
  rpad(' ', (lvl-1)*2, ' ')||t.prod_name as pad,
  t.*
from 
  super_tree t
order by branch_id, prod_path;


...
Рейтинг: 0 / 0
14.06.2017, 11:48
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39471840
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
rpovarov,

А какую задачу ты сейчас решаешь?
Получить для каждого листа всех его родителей?

Тогда можно взять мой запрос тут 20524387 и добавить в него выделенное.

Код: 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.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
with prods as (
  select 1  as prod_id,  null  as parent_id, 'ROOT1'    as prod_name from dual union
  select 2  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 3  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 4  as prod_id,  1     as parent_id, 'PROD12'   as prod_name from dual union
  select 5  as prod_id,  2     as parent_id, 'PROD121'  as prod_name from dual union
  select 6  as prod_id,  3     as parent_id, 'PROD121'  as prod_name from dual union
  select 7  as prod_id,  4     as parent_id, 'PROD121'  as prod_name from dual union
  select 50 as prod_id,  2     as parent_id, 'PROD122'  as prod_name from dual union
  select 60 as prod_id,  3     as parent_id, 'PROD122'  as prod_name from dual union
  select 70 as prod_id,  4     as parent_id, 'PROD123'  as prod_name from dual union
  select 8  as prod_id,  1     as parent_id, 'PROD2'    as prod_name from dual union
  select 9  as prod_id,  8     as parent_id, 'PROD22'   as prod_name from dual union
  select 10 as prod_id,  null  as parent_id, 'ROOT2'    as prod_name from dual union
  select 11 as prod_id,  10    as parent_id, 'PROD22'   as prod_name from dual
),
product_tree as (
select
  prod_id,
  parent_id,
  connect_by_root p.prod_id                 as root_id,
  connect_by_root p.prod_name               as root_prod,
  p.prod_name                               as prod_name,
  rpad(' ', (level-1)*2, ' ')||p.prod_name  as prod_name_pad,
  sys_connect_by_path(p.prod_name, '|')     as prod_path,
  --
  sys_connect_by_path(p.prod_id, '|')||'|'  as unique_path,
  connect_by_isleaf                         as isleaf
from prods p
start with parent_id is null
connect by prior prod_id = parent_id),
super_tree as (
select pt1.prod_id leaf_id, pt2.*
  from product_tree pt1
  left join product_tree pt2
    on pt1.unique_path like pt2.unique_path || '%'
 where pt1.isleaf = 1)
select * from super_tree;


Семь листьев глубины три и один глубины два итого 7*3+1*2=23 строки. Это ожидается?
...
Рейтинг: 0 / 0
15.06.2017, 01:44
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472054
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
dbms_photoshoprpovarov,
А какую задачу ты сейчас решаешь?
Получить для каждого листа всех его родителей?
...
Тогда можно взять мой запрос тут 20524387 и добавить в него выделенное.
Семь листьев глубины три и один глубины два итого 7*3+1*2=23 строки. Это ожидается?
Если идти от листьев, то каждый лист генерирует свою собственную ветку, это неправильно. В последнем примере продукты PROD121 и PROD122 оба являются листьями и находятся в одной ветке на одинаковом уровне под общим родителем PROD12. Они так и должны остаться вместе в одной ветке.

Т.е. должно получиться не две:
Код: plaintext
1.
2.
3.
4.
5.
6.
ROOT1
  PROD12
    PROD121
ROOT1
  PROD12
    PROD122

а одна:
Код: plaintext
1.
2.
3.
4.
ROOT1
  PROD12
    PROD121
    PROD122

В примере три экземпляра продукта PROD12, они ближе всего к корню, значит с них и начинаем делить. В результате должно получиться три полные ветви для каждого из PROD12, и одна "дефолтная" для остальных продуктов, не попадающих в те три ветви.

Вот ещё один боевой пример прямо из базы (привязок нет, NDA не нарушается), в нём куча "листьев"
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
select '1-1WWDY9C' prod_id,  null parent_id, 'PR0103' prod_name from dual union
select '1-1WWDY9G' prod_id,  '1-1WWDY9C' parent_id, 'OP0800' prod_name from dual union
select '1-1WWDY9O' prod_id,  '1-1WWDY9C' parent_id, 'OP0807' prod_name from dual union
select '1-1WWDYA2' prod_id,  '1-1WWDY9O' parent_id, 'OP0801' prod_name from dual union
select '1-1WWDYAF' prod_id,  '1-1WWDYA2' parent_id, 'ZR0132' prod_name from dual union
select '1-1WWDYAN' prod_id,  '1-1WWDYA2' parent_id, 'OP0804' prod_name from dual union
select '1-1WWDYAT' prod_id,  '1-1WWDYAN' parent_id, 'OP0002' prod_name from dual union
select '1-986USNA' prod_id,  '1-1WWDYA2' parent_id, '174079' prod_name from dual union
select '1-EWBDD75' prod_id,  '1-1WWDYA2' parent_id, 'OP1223' prod_name from dual union
select '1-L7G9LAI' prod_id,  '1-1WWDYA2' parent_id, 'CPSZ0120' prod_name from dual union
select '1-L7G9LAM' prod_id,  '1-L7G9LAI' parent_id, 'CPSN0096' prod_name from dual union
select '1-1WWDYBG' prod_id,  '1-1WWDY9O' parent_id, 'OP0801' prod_name from dual union
select '1-1WWDYCB' prod_id,  '1-1WWDYBG' parent_id, 'ZR0132' prod_name from dual union
select '1-1WWDYCF' prod_id,  '1-1WWDYBG' parent_id, 'OP0804' prod_name from dual union
select '1-1WWDYCL' prod_id,  '1-1WWDYCF' parent_id, 'OP0002' prod_name from dual union
select '1-EWBDD79' prod_id,  '1-1WWDYBG' parent_id, 'OP1223' prod_name from dual union
select '1-IYJ8E7W' prod_id,  '1-1WWDYBG' parent_id, '174076' prod_name from dual union
select '1-L7G9LAQ' prod_id,  '1-1WWDYBG' parent_id, 'CPSZ0120' prod_name from dual union
select '1-L7G9LAU' prod_id,  '1-L7G9LAQ' parent_id, 'CPSN0096' prod_name from dual union
select '1-IYJ8E8E' prod_id,  '1-1WWDY9C' parent_id, 'OP1725' prod_name from dual



Если его подставить в твой пример, то листья сгенерируют 10+ веток, а их на самом деле всего три (две для продуктов OP0801, и одна общая).
...
Рейтинг: 0 / 0
15.06.2017, 11:26
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472098
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
rpovarovВ примереВроде мой последний запрос возвращает то же количество строк, что и твой последний.

Можно, конечно, допилить с учетом новых хотелок, но есть подозрение, что ты уже пытаешься реализовать то, что делать не надо.

В конце концов, тебе виднее, что за логику надо реализовать. Если будут конкретные вопросы - спрашивай.
...
Рейтинг: 0 / 0
15.06.2017, 11:53
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472152
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
dbms_photoshopВроде мой последний запрос возвращает то же количество строк, что и твой последний.

Если подставить "боевой", то количество строк разное, в твоём больше (48 против 28). Но количество строк в принципе не так важно, основная проблема в том, что ветки разбиваются и потом не совпадают с map-таблицей.

dbms_photoshopМожно, конечно, допилить с учетом новых хотелок, но есть подозрение, что ты уже пытаешься реализовать то, что делать не надо.
В конце концов, тебе виднее, что за логику надо реализовать. Если будут конкретные вопросы - спрашивай.
Последняя версия кода вроде делает всё что нужно. По крайней мере, пока ещё не наткнулся на вариант, где она лажает. Благодаря твоим вариантам и моим попыткам их доработать нашлись и были исправлены другие местные косяки в данных и map-таблицах :)

Вопрос, собственно, такой - можно ли как-то совместить эти две конструкции в одну (и есть ли в этом смысл с точки зрения оптимизации)? В первой считаются пути и их количество для выявления нескольких экземпляров одного продукта, во второй на основании этого количества генерируется параметр branch.
Код: 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.
...
product_tree as (
  select 
    prod_id, parent_id, prod_name,
    connect_by_root p.prod_id              as root_id,
    sys_connect_by_path(prod_name, '|')    as prod_path,
    sys_connect_by_path(prod_id, '|')      as unique_path,
    level                                  as lvl,
    count(*) over (partition by sys_connect_by_path(prod_name, '|')) as qty
  from prods p start with parent_id is null
  connect by prior prod_id = parent_id
),
product_tree_branch (prod_id, parent_id, root_id, prod_name, lvl, prod_path, qty, branch) as (
  select 
    prod_id, parent_id, root_id, prod_name, lvl, prod_path, qty, 'x' branch
  from product_tree
  where parent_id is null
  union all
  select 
    t.prod_id, t.parent_id, t.root_id, t.prod_name, t.lvl, t.prod_path, t.qty, 
    case when (p.branch = 'x' and t.qty > 1) then t.unique_path else p.branch end branch
  from product_tree t
  join product_tree_branch p on p.prod_id = t.parent_id
),
...



И ещё один вопрос, который тут не обсуждался, но связан с темой. В части, выделенной красным цветом - я правильно надеюсь на short-circuit evaluation, и при выполнении условия pm.param_key is null Оракл не пойдёт дальше и не будет выполнять селект из второго условия?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
                          select pt.*,
                               pm.id, pm.type, pm.target_product_code, pm.target_entity_type, pm.duplication_group, pm.duplication_depth, pm.cnt, pm.position,
                               count(pm.prod_path) over(partition by pt.root_row_id, pt.branch_id, pm.id) cnt_root_map
                          from super_tree pt
                          left join V_MAP_FIX_MIGRATION_TREE pm
                            on  pt.prod_path = pm.prod_path 
                            and (
                                  -- non-parametrized product
                                  ( pm.param_key is null ) 
                                  OR 
                                  -- parametrized product, check asset attribute
                                  ( pm.param_value is not null 
                                    and pm.param_value = 
                                    ( select /*+ index(pr) */ char_val from L0_CLD_FIX_PROD_PARAMS pr where asset_id = pt.row_id and attr_name = pm.param_key ) 
                                  ) 
                                )
...
Рейтинг: 0 / 0
15.06.2017, 12:28
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472213
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
rpovarovВопрос, собственно, такой - можно ли как-то совместить эти две конструкции в однуОтвет: rec with там вообще не нужен.
rpovarovЕсли идти от листьев, то каждый лист генерирует свою собственную ветку, это неправильно. В последнем примере продукты PROD121 и PROD122 оба являются листьями и находятся в одной ветке на одинаковом уровне под общим родителем PROD12. Они так и должны остаться вместе в одной ветке.Что значит на одной "ветке"?
Имеется в виду пути до листьев полностью совпадают?
Ну так это поведение по умолчанию connect by выведет всех родителей по разу и каждый лист по разу и ты получаешь.
rpovarov
Код: plaintext
1.
2.
3.
4.
ROOT1
  PROD12
    PROD121
    PROD122
Непонятно уже надо тебе что-то размножать или нет и если надо, то при каких условиях.
Когда ты это ясно сможешь для себя сформулировать, тогда и запрос сам собой напишется.
rpovarovВопрос, собственно, такой - можно ли как-то совместить эти две конструкции в однуЛюбители абсолютной строгости рекомендуют прибегать к case. 20384658
...
Рейтинг: 0 / 0
15.06.2017, 13:07
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472222
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
В последней цитате должен быть второй вопрос.
...
Рейтинг: 0 / 0
15.06.2017, 13:16
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472229
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
dbms_photoshopНепонятно уже надо тебе что-то размножать или нет и если надо, то при каких условиях.
Когда ты это ясно сможешь для себя сформулировать, тогда и запрос сам собой напишется.
Ну я же привёл рабочий пример в 20563070 . Этот код делает сейчас именно то, что нужно. В случае нескольких экземпляров одного и того же продукта генерируются полные ветки для них и обозначаются идентификатором branch_id.
...
Рейтинг: 0 / 0
15.06.2017, 13:22
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472292
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
rpovarov,

Ммм... то, что делает твой запрос понятно только тебе.
Хотя, подозреваю, если ты посмотришь на него через год и тебе будет вообще непонятно что это за 'x' и что курил аффтар.

Код: plaintext
1.
2.
select prod_id, parent_id, prod_path, branch_id
from ...
order by prod_path, prod_id, parent_id, branch_id

Код: plaintext
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.
   PROD_ID  PARENT_ID PROD_PATH                      BRANCH_ID 
---------- ---------- ------------------------------ ----------
         1            |ROOT1                         x         
         1            |ROOT1                         |1|2      
         1            |ROOT1                         |1|3      
         1            |ROOT1                         |1|4      
         2          1 |ROOT1|PROD12                  |1|2      
         3          1 |ROOT1|PROD12                  |1|3      
         4          1 |ROOT1|PROD12                  |1|4      
         5          2 |ROOT1|PROD12|PROD121          |1|2      
         6          3 |ROOT1|PROD12|PROD121          |1|3      
         7          4 |ROOT1|PROD12|PROD121          |1|4      
        50          2 |ROOT1|PROD12|PROD122          |1|2      
        60          3 |ROOT1|PROD12|PROD122          |1|3      
        70          4 |ROOT1|PROD12|PROD123          |1|4      
         8          1 |ROOT1|PROD2                   x         
         8          1 |ROOT1|PROD2                   |1|2      
         8          1 |ROOT1|PROD2                   |1|3      
         8          1 |ROOT1|PROD2                   |1|4      
         9          8 |ROOT1|PROD2|PROD22            x         
         9          8 |ROOT1|PROD2|PROD22            |1|2      
         9          8 |ROOT1|PROD2|PROD22            |1|3      
         9          8 |ROOT1|PROD2|PROD22            |1|4      
        10            |ROOT2                         x         
        11         10 |ROOT2|PROD22                  x         

23 rows selected.

Зачем продукт с ид 7 и 8 продублированы по 4 раза?
Почему продукт с ид 1 тоже продублирован именно 4 раза?
Почему ветку "идентифицируют" два айдишника независимо от глубины.
и т. д.
...
Рейтинг: 0 / 0
15.06.2017, 14:12
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472325
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
dbms_photoshop,

Если отсортировать по другому, то так понятнее.

Код: plaintext
PADBRANCH_IDPROD_IDPROD_PATHROOT1x1|ROOT1 PROD2x8|ROOT1|PROD2 PROD22x9|ROOT1|PROD2|PROD22ROOT2x10|ROOT2 PROD22x11|ROOT2|PROD22ROOT1|1|21|ROOT1 PROD12|1|22|ROOT1|PROD12 PROD121|1|25|ROOT1|PROD12|PROD121 PROD122|1|250|ROOT1|PROD12|PROD122 PROD2|1|28|ROOT1|PROD2 PROD22|1|29|ROOT1|PROD2|PROD22ROOT1|1|31|ROOT1 PROD12|1|33|ROOT1|PROD12 PROD121|1|36|ROOT1|PROD12|PROD121 PROD122|1|360|ROOT1|PROD12|PROD122 PROD2|1|38|ROOT1|PROD2 PROD22|1|39|ROOT1|PROD2|PROD22ROOT1|1|41|ROOT1 PROD12|1|44|ROOT1|PROD12 PROD121|1|47|ROOT1|PROD12|PROD121 PROD123|1|470|ROOT1|PROD12|PROD123 PROD2|1|48|ROOT1|PROD2 PROD22|1|49|ROOT1|PROD2|PROD22
Продукты 8 (PROD2) и 9 (PROD22) не принадлежали веткам с multi-instance продуктами, но они могут быть важны для идентификации службы. Например, если служба описана как комбинация продуктов ROOT1, PROD12, PROD121, PROD122, PROD2, то, не включив PROD2, я теряю всю службу.

Дупликаты продуктов не мешают, они отфильтровываются на конечном этапе.

Ветка идентифицируется через unique_path продукта с multi-instance, ближайшего к корню. В примере это PROD12, он на втором уровне, поэтому branch_id состоит из двух элементов. Если разветвление будет на третьем уровне, то branch_id будет из трёх элементов ("боевой" пример как раз такой).
...
Рейтинг: 0 / 0
15.06.2017, 14:39
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472328
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
dbms_photoshopХотя, подозреваю, если ты посмотришь на него через год и тебе будет вообще непонятно что это за 'x' и что курил аффтар.


Борюсь с этим явлением щедро рассыпая комментарии и пояснения в коде :)
...
Рейтинг: 0 / 0
15.06.2017, 14:42
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472528
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
rpovarov,

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

Учитывая сказанное "размножение" опять таки достигается путем self join только условие соединения более замысловатое.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select pt1.prod_name_pad pad,
       pt2.unique_path branch,
       pt1.prod_id,
       pt1.prod_path
  -- for lvl 2 branches
  from (select * from product_tree where lvl = 2) pt2
  join product_tree pt1
       -- within the same root
    on pt1.root_id = pt2.root_id and
       (
       -- replicate children
       pt1.unique_path like pt2.unique_path || '%'
       -- replicate roots
       or pt1.lvl = 1
       -- replicate nodes from other bracnges when following conditions are met
       or (pt1.cnt = 1 and pt2.cnt <> 1 and not pt1.prod_path like pt2.prod_path || '%')
       )
order by pt2.unique_path, pt1.prod_path;



Код: plaintext
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.
PAD                  BRANCH        PROD_ID PROD_PATH                     
-------------------- ---------- ---------- ------------------------------
ROOT2                |10|11|            10 |ROOT2                        
  PROD22             |10|11|            11 |ROOT2|PROD22                 
ROOT1                |1|2|               1 |ROOT1                        
  PROD12             |1|2|               2 |ROOT1|PROD12                 
    PROD121          |1|2|               5 |ROOT1|PROD12|PROD121         
    PROD122          |1|2|              50 |ROOT1|PROD12|PROD122         
  PROD2              |1|2|               8 |ROOT1|PROD2                  
    PROD22           |1|2|               9 |ROOT1|PROD2|PROD22           
ROOT1                |1|3|               1 |ROOT1                        
  PROD12             |1|3|               3 |ROOT1|PROD12                 
    PROD121          |1|3|               6 |ROOT1|PROD12|PROD121         
    PROD122          |1|3|              60 |ROOT1|PROD12|PROD122         
  PROD2              |1|3|               8 |ROOT1|PROD2                  
    PROD22           |1|3|               9 |ROOT1|PROD2|PROD22           
ROOT1                |1|4|               1 |ROOT1                        
  PROD12             |1|4|               4 |ROOT1|PROD12                 
    PROD121          |1|4|               7 |ROOT1|PROD12|PROD121         
    PROD123          |1|4|              70 |ROOT1|PROD12|PROD123         
  PROD2              |1|4|               8 |ROOT1|PROD2                  
    PROD22           |1|4|               9 |ROOT1|PROD2|PROD22           
ROOT1                |1|8|               1 |ROOT1                        
  PROD2              |1|8|               8 |ROOT1|PROD2                  
    PROD22           |1|8|               9 |ROOT1|PROD2|PROD22           

23 rows selected.
...
Рейтинг: 0 / 0
15.06.2017, 17:20
| Ответить | Цитировать | Написать  
Поиск комбинаций строк в таблице
    #39472554
rpovarov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
rpovarov
Гость
dbms_photoshoprpovarov,
У тебя логика завязана на второй уровень и не надо создавать иллюзии, что размножение будет корректным при любой ветвистости и дубликатных путях.


У меня как раз уровень вычисляется динамически, берётся первый, где нашлись дубликаты. Для этого и делался второй проход with rec. Например, вот ветвление на третьем (|PR0103|OP0807|OP0801):
Код: 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.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
with prods as (
select '1-1WWDY9C' prod_id,  null parent_id, 'PR0103' prod_name from dual union
select '1-1WWDY9G' prod_id,  '1-1WWDY9C' parent_id, 'OP0800' prod_name from dual union
select '1-1WWDY9O' prod_id,  '1-1WWDY9C' parent_id, 'OP0807' prod_name from dual union
select '1-1WWDYA2' prod_id,  '1-1WWDY9O' parent_id, 'OP0801' prod_name from dual union
select '1-1WWDYAF' prod_id,  '1-1WWDYA2' parent_id, 'ZR0132' prod_name from dual union
select '1-1WWDYAN' prod_id,  '1-1WWDYA2' parent_id, 'OP0804' prod_name from dual union
select '1-1WWDYAT' prod_id,  '1-1WWDYAN' parent_id, 'OP0002' prod_name from dual union
select '1-986USNA' prod_id,  '1-1WWDYA2' parent_id, '174079' prod_name from dual union
select '1-EWBDD75' prod_id,  '1-1WWDYA2' parent_id, 'OP1223' prod_name from dual union
select '1-L7G9LAI' prod_id,  '1-1WWDYA2' parent_id, 'CPSZ0120' prod_name from dual union
select '1-L7G9LAM' prod_id,  '1-L7G9LAI' parent_id, 'CPSN0096' prod_name from dual union
select '1-1WWDYBG' prod_id,  '1-1WWDY9O' parent_id, 'OP0801' prod_name from dual union
select '1-1WWDYCB' prod_id,  '1-1WWDYBG' parent_id, 'ZR0132' prod_name from dual union
select '1-1WWDYCF' prod_id,  '1-1WWDYBG' parent_id, 'OP0804' prod_name from dual union
select '1-1WWDYCL' prod_id,  '1-1WWDYCF' parent_id, 'OP0002' prod_name from dual union
select '1-EWBDD79' prod_id,  '1-1WWDYBG' parent_id, 'OP1223' prod_name from dual union
select '1-IYJ8E7W' prod_id,  '1-1WWDYBG' parent_id, '174076' prod_name from dual union
select '1-L7G9LAQ' prod_id,  '1-1WWDYBG' parent_id, 'CPSZ0120' prod_name from dual union
select '1-L7G9LAU' prod_id,  '1-L7G9LAQ' parent_id, 'CPSN0096' prod_name from dual union
select '1-IYJ8E8E' prod_id,  '1-1WWDY9C' parent_id, 'OP1725' prod_name from dual 
),
product_tree as (
  select 
    prod_id, parent_id, prod_name,
    connect_by_root p.prod_id              as root_id,
    sys_connect_by_path(prod_name, '|')    as prod_path,
    sys_connect_by_path(prod_id, '|')      as unique_path,
    level                                  as lvl,
    count(*) over (partition by sys_connect_by_path(prod_name, '|')) as qty
  from prods p start with parent_id is null
  connect by prior prod_id = parent_id
),
product_tree_branch (prod_id, parent_id, root_id, prod_name, lvl, prod_path, qty, branch) as (
  select 
    prod_id, parent_id, root_id, prod_name, lvl, prod_path, qty, 'x' branch
  from product_tree
  where parent_id is null
  union all
  select 
    t.prod_id, t.parent_id, t.root_id, t.prod_name, t.lvl, t.prod_path, t.qty, 
    case when (p.branch = 'x' and t.qty > 1) then t.unique_path else p.branch end branch
  from product_tree t
  join product_tree_branch p on p.prod_id = t.parent_id
),
super_tree as (
  select br.branch as branch_id, t.* 
  from product_tree_branch t
  join (select distinct root_id, branch from product_tree_branch) br on t.root_id = br.root_id and t.branch in (br.branch, 'x')
)
select
  rpad(' ', (lvl-1)*2, ' ')||t.prod_name as pad,
  branch_id, prod_id, prod_path
from 
  super_tree t
order by branch_id, prod_path;


Код: plaintext
PADBRANCH_IDPROD_IDPROD_PATHPR0103x1-1WWDY9C|PR0103 OP0800x1-1WWDY9G|PR0103|OP0800 OP0807x1-1WWDY9O|PR0103|OP0807 OP1725x1-IYJ8E8E|PR0103|OP1725PR0103|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-1WWDY9C|PR0103 OP0800|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-1WWDY9G|PR0103|OP0800 OP0807|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-1WWDY9O|PR0103|OP0807 OP0801|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-1WWDYA2|PR0103|OP0807|OP0801 CPSZ0120|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-L7G9LAI|PR0103|OP0807|OP0801|CPSZ0120 CPSN0096|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-L7G9LAM|PR0103|OP0807|OP0801|CPSZ0120|CPSN0096 OP0804|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-1WWDYAN|PR0103|OP0807|OP0801|OP0804 OP0002|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-1WWDYAT|PR0103|OP0807|OP0801|OP0804|OP0002 OP1223|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-EWBDD75|PR0103|OP0807|OP0801|OP1223 ZR0132|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-1WWDYAF|PR0103|OP0807|OP0801|ZR0132 174079|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-986USNA|PR0103|OP0807|OP0801|174079 OP1725|1-1WWDY9C|1-1WWDY9O|1-1WWDYA21-IYJ8E8E|PR0103|OP1725PR0103|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-1WWDY9C|PR0103 OP0800|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-1WWDY9G|PR0103|OP0800 OP0807|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-1WWDY9O|PR0103|OP0807 OP0801|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-1WWDYBG|PR0103|OP0807|OP0801 CPSZ0120|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-L7G9LAQ|PR0103|OP0807|OP0801|CPSZ0120 CPSN0096|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-L7G9LAU|PR0103|OP0807|OP0801|CPSZ0120|CPSN0096 OP0804|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-1WWDYCF|PR0103|OP0807|OP0801|OP0804 OP0002|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-1WWDYCL|PR0103|OP0807|OP0801|OP0804|OP0002 OP1223|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-EWBDD79|PR0103|OP0807|OP0801|OP1223 ZR0132|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-1WWDYCB|PR0103|OP0807|OP0801|ZR0132 174076|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-IYJ8E7W|PR0103|OP0807|OP0801|174076 OP1725|1-1WWDY9C|1-1WWDY9O|1-1WWDYBG1-IYJ8E8E|PR0103|OP1725

Можно даже оба набора исходных данных (со вторым и третьим уровнем) слить воедино, и скрипт правильно отработает:
 
Код: 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.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
with prods as (
select '1-1WWDY9C' prod_id,  null parent_id, 'PR0103' prod_name from dual union
select '1-1WWDY9G' prod_id,  '1-1WWDY9C' parent_id, 'OP0800' prod_name from dual union
select '1-1WWDY9O' prod_id,  '1-1WWDY9C' parent_id, 'OP0807' prod_name from dual union
select '1-1WWDYA2' prod_id,  '1-1WWDY9O' parent_id, 'OP0801' prod_name from dual union
select '1-1WWDYAF' prod_id,  '1-1WWDYA2' parent_id, 'ZR0132' prod_name from dual union
select '1-1WWDYAN' prod_id,  '1-1WWDYA2' parent_id, 'OP0804' prod_name from dual union
select '1-1WWDYAT' prod_id,  '1-1WWDYAN' parent_id, 'OP0002' prod_name from dual union
select '1-986USNA' prod_id,  '1-1WWDYA2' parent_id, '174079' prod_name from dual union
select '1-EWBDD75' prod_id,  '1-1WWDYA2' parent_id, 'OP1223' prod_name from dual union
select '1-L7G9LAI' prod_id,  '1-1WWDYA2' parent_id, 'CPSZ0120' prod_name from dual union
select '1-L7G9LAM' prod_id,  '1-L7G9LAI' parent_id, 'CPSN0096' prod_name from dual union
select '1-1WWDYBG' prod_id,  '1-1WWDY9O' parent_id, 'OP0801' prod_name from dual union
select '1-1WWDYCB' prod_id,  '1-1WWDYBG' parent_id, 'ZR0132' prod_name from dual union
select '1-1WWDYCF' prod_id,  '1-1WWDYBG' parent_id, 'OP0804' prod_name from dual union
select '1-1WWDYCL' prod_id,  '1-1WWDYCF' parent_id, 'OP0002' prod_name from dual union
select '1-EWBDD79' prod_id,  '1-1WWDYBG' parent_id, 'OP1223' prod_name from dual union
select '1-IYJ8E7W' prod_id,  '1-1WWDYBG' parent_id, '174076' prod_name from dual union
select '1-L7G9LAQ' prod_id,  '1-1WWDYBG' parent_id, 'CPSZ0120' prod_name from dual union
select '1-L7G9LAU' prod_id,  '1-L7G9LAQ' parent_id, 'CPSN0096' prod_name from dual union
select '1-IYJ8E8E' prod_id,  '1-1WWDY9C' parent_id, 'OP1725' prod_name from dual union
  select '1'  as prod_id,  null    as parent_id, 'ROOT1'    as prod_name from dual union
  select '2'  as prod_id,  '1'     as parent_id, 'PROD12'   as prod_name from dual union
  select '3'  as prod_id,  '1'     as parent_id, 'PROD12'   as prod_name from dual union
  select '4'  as prod_id,  '1'     as parent_id, 'PROD12'   as prod_name from dual union
  select '5'  as prod_id,  '2'     as parent_id, 'PROD121'  as prod_name from dual union
  select '6'  as prod_id,  '3'     as parent_id, 'PROD121'  as prod_name from dual union
  select '7'  as prod_id,  '4'     as parent_id, 'PROD121'  as prod_name from dual union
  select '50' as prod_id,  '2'     as parent_id, 'PROD122'  as prod_name from dual union
  select '60' as prod_id,  '3'     as parent_id, 'PROD122'  as prod_name from dual union
  select '70' as prod_id,  '4'     as parent_id, 'PROD123'  as prod_name from dual union
  select '8'  as prod_id,  '1'     as parent_id, 'PROD2'    as prod_name from dual union
  select '9'  as prod_id,  '8'     as parent_id, 'PROD22'   as prod_name from dual union
  select '10' as prod_id,  null    as parent_id, 'ROOT2'    as prod_name from dual union
  select '11' as prod_id,  '10'    as parent_id, 'PROD22'   as prod_name from dual
),
product_tree as (
  select 
    prod_id, parent_id, prod_name,
    connect_by_root p.prod_id              as root_id,
    sys_connect_by_path(prod_name, '|')    as prod_path,
    sys_connect_by_path(prod_id, '|')      as unique_path,
    level                                  as lvl,
    count(*) over (partition by sys_connect_by_path(prod_name, '|')) as qty
  from prods p start with parent_id is null
  connect by prior prod_id = parent_id
),
product_tree_branch (prod_id, parent_id, root_id, prod_name, lvl, prod_path, qty, branch) as (
  select 
    prod_id, parent_id, root_id, prod_name, lvl, prod_path, qty, 'x' branch
  from product_tree
  where parent_id is null
  union all
  select 
    t.prod_id, t.parent_id, t.root_id, t.prod_name, t.lvl, t.prod_path, t.qty, 
    case when (p.branch = 'x' and t.qty > 1) then t.unique_path else p.branch end branch
  from product_tree t
  join product_tree_branch p on p.prod_id = t.parent_id
),
super_tree as (
  select br.branch as branch_id, t.* 
  from product_tree_branch t
  join (select distinct root_id, branch from product_tree_branch) br on t.root_id = br.root_id and t.branch in (br.branch, 'x')
)
select
  rpad(' ', (lvl-1)*2, ' ')||t.prod_name as prod_name_pad,
  branch_id, prod_id, prod_path
from 
  super_tree t
order by branch_id, prod_path;

...
Рейтинг: 0 / 0
15.06.2017, 17:41
| Ответить | Цитировать | Написать  
15 сообщений из 40, страница 2 из 2
  2  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Поиск комбинаций строк в таблице
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39472229&tid=1885749]:
 0ms
get settings:
 6ms
get forum list:
 22ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 166ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 32ms
get tp. blocked users:
 1ms
others: 215ms
total:  453ms
созд. / загр.: 453ms
x
x
Закрыть


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