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

Откуда:
Сообщений: 144
Существует иерархия элементов,

item_id -- идентификатор элемента
parent_id -- идентификатор родительского элемента (-1 означает, что элемент на первом уровне)
item_name -- наименование

item_ord -- порядковый номер, определяющий позицию среди других элементов, подчиненных одному и тому же родителькому элементу.

WITH h AS (
SELECT 10 AS item_id, -1 AS parent_id, 1 AS item_ord, '1' AS item_name FROM dual
UNION ALL
SELECT 34, -1, 4, '4' FROM dual
UNION ALL
SELECT 17, -1, 5, '5' FROM dual
UNION ALL
SELECT 87, 10, 2, '1.2' FROM dual
UNION ALL
SELECT 21, 10, 1, '1.1' FROM dual
UNION ALL
SELECT 7, -1, 2, '2' FROM dual
UNION ALL
SELECT 2, -1, 3, '3' FROM dual
UNION ALL
SELECT 22, 2, 1, '3.1' FROM dual
UNION ALL
SELECT 18, 2, 2, '3.2' FROM dual
UNION ALL
SELECT 41, 18, 1, '3.2.1' FROM dual
)
SELECT item_id,
       parent_id,
       item_ord,
       LPAD(' ', 2 * LEVEL - 2) || item_name
  FROM h
 START WITH parent_id = -1
CONNECT BY PRIOR item_id = parent_id
ORDER SIBLINGS BY item_ord

1
1.1
1.2
2
3
3.1
3.2
3.2.1
4
5

Задача: обеспечить правильную сортировку элементов с учетом уровня и значения item_ord, при условии что:

1) Иерархия убирается (т.е. всем элементам назначается parent_id = -1)
2) На элементы наложен фильтр, такой, что некоторые родительские элементы его не проходят.

Можно использовать PL/SQL

Пример: наложен фильтр на элементы '1', '1.2', '3.2', '3.2.1', '5'

В результате должно получится

1
1.2
3.2
3.2.1
5

Сейчас реализовано через построение полной иерархии всех элементов (даже не удовлетворяющих условию фильтра) с использованием ORDER SIBLINGS BY item_ord.
Если элементов много (например 30 тысяч), а в фильтре их мало (например 6 элементов), это становится очень накладно.

Как это сделать проще? Можно просто идею.
18 июн 07, 13:21    [4279917]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Timm
Member

Откуда: Moscow, Ё-burg
Сообщений: 3696
Nike_K

Задача: обеспечить правильную сортировку элементов с учетом уровня и значения item_ord, при условии что:

1) Иерархия убирается (т.е. всем элементам назначается parent_id = -1)
2) На элементы наложен фильтр, такой, что некоторые родительские элементы его не проходят.

Какие то противоречивые условия. Иерархии нет, но родители у элементов есть.
18 июн 07, 13:26    [4279953]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Nike_K
Member

Откуда:
Сообщений: 144
Timm

Какие то противоречивые условия. Иерархии нет, но родители у элементов есть.


Я невнятно поставил условие. Скажем так, иерархия элементов в результате не важна. Мне важен только их порядок. При выстраивании этого порядка иерархия играет свою роль.
18 июн 07, 13:30    [4279983]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Stax..
Guest
в item_name токо числа?
зы
напр дополнить до 000003.000002.000001
.....
stax
18 июн 07, 13:40    [4280080]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Nike_K
Member

Откуда:
Сообщений: 144
Stax..
в item_name токо числа?
зы
напр дополнить до 000003.000002.000001
.....
stax


В item_name, может быть все что угодно. Может как-то отсортировывать по
SYS_CONNECT_BY _PATH?
18 июн 07, 13:47    [4280146]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Elic
Member

Откуда:
Сообщений: 29987
STFF посегментная числовая сортировка
18 июн 07, 14:13    [4280304]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Nike_K
Member

Откуда:
Сообщений: 144
Спасибо за подсказку, ухожу читать.
18 июн 07, 14:24    [4280372]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Nike_K
Member

Откуда:
Сообщений: 144
Если кому-то интересно, решение, созданное на основе указанной Elic'ом ссылки:

Создается тип -- коллекция NUMBER

CREATE TYPE t_numberlist_table IS TABLE OF NUMBER

Создается вспомогательный тип, функция ord которого будет использована для сортировки

CREATE OR REPLACE TYPE t_order_machine AS OBJECT
(

  c tnumberlist_table,

  ORDER MEMBER FUNCTION ord(p_o t_order_machine) RETURN NUMBER

) FINAL
/
CREATE OR REPLACE TYPE BODY t_order_machine AS

  ORDER MEMBER FUNCTION ord(p_o t_order_machine) RETURN NUMBER IS
    l_offset NUMBER := 0;
    l_one_count NUMBER;
    l_another_count NUMBER;
    l_one_ord NUMBER;
    l_another_ord NUMBER;
  BEGIN
    
    l_one_count := c.COUNT;
    
    l_another_count := p_o.c.COUNT;
    
    LOOP
      
      IF (l_one_count > l_offset) THEN
      
        l_one_ord := c(l_one_count - l_offset);
      
      ELSE
      
        RETURN -1;
        
      END IF;
      
      IF (l_another_count > l_offset) THEN        
      
        l_another_ord := p_o.c(l_another_count - l_offset);
        
      ELSE
        
        RETURN 1;
        
      END IF;
      
      IF (l_one_ord < l_another_ord) THEN
      
        RETURN -1;
        
      ELSIF (l_one_ord > l_another_ord) THEN
        
        RETURN 1;
      
      ELSE
        
        NULL;
        
      END IF;  
      
      l_offset := l_offset + 1;
      
    END LOOP;
  
    RETURN 0;
  
  END ord;

END;
/

Запрос, сортирующий элементы, решение задачи

WITH h AS (
SELECT 10 AS item_id, -1 AS parent_id, 1 AS item_ord, '1' AS item_name FROM dual
UNION ALL
SELECT 34, -1, 4, '4' FROM dual
UNION ALL
SELECT 17, -1, 5, '5' FROM dual
UNION ALL
SELECT 87, 10, 2, '1.2' FROM dual
UNION ALL
SELECT 21, 10, 1, '1.1' FROM dual
UNION ALL
SELECT 7, -1, 2, '2' FROM dual
UNION ALL
SELECT 2, -1, 3, '3' FROM dual
UNION ALL
SELECT 22, 2, 1, '3.1' FROM dual
UNION ALL
SELECT 18, 2, 2, '3.2' FROM dual
UNION ALL
SELECT 41, 18, 1, '3.2.1' FROM dual
)
SELECT k.*
  FROM h k
 WHERE k.item_name IN ('1', '1.2', '3.2', '3.2.1', '5')
 ORDER BY t_order_machine(CAST(MULTISET (SELECT item_ord
                                  FROM h g
                                 START WITH g.item_id = k.item_id
                                CONNECT BY PRIOR parent_id = item_id) AS
                               t_numberlist_table))

Результат

1
1.2
3.2
3.2.1
5
18 июн 07, 15:48    [4280973]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Elic
Member

Откуда:
Сообщений: 29987
Nike_K
 ORDER BY t_order_machine(CAST(MULTISET (SELECT item_ord
                                  FROM h g
                                 START WITH g.item_id = k.item_id
                                CONNECT BY PRIOR parent_id = item_id) AS
                               t_numberlist_table))
IMHO, перебор. Достаточно
sys_connect_by_path(item_ord, '.')
+
order member function ord (o in abm_t1)
18 июн 07, 17:03    [4281531]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Nike_K
Member

Откуда:
Сообщений: 144
Elic
IMHO, перебор. Достаточно
sys_connect_by_path(item_ord, '.')
+
order member function ord (o in abm_t1)


Не хотелось закладывать ограничение на длину VARCHAR2 в SQL, иметь возможные баги с ORA-30004 и завязываться на производительность оракловой реализации функций работы со строками. Нареканий по скорости пока нет.
18 июн 07, 17:16    [4281620]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
1
Guest
автор
Не хотелось закладывать ограничение на длину VARCHAR2 в SQL, иметь возможные баги с ORA-30004 и завязываться на производительность оракловой реализации функций работы со строками. Нареканий по скорости пока нет.
конечно, описанные тобой минусы могут проявиться, но вариант без member function'ов:

SQL> WITH h AS (
  2            SELECT 10 AS item_id, -1 AS parent_id, 1 AS item_ord, '1' AS item_name FROM dual
  3            UNION ALL
  4            SELECT 34, -1, 4, '4' FROM dual
  5            UNION ALL
  6            SELECT 17, -1, 5, '5' FROM dual
  7            UNION ALL
  8            SELECT 87, 10, 2, '1.2' FROM dual
  9            UNION ALL
 10            SELECT 21, 10, 1, '1.1' FROM dual
 11            UNION ALL
 12            SELECT 7, -1, 2, '2' FROM dual
 13            UNION ALL
 14            SELECT 2, -1, 3, '3' FROM dual
 15            UNION ALL
 16            SELECT 22, 2, 1, '3.1' FROM dual
 17            UNION ALL
 18            SELECT 18, 2, 2, '3.2' FROM dual
 19            UNION ALL
 20            SELECT 41, 18, 1, '3.2.1' FROM dual
 21            )
 22  --
 23  select max(decode(lev, 1, item_id)) item_id,
 24         max(decode(lev, 1, parent_id)) parent_id,
 25         max(decode(lev, 1, item_ord)) item_ord,
 26         max(decode(lev, 1, item_name)) item_name
 27    from (SELECT k.*,
 28                 sys_connect_by_path(rpad(item_ord, 5, '0'), '/') path,
 29                 level lev,
 30                 connect_by_root(item_id) root
 31            FROM h k
 32          connect by prior parent_id = item_id
 33           start with item_name IN ('1', '1.2', '3.2', '3.2.1', '5'))
 34   group by root
 35   order by reverse(max(path))
 36  /

   ITEM_ID  PARENT_ID   ITEM_ORD ITEM_NAME
---------- ---------- ---------- ---------
        10         -1          1 1
        87         10          2 1.2
        18          2          2 3.2
        41         18          1 3.2.1
        17         -1          5 5

SQL> 
18 июн 07, 21:43    [4282540]     Ответить | Цитировать Сообщить модератору
 Re: Порядок  [new]
Elic
Member

Откуда:
Сообщений: 29987
1
rpad(..., 5, ...)
Этим всё сказано :)
19 июн 07, 14:02    [4285829]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить