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

Откуда: зачем Вам?
Сообщений: 253
Была задачка:
Как найти Service 1го предыдущего элемента в ктором Тyp= 'CC'.
Нужно двигатся к первому элементу дерева. Если попадается элемент с Тyp= 'CC', берется его service и прекращаем поиск. И так для каждого элемента.
Коллега SY предложил следующее правильное решениe



with tab#(
          id,
          parents,
          childs,
          starttime,
          service,
          typ,
          service_extern
         ) as(
             select 1000,1,NULL,date '2015-01-01','BAHNCARD_1','CC',NULL from dual union all
             select 1001,2,1,date '2015-02-01','BAHNCARD_2','CC',NULL from dual union all
             select 1002,3,2,date '2015-03-01','BAHNCARD_3','FE',NULL from dual union all
             select 1003,4,2,date '2015-04-01','BAHNCARD_4','CC',NULL from dual union all
             select 1004,5,4,date '2015-05-01','BAHNCARD_5','FE',NULL from dual union all
             ----------------Pruefung Ohne Root
             select 1005,11,10,date '2015-01-13','BAHNCARD_6','CC',NULL from dual union all
             select 1006,12,11,date '2015-01-23','BAHNCARD_7','FE',NULL from dual union all
             select 1007,13,12,date '2015-01-24','BAHNCARD_8','CC',NULL from dual union all
             select 1008,14,12,date '2015-01-25','BAHNCARD_9','FF',NULL from dual union all
             select 1009,15,14,date '2015-01-26','BAHNCARD_10','CC',NULL from dual
            ),
    hier#(
          id,
          parents,
          childs,
          starttime,
          service,
          typ,
          service_extern,
          last_childs,
          cc_service
         )
          as(
              select  id,
                      parents,
                      childs,
                      starttime,
                      service,
                      typ,
                      service_extern,
                      childs,
                      null
                from  tab#
             union all
              select  h.id,
                      h.parents,
                      h.childs,
                      h.starttime,
                      h.service,
                      h.typ,
                      h.service_extern,
                      t.childs,
                      case t.typ
                        when 'CC' then t.service
                      end cc_service
                from  hier# h,
                      tab# t
                where t.parents = h.last_childs
                  and h.cc_service is null
            )
select  id,
        parents,
        childs,
        starttime,
        service,
        typ,
        service_extern,
        max(cc_service) cc_service
  from  hier#
  group by id,
            parents,
            childs,
            starttime,
            service,
            typ,
            service_extern
  order by id



         ID     PARENTS      CHILDS STARTTIME SERVICE     TY S CC_SERVICE
----------- ----------- ----------- --------- ----------- -- - -----------
1000 1 01-JAN-15 BAHNCARD_1 CC
1001 2 1 01-FEB-15 BAHNCARD_2 CC BAHNCARD_1
1002 3 2 01-MAR-15 BAHNCARD_3 FE BAHNCARD_2
1003 4 2 01-APR-15 BAHNCARD_4 CC BAHNCARD_2
1004 5 4 01-MAY-15 BAHNCARD_5 FE BAHNCARD_4
1005 11 10 13-JAN-15 BAHNCARD_6 CC
1006 12 11 23-JAN-15 BAHNCARD_7 FE BAHNCARD_6
1007 13 12 24-JAN-15 BAHNCARD_8 CC BAHNCARD_6
1008 14 12 25-JAN-15 BAHNCARD_9 FF BAHNCARD_6
1009 15 14 26-JAN-15 BAHNCARD_10 CC BAHNCARD_6

Как найти LEVEL и показать Parents_Root для каждого элемента?
Заранее спасибо

Сообщение было отредактировано: 24 фев 16, 09:31
23 фев 16, 18:06    [18854315]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
Elic
Member

Откуда:
Сообщений: 29977
Hans Christian Andersen
Как
На что ты надеешься, в третий раз и также косноязычно талдыча одно и то же?
23 фев 16, 18:13    [18854332]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
Elic,

решу сам. Подскажите, в каком направление двигаться
23 фев 16, 18:16    [18854337]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
Elic
Member

Откуда:
Сообщений: 29977
Hans Christian Andersen
Подскажите, в каком направление двигаться
Elic
покажи хотя бы желаемый результат.
23 фев 16, 18:19    [18854344]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
Elic,
Смотрите два последних поля level_id и parents_root

with tab#(
          id,
          parents,
          childs,
          starttime,
          service,
          typ,
          service_extern,
          level_id,
          parents_root          
         ) as(
             select 1000,1,NULL,date '2015-01-01','BAHNCARD_1','CC',NULL, 1 as level_id, 1 as parents_root from dual union all
             select 1001,2,1,date '2015-02-01','BAHNCARD_2','CC',NULL, 2 as level_id, 1 as parents_root     from dual union all
             select 1002,3,2,date '2015-03-01','BAHNCARD_3','FE',NULL, 3 as level_id, 1 as parents_root     from dual union all
             select 1003,4,2,date '2015-04-01','BAHNCARD_4','CC',NULL, 3 as level_id, 1 as parents_root     from dual union all
             select 1004,5,4,date '2015-05-01','BAHNCARD_5','FE',NULL, 4 as level_id,1 as parents_root      from dual union all
             ----------------Pruefung Ohne Root
             select 1005,11,10,date '2015-01-13','BAHNCARD_6','CC',NULL, 1 as level_id, 11 as parents_root from dual union all
             select 1006,12,11,date '2015-01-23','BAHNCARD_7','FE',NULL,2 as level_id, 11 as parents_root from dual union all
             select 1007,13,12,date '2015-01-24','BAHNCARD_8','CC',NULL,3 as level_id, 11 as parents_root from dual union all
             select 1008,14,12,date '2015-01-25','BAHNCARD_9','FF',NULL, 3 as level_id, 11 as parents_root from dual union all
             select 1009,15,14,date '2015-01-26','BAHNCARD_10','CC',NULL, 4 as level_id, 11 as parents_root from dual
            ),
    hier#(
          id,
          parents,
          childs,
          starttime,
          service,
          typ,
          service_extern,
          last_childs,
          cc_service,
          level_id,
          parents_root
         )
          as(
              select  id,
                      parents,
                      childs,
                      starttime,
                      service,
                      typ,
                      service_extern,
                      childs,
                      null,
                      level_id,
                      parents_root
                from  tab#
             union all
              select  h.id,
                      h.parents,
                      h.childs,
                      h.starttime,
                      h.service,
                      h.typ,
                      h.service_extern,
                      t.childs,
                      case t.typ
                        when 'CC' then t.service
                      end cc_service,
                      h.level_id,
                      h.parents_root                      
                from  hier# h,
                      tab# t
                where t.parents = h.last_childs
                  and h.cc_service is null
            )
select  id,
        parents,
        childs,
        starttime,
        service,
        typ,
        service_extern,    
        max(cc_service) cc_service,
        level_id,
        parents_root        
  from  hier#
  group by id,
            parents,
            childs,
            starttime,
            service,
            typ,
            service_extern,
            level_id,
            parents_root
  order by id




        ID    PARENTS     CHILDS STARTTIME SERVICE     TYP SERVICE_EXTERN CC_SERVICE    LEVEL_ID PARENTS_ROOT
---------- ---------- ---------- --------- ----------- --- -------------- ----------- ---------- ------------
1000 1 01.01.15 BAHNCARD_1 CC 1 1
1001 2 1 01.02.15 BAHNCARD_2 CC BAHNCARD_1 2 1
1002 3 2 01.03.15 BAHNCARD_3 FE BAHNCARD_2 3 1
1003 4 2 01.04.15 BAHNCARD_4 CC BAHNCARD_2 3 1
1004 5 4 01.05.15 BAHNCARD_5 FE BAHNCARD_4 4 1
1005 11 10 13.01.15 BAHNCARD_6 CC 1 11
1006 12 11 23.01.15 BAHNCARD_7 FE BAHNCARD_6 2 11
1007 13 12 24.01.15 BAHNCARD_8 CC BAHNCARD_6 3 11
1008 14 12 25.01.15 BAHNCARD_9 FF BAHNCARD_6 3 11
1009 15 14 26.01.15 BAHNCARD_10 CC BAHNCARD_6 4 11
23 фев 16, 18:33    [18854382]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Hans Christian Andersen,

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

Спасибо.
23 фев 16, 20:02    [18854698]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
--Eugene--,

извините, хотел сам решить. Но не получается.
SY помог. Набросал почти все решение.
Дело в том, что у меня не классическая иерархия. Т.е. в некоторых деревьях отсутствует корневой элемент (см. пример).
Попробую описать задачу, отталкиваясь от решения SY.

Нужно создать дополнительное поле - банальный флаг (flag), который получит следующие значения:
1. Если (Level =1), тогда флаг получает значение 1
2. Если (service = CC_SERVICE) или (service = service_extern), тогда флаг получает значение 2
3. Если (service is NULL), тогда флаг перенимает значение флага предыдущего (сортировка по Level и можно еще дополнительно сортировать по starttime, т.к. может иметься несколько листьев) элемента.
3а. Если же у предыдущего элемента, так же отсутствовал service (service is NULL), тогда флаг получает 2

P.S. 1. ID - В сортировке не может использоваться, т.к id может повторяться в дереве.
2. Циклов не имеется

Проблема: не получается создать "окно" для аналитики, для решения пункта 3. и 3а

Набросал пример данных (Oracle сейчас нет под рукой, поэтому не могу показать результат)
Заранее спасибо и извините за беспокойство!

       with tab#(
          id,
          parents,
          childs,
          starttime,
          service,
          typ,
          service_extern
         ) as(
             select 1000,1,NULL,date '2015-01-01','BAHNCARD_1','CC',NULL as service_extern from dual union all 
             select 1001,2,1,date '2015-02-01','BAHNCARD_1’,’CC',NULL from dual union all                        
             select 1002,3,2,date '2015-03-01','BAHNCARD_3','FE','BAHNCARD_3' from dual union all
             select 1003,4,2,date '2015-04-01','BAHNCARD_4','CC',NULL from dual union all
             select 1004,5,4,date '2015-05-01‘,NULL,’FE',NULL from dual union all
             select 1005,6,5,date '2015-05-01','BAHNCARD_4','FE',NULL from dual union all
             select 1005,7,6,date '2015-05-01‘,NULL,’FE',NULL from dual union all
             select 1005,8,7,date '2015-05-01‘,NULL,’FE',NULL from dual union all
             ----------------Pruefung Ohne Root
             select 1005,11,10,date '2015-01-13','BAHNCARD_6','CC',NULL from dual union all
             select 1006,12,11,date '2015-01-23','BAHNCARD_6’,’FE',NULL from dual union all
             select 1007,13,12,date '2015-01-24‘, NULL,’CC',NULL from dual union all
             select 1008,14,12,date '2015-01-25','BAHNCARD_9','FF', BAHNCARD_9 from dual union all
             select 1009,15,14,date '2015-01-26','BAHNCARD_6’,’CC',NULL from dual
            ),
    hier#(
          id,
          parents,
          childs,
          starttime,
          service,
          typ,
          service_extern,
          last_childs,
          cc_service
         )
          as(
              select  id,
                      parents,
                      childs,
                      starttime,
                      service,
                      typ,
                      service_extern,
                      childs,
                      null
                from  tab#
             union all
              select  h.id,
                      h.parents,
                      h.childs,
                      h.starttime,
                      h.service,
                      h.typ,
                      h.service_extern,
                      t.childs,
                      case t.typ
                        when 'CC' then t.service
                      end cc_service
                from  hier# h,
                      tab# t
                where t.parents = h.last_childs
                  and h.cc_service is null
            )
select  id,
        parents,
        childs,
        starttime,
        service,
        typ,
        service_extern,
        max(cc_service) cc_service
  from  hier#
  group by id,
            parents,
            childs,
            starttime,
            service,
            typ,
            service_extern
  order by id
23 фев 16, 21:54    [18855121]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Hans Christian Andersen,

если у одного узла есть оба атрибута - и "parents" и "childs" - это значит, у него может быть как несколько "детей", так и несколько "родителей".
вам следует определиться со структурой таблицы и четко обозначить колонку первичного ключа, и колонку родительского значения первичного ключа.
если колонка id не есть суть PK, зачем её вообще приводить в примере? - это лишь вводит в заблуждение.

что же касается того, что вы постоянно твердите, что "в некоторых деревьях отсутствует корневой элемент" - это некорректно. если есть дерево - есть и корень. другое дело какое у него значение (NULL - вообще-то, тоже значение).
23 фев 16, 22:42    [18855244]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
--Eugene--
Hans Christian Andersen,

если у одного узла есть оба атрибута - и "parents" и "childs" - это значит, у него может быть как несколько "детей", так и несколько "родителей".
вам следует определиться со структурой таблицы и четко обозначить колонку первичного ключа, и колонку родительского значения первичного ключа.
если колонка id не есть суть PK, зачем её вообще приводить в примере? - это лишь вводит в заблуждение.

что же касается того, что вы постоянно твердите, что "в некоторых деревьях отсутствует корневой элемент" - это некорректно. если есть дерево - есть и корень. другое дело какое у него значение (NULL - вообще-то, тоже значение).


1. Прикрепляю картинку самого сложного случая иерархии, который может встретится.
2. На счет детей и родителей: приведу пример, как бы выглядел классический запрос в моем случае.
start with childs is null connect by prior parents = childs

3. На счет корневого каталога:
В следующем примере отсутствует корень, т.к. его нельзя определить с помощью "start with childs is null"
Не смотря на это, нужно в таком дереве обрабатывать данные.

select 1005,11,10,date '2015-01-13','BAHNCARD_6','CC',NULL from dual union all
select 1006,12,11,date '2015-01-23','BAHNCARD_6’,’FE',NULL from dual union all
select 1007,13,12,date '2015-01-24‘, NULL,’CC',NULL from dual union all
select 1008,14,12,date '2015-02-25','BAHNCARD_9','FF', BAHNCARD_9 from dual union all
select 1009,15,14,date '2015-03-26','BAHNCARD_6’,’CC',NULL from dual

Если сортировать по starttime, то можно увидеть, какой элемент первый, второй...
Я подкорректировал пример, в котором можно сортировать по starttime. В настоящей базе данных поля parents и  childs имеют текстовые значения

             select 1000,1,NULL,date '2015-01-01','BAHNCARD_1','CC',NULL as service_extern from dual union all 
             select 1001,2,1,date '2015-02-01','BAHNCARD_1’,’CC',NULL from dual union all                        
             select 1002,3,2,date '2015-03-01','BAHNCARD_3','FE','BAHNCARD_3' from dual union all
             select 1003,4,2,date '2015-05-01','BAHNCARD_4','CC',NULL from dual union all
             select 1004,5,4,date '2015-06-01‘,NULL,’FE',NULL from dual union all
             select 1005,6,5,date '2015-07-01','BAHNCARD_4','FE',NULL from dual union all
             select 1005,7,6,date '2015-08-01‘,NULL,’FE',NULL from dual union all
             select 1005,8,7,date '2015-09-01‘,NULL,’FE',NULL from dual union all
             ————————without root
             select 1005,11,10,date '2015-01-13','BAHNCARD_6','CC',NULL from dual union all
             select 1006,12,11,date '2015-01-23','BAHNCARD_6’,’FE',NULL from dual union all
             select 1007,13,12,date '2015-01-24‘, NULL,’CC',NULL from dual union all
             select 1008,14,12,date '2015-02-25','BAHNCARD_9','FF', BAHNCARD_9 from dual union all
             select 1009,15,14,date '2015-03-26','BAHNCARD_6’,’CC',NULL from dual 


К сообщению приложен файл. Размер - 90Kb
23 фев 16, 23:43    [18855403]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
Hans Christian Andersen
В следующем примере отсутствует корень, т.к. его нельзя определить с помощью "start with childs is null"


Корни это строки у которых нет родителей а не те у которых поле родителя NULL:

SQL> with tab#(
  2            id,
  3            parents,
  4            childs,
  5            starttime,
  6            service,
  7            typ,
  8            service_extern
  9           )
 10        as (
 11            select 1005,11,10,date '2015-01-13','BAHNCARD_6','CC',NULL from dual union all
 12            select 1006,12,11,date '2015-01-23','BAHNCARD_6','FE',NULL from dual union all
 13            select 1007,13,12,date '2015-01-24', NULL,'CC',NULL from dual union all
 14            select 1008,14,12,date '2015-02-25','BAHNCARD_9','FF', 'BAHNCARD_9' from dual union all
 15            select 1009,15,14,date '2015-03-26','BAHNCARD_6','CC',NULL from dual
 16            )
 17  select  level,
 18          tab#.*
 19    from  tab#
 20    start with childs not in (select parents from tab#)
 21    connect by childs = prior parents
 22  /

     LEVEL         ID    PARENTS     CHILDS STARTTIME SERVICE    TY SERVICE_EX
---------- ---------- ---------- ---------- --------- ---------- -- ----------
         1       1005         11         10 13-JAN-15 BAHNCARD_6 CC
         2       1006         12         11 23-JAN-15 BAHNCARD_6 FE
         3       1007         13         12 24-JAN-15            CC
         3       1008         14         12 25-FEB-15 BAHNCARD_9 FF BAHNCARD_9
         4       1009         15         14 26-MAR-15 BAHNCARD_6 CC

SQL>


SY.

Сообщение было отредактировано: 24 фев 16, 02:10
24 фев 16, 02:10    [18855627]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекрусии, если запрос начинается с листьем  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Hans Christian Andersen,

FYI: START WITH Clause не является обязательной. Поэкспериментируйте без нее.
24 фев 16, 03:10    [18855674]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
--Eugene--,



спасибо попробую
24 фев 16, 11:56    [18856468]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
SY,

такого рода запрос очень медленный
24 фев 16, 11:56    [18856471]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
--Eugene--,


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

queue_level - флаг
QUEUE_LEVEL_WITH3 - промежуточный флаг, для вариантов, если (Service is null)

 with tab#
                  as
                  ( select 1000 as id,1 as childs, NULL as parents, to_date ('01.01.2015','dd.mm.yyyy') as starttime, 'BAHNCARD' as service, 'FE' as typ, NULL service_extern from dual
                     union all
                    select 1001 as id, 2 childs, 1 parents, to_date ('01.02.2015','dd.mm.yyyy'), 'BAHNCARD_1' as service,'CC' as typ,NULL service_extern from dual
                     union all
                    select 1002 as id, 3 childs, 2 parents, to_date ('01.03.2015','dd.mm.yyyy'), 'BAHNCARD_0' as service,'FE' as typ,NULL service_extern from dual 
                      union all
                    select 1003 as id , 4 childs, 3 parents, to_date ('01.04.2015','dd.mm.yyyy'), 'BAHNCARD_1' as service,'FE' as typ,NULL  service_extern from dual 
                   ----------------Pruefung Ohne Root
                      UNION ALL 
                    select 1005 as id, 11 childs, 10 parents, to_date ('13.01.2015','dd.mm.yyyy'), 'BAHNCARD_1' as service,'CC' as typ,NULL service_extern from dual
                     union all
                    select 1006 as id, 12 childs, 11 parents, to_date ('23.01.2015','dd.mm.yyyy'), 'BAHNCARD_0' as service,'CC' as typ,NULL service_extern from dual 
                      union all
                    select 1007 as id , 13 childs, 12 parents, to_date ('25.01.2015','dd.mm.yyyy'), 'BAHNCARD_1' as service,'CC' as typ,NULL service_extern from dual 
                   ---------------- Pruefung Extern
                    UNION ALL
                    select 1010 as id,20 as childs, NULL as parents, to_date ('11.12.2015','dd.mm.yyyy') as starttime, 'BAHNCARD_1' as service, 'FE' as typ, 'BAHNCARD_1' service_extern  from dual
                     union all
                    select 1011 as id, 21 childs, 20 parents, to_date ('13.12.2015','dd.mm.yyyy'), 'BAHNCARD_1' as service,'FE' as typ,'BAHNCARD_1' service_extern from dual
                     union all
                    select 1012 as id, 22 childs, 21 parents, to_date ('14.12.2015','dd.mm.yyyy'), 'BAHNCARD_0' as service,'FE' as typ,'BAHNCARD_1' service_extern from dual 
                      union all
                    select 1013 as id , 23 childs, 22 parents, to_date ('15.12.2015','dd.mm.yyyy'), 'BAHNCARD_1' as service,'CC' as typ,'BAHNCARD_1' service_extern from dual 
                   ---------------- Pruefung Service is NULL
                    UNION ALL
                    select 1021 as id,30 as childs, NULL as parents, to_date ('11.11.2015','dd.mm.yyyy') as starttime, NULL as service, 'FE' as typ, NULL service_extern  from dual
                     union all
                    select 1022 as id, 31 childs, 30 parents, to_date ('13.11.2015','dd.mm.yyyy'), 'BAHNCARD_0' as service,'CC' as typ,NULL service_extern from dual
                     union all
                    select 1023 as id, 32 childs, 31 parents, to_date ('14.11.2015','dd.mm.yyyy'), NULL as service,'FE' as typ,NULL service_extern from dual 
                      union all
                    select 1024 as id , 33 childs, 32 parents, to_date ('15.11.2015','dd.mm.yyyy'), 'BAHNCARD_1' as service,'CC' as typ,NULL service_extern from dual                    
                   
                  ) 
          SELECT  last_of_child,root_child, last_of_service, last_of_starttime, queue_level_with3,
                  CASE queue_level_with3
                       WHEN 3 THEN lag (queue_level_with3,1,1) over (partition by root_child order by last_of_starttime)
                       else queue_level_with3
                  end as queue_level
          FROM   
          (
            SELECT last_of_child,root_child, last_of_service, last_of_starttime,max(queue_level) as queue_level_with3
                FROM
             (                  
                 SELECT childs, service, typ, starttime, last_of_child, last_of_service,last_of_starttime,service_extern,
                        CASE 
                              WHEN (last_of_service = service and sum_of_typ_cc = 1 and typ='CC') or (last_of_service = service_extern)  THEN 2
                              WHEN last_of_service IS NULL THEN 3 
                              ELSE 1
                        END as queue_level,
                        sum_of_typ_cc,
                        anzahl_elementen,
                        ebene,
                        root_child
                 FROM
                 (
                  select connect_by_root(id) as id, connect_by_root(childs) as childs, connect_by_root(parents) as parents,CONNECT_BY_ROOT(starttime) as starttime,
                         CONNECT_BY_ROOT(service) as service, CONNECT_BY_ROOT(typ) as typ,CONNECT_BY_ROOT(service_extern) as service_extern,id as last_of_id, childs as last_of_child, service as last_of_service,starttime as last_of_starttime,
                         SUM( CASE 
                                    WHEN CONNECT_BY_ROOT(TYP)  = 'CC' and level !=1 THEN 1
                                    ELSE 0
                              END ) over (partition by childs order by CONNECT_BY_ROOT(starttime) desc rows between unbounded preceding and current row) as sum_of_typ_cc,
                         count (*) over (partition by childs  ) anzahl_elementen,
                        level as ebene,
                        first_value( CONNECT_BY_ROOT(childs)) over (partition by childs order by CONNECT_BY_ROOT(starttime) asc) as root_child
                  from tab#
                  connect by   parents= prior childs
                  order by last_of_child desc,starttime desc
                  )
                  where anzahl_elementen =1 or ebene !=1 
             )
            group by last_of_child, root_child, last_of_service, last_of_starttime
        );




LAST_OF_CHILD ROOT_CHILD LAST_OF_SERVICE LAST_OF_STARTTIME   QUEUE_LEVEL_WITH3 QUEUE_LEVEL
------------- ---------- --------------- ------------------- ----------------- -----------
1 1 BAHNCARD 01.01.2015 00:00:00 1 1
2 1 BAHNCARD_1 01.02.2015 00:00:00 1 1
3 1 BAHNCARD_0 01.03.2015 00:00:00 1 1
4 1 BAHNCARD_1 01.04.2015 00:00:00 2 2
11 11 BAHNCARD_1 13.01.2015 00:00:00 1 1
12 11 BAHNCARD_0 23.01.2015 00:00:00 1 1
13 11 BAHNCARD_1 25.01.2015 00:00:00 1 1
20 20 BAHNCARD_1 11.12.2015 00:00:00 2 2
21 20 BAHNCARD_1 13.12.2015 00:00:00 2 2
22 20 BAHNCARD_0 14.12.2015 00:00:00 1 1
23 20 BAHNCARD_1 15.12.2015 00:00:00 2 2
30 30 11.11.2015 00:00:00 3 1
31 30 BAHNCARD_0 13.11.2015 00:00:00 1 1
32 30 14.11.2015 00:00:00 3 1
33 30 BAHNCARD_1 15.11.2015 00:00:00 1 1
24 фев 16, 13:10    [18856893]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Hans Christian Andersen
Нужно создать дополнительное поле - банальный флаг (flag)
поделитесь, зачем вам нужен этот флаг?
Предвижу ответ в ключе "для того чтобы знать следующее:
1. Если (Level =1)
2. Если (service = CC_SERVICE) или (service = service_extern)
3. Если (service is NULL)
[...]"
в таком случае уточняю: зачем вам нужна эта информация?
24 фев 16, 13:24    [18856965]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
--Eugene--,

Делюсь:
Флаг - это конечный результат, который нужно получить.
На базе этого флага будут созданы определенные measures, но уже в другой логики.
24 фев 16, 13:41    [18857103]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Hans Christian Andersen
1. Если (Level =1), тогда флаг получает значение 1
2. Если (service = CC_SERVICE) или (service = service_extern), тогда флаг получает значение 2
3. Если (service is NULL), тогда флаг перенимает значение флага предыдущего (сортировка по Level и можно еще дополнительно сортировать по starttime, т.к. может иметься несколько листьев) элемента.
3а. Если же у предыдущего элемента, так же отсутствовал service (service is NULL), тогда флаг получает 2

А если Level > 1
и service != CC_SERVICE и service != service_extern
и service is not NULL
?
24 фев 16, 15:48    [18857976]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
--Eugene--,

тогда 1
24 фев 16, 16:04    [18858103]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Hans Christian Andersen,

+
with t as (
	select 0 name, 0 id, 0 parent_id, null starttime, '' service, '' typ, null service_extern from dual where 1 = 0
	union all select 1000,  1, null, date'2015-01-01',   'BAHNCARD', 'FE',           '' from dual
	union all select 1001,  2,    1, date'2015-02-01', 'BAHNCARD_1', 'CC',           '' from dual
	union all select 1002,  3,    2, date'2015-03-01', 'BAHNCARD_0', 'FE',           '' from dual 
	union all select 1003,  4,    3, date'2015-04-01', 'BAHNCARD_1', 'FE',           '' from dual 
	union all select 1005, 11,   10, date'2015-01-13', 'BAHNCARD_1', 'CC',           '' from dual
	union all select 1006, 12,   11, date'2015-01-23', 'BAHNCARD_0', 'CC',           '' from dual 
	union all select 1007, 13,   12, date'2015-01-25', 'BAHNCARD_1', 'CC',           '' from dual 
	union all select 1010, 20, null, date'2015-12-11', 'BAHNCARD_1', 'FE', 'BAHNCARD_1' from dual
	union all select 1011, 21,   20, date'2015-12-13', 'BAHNCARD_1', 'FE', 'BAHNCARD_1' from dual
	union all select 1012, 22,   21, date'2015-12-14', 'BAHNCARD_0', 'FE', 'BAHNCARD_1' from dual 
	union all select 1013, 23,   22, date'2015-12-15', 'BAHNCARD_1', 'CC', 'BAHNCARD_1' from dual 
	union all select 1021, 30, null, date'2015-11-11',           '', 'FE',           '' from dual
	union all select 1022, 31,   30, date'2015-11-13', 'BAHNCARD_0', 'CC',           '' from dual
	union all select 1023, 32,   31, date'2015-11-14',           '', 'FE',           '' from dual
	union all select 1024, 33,   32, date'2015-11-15', 'BAHNCARD_1', 'CC',           '' from dual
),
t1 as (
	select name,
			id,
			parent_id,
			starttime,
			case typ when 'CC' then 'CC_SERVICE' else service end service,
			typ,
			service_extern
		from t
),
t2(
	name,
	id,
	parent_id,
	starttime,
	service,
	typ,
	service_extern,
	flag)
as (
	select name, id, parent_id, starttime, service, typ, service_extern, 1 flag
		from t1
		where parent_id is null
			or parent_id not in (select id from t)
	union all
	select t1.name, t1.id, t1.parent_id, t1.starttime, t1.service, t1.typ, t1.service_extern,
			case
				when t1.service is null then nvl2(t2.service, t2.flag, 2)
				when t1.service = any('CC_SERVICE', t1.service_extern) then 2
				else 1
			end flag
		from t1, t2
		where t2.id = t1.parent_id
)
select lpad('_', 4*(level-1), '_') || id title,
		t2.*
	from t2
	start with parent_id is null or parent_id not in (select id from t)
	connect by prior id = parent_id
	order siblings by id
TITLENAMEIDPARENT_IDSTARTTIMESERVICETYPSERVICE_EXTERNFLAG
110001 1.1.2015BAHNCARDFE 1
____21001212.1.2015CC_SERVICECC 2
________31002323.1.2015BAHNCARD_0FE 1
____________41003434.1.2015BAHNCARD_1FE 1
11100511101.13.2015CC_SERVICECC 1
____12100612111.23.2015CC_SERVICECC 2
________13100713121.25.2015CC_SERVICECC 2
20101020 12.11.2015BAHNCARD_1FEBAHNCARD_11
____211011212012.13.2015BAHNCARD_1FEBAHNCARD_12
________221012222112.14.2015BAHNCARD_0FEBAHNCARD_11
____________231013232212.15.2015CC_SERVICECCBAHNCARD_12
30102130 11.11.2015 FE 1
____311022313011.13.2015CC_SERVICECC 2
________321023323111.14.2015 FE 2
____________331024333211.15.2015CC_SERVICECC 2
24 фев 16, 16:34    [18858303]     Ответить | Цитировать Сообщить модератору
 Re: Найти Level в рекурсии, если запрос начинается с листьев  [new]
Hans Christian Andersen
Member

Откуда: зачем Вам?
Сообщений: 253
--Eugene--,

спасибо за решение. Очень оригинально.
Я попробую на настоящих данных, но боюсь, что запрос будет еще дольше выполняться, чем мое решение.
24 фев 16, 17:02    [18858540]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить