Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Плоскую таблицу преобразовать в Иерархическую / 25 сообщений из 42, страница 1 из 2
  1  все
19.12.2021, 16:57
    #40121390
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Добрий день!
К сожалению ничего не нашел, может куда направите. :)
Есть плоска таблица с полями
Name1, Name2, Name3, Name4, Name5, Name6, ...
Каждое поле Name это отдельный уровень.
Нужно это преобразовать в иерархическую таблицу
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE HIERARCHICAL (
  ID   NUMBER(10),
  PID  NUMBER,
  NAME VARCHAR2(250) NOT NULL,
  CONSTRAINT PK_HIERARCHICAL_ID PRIMARY KEY (ID),
  CONSTRAINT FK_HIERARCHICAL_PID FOREIGN KEY (PID)
  REFERENCES HIERARCHICAL (ID)
);
CREATE UNIQUE INDEX UK_HIERARCHICAL ON HIERARCHICAL (ID, NAME);


Сейчас я делаю для каждого уровня отдельный Insert.
Сложность еще состоит в том, что некоторые уровни (Name{N}) могут быть пустыми, следовательно, этот уровень нужно пропустить при добавлении в HIERARCHICAL. В этом случае количество Insert-ов для следующих уровней увеличивается в 2 раза.
Кроме того. Сейчас я удаляю все в HIERARCHICAL, а потом создаю наново, но это плохо. Мне нужны ID для дальнейшего использования, а они каждый раз при Insert будут (могут быть) разные. Конечно это тоже можно сделать Insert только новых и Delete отсутствующих, но это еще усложняет обновление HIERARCHICAL.
Может будут какие-то идеи упрощения всего этого процесса?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.12.2021, 17:02
    #40121393
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiWМожет будут какие-то идеи упрощения всего этого процесса?

Использовать PL/SQL для преобразования данных.
Старую таблицу удалить чтобы этот процесс был одноразовым.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.12.2021, 17:11
    #40121394
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Dimitry Sibiryakov,

Спасибо!
Буду думать как это решить на PL/SQL.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.12.2021, 17:16
    #40121395
Никанор Кузьмич
Никанор Кузьмич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiW
Есть плоска таблица с полями
Name1, Name2, Name3, Name4, Name5, Name6, ...
...
Может будут какие-то идеи упрощения всего этого процесса?
Я бы сделал примерно так: unpivot + rownumber(), потом отсортировать по какому-нибудь старому полю + NameN, rownumber() становится id, rownumber() предыдущей строки использовать как parent_id. Вроде относительно просто.
Это если я правильно понял суть задачи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.12.2021, 17:53
    #40121403
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiW
Может будут какие-то идеи упрощения всего этого процесса?
В эсэсэсэрах не научили предоставлять характерные исходные данные?
SergiiW
Мне нужны ID для дальнейшего использования, а они каждый раз при Insert будут (могут быть) разные. Конечно это тоже можно сделать Insert только новых и Delete отсутствующих, но это еще усложняет обновление HIERARCHICAL.
RTFM MERGE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.12.2021, 20:40
    #40121458
Никанор Кузьмич
Никанор Кузьмич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Что-то типа такого нужно?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with t as (select 1 id, 'Vasya' name1, 'Petya' name2, '' name3 from dual union all
           select 2, 'Masha', 'Katya', 'Olya' from dual)
select id, lvl, val, new_pk, lag(new_pk, 1, null) over (partition by id order by id, lvl) parent_id
  from (select id, lvl, val, row_number() over (order by id, lvl) new_pk
          from (select id, lvl, val
                  from t
               unpivot (val for lvl in (name1 as 1, name2 as 2, name3 as 3))));

        ID        LVL VAL       NEW_PK  PARENT_ID
---------- ---------- ----- ---------- ----------
         1          1 Vasya          1           
         1          2 Petya          2          1
         2          1 Masha          3           
         2          2 Katya          4          3
         2          3 Olya           5          4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.12.2021, 21:09
    #40121469
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiW

Сложность еще состоит в том, что некоторые уровни (Name{N}) могут быть пустыми, следовательно, этот уровень нужно пропустить при добавлении в HIERARCHICAL.



Не уверен что понял. Понял так: Name{N} пустой значит Name{N + 1} пустой и Name{N + 2} пустой и.т.д. ибо число полей плоской таблицы есть MAX(N).

Код: 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.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
CREATE TABLE TEST
  AS
     SELECT  'VANIA' NAME1,
             'PETYA' NAME2,
             'SASHA' NAME3,
             'PASHA' NAME4,
             'VOVA'  NAME5,
             NULL    NAME6
       FROM  DUAL
    UNION ALL
     SELECT  'MISHA' NAME1,
             'MASHA' NAME2,
             'PASHA' NAME3,
             'DASHA' NAME4,
             'GOSHA' NAME5,
             'KESHA' NAME6
       FROM  DUAL
/
INSERT
  INTO HIERARCHICAL
  WITH R(
         LVL,
         RID,
         ID,
         PID,
         NAME,
         OFFSET
        )
    AS (
         SELECT  1      LVL,
                 ROWID  RID,
                 ROWNUM ID,
                 NULL   PID,
                 NAME1  NAME,
                 COUNT(*) OVER() OFFSET
           FROM  TEST
        UNION ALL
         SELECT  R.LVL + 1         LVL,
                 R.RID             RID,
                 R.OFFSET + ROWNUM ID,
                 R.ID              PID,
                 CASE R.LVL
                   WHEN 1 THEN T.NAME2
                   WHEN 2 THEN T.NAME3
                   WHEN 3 THEN T.NAME4
                   WHEN 4 THEN T.NAME5
                   WHEN 5 THEN T.NAME6
                 END NAME,
                 R.OFFSET + COUNT(*) OVER() OFFSET
           FROM  R,
                 TEST T
           WHERE T.ROWID = R.RID
             AND R.NAME IS NOT NULL
             AND R.LVL <= 6
       )
  SELECT  ID,
          PID,
          NAME
    FROM  R
    WHERE NAME IS NOT NULL
/,

11 rows created.

SQL> SELECT  LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  2          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  3          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  4    FROM  HIERARCHICAL
  5    START WITH PID IS NULL
  6    CONNECT BY PID = PRIOR ID
  7  /

ID                   PID                  NAME
-------------------- -------------------- --------------------
1                                         VANIA
  3                    1                    PETYA
    5                    3                    SASHA
      7                    5                    PASHA
        9                    7                    VOVA
2                                         MISHA
  4                    2                    MASHA
    6                    4                    PASHA
      8                    6                    DASHA
        10                   8                    GOSHA
          12                   10                   KESHA

11 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.12.2021, 21:37
    #40121482
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Перемудрил. Все что нужно это пронумеровать строки плоской таблицы + LATERAL/CROSS APPLY:

Код: 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.
INSERT
  INTO HIERARCHICAL
  WITH T AS (
             SELECT  ROWNUM * 1000 BASE_ID,
                     T.*
               FROM  TEST T
            )
  SELECT  T.BASE_ID + L.LVL,
          NULLIF(T.BASE_ID + L.LVL - 1,T.BASE_ID) PID,
          L.NAME
    FROM  T,
          LATERAL(
                  SELECT  LEVEL LVL,
                          CASE LEVEL
                            WHEN 1 THEN T.NAME1
                            WHEN 2 THEN T.NAME2
                            WHEN 3 THEN T.NAME3
                            WHEN 4 THEN T.NAME4
                            WHEN 5 THEN T.NAME5
                            ELSE NAME6
                          END NAME
                    FROM  DUAL
                    CONNECT BY LEVEL <= 6
                 ) L
    WHERE NAME IS NOT NULL
/

10 rows created.

SQL> SELECT  LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  2          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  3          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  4    FROM  HIERARCHICAL
  5    START WITH PID IS NULL
  6    CONNECT BY PID = PRIOR ID
  7  /

ID                   PID                  NAME
-------------------- -------------------- --------------------
1001                                      VANIA
  1002                 1001                 PETYA
    1003                 1002                 SASHA
      1004                 1003                 PASHA
2001                                      MISHA
  2002                 2001                 MASHA
    2003                 2002                 PASHA
      2004                 2003                 DASHA
        2005                 2004                 GOSHA
          2006                 2005                 KESHA

10 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.12.2021, 23:09
    #40121490
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SY
Перемудрил.
Код: plsql
1.
INSERT

Недо-
Для сохранения id-шников нужен merge по пути.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 01:24
    #40121511
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Elic

Для сохранения id-шников нужен merge по пути.


Не понял. Ты имеешь ввиду строки плоской таблицы связаны и Вася во всех строках плоской таблицы это один и тот-же Вася?

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 01:56
    #40121513
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
А, ты имеешь ввиду "Мне нужны ID для дальнейшего использования, а они каждый раз при Insert будут (могут быть) разные". Это решается добавлением уникального поля к плоской таблице вместо ROWNUM:

Код: 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.
71.
72.
73.
74.
DROP TABLE TEST PURGE
/
CREATE TABLE TEST
  AS
     SELECT  1 HIERARCHY_ID,
             'VANIA' NAME1,
             'PETYA' NAME2,
             'SASHA' NAME3,
             'PASHA' NAME4,
             NULL  NAME5,
             NULL    NAME6
       FROM  DUAL
    UNION ALL
     SELECT  2 HIERARCHY_ID,
             'MISHA' NAME1,
             'MASHA' NAME2,
             'PASHA' NAME3,
             'DASHA' NAME4,
             'GOSHA' NAME5,
             'KESHA' NAME6
       FROM  DUAL
/
TRUNCATE TABLE HIERARCHICAL
/
INSERT
  INTO HIERARCHICAL
  SELECT  T.HIERARCHY_ID * 1000 + L.LVL - 1 ID,
          CASE
            WHEN L.LVL > 1 THEN T.HIERARCHY_ID * 1000 + L.LVL - 2
          END PID,
          L.NAME
    FROM  TEST T,
          LATERAL(
                  SELECT  LEVEL LVL,
                          CASE LEVEL
                            WHEN 1 THEN T.NAME1
                            WHEN 2 THEN T.NAME2
                            WHEN 3 THEN T.NAME3
                            WHEN 4 THEN T.NAME4
                            WHEN 5 THEN T.NAME5
                            ELSE NAME6
                          END NAME
                    FROM  DUAL
                    CONNECT BY LEVEL <= 6
                 ) L
    WHERE NAME IS NOT NULL
/

10 rows created.

SQL> SELECT  LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  2          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  3          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  4    FROM  HIERARCHICAL
  5    START WITH PID IS NULL
  6    CONNECT BY PID = PRIOR ID
  7  /

ID                   PID                  NAME
-------------------- -------------------- --------------------
1000                                      VANIA
  1001                 1000                 PETYA
    1002                 1001                 SASHA
      1003                 1002                 PASHA
2000                                      MISHA
  2001                 2000                 MASHA
    2002                 2001                 PASHA
      2003                 2002                 DASHA
        2004                 2003                 GOSHA
          2005                 2004                 KESHA

10 rows selected.

SQL>



Поскольку таблица не может иметь больше 1000 полей иерархия из плоской таблицы не может иметь более 1000 уровней. Так-что HIERARCHY_ID * 1000 до HIERARCHY_ID * 1000 + 999 покрывает все возможные уровни и гарантирует каждый раз при insert ID/PID не будут меняться.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 03:02
    #40121520
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Ну а для оптимизации я бы добавил поле версия в обе таблицы:

Код: 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.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
DROP SEQUENCE HIERARCHY_SEQ
/
DROP SEQUENCE VERSION_SEQ
/
DROP TABLE HIERARCHICAL PURGE
/
DROP TABLE FLAT_HIERARCHY PURGE
/
CREATE SEQUENCE HIERARCHY_SEQ
/
CREATE SEQUENCE VERSION_SEQ
/
CREATE TABLE FLAT_HIERARCHY(
                            HIERARCHY_ID NUMBER NOT NULL,
                            VERSION       NUMBER NOT NULL,
                            NAME1         VARCHAR2(10),
                            NAME2         VARCHAR2(10),
                            NAME3         VARCHAR2(10),
                            NAME4         VARCHAR2(10),
                            NAME5         VARCHAR2(10),
                            NAME6         VARCHAR2(10)
                           )
/
ALTER TABLE FLAT_HIERARCHY
  ADD CONSTRAINT FLAT_HIERARCHY_PK
    PRIMARY KEY(HIERARCHY_ID)
/
ALTER TABLE FLAT_HIERARCHY
  ADD CONSTRAINT FLAT_HIERARCHY_UK1
    UNIQUE(HIERARCHY_ID,VERSION)
/
INSERT
  INTO FLAT_HIERARCHY
  VALUES(
         HIERARCHY_SEQ.NEXTVAL,
         VERSION_SEQ.NEXTVAL,
         'VANIA',
         'PETYA',
         'SASHA',
         'PASHA',
         NULL,
         NULL
        )
/
INSERT
  INTO FLAT_HIERARCHY
  VALUES(
         HIERARCHY_SEQ.NEXTVAL,
         VERSION_SEQ.NEXTVAL,
         'MISHA',
         'MASHA',
         'PASHA',
         'DASHA',
         'GOSHA',
         'KESHA'
        )
/
CREATE TABLE HIERARCHICAL(
                          HIERARCHY_ID NUMBER NOT NULL,
                          VERSION      NUMBER NOT NULL,
                          ID           NUMBER(10) NOT NULL,
                          PID          NUMBER,
                          NAME         VARCHAR2(250) NOT NULL,
                          CONSTRAINT PK_HIERARCHICAL PRIMARY KEY(ID),
                          CONSTRAINT FK1_HIERARCHICAL FOREIGN KEY(PID) REFERENCES HIERARCHICAL(ID),
                          CONSTRAINT FK2_HIERARCHICAL FOREIGN KEY(HIERARCHY_ID,VERSION)
                            REFERENCES FLAT_HIERARCHY(HIERARCHY_ID,VERSION)
                         )
/
INSERT
  INTO HIERARCHICAL
  SELECT  T.HIERARCHY_ID,
          T.VERSION,
          T.HIERARCHY_ID * 1000 + L.LVL - 1 ID,
          CASE
            WHEN L.LVL > 1 THEN T.HIERARCHY_ID * 1000 + L.LVL - 2
          END PID,
          L.NAME
    FROM  FLAT_HIERARCHY T,
          LATERAL(
                  SELECT  LEVEL LVL,
                          CASE LEVEL
                            WHEN 1 THEN T.NAME1
                            WHEN 2 THEN T.NAME2
                            WHEN 3 THEN T.NAME3
                            WHEN 4 THEN T.NAME4
                            WHEN 5 THEN T.NAME5
                            ELSE NAME6
                          END NAME
                    FROM  DUAL
                    CONNECT BY LEVEL <= 6
                 ) L
    WHERE NAME IS NOT NULL
/

10 rows created.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  HIERARCHY_ID,
  2          VERSION,
  3          LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  4          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  5          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  6    FROM  HIERARCHICAL
  7    START WITH PID IS NULL
  8    CONNECT BY PID = PRIOR ID
  9  /

HIERARCHY_ID    VERSION ID                   PID                  NAME
------------ ---------- -------------------- -------------------- --------------------
           1          1 1000                                      VANIA
           1          1   1001                 1000                 PETYA
           1          1     1002                 1001                 SASHA
           1          1       1003                 1002                 PASHA
           2          2 2000                                      MISHA
           2          2   2001                 2000                 MASHA
           2          2     2002                 2001                 PASHA
           2          2       2003                 2002                 DASHA
           2          2         2004                 2003                 GOSHA
           2          2           2005                 2004                 KESHA

10 rows selected.

SQL>




Добавим новую иерархию и изменим существующую:

Код: 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.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
INSERT
  INTO FLAT_HIERARCHY
  VALUES(
         HIERARCHY_SEQ.NEXTVAL,
         VERSION_SEQ.NEXTVAL,
         'KOLYA',
         'POLYA',
         'SONYA',
         'MITYA',
         'VITYA',
         NULL
        )
/
DELETE HIERARCHICAL
  WHERE HIERARCHY_ID = 2
/
UPDATE FLAT_HIERARCHY
   SET VERSION = VERSION_SEQ.NEXTVAL,
       NAME3 = 'ANYA',
       NAME5 = 'MANYA'
 WHERE HIERARCHY_ID = 2
/
SELECT  *
  FROM  FLAT_HIERARCHY
/

HIERARCHY_ID    VERSION NAME1      NAME2      NAME3      NAME4      NAME5      NAME6
------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
           1          1 VANIA      PETYA      SASHA      PASHA
           2          8 MISHA      MASHA      ANYA       DASHA      MANYA      KESHA
           5          7 KOLYA      POLYA      SONYA      MITYA      VITYA

INSERT
  INTO HIERARCHICAL
  SELECT  T.HIERARCHY_ID,
          T.VERSION,
          T.HIERARCHY_ID * 1000 + L.LVL - 1 ID,
          CASE
            WHEN L.LVL > 1 THEN T.HIERARCHY_ID * 1000 + L.LVL - 2
          END PID,
          L.NAME
    FROM  FLAT_HIERARCHY T,
          LATERAL(
                  SELECT  LEVEL LVL,
                          CASE LEVEL
                            WHEN 1 THEN T.NAME1
                            WHEN 2 THEN T.NAME2
                            WHEN 3 THEN T.NAME3
                            WHEN 4 THEN T.NAME4
                            WHEN 5 THEN T.NAME5
                            ELSE NAME6
                          END NAME
                    FROM  DUAL
                    CONNECT BY LEVEL <= 6
                 ) L
    WHERE NAME IS NOT NULL
      AND T.HIERARCHY_ID NOT IN (
                                 SELECT  H.HIERARCHY_ID
                                   FROM  HIERARCHICAL H
                                )
/

11 rows created.

SQL> SELECT  HIERARCHY_ID,
  2          VERSION,
  3          LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  4          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  5          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  6    FROM  HIERARCHICAL
  7    START WITH PID IS NULL
  8    CONNECT BY PID = PRIOR ID
  9  /


HIERARCHY_ID    VERSION ID                   PID                  NAME
------------ ---------- -------------------- -------------------- --------------------
           1          1 1000                                      VANIA
           1          1   1001                 1000                 PETYA
           1          1     1002                 1001                 SASHA
           1          1       1003                 1002                 PASHA
           2          4 2000                                      MISHA
           2          4   2001                 2000                 MASHA
           2          4     2002                 2001                 ANYA
           2          4       2003                 2002                 DASHA
           2          4         2004                 2003                 MANYA
           2          4           2005                 2004                 KESHA
           3          3 3000                                      KOLYA
           3          3   3001                 3000                 POLYA
           3          3     3002                 3001                 SONYA
           3          3       3003                 3002                 MITYA
           3          3         3004                 3003                 VITYA

15 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 03:38
    #40121523
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiW,

А зачем вообще все это нужно? Вы же понимаете, что хоть плоская таблица и менее "гибкая", но более эффективная для получения родительских и дочерних узлов?
Ну, и может проще вьюху тогда уж сделать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 08:04
    #40121544
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Спасибо всем за ответы! Не ожидал такого участия. :) Буду читать и анализировать.
SY
Не уверен что понял. Понял так: Name{N} пустой значит Name{N + 1} пустой и Name{N + 2} пустой и.т.д.

Например если Name3 пусто, то Name4 будет связано с родителем Name2.
Но нужно признать, что, на сегодня, только одно поле Name3 может быть пустое. Все остальные поля должны быть заполнены.
Есть другие нюанси, которые я упустил. :(
Количество записей 1-2 млн.
Обновлять нужно каждый день.
Sayan MalakshinovНу, и может проще вьюху тогда уж сделать?
Нужно работать с деревом, а вьюха не будет достаточно быстрой для отображения в интерфейсе. По крайней мере, у меня ничего не получилось.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 08:28
    #40121547
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiW,

Иерархическая таблица обычно в разы, а иногда и на порядки хуже для работы с деревьями, например при указанной высоте 6 для получения предков получится минимум 12 логических чтений (6 раз индекс + 6 к таблице), а с плоской всего 2.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 08:30
    #40121548
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
При большом объёме обычно как раз оптимизируют путем создания плоских мвьюх по иерархическим таблицам
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 11:50
    #40121603
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Sayan Malakshinov,

Мне нужно идти от родителя. Для иерархический это просто.
Код: plsql
1.
SELECT * from HIERARCHICAL WHERE PID = :PID

это, обычно, 5-20 записей.
Для плоской нужно указывать всех родителей.
Код: plsql
1.
SELECT * FROM table WHERE Name1 =:Name1 and Name2=:Name2 AND ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 14:11
    #40121669
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiWДля плоской нужно указывать всех родителей.

У вас реально задача на поиск всех поддеревьев с заданным именем?

Даже с указанием всех родителей и индексом на каждое поле это всё равно
получается быстрее и проще, чем в иерархии.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 14:41
    #40121684
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiW
Sayan Malakshinov,

Мне нужно идти от родителя. Для иерархический это просто.
Код: plsql
1.
SELECT * from HIERARCHICAL WHERE PID = :PID


это, обычно, 5-20 записей.
Для плоской нужно указывать всех родителей.
Код: plsql
1.
SELECT * FROM table WHERE Name1 =:Name1 and Name2=:Name2 AND ...

у вас какая-то странная и непонятная плоская таблица... обычно делается что-то типа такого:
ID, LVL, NAME, LVL0, LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7, LVL8, LVL9
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 16:07
    #40121717
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Dimitry Sibiryakov
Даже с указанием всех родителей и индексом на каждое поле это всё равно
получается быстрее и проще, чем в иерархии.
Это утверждение для меня полностью непонятно. :(
Я даже возразить ничего не могу, так как для меня очевидно, что иерархическая таблица во много раз проще и быстрее. Для нее нужно указать только PID, а для плоской всех родителей в текстовом виде. Реально у меня быстродействие на несколько порядков больше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 16:08
    #40121718
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Sayan Malakshinov
у вас какая-то странная и непонятная плоская таблица
Какая есть. Другой нет и не предвидеться. :(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 16:09
    #40121719
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
SergiiWЯ даже возразить ничего не могу, так как для меня очевидно, что иерархическая
таблица во много раз проще и быстрее.

А для некоторых очевидно, что солнце вращается вокруг плоской земли...
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 16:16
    #40121721
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Никанор Кузьмич
Я бы сделал примерно так: unpivot + rownumber(), потом отсортировать по какому-нибудь старому полю + NameN, rownumber() становится id, rownumber() предыдущей строки использовать как parent_id. Вроде относительно просто.
Спасибо, хорошая идея! Но есть но, мне нужны постоянные ID при обновлении таблицы. :( Если бы не постоянные ID - однозначно бы воспользовался идеей, мне она понравилась.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 16:24
    #40121724
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Elic
RTFM MERGE
Как по мне два Insert и Delete проще для понимания и обслуживания чем один MERGE или я не понял.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.12.2021, 16:35
    #40121726
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Плоскую таблицу преобразовать в Иерархическую
Никанор Кузьмич
 
Что-то типа такого нужно?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with t as (select 1 id, 'Vasya' name1, 'Petya' name2, '' name3 from dual union all
           select 2, 'Masha', 'Katya', 'Olya' from dual)
select id, lvl, val, new_pk, lag(new_pk, 1, null) over (partition by id order by id, lvl) parent_id
  from (select id, lvl, val, row_number() over (order by id, lvl) new_pk
          from (select id, lvl, val
                  from t
               unpivot (val for lvl in (name1 as 1, name2 as 2, name3 as 3))));

        ID        LVL VAL       NEW_PK  PARENT_ID
---------- ---------- ----- ---------- ----------
         1          1 Vasya          1           
         1          2 Petya          2          1
         2          1 Masha          3           
         2          2 Katya          4          3
         2          3 Olya           5          4

Еще раз спасибо, именно то что нужно было бы, если бы не не постоянные ID. Но идея великолепная!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Плоскую таблицу преобразовать в Иерархическую / 25 сообщений из 42, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 1023ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1879660]:
 0ms
get settings:
 20ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 66ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 605ms
get tp. blocked users:
 2ms
others: 298ms
total:  1023ms
x
x
Закрыть


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