ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Фиксированные ID для каждого уровня иерархии
21 сообщений из 21, страница 1 из 1
Фиксированные ID для каждого уровня иерархии
    #40004220
Фотография Gena928
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gena928
Участник
Всем привет,
есть таблица где сидит иерархия для куба аналитики:
Код: sql
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.
drop table if exists #tbl_test;
create table #tbl_test (
       row_id int identity (1,1) primary key,
       lvl1 nvarchar(100),
       lvl2 nvarchar(100),
       lvl3 nvarchar(100),
       lvl4 nvarchar(100))
 
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Молотки', N'Импортные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Молотки', N'Отечественные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Пилы', N'Отечественные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'собственные', N'гавно и палки', N'собственное');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Свежеспиленные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Сушеные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Хрен знает откуда взятые');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'елки', N'Елки зеленые');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'елки', N'Сухостой');
 

select row_id, lvl1, lvl2, lvl3, lvl4,
             DENSE_RANK() over (order by lvl1) as lvl1_id,
             DENSE_RANK() over (order by lvl1, lvl2) as lvl2_id,
             DENSE_RANK() over (order by lvl1, lvl2, lvl3) as lvl3_id,
             DENSE_RANK() over (order by lvl1, lvl2, lvl3, lvl4) as lvl4_id
from #tbl_test
order by lvl1, lvl2, lvl3, lvl4;



Задача - сделать так, чтобы у каждого названия (lvl) был свой ключик и он был уникальный внутри иерархии.
Например
- для lvl2 ключик это комбинация lvl1+lvl2
- для lvl3 ключик это комбинация lvl1+lvl2+lvl3
...
- для lvl8 ключик это комбинация lvl1+lvl2+lvl3+lvl4+lvl5+lvl6+lvl7+lvl8
В реальной таблице 8 уровней и около 2 тысяч строк.

Текущий код это хорошо делает с помощью DENSE_RANK()
Код: sql
1.
2.
3.
4.
5.
6.
7.
select row_id, lvl1, lvl2, lvl3, lvl4,
             DENSE_RANK() over (order by lvl1) as lvl1_id,
             DENSE_RANK() over (order by lvl1, lvl2) as lvl2_id,
             DENSE_RANK() over (order by lvl1, lvl2, lvl3) as lvl3_id,
             DENSE_RANK() over (order by lvl1, lvl2, lvl3, lvl4) as lvl4_id
from #tbl_test
order by lvl1, lvl2, lvl3, lvl4;



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

Задачка:
как можно зафиксировать эти ключики каким-то автоматическим образом. Чтобы при добавлении новых записей они не генерились заново все, а добавлялись новые? Нужен какой-то простой кодик, чтобы это делать. В таблице около 2 тысяч записей и 8 уровней (читай колонок с названиями). У меня ничего простого не получается....

спасибо
...
Рейтинг: 0 / 0
30.09.2020, 11:38
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004225
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Gena928
Задача - сделать так, чтобы у каждого названия (lvl) был свой ключик и он был уникальный внутри иерархии.
Например
- для lvl2 ключик это комбинация lvl1+lvl2
- для lvl3 ключик это комбинация lvl1+lvl2+lvl3
Заведите себе справочник (id primary key, lvl, name, unique (lvl, name))
...
Рейтинг: 0 / 0
30.09.2020, 12:06
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004229
Фотография Gena928
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gena928
Участник
invm,

не совсем понял.
Вот прилетает строка, где 8 колонок для иерархии. Как поможет справочник?
...
Рейтинг: 0 / 0
30.09.2020, 12:17
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004230
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Gena928
Как поможет справочник?
Написать запрос и получить значения ключей не вариант?
...
Рейтинг: 0 / 0
30.09.2020, 12:19
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004236
Фотография Gena928
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gena928
Участник
invm,

решение в студию.
вы предлагаете развернуть таблицу из горизонтальной в вертикальную и потом проверять каждую прилетевшую колонку на предмет есть/нет в справочнике?
...
Рейтинг: 0 / 0
30.09.2020, 12:38
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004238
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Gena928
вы предлагаете развернуть таблицу из горизонтальной в вертикальную и потом проверять каждую прилетевшую колонку на предмет есть/нет в справочнике?
Нет.
Я предлагаю N соединений со справочником, а не разворачивать таблицу.
...
Рейтинг: 0 / 0
30.09.2020, 12:45
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004242
Фотография Gena928
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gena928
Участник
invm,

а по каким полям джойнится?
у исходника на 8 уровне ключом является lvl1+lvl2+lvl3+lvl4+lvl5+lvl6+lvl7+lvl8
...
Рейтинг: 0 / 0
30.09.2020, 12:54
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004249
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create table dbo.NamesCatalog
(
 id int identity not null primary key nonclustered,
 [level] int not null,
 [name] nvarchar(100) not null,
 unique clustered ([level], [name])
);

select
 ...
from
 MyTable t join
 dbo.NamesCatalog l1 on l1.[level] = 1 and l1.[name] = t.lvl1 join
 dbo.NamesCatalog l1 on l2.[level] = 2 and l2.[name] = t.lvl2 join
 ...
 dbo.NamesCatalog lN on lN.[level] = N and lN.[name] = t.lvlN


Справочник можно поддерживать триггером на MyTable
...
Рейтинг: 0 / 0
30.09.2020, 13:19
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004263
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
invm
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create table dbo.NamesCatalog
(
 id int identity not null primary key nonclustered,
 [level] int not null,
 [name] nvarchar(100) not null,
 unique clustered ([level], [name])
);

select
 ...
from
 MyTable t join
 dbo.NamesCatalog l1 on l1.[level] = 1 and l1.[name] = t.lvl1 join
 dbo.NamesCatalog l1 on l2.[level] = 2 and l2.[name] = t.lvl2 join
 ...
 dbo.NamesCatalog lN on lN.[level] = N and lN.[name] = t.lvlN



Справочник можно поддерживать триггером на MyTable


Проблема в том, что N'Отечественные' N'Молотки' и N'Отечественные' N'Пилы' - это разные категории товаров.
...
Рейтинг: 0 / 0
30.09.2020, 13:46
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004266
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
msLex
Проблема в том, что N'Отечественные' N'Молотки' и N'Отечественные' N'Пилы' - это разные категории товаров.
Именно для этого в справочнике есть столбец level
...
Рейтинг: 0 / 0
30.09.2020, 13:52
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004268
Фотография Gena928
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gena928
Участник
invm,

спасибо.
я на всякий случай в таблицу еще добавлю название родителя. Так будет уникальной комбинация [level], [name], [родитель].

Код: sql
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.
 
-- Будет уникальный индекс на [level], [name], [item_parent_name]
drop table if exists #tbl_dic;
create table #tbl_dic (
       item_id int identity(1,1) primary key,
       item_level int,
       item_name nvarchar(100),
       item_parent_name nvarchar(100));

-- Заполнение справочников      
insert into #tbl_dic (item_level, item_name, item_parent_name)
select distinct 1 as iteim_level, main.lvl1 as item_name, '' as item_parent_name
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl1 = dic.item_name and dic.item_level = 1
where (dic.item_id is null);
 
insert into #tbl_dic (item_level, item_name, item_parent_name)
select distinct 2 as iteim_level, main.lvl2 as item_name, main.lvl1 as item_parent_name
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl2 = dic.item_name and dic.item_level = 2 and dic.item_parent_name = main.lvl1
where (dic.item_id is null);
 
insert into #tbl_dic (item_level, item_name, item_parent_name)
select distinct 3 as iteim_level, main.lvl3 as item_name, main.lvl2 as item_parent_name
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl3 = dic.item_name and dic.item_level = 3 and dic.item_parent_name = main.lvl2
where (dic.item_id is null);
 
insert into #tbl_dic (item_level, item_name, item_parent_name)
select distinct 4 as iteim_level, main.lvl4 as item_name, main.lvl3 as item_parent_name
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl4 = dic.item_name and dic.item_level = 4 and dic.item_parent_name = main.lvl3
where (dic.item_id is null);
 
 
-- Что там??
select *
from #tbl_dic
 
select *
from #tbl_test
 
 
select main.*,
             dic.item_id as lvl1_id,
             dic2.item_id as lvl2_id,
             dic3.item_id as lvl3_id,
             dic4.item_id as lvl4_id
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl1 = dic.item_name and dic.item_level = 1 and dic.item_parent_name = ''
       left outer join #tbl_dic as dic2 on main.lvl2 = dic2.item_name and dic2.item_level = 2 and dic2.item_parent_name = main.lvl1
       left outer join #tbl_dic as dic3 on main.lvl3 = dic3.item_name and dic3.item_level = 3 and dic3.item_parent_name = main.lvl2
       left outer join #tbl_dic as dic4 on main.lvl4 = dic4.item_name and dic4.item_level = 4 and dic4.item_parent_name = main.lvl3
...
Рейтинг: 0 / 0
30.09.2020, 13:55
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004270
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
invm
msLex
Проблема в том, что N'Отечественные' N'Молотки' и N'Отечественные' N'Пилы' - это разные категории товаров.
Именно для этого в справочнике есть столбец level


Обе N'Отечественные' находятся на одном уровне - 4, но к ним разные пути.
...
Рейтинг: 0 / 0
30.09.2020, 13:56
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004271
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
Gena928
invm,

спасибо.
я на всякий случай в таблицу еще добавлю название родителя. Так будет уникальной комбинация [level], [name], [родитель].

Код: sql
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.
 
-- Будет уникальный индекс на [level], [name], [item_parent_name]
drop table if exists #tbl_dic;
create table #tbl_dic (
       item_id int identity(1,1) primary key,
       item_level int,
       item_name nvarchar(100),
       item_parent_name nvarchar(100));

-- Заполнение справочников      
insert into #tbl_dic (item_level, item_name, item_parent_name)
select distinct 1 as iteim_level, main.lvl1 as item_name, '' as item_parent_name
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl1 = dic.item_name and dic.item_level = 1
where (dic.item_id is null);
 
insert into #tbl_dic (item_level, item_name, item_parent_name)
select distinct 2 as iteim_level, main.lvl2 as item_name, main.lvl1 as item_parent_name
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl2 = dic.item_name and dic.item_level = 2 and dic.item_parent_name = main.lvl1
where (dic.item_id is null);
 
insert into #tbl_dic (item_level, item_name, item_parent_name)
select distinct 3 as iteim_level, main.lvl3 as item_name, main.lvl2 as item_parent_name
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl3 = dic.item_name and dic.item_level = 3 and dic.item_parent_name = main.lvl2
where (dic.item_id is null);
 
insert into #tbl_dic (item_level, item_name, item_parent_name)
select distinct 4 as iteim_level, main.lvl4 as item_name, main.lvl3 as item_parent_name
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl4 = dic.item_name and dic.item_level = 4 and dic.item_parent_name = main.lvl3
where (dic.item_id is null);
 
 
-- Что там??
select *
from #tbl_dic
 
select *
from #tbl_test
 
 
select main.*,
             dic.item_id as lvl1_id,
             dic2.item_id as lvl2_id,
             dic3.item_id as lvl3_id,
             dic4.item_id as lvl4_id
from #tbl_test as main
       left outer join #tbl_dic as dic on main.lvl1 = dic.item_name and dic.item_level = 1 and dic.item_parent_name = ''
       left outer join #tbl_dic as dic2 on main.lvl2 = dic2.item_name and dic2.item_level = 2 and dic2.item_parent_name = main.lvl1
       left outer join #tbl_dic as dic3 on main.lvl3 = dic3.item_name and dic3.item_level = 3 and dic3.item_parent_name = main.lvl2
       left outer join #tbl_dic as dic4 on main.lvl4 = dic4.item_name and dic4.item_level = 4 and dic4.item_parent_name = main.lvl3



Как только у вас появятся "Молотки"->"Отечественные"->"С деревянной ручкой" и "Топоры"->"Отечественные"->"С деревянной ручкой" одного родителя не хватит.
...
Рейтинг: 0 / 0
30.09.2020, 13:59
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004274
Фотография Gena928
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gena928
Участник
msLex

Как только у вас появятся "Молотки"->"Отечественные"->"С деревянной ручкой" и "Топоры"->"Отечественные"->"С деревянной ручкой" одного родителя не хватит.


Это да.
Тогда надо вводить понятие id родителя и в момент вставки исходных данных искать его. Что в общем-то жопа.
...
Рейтинг: 0 / 0
30.09.2020, 14:14
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004277
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
msLex
Обе N'Отечественные' находятся на одном уровне - 4, но к ним разные пути.
Ну если в таком разрезе, тогда как-то так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create table dbo.NamesCatalog
(
 id int identity not null primary key nonclustered,
 [path] nvarchar(4000) not null,
 [name] nvarchar(100) not null,
 unique clustered ([path], [name])
);

select
 ...
from
 MyTable t join
 dbo.NamesCatalog l1 on l1.[path] = '[0]' and l1.[name] = t.lvl1 join
 dbo.NamesCatalog l1 on l2.[path] = l1.[path] + quotename(cast(l1.id as nvarchar(100))) and l2.[name] = t.lvl2 join
 ...
 dbo.NamesCatalog lN on lN.[path] = [lN-1].[path] +  + quotename(cast(l[N-1].id as nvarchar(100))) and lN.[name] = t.lvlN
...
Рейтинг: 0 / 0
30.09.2020, 14:29
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004282
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
Gena928
msLex

Как только у вас появятся "Молотки"->"Отечественные"->"С деревянной ручкой" и "Топоры"->"Отечественные"->"С деревянной ручкой" одного родителя не хватит.


Это да.
Тогда надо вводить понятие id родителя и в момент вставки исходных данных искать его. Что в общем-то жопа.


Что приводит вас к классической древовидной сируктуре id, parent_id, name
...
Рейтинг: 0 / 0
30.09.2020, 14:36
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004284
Фотография Gena928
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gena928
Участник
msLex
Что приводит вас к классической древовидной сируктуре id, parent_id, name


да это коню понятно. Тут вопрос в том, как сделать эту структуру из горизонтальной таблицы, чтобы это все нормально выглядело и работало.
...
Рейтинг: 0 / 0
30.09.2020, 14:40
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004286
Фотография Gena928
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gena928
Участник
invm
msLex
Обе N'Отечественные' находятся на одном уровне - 4, но к ним разные пути.
Ну если в таком разрезе, тогда как-то так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create table dbo.NamesCatalog
(
 id int identity not null primary key nonclustered,
 [path] nvarchar(4000) not null,
 [name] nvarchar(100) not null,
 unique clustered ([path], [name])
);

select
 ...
from
 MyTable t join
 dbo.NamesCatalog l1 on l1.[path] = '[0]' and l1.[name] = t.lvl1 join
 dbo.NamesCatalog l1 on l2.[path] = l1.[path] + quotename(cast(l1.id as nvarchar(100))) and l2.[name] = t.lvl2 join
 ...
 dbo.NamesCatalog lN on lN.[path] = [lN-1].[path] +  + quotename(cast(l[N-1].id as nvarchar(100))) and lN.[name] = t.lvlN



можно образец вставки данных в справочник?
...
Рейтинг: 0 / 0
30.09.2020, 14:42
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004335
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Gena928
можно образец вставки данных в справочник?
 
Код: sql
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.
129.
130.
131.
132.
133.
134.
135.
136.
137.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

drop table if exists dbo.tbl_test;

create table dbo.tbl_test (
       row_id int identity (1,1) primary key,
       lvl1 nvarchar(100),
       lvl2 nvarchar(100),
       lvl3 nvarchar(100),
       lvl4 nvarchar(100));

drop table if exists dbo.NamesCatalog;

create table dbo.NamesCatalog
(
 id int identity not null primary key nonclustered,
 [path] nvarchar(4000) not null,
 [name] nvarchar(100) not null,
 unique clustered ([path], [name])
);
go

create trigger dbo.tr__tbl_tes__insert__update
on dbo.tbl_test
after insert, update
as
begin
 set nocount on;

 if not exists(select 1 from inserted)
  return;

 with s as
 (
  select distinct
   '[0]' as [path],
   i.lvl1 as [name]
  from
   inserted i
 )
 merge into dbo.NamesCatalog t
 using s on s.[path] = t.[path] and s.[name] = t.[name]
 when not matched then
  insert
   ([path], [name])
  values
   (s.[path], s.[name]);

 with s as
 (
  select distinct
   '[0]' + quotename(cast(l1.id as nvarchar(100))) as [path],
   i.lvl2 as [name]
  from
   inserted i join
   dbo.NamesCatalog l1 on l1.[path] = '[0]' and l1.[name] = i.lvl1
 )
 merge into dbo.NamesCatalog t
 using s on s.[path] = t.[path] and s.[name] = t.[name]
 when not matched then
  insert
   ([path], [name])
  values
   (s.[path], s.[name]);

 with s as
 (
  select
   l1.[path] + quotename(cast(l1.id as nvarchar(100))) + quotename(cast(l2.id as nvarchar(100))) as [path],
   i.lvl3 as [name]
  from
   inserted i join
   dbo.NamesCatalog l1 on l1.[path] = '[0]' and l1.[name] = i.lvl1 join
   dbo.NamesCatalog l2 on l2.[path] = l1.[path] + quotename(cast(l1.id as nvarchar(100))) and l2.[name] = i.lvl2
 )
 merge into dbo.NamesCatalog t
 using s on s.[path] = t.[path] and s.[name] = t.[name]
 when not matched then
  insert
   ([path], [name])
  values
   (s.[path], s.[name]);

 with s as
 (
  select --distinct
   l1.[path] + quotename(cast(l1.id as nvarchar(100))) + quotename(cast(l2.id as nvarchar(100))) + quotename(cast(l3.id as nvarchar(100))) as [path],
   i.lvl4 as [name]
  from
   inserted i join
   dbo.NamesCatalog l1 on l1.[path] = '[0]' and l1.[name] = i.lvl1 join
   dbo.NamesCatalog l2 on l2.[path] = l1.[path] + quotename(cast(l1.id as nvarchar(100))) and l2.[name] = i.lvl2 join
   dbo.NamesCatalog l3 on l3.[path] = l1.[path] + quotename(cast(l1.id as nvarchar(100))) + quotename(cast(l2.id as nvarchar(100))) and l3.[name] = i.lvl3
 )
 merge into dbo.NamesCatalog t
 using s on s.[path] = t.[path] and s.[name] = t.[name]
 when not matched then
  insert
   ([path], [name])
  values
   (s.[path], s.[name]);

end;
go

insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Молотки', N'Импортные');
insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Молотки', N'Отечественные');
insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Пилы', N'Отечественные');
insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'собственные', N'гавно и палки', N'собственное');
insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Свежеспиленные');
insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Сушеные');
insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Хрен знает откуда взятые');
insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'елки', N'Елки зеленые');
insert into dbo.tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'елки', N'Сухостой');
go

create or alter view dbo.v__tbl_test
as
select
 t.row_id,
 t.lvl1, l1.id as lvl1_id,
 t.lvl2, l2.id as lvl2_id,
 t.lvl3, l3.id as lvl3_id,
 t.lvl4, l4.id as lvl4_id
from
 dbo.tbl_test t join
 dbo.NamesCatalog l1 on l1.path = '[0]' and l1.name = t.lvl1 join
 dbo.NamesCatalog l2 on l2.[path] = l1.[path] + quotename(cast(l1.id as nvarchar(100))) and l2.[name] = t.lvl2 join
 dbo.NamesCatalog l3 on l3.[path] = l1.[path] + quotename(cast(l1.id as nvarchar(100))) + quotename(cast(l2.id as nvarchar(100))) and l3.[name] = t.lvl3 join
 dbo.NamesCatalog l4 on l4.[path] = l1.[path] + quotename(cast(l1.id as nvarchar(100))) + quotename(cast(l2.id as nvarchar(100))) + quotename(cast(l3.id as nvarchar(100))) and l4.[name] = t.lvl4
go

select * from dbo.v__tbl_test;
select * from dbo.NamesCatalog;
go

...
Рейтинг: 0 / 0
30.09.2020, 18:33
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004374
Alekzandero
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alekzandero
Гость
Можно использовать хэши строк:
Код: sql
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.
drop table if exists #tbl_test;
create table #tbl_test (
       row_id int identity (1,1) primary key,
       lvl1 nvarchar(100),
       lvl2 nvarchar(100),
       lvl3 nvarchar(100),
       lvl4 nvarchar(100))
 
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Молотки', N'Импортные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Молотки', N'Отечественные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'закупленные', N'Пилы', N'Отечественные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'собственные', N'гавно и палки', N'собственное');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Свежеспиленные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Сушеные');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'дубы', N'Хрен знает откуда взятые');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'елки', N'Елки зеленые');
insert into #tbl_test (lvl1, lvl2, lvl3, lvl4) values (N'Материалы', N'спиленные в лесу', N'елки', N'Сухостой');
 
 
-- Генерируем постоянные ключи для существующих элементов
declare @account_master_key table( [account_uid] uniqueidentifier primary key, [account_id] int identity(1,1) )
 
insert into @account_master_key( [account_uid] )
select distinct cast( hashbytes( 'MD5', lvl1 ) as uniqueidentifier ) from #tbl_test
union all
select distinct cast( hashbytes( 'MD5', concat( lvl1, lvl2 ) ) as uniqueidentifier ) from #tbl_test
union all
select distinct cast( hashbytes( 'MD5', concat( lvl1, lvl2, lvl3 ) ) as uniqueidentifier ) from #tbl_test
union all
select distinct cast( hashbytes( 'MD5', concat( lvl1, lvl2, lvl3, lvl4 ) ) as uniqueidentifier ) from #tbl_test
 
 
-- Постоянные ключи по уровням ДО добавления новых элементов
select
       T.*,
       [lvl1_id] = K1.account_id,
       [lvl2_id] = K2.account_id,
       [lvl3_id] = K3.account_id,
       [lvl4_id] = K4.account_id
from
       #tbl_test T
       left join @account_master_key K1 on cast( hashbytes( 'MD5', lvl1 ) as uniqueidentifier ) = K1.account_uid
       left join @account_master_key K2 on cast( hashbytes( 'MD5', concat( lvl1, lvl2 ) ) as uniqueidentifier ) = K2.account_uid
       left join @account_master_key K3 on cast( hashbytes( 'MD5', concat( lvl1, lvl2, lvl3 ) ) as uniqueidentifier ) = K3.account_uid
       left join @account_master_key K4 on cast( hashbytes( 'MD5', concat( lvl1, lvl2, lvl3, lvl4 ) ) as uniqueidentifier ) = K4.account_uid
order by T.lvl1, T.lvl2, T.lvl3, T.lvl4;
 
 
-- Добавляем новые элементы в многоуровневый справочник
declare @added_account table( row_id int, lvl1 nvarchar(100), lvl2 nvarchar(100), lvl3 nvarchar(100), lvl4 nvarchar(100) )
insert into
       #tbl_test (lvl1, lvl2, lvl3, lvl4)
output
       inserted.*
into
       @added_account
values
       ( N'Материалы', N'закупленные', N'Молотки', N'СУШЕНЫЕ' ),
       ( N'Материалы', N'ПРОДАННЫЕ', N'Молотки', N'Отечественные' )
 
-- Дополняем справочник постоянных ключей значениями для новых элементов
;with
       [new_account]
             as
             (
                    select * from @added_account
             ),
       [new_account_uid_set]
             as
             (
                    select [account_uid] = cast( hashbytes( 'MD5', lvl1 ) as uniqueidentifier ) from [new_account]
                    union all
                    select [account_uid] = cast( hashbytes( 'MD5', concat( lvl1, lvl2 ) ) as uniqueidentifier ) from [new_account]
                    union all
                    select [account_uid] = cast( hashbytes( 'MD5', concat( lvl1, lvl2, lvl3 ) ) as uniqueidentifier ) from [new_account]
                    union all
                    select [account_uid] = cast( hashbytes( 'MD5', concat( lvl1, lvl2, lvl3, lvl4 ) ) as uniqueidentifier ) from [new_account]
             )
insert into
       @account_master_key( [account_uid] )
select
       N.[account_uid]
from
       [new_account_uid_set] N
       left join
       @account_master_key K
       on
             N.[account_uid] = K.[account_uid]
where
       K.[account_uid] is null
 
 
-- Постоянные ключи по уровням ПОСЛЕ добавления новых элементов
select
       T.*,
       [lvl1_id] = K1.account_id,
       [lvl2_id] = K2.account_id,
       [lvl3_id] = K3.account_id,
       [lvl4_id] = K4.account_id
from
       #tbl_test T
       left join @account_master_key K1 on cast( hashbytes( 'MD5', lvl1 ) as uniqueidentifier ) = K1.account_uid
       left join @account_master_key K2 on cast( hashbytes( 'MD5', concat( lvl1, lvl2 ) ) as uniqueidentifier ) = K2.account_uid
       left join @account_master_key K3 on cast( hashbytes( 'MD5', concat( lvl1, lvl2, lvl3 ) ) as uniqueidentifier ) = K3.account_uid
       left join @account_master_key K4 on cast( hashbytes( 'MD5', concat( lvl1, lvl2, lvl3, lvl4 ) ) as uniqueidentifier ) = K4.account_uid
order by T.lvl1, T.lvl2, T.lvl3, T.lvl4;
...
Рейтинг: 0 / 0
30.09.2020, 23:12
| Ответить | Цитировать | Написать  
Фиксированные ID для каждого уровня иерархии
    #40004481
Агрох
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрох
Участник
Предполагается ли удаление данных из таблицы?
Предполагается ли редактирование записей с сохранением ID?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
WITH R0 AS (
select row_id, lvl1, lvl2, lvl3, lvl4,
MIN(row_id) OVER(PARTITION BY lvl1) AS MIN_1,
MIN(row_id) OVER(PARTITION BY lvl1, lvl2) AS MIN_12,
MIN(row_id) OVER(PARTITION BY lvl1, lvl2, lvl3) AS MIN_123,
MIN(row_id) OVER(PARTITION BY lvl1, lvl2, lvl3, lvl4) AS MIN_1234
from #tbl_test
)
SELECT  row_id, lvl1, lvl2, lvl3, lvl4,
  DENSE_RANK() over (order by MIN_1) as lvl1_id,
  DENSE_RANK() over (order by MIN_12) as lvl2_id,
  DENSE_RANK() over (order by MIN_123) as lvl3_id,
  DENSE_RANK() over (order by MIN_1234) as lvl4_id
FROM R0
...
Рейтинг: 0 / 0
01.10.2020, 11:21
| Ответить | Цитировать | Написать  
21 сообщений из 21, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Фиксированные ID для каждого уровня иерархии
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=46&tid=1685585]:
 0ms
get settings:
 7ms
get forum list:
 11ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 41ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 102ms
get tp. blocked users:
 1ms
others: 50ms
total:  233ms
созд. / загр.: 233ms
x
x
Закрыть


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