gifts2017

Соединение таблиц значений (Таблица Значений) по ключевым полям

Опубликовал Александр Перевислый (sashapere) в раздел Программирование - Универсальные функции

Иногда при разработке больших отчётов и различных шахматок приходится соединять таблицы из разных выборок по определенному полю, например, выборка по продаже и выборка по производству - объединяем по номенклатуре, в запросе, бывает, это делать неудобно, т.к. в таблицу можно присваивать различные структуры для расшифровок.

Сделал функцию, которая объединяет 2 таблицы в одну, наподобие полного соединения в запросе.

 

ТЗ1 - первая Таблица значений

ТЗ2 - вторая Таблица значений

KEYт_str - колонки, по которым происходит объединение, пример:  "номенклатура" или "номенклатура,ЕдИзм"

функция ПОЛНОЕ_СОЕДИНЕНИЕ(ТЗ1,ТЗ2,KEYт_str)

ТЗ = новый ТаблицаЗначений;

//---------------------------------------------
// разбор ключевых колонок в масив
//---------------------------------------------
str = "" + KEYт_str; // Копирование исходной строки
KEYт_mas = новый Массив;
str = СтрЗаменить(str,",",Символы.ПС);
Для N=1 по СтрЧислоСтрок(str) Цикл
name = СтрПолучитьСтроку(str,N);
KEYт_mas.Добавить(name);
КонецЦикла;
//---------------------------------------------


//---- проверка на наличие ключевых колонок ---
Для каждого KEYт из KEYт_mas Цикл
Если (ТЗ1.Колонки.Найти(KEYт)=неопределено) 
ИЛИ (ТЗ2.Колонки.Найти(KEYт)=неопределено) Тогда
Сообщить("нет ключевой колонки"); 
возврат неопределено; 
КонецЕсли;
КонецЦикла;
//---------------------------------------------


//--- Формируем новую ТЗ из 2х таблиц ---------
Для каждого К Из ТЗ1.Колонки Цикл
// из первой ТЗ берём все колонки
ТЗ.Колонки.Добавить(К.Имя);
КонецЦикла; 
Для каждого К Из ТЗ2.Колонки Цикл
// из второй все кроме ключевой
Если KEYт_mas.Найти(К.Имя) <> неопределено тогда 
Продолжить;
КонецЕсли;
ТЗ.Колонки.Добавить(К.Имя);
КонецЦикла; 
//---------------------------------------------



//----------------------------------------------------
// Заполняем строчки ключей которых НЕТ в ТЗ2
//----------------------------------------------------
Для каждого стр1 Из ТЗ1 Цикл

// Формируем структуру поиска
Отбор = Новый Структура;
Для каждого KEYт из KEYт_mas Цикл
Отбор.Вставить(KEYт,стр1[KEYт]);
КонецЦикла;

Если ТЗ2.НайтиСтроки(Отбор).Количество() = 0 тогда
стр = ТЗ.Добавить();
// копируем все значения из строчки ТЗ1
Для каждого К Из ТЗ1.Колонки Цикл
стр[К.Имя] = стр1[К.Имя];
КонецЦикла;
КонецЕсли;

КонецЦикла; 
//----------------------------------------------------



//----------------------------------------------------
// Заполняем строчки ключи которых ЕСТЬ в ТЗ2
//----------------------------------------------------
Для каждого стр1 Из ТЗ1 Цикл

// Формируем структуру поиска
Отбор = Новый Структура;
Для каждого KEYт из KEYт_mas Цикл
Отбор.Вставить(KEYт,стр1[KEYт]);
КонецЦикла;

НайденныеСтроки = ТЗ2.НайтиСтроки(Отбор);

Если НайденныеСтроки.Количество() > 0 тогда
Для каждого стр2 Из НайденныеСтроки Цикл
//заполняем все колонки
стр = ТЗ.Добавить();
// из ТЗ1
Для каждого К Из ТЗ1.Колонки Цикл
стр[К.Имя] = стр1[К.Имя];
КонецЦикла;
// из ТЗ2
Для каждого К Из ТЗ2.Колонки Цикл
стр[К.Имя] = стр2[К.Имя];
КонецЦикла;
КонецЦикла;

КонецЕсли;

КонецЦикла;
//----------------------------------------------------



//----------------------------------------------------
// Заполняем строчки из ТЗ2 ключей которых НЕТ в ТЗ1
//----------------------------------------------------
Для каждого стр2 Из ТЗ2 Цикл
// Формируем структуру поиска
Отбор = Новый Структура;
Для каждого KEYт из KEYт_mas Цикл
Отбор.Вставить(KEYт,стр2[KEYт]);
КонецЦикла;

Если ТЗ1.НайтиСтроки(Отбор).Количество() = 0 тогда
стр = ТЗ.Добавить();
// копируем все значения из строчки ТЗ1
Для каждого К Из ТЗ2.Колонки Цикл
стр[К.Имя] = стр2[К.Имя];
КонецЦикла;
КонецЕсли;

КонецЦикла;
//----------------------------------------------------

возврат ТЗ; 

КонецФункции

 

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Сергей Ожерельев (Поручик) 05.07.11 23:50
>>>> приходится соединять таблицы из разных выборок по определенному полю
>>>> в запросе бывает это делать неудобно

Чего я не понимаю или где я отстал от этой жизни? Пакетные запросы с временными таблицами ужо не канают?
2. Роман Романов (romansun) 06.07.11 00:21
>>в запросе бывает это делать неудобно т.к. в Таблицу можно присваивать различные структуры для расшифровок.

хм, а что это значит? Имеются ввиду вложенные таблицы, структуры и т.п.? В таких случаях, да, временные таблицы сносят все внутренности..

Но, имхо, всё равно лучше соединиться-таки временными таблицами, а потом уже загрузить как-нить колонку со структурами (по ключу какому-нить или ловко воспользоваться "ЗагрузитьКолонку", если после левого джоина строки не раздублятся в первой ТЗ... или еще как-нить)

А тут более десятка циклов. Что будет, если ТЗ тысяч по 10-15 будет?


[а так ваще, всякие ТЗ в ТЗ - чур меня :)... хотя иногда и бывает удобно, типа Структуры в ТЗ, но как раз для случаев, когда ожидаются запросы к таким таблицам, лучше внутренние структуры не делать]
3. креек фелер (GoodWinSpr) 06.07.11 12:43
>>в запросе бывает это делать неудобно

Неудобно спать стоя, а это нужно делать в запросе в ВТ
4. Ийон Тихий (cool.vlad4) 06.07.11 12:46
(3) :D я думаю просто это из той серии, сам придумал проблему, сам ее решил...в общем (0) неплохо бы увидеть реальный пример применения и чем лучше ВТ
5. bulpi bulpi (bulpi) 06.07.11 16:22
Временные таблицы, временные таблицы....
Нас и здесь неплохо кормят :). Тьфу, и с таблицами значений неплохо.
6. Артур Аюханов (artbear) 06.07.11 16:52
(0) 1. Код совсем неоптимальный :( на средних и больших таблицах совсем долго работать будет.
а) Зачем постоянно создавать и заполнять структуру отбора?
б) в двух циклах по ТЗ1 идет одно и то же заполнение :( дважды :(
в) зачем вообще нужен второй цикл по ТЗ1 ?
и т.п.
2. В Коде нет некоторых простейших проверок - например, если в обеих таблицах есть колонки с одинаковым именем, которые не являются ключевыми, будет выдано исключение :( - Колонки.Добавить()
и т.п.

ЗЫ юзай Разукрашку - она есть в рекомендациях при создании/изменении публикаций
7. Артур Аюханов (artbear) 06.07.11 16:59
+ (6)
1. г) Вместо тормозного кода (интерпретатор все-таки)
Для каждого К Из ТЗ1.Колонки Цикл // или Тз2
стр[К.Имя] = стр1[К.Имя];
КонецЦикла;
можно юзать очень быстрое
ЗаполнитьЗначенияСвойств(стр, стр1) - все равно одинаковых колонок не будет
8. Сергей Ожерельев (Поручик) 06.07.11 20:22
>>> юзай Разукрашку

Лучше юзай Закрашку
9. Александр Перевислый (sashapere) 07.07.11 13:29
artbear , по поводу ЗАполнитьЗначенияСвойств, замечания приняты ;)

по поводу скорости, ТЗ работает довольно быстро, проверял даж на таблицах 2+2_тысячи. :D

по поводу ВТ)) попробуйте в неё выбрать таблицу из структур)) 8-)
10. Александр Перевислый (sashapere) 07.07.11 13:30
для тогож эта функция и делалась штобы можно было обьеденить таблицы без ВТ, просто как альтернативный вариант
11. Артур Аюханов (artbear) 07.07.11 16:09
(9) А другие замечания? нафига вообще второй цикл по ТЗ1 и повторное заполнение данных :(
Жаль, что ты не осознал, значит, не искупил :( минус будет висеть :)
sashapere; +1 Ответить
12. Александр Перевислый (sashapere) 07.07.11 18:14
Так, объясняю зачем нужен второй цикл по ТЗ1 :!:

Второй Цикл По ТЗ1 нужен для того штобы перенести те строчки из ТЗ2 которым нет пары в ТЗ1 ;)
13. Артур Аюханов (artbear) 07.07.11 19:31
Народ, поминусуйте еще, автор не осознал :(
(12) еще подумай,плиз.
14. Роман Романов (romansun) 07.07.11 21:08
[злым шепотом] афтар, скажи что осознал, одумался, ненарошно, исправишься и искупишь.. ))

если кто возьмётся оптимизировать, предлагаю сразу дополнить функционал левым и внутренним соединением тож :)
15. Ярослав Радкевич (WKBAPKA) 08.07.11 00:14
а меня вот заинтересовало, а ШАХМОТКИ это чо?
16. Вадим М. (provadyuga) 08.07.11 08:09
Неплохо было бы, если бы разработка была в виде работающего примера.
17. Александр Медведев (anig99) 08.07.11 08:18
18. Александр Перевислый (sashapere) 08.07.11 09:33
artbear: Первый обход по ТЗ1 нужен для того штоб перенести в новую таблицу те строки из ТЗ1 которым нет пары в ТЗ2

Второй Обход по ТЗ1 нужен для того штобы перенести строки которым есть парыв ТЗ2.
Можно было конешно запихнуть в один цикл, но сделал специально раздельно для наглядного разделения выполняемыхопрераций.

нрод прежде чем минусовать разберитесь в коде!.

Если што-то не так укажыте конкретно строчку и вопрос.
19. Артур Аюханов (artbear) 08.07.11 17:19
(18) Для такого метода важна производительность, а разделение операций организовать очень легко - у тебя должен быть всего ОДИН цикл,
а код будет выполняться в разных ветках в зависимости
НайденныеСтроки = ТЗ2.НайтиСтроки(Отбор);
Если НайденныеСтроки.Количество() > 0 тогда // или = 0
можешь сам сделать исправления и посмотреть, насколько быстрее стал выполняться код :)
20. Ийон Тихий (cool.vlad4) 08.07.11 17:25
На самом деле я подобную функцию делал...кода под рукой нет, а на 1С давно не программил, но смысл в том, что идет итерация по одной таблице и ищутся строки, как-то так


Для j = 0 По iterator Цикл

Стр = ТЗ1[j];

ЗаполнитьЗначенияСвойств(Отбор,Стр);


Если i < n Тогда

Строки = ТЗ2.НайтиСтроки(Отбор);
col = Строки.Количество();

Если col > 0 Тогда


А дальше С помощью ЗаполнитьЗначенияСвойств заполнялись строки
22. г. Казань Рустем Гумеров (Rustig) 08.07.11 17:47
(0) пишите, пожалуйста, русским транскриптом на языке 1С (в качестве предыстории напишите, откуда у вас привычка писать англ. символами?) (9) в качестве примера у вас "номенклатура", поэтому приведите пожалуйста задачу из жизни, из реальной практики клиентов, пример со структурами ...
и честно признайтесь - это задача из жизни клиентов или для зачета в универе? :)
23. Владимир (Strange Device) 10.07.11 14:06
Вообще временные таблицы сохраняются на жесткий диск, поэтому скорость такого соединения оставляет желать лучшего, фактически это тот же самый семерочный подход, когда для того, чтобы к таблице значений можно было применить семерочный запрос она сохранялась в отдельный документ. Это не есть гуд. Поэтому возможность сделать соединение непосредственно в оперативке должно бы ускорять процедуру довольно существенно, хотя последнее утверждение стоит, конечно, потестировать...
24. Игорь Исхаков (Ish_2) 10.07.11 15:35
(0) Отрицательные примеры тоже полезны.
Текущая статья является хорошей иллюстрацией к теме : не пробуйте повторить !
Даже разжевывать не хочется , почему такие процедуры лючше выполнять запросом с использованием временных таблиц.
(23)
Вообще временные таблицы сохраняются на жесткий диск, поэтому скорость такого соединения оставляет желать лучшего, фактически это тот же самый семерочный подход


Жуть.
25. Ийон Тихий (cool.vlad4) 11.07.11 09:27
(23) откуда у вас такие "каббалистические" сведения? :D
26. Владимир (Strange Device) 11.07.11 19:13
Не поверишь, от самой 1С. В курсах по обучению 1С это написано...
27. Владимир (Strange Device) 11.07.11 19:16
+(26) Это именно та причина, по которой 1С советует думать о производительности перед использованием временных таблиц, народ же решил, что вся эта красота даром и использует эту технологию где ни попадя...
28. Ийон Тихий (cool.vlad4) 11.07.11 19:23
(26) пруфлинки...на курсы хотя бы...про tempdb вам рассказывали?....да и кэширование не тоже , что и хранение...
29. Владимир (Strange Device) 11.07.11 19:32
Дословно, привожу, что пишет по этому поводу 1С: "... необходимо понимать, что в любом случае - работа с ввременной таблицей подразумевает ее сохранение в базе данных (пусть и на время), чтение из нее (т.е. из базы данных) и уничтожение ее при уничтожении или закрытии экземпляра менеджера временных таблиц (например, при окончании процедуры). Данные действия, конечно же, при своем исполнении потребляют ресурсы, т.е. в общем случае потенциально снижают быстродействие алгоритма."
30. Роман Романов (romansun) 11.07.11 20:31
а вы попробуйте сделать какую либо сложную обработку кодом по ТЗ и тоже самое запросом - получите интересные цифры для сравнения :)

у меня есть конкретные примеры, когда переписывание каскада корявых циклов с удалениями, добавлениями и прочими манипуляциями строк в ТЗ на запрос с ВТ приводило к увеличения производительности с полутора часов(!) до 40-50 сек на таблицах >25'000 строк


1С всё верно пишет, другое дело, что с они не с ТЗ сравнивают :) . Они говорят про запросы с ВТ и без ВТ. Имхо, это всё-таки относится к t-sql в большей степени, на том уровне разработки - да, есть проблемы. Но с 1С сравнивать это нельзя.

Я тут приводил примеры в одной теме как раз про использованием ВТ самими 1С

БП 1.6. Отчет "РегистрРасчетАмортизацииОсновныхСредств". Запрос без ВТ на 250 строк.
БП 2.0, тот же отчет, временных таблиц 22 шт, запрос уже на 550 строк


(28) возможно ноги в курсах растут вот отсюда, к примеру http://articles.org.ru/cfaq/index.php?qid=2547, параграф "Ограничения временных таблиц"
31. Ийон Тихий (cool.vlad4) 12.07.11 10:12
(29) и где упоминания про жесткий диск?
Вообще временные таблицы сохраняются на жесткий диск, поэтому скорость такого соединения оставляет желать лучшего,
(30) насчет курсов это была издевка, приписываемая вышеуказанная фраза(про жесткие диски, а в особенности про низкую скорость работы, это означает все блин табу на временные таблицы?) не могла быть в курсах
PS Кстати по сравнению с чем, скорость оставляет желать лучшего...
32. Роман Романов (romansun) 13.07.11 21:54
(31)
ну вообще да, хотелось бы чуть больше цитаты с курсов, а то непонятно в каком контексте всё это у них

на мой взгляд, курсы сравнивают запросы с и без ВТ

а Strange Device сравнивает циклы по ТЗ и запросы с ВТ

(29)
кстати, ведь ТЗ - это тоже ВТ в итоге ))). Просто когда мы создаем ТЗ и начинаем циклом по ней елозить, это всё в sql преобразуется не в 1* большой запрос (как в случае запроса с ВТ или без них), а в N маленьких запросиков на каждую итерацию цикла. Особенно начинается тормоз, когда идёт вытаскивание полей в теле цикла по точке "Ссылка.Реквизит11.Реквизит22" - ведь это каждый раз делаются запросики с левым соединением на каждую итерацию.

А что лучше, 1* большой запрос или N маленьких - вопрос риторический.

Поэтому и рекомендуют - если работаете циклами по ТЗ, подготавливайте заранее данные, чтоб в теле цикла не вытаскивать по точкам, не получать константы и пр. А еще лучше - с ТЗ не работайте, а делайте запросы с ВТ или без них - всё равно однозначно быстрее :)

* - условно один,.. скорее всего, запросов будет несколько (надо смотреть профайлер sql) - всякие подготовительные скидки-перескидки, "перевод" чисто 1С-ных запросных конструкций на sql и пр... Но в любом случае, это не N запросов
33. Владимир (Strange Device) 14.07.11 13:21
(32) Касательно фразы "а Strange Device сравнивает циклы по ТЗ и запросы с ВТ", уважаемый romansun, считает, что цикл по уже сформированной ТЗ будет работать существенно медленнее, чем цикл по уже исполненному запросу? Посему сравнивать цикл один с другим не имеет никакого смысла. Замечу кстати, что некорректно можно работать не только с ТЗ вытаскивая из нее данные по типу "Ссылка.Реквизит11.Реквизит22", но и с результатами запроса, причем все это никоим образом не зависит от достоинств и недостатков ТЗ или запроса, а определяется только и исключительно квалификацией программиста...
34. Роман Романов (romansun) 14.07.11 14:36
не совсем так, циклы по ТЗ (ТЗ неважно как полученные) работают одинаково, да.

Я имею ввиду, что если у нас на исходных позициях две одинаковые ТЗ, в случае А мы работаем по ней циклом, в случае Б загоняем её в ВТ, делаем запрос и выгружаем её опять в ТЗ.

Так вот вариант Б будет работать быстрее. Т.е. в варианте Б вообще нет 1С-ных циклов.
35. Владимир (Strange Device) 17.07.11 22:44
Последний вариант тоже зависит от целой кучи параметров, так что не стоит огульно утверждать, что вариант 2 будет работать быстрее. Это опять таки зависит от величины таблиц, сложности обработки ТЗ и сложности запроса и т.д., в любом случае компетентный программист сначала поглядит на конкретный случай, а потом уже будет применять наиболее с его т.зрения быстрое решение...
36. Александр Перевислый (sashapere) 18.07.11 14:39
romansun пишет:

(31)

ну вообще да, хотелось бы чуть больше цитаты с курсов, а то непонятно в каком контексте всё это у них



на мой взгляд, курсы сравнивают запросы с и без ВТ



а Strange Device сравнивает циклы по ТЗ и запросы с ВТ



(29)

кстати, ведь ТЗ - это тоже ВТ в итоге ))). Просто когда мы создаем ТЗ и начинаем циклом по ней елозить, это всё в sql преобразуется не в 1* большой запрос (как в случае запроса с ВТ или без них), а в N маленьких запросиков на каждую итерацию цикла. Особенно начинается тормоз, когда идёт вытаскивание полей в теле цикла по точке "Ссылка.Реквизит11.Реквизит22" - ведь это каждый раз делаются запросики с левым соединением на каждую итерацию.



А что лучше, 1* большой запрос или N маленьких - вопрос риторический.



Поэтому и рекомендуют - если работаете циклами по ТЗ, подготавливайте заранее данные, чтоб в теле цикла не вытаскивать по точкам, не получать константы и пр. А еще лучше - с ТЗ не работайте, а делайте запросы с ВТ или без них - всё равно однозначно быстрее



* - условно один,.. скорее всего, запросов будет несколько (надо смотреть профайлер sql) - всякие подготовительные скидки-перескидки, "перевод" чисто 1С-ных запросных конструкций на sql и пр... Но в любом случае, это не N запросов




ТЗ- это не ВТ, ТЗ работает очень быстро,также как и (масивы,ТЗ,структуры,Соответствия), ТЗ всегда висит в оперативке, при обрашении к полям ТЗ неделается ни каких маленьких запросов, мыже данные не из базы по ссылким вытягиваем.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа