Реляционное деление на 1С: найти элементы, у которых табличная часть совпадает с эталоном

в 17:16, , рубрики: 1С, null, запросы 1с, левое соединение, пустое множество, реляционное деление, собеседование программиста, сравнение множеств, табличная часть, язык запросов 1с

Реляционное деление на 1С: найти элементы, у которых табличная часть совпадает с эталоном

Одна компания даёт кандидатам на должность программиста 1С тестовое задание из шести коротких задач и предупреждает: «Задачи довольно простые, но практика показывает, что 80% кандидатов понимают их неправильно или, как минимум, учитывают не все случаи».

Разберу третью задачу из этого задания вместе с настоящим решением одного кандидата. Это классическая задача сравнения множеств, её знает любой, кто писал SQL. Интересно в ней то, что почти все решения проверяют только половину условия, а рабочее решение кандидата ломается на пустом множестве — случае, о котором никто не думает, пока он не сломает запрос.

Условие

Есть справочник «ТырымПырым» с табличной частью ТЧ, в которой есть реквизиты «Контрагент» и «Договор». Есть эталонная таблица значений такой же структуры, как ТЧ. Нужно написать запрос, который найдет элементы справочника, у которых ТЧ в точности совпадает с эталонной таблицей значений. Строки в обеих таблицах уникальны.

Пример. Эталон из двух строк: «Ромашка / Договор 1», «Лютик / Договор 2». Элементы справочника:

  • А — Ромашка / Договор 1, Лютик / Договор 2 — подходит.

  • Б — те же строки в другом порядке — подходит.

  • В — только Ромашка / Договор 1 — не хватает строки.

  • Г — обе строки эталона плюс Василёк / Договор 3 — лишняя строка.

  • Д — Ромашка / Договор 1, Лютик / Договор 5 — строк столько же, но одна другая.

  • Е — табличная часть пустая.

Запрос должен вернуть А и Б. А если эталон пустой, запрос должен вернуть Е: пустая табличная часть в точности совпадает с пустой таблицей.

На этих шести элементах видна каждая ошибка.

Что нужно знать о запросах 1С

Для тех, кто пришёл из SQL, несколько особенностей.

  • Табличная часть в запросе — отдельная таблица Справочник.ТырымПырым.ТЧ с полями Ссылка (владелец), НомерСтроки и реквизитами. У элемента без строк в ней нет ни одной записи — найти его можно, только начав с Справочник.ТырымПырым и присоединив ТЧ левым соединением.

  • Таблицу значений можно передать параметром, но выбирать из неё можно только в запросе с ПОМЕСТИТЬ, то есть сначала положить во временную таблицу. Колонки должны быть типизированы.

  • Пакетный запрос — несколько запросов через ;, каждый следующий видит временные таблицы предыдущих.

  • Агрегатные функции пропускают NULL. КОЛИЧЕСТВО(Поле) считает только значения, отличные от NULL, а СУММА от одних NULL возвращает NULL, а не ноль. Эта деталь решит задачу.

  • Операций EXCEPT и INTERSECT в языке запросов 1С нет. Классический приём «A минус B пусто и B минус A пусто» приходится собирать из соединений.

Две таблицы совпадают, если выполнены оба условия

Каждая строка эталона есть в ТЧ, и каждая строка ТЧ есть в эталоне. Почти все ошибки — проверка только одного из них.

Ошибка 1. «Все строки эталона нашлись»

ВЫБРАТЬ
	ТЧ.Ссылка
ИЗ
	Справочник.ТырымПырым.ТЧ КАК ТЧ
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Эталон КАК Эталон
		ПО ТЧ.Контрагент = Эталон.Контрагент
			И ТЧ.Договор = Эталон.Договор
СГРУППИРОВАТЬ ПО
	ТЧ.Ссылка
ИМЕЮЩИЕ
	КОЛИЧЕСТВО(*) = &КоличествоСтрокЭталона

Вернёт А, Б и Г. У Г есть обе строки эталона, а лишнюю строку «Василёк» внутреннее соединение просто отбросило. Г содержит всё нужное и ещё немного — как чемодан, собранный «на всякий случай».

Ошибка 2. «Нет строк, которых нет в эталоне»

Обратная проверка: выбрать элементы без единой строки вне эталона. Вернёт А, Б, В, а если начинать со справочника — ещё и Е. У В нет лишних строк, у него просто не хватает одной. У пустого Е лишних строк нет тем более.

Ошибка 3. Сравнение по номерам строк

... ПО ТЧ.НомерСтроки = Эталон.НомерСтроки
	И ТЧ.Контрагент = Эталон.Контрагент
	И ТЧ.Договор = Эталон.Договор

Не найдёт Б: строки те же, но в другом порядке. В условии про порядок ничего нет, а строки названы уникальными — значит, речь о совпадении наборов. Если вы считаете, что порядок важен, это допущение нужно записать явно.

Ошибка 4. Правильная идея, но от табличной части

Сравнить количества правильно, но собрать запрос от Справочник.ТырымПырым.ТЧ, а не от справочника. На эталоне из примера всё работает. При пустом эталоне правильный ответ — Е, а у него нет ни одной записи в таблице ТЧ. Запрос не увидит его в принципе.

Ошибка 5. Не запрос

Перебрать элементы в цикле и сравнить каждую ТЧ в коде. Работать будет, но условие просит запрос. Да и на десяти тысячах элементов цикл успеет надоесть.

Решение кандидата

Кандидат написал пакет из семи запросов (комментарии и имена его, включая опечатку):

ВЫБРАТЬ
	Т.Контрагент КАК Контрагент,
	Т.Договор КАК Договор
ПОМЕСТИТЬ Таблица
ИЗ
	&Таблица КАК Т
;
// ссылки, у которых хотя бы одна запись ТЧ совпадает с эталонной таблицей
ВЫБРАТЬ РАЗЛИЧНЫЕ
	Т1.Ссылка КАК Ссылка
ПОМЕСТИТЬ Кандидаты
ИЗ
	Справочник.ТырымПырым.ТЧ КАК Т1
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таблица КАК Т2
		ПО Т1.Контрагент = Т2.Контрагент
			И Т1.Договор = Т2.Договор
;
// дополняем эталонную таблицу ссылкой из таблицы кандидатов
ВЫБРАТЬ
	Т1.Ссылка КАК Ссылка,
	Т2.Контрагент КАК Контрагент,
	Т2.Договор КАК Договор
ПОМЕСТИТЬ ДополненнаяТаблиа
ИЗ
	Кандидаты КАК Т1,
	Таблица КАК Т2
;
// ссылки, в ТЧ которых есть строки, которых нет в эталонной таблице
ВЫБРАТЬ РАЗЛИЧНЫЕ
	Т1.Ссылка КАК Ссылка
ПОМЕСТИТЬ ОтсевСлева
ИЗ
	Справочник.ТырымПырым.ТЧ КАК Т1
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Кандидаты КАК Т3
		ПО Т1.Ссылка = Т3.Ссылка
		ЛЕВОЕ СОЕДИНЕНИЕ ДополненнаяТаблиа КАК Т2
		ПО Т1.Контрагент = Т2.Контрагент
			И Т1.Договор = Т2.Договор
			И Т1.Ссылка = Т2.Ссылка
ГДЕ
	Т2.Ссылка ЕСТЬ NULL
;
// ссылки, в ТЧ которых нет строк, присутствующих в эталонной таблице
ВЫБРАТЬ РАЗЛИЧНЫЕ
	Т2.Ссылка КАК Ссылка
ПОМЕСТИТЬ ОтсевСправа
ИЗ
	Справочник.ТырымПырым.ТЧ КАК Т1
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Кандидаты КАК Т3
		ПО Т1.Ссылка = Т3.Ссылка
		ПРАВОЕ СОЕДИНЕНИЕ ДополненнаяТаблиа КАК Т2
		ПО Т1.Контрагент = Т2.Контрагент
			И Т1.Договор = Т2.Договор
			И Т1.Ссылка = Т2.Ссылка
ГДЕ
	Т1.Ссылка ЕСТЬ NULL
;
// удаляем из таблицы кандидатов ссылки,
// в ТЧ которых есть строки, которых нет в эталонной таблице
ВЫБРАТЬ
	Т1.Ссылка КАК Ссылка
ПОМЕСТИТЬ _Кандидаты
ИЗ
	Кандидаты КАК Т1
		ЛЕВОЕ СОЕДИНЕНИЕ ОтсевСлева КАК Т2
		ПО Т1.Ссылка = Т2.Ссылка
ГДЕ
	Т2.Ссылка ЕСТЬ NULL
;
// удаляем из новой таблицы кандидатов ссылки,
// в ТЧ которых нет строк, присутствующих в эталонной таблице
ВЫБРАТЬ
	Т1.Ссылка КАК Ссылка
ИЗ
	_Кандидаты КАК Т1
		ЛЕВОЕ СОЕДИНЕНИЕ ОтсевСправа КАК Т3
		ПО Т1.Ссылка = Т3.Ссылка
ГДЕ
	Т3.Ссылка ЕСТЬ NULL

По шагам:

  1. Кандидаты — элементы, у которых совпала хотя бы одна строка с эталоном.

  2. ДополненнаяТаблица — каждому кандидату приписан полный эталон: «вот какие строки у него должны быть».

  3. ОтсевСлева — кандидаты с лишней строкой.

  4. ОтсевСправа — кандидаты, которым не хватает строки.

  5. Результат — кандидаты минус оба отсева.

По сути это и есть EXCEPT в обе стороны, собранный из левых и правых соединений. Главное кандидат сделал: проверил оба условия, ошибки 1 и 2 обошёл, порядок строк не учитывается. На эталоне из примера запрос работает.

Чего не хватает:

  1. Пустой эталон. Кандидаты отбираются внутренним соединением с эталоном. Эталон пустой — кандидатов нет — запрос ничего не возвращает, хотя должен вернуть Е. Та же ошибка 4, только пришедшая с другой стороны.

  2. Декартово произведение. ДополненнаяТаблица — все кандидаты, умноженные на все строки эталона. Кандидатом становится любой элемент, у которого совпала хоть одна строка. Эталон на 100 строк с популярным контрагентом, который встречается в 10 000 элементах, — и во временной таблице миллион строк.

  3. Объём. Семь запросов и три уровня отсева. Логика верная, но проверяющему придётся потратить время, чтобы в этом убедиться.

Засчитают ли? Скорее да — с вопросом «А что вернёт ваш запрос, если эталон пустой?». Хороший ответ: «Ничего, и это ошибка — вот как исправить».

Отдельно интересно, почему кандидат не заметил ошибку. Он сделал генератор тестовых данных: в каждый элемент добавляется минимум две строки. Пустых табличных частей в его базе не было вообще, и ошибка с пустым эталоном не могла проявиться. Тестовые данные строились с теми же предположениями, что и решение.

Решение одним запросом: сравнить два количества

Идея: элемент подходит, если одновременно

  • строк в его ТЧ столько же, сколько в эталоне, и

  • совпавших с эталоном строк тоже столько же.

Почему этого достаточно. Строки уникальны, поэтому совпавшие строки — это пересечение ТЧ и эталона. Если пересечение по размеру равно и ТЧ, и эталону, оно совпадает с каждым из них — значит, ТЧ и эталон одинаковы.

На примере (в эталоне 2 строки):

  • А: в ТЧ 2, совпало 2 — да

  • Б: 2 и 2 — да

  • В: 1 и 1 — нет

  • Г: 3 и 2 — нет

  • Д: 2 и 1 — нет

  • Е: 0 и 0 — нет

При пустом эталоне нужны оба нуля — подходит только Е.

// Возвращает элементы справочника ТырымПырым, у которых табличная часть ТЧ
// совпадает с эталоном как набор строк: порядок строк не учитывается.
//
// Параметры:
//  Эталон - ТаблицаЗначений - типизированные колонки Контрагент и Договор, строки уникальны
//
// Возвращаемое значение:
//  Массив из СправочникСсылка.ТырымПырым
//
Функция ЭлементыСТакойЖеТЧ(Эталон) Экспорт

	Запрос = Новый Запрос;
	Запрос.УстановитьПараметр("Эталон", Эталон);
	Запрос.УстановитьПараметр("КоличествоСтрокЭталона", Эталон.Количество());
	Запрос.Текст =
	"ВЫБРАТЬ
	|	Т.Контрагент КАК Контрагент,
	|	Т.Договор КАК Договор
	|ПОМЕСТИТЬ Эталон
	|ИЗ
	|	&Эталон КАК Т
	|;
	|
	|////////////////////////////////////////////////////////////////////////////////
	|ВЫБРАТЬ
	|	Спр.Ссылка КАК Ссылка
	|ИЗ
	|	Справочник.ТырымПырым КАК Спр
	|		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ТырымПырым.ТЧ КАК ТЧ
	|		ПО (ТЧ.Ссылка = Спр.Ссылка)
	|		ЛЕВОЕ СОЕДИНЕНИЕ Эталон КАК Эталон
	|		ПО (Эталон.Контрагент = ТЧ.Контрагент)
	|			И (Эталон.Договор = ТЧ.Договор)
	|
	|СГРУППИРОВАТЬ ПО
	|	Спр.Ссылка
	|
	|ИМЕЮЩИЕ
	|	КОЛИЧЕСТВО(ТЧ.НомерСтроки) = &КоличествоСтрокЭталона
	|	И КОЛИЧЕСТВО(Эталон.Контрагент) = &КоличествоСтрокЭталона";

	Возврат Запрос.Выполнить().Выгрузить().ВыгрузитьКолонку("Ссылка");

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

Как это работает:

  • Запрос начинается со справочника. Элемент без строк после левого соединения даёт одну строку, где все поля ТЧ равны NULL, — и не теряется.

  • КОЛИЧЕСТВО(ТЧ.НомерСтроки) — сколько строк в ТЧ. Для пустой ТЧ это 0: NULL не считается.

  • КОЛИЧЕСТВО(Эталон.Контрагент) — сколько строк ТЧ нашли пару в эталоне. Строки без пары дают NULL и не считаются.

  • Одно сравнение с &КоличествоСтрокЭталона отсекает и лишние строки, и недостающие.

Почему КОЛИЧЕСТВО, а не СУММА(1) по совпавшим строкам: у Е при пустом эталоне сумма по одним NULL даст NULL, а NULL = 0 — не «истина». Элемент потерялся бы ровно в том случае, ради которого всё затевалось.

По сравнению с решением кандидата: один запрос вместо шести после помещения эталона, каждая строка ТЧ соединяется с эталоном один раз, пустой эталон обрабатывается без особого случая.

Где решение опирается на условие

Уникальность строк. Представьте ТЧ из двух одинаковых строк «Ромашка / Договор 1» и эталон «Ромашка / Договор 1», «Лютик / Договор 2». Строк в ТЧ — 2, совпавших — тоже 2 (обе нашли пару), и запрос скажет «совпадает», хотя Лютика в ТЧ нет. Условие гарантирует уникальность, но на собеседовании стоит сказать вслух, что решение на неё опирается. Без этой гарантии придётся сравнивать сгруппированные строки с количеством повторов.

Вопросы к заказчику, которые показывают, что вы заметили неоднозначности:

  • Порядок строк. Я считаю ТЧ набором. Если порядок важен — в соединение добавляется НомерСтроки, в эталон — колонка с номером.

  • Помеченные на удаление. Запрос их не отбрасывает. Если не нужны — ГДЕ НЕ Спр.ПометкаУдаления.

  • Группы справочника. В иерархическом справочнике у групп обычно нет строк ТЧ. При пустом эталоне они попадут в результат — их стоит отсечь условием НЕ Спр.ЭтоГруппа.

Краевые случаи

  • Эталон совпадает с ТЧ одного элемента — этот элемент.

  • Те же строки в другом порядке — найден.

  • ТЧ — подмножество эталона — не найден.

  • ТЧ — надмножество эталона — не найден.

  • Строк столько же, одна отличается — не найден.

  • Несколько элементов с одинаковой ТЧ — найдены все.

  • Пустой эталон — элементы с пустой ТЧ.

  • Пустой эталон, элементов с пустой ТЧ нет — пустой результат без ошибок.

  • Справочник пустой — пустой результат.

Если вы проводите собеседования

Задача хороша тем, что ответы раскладываются по уровням. Ошибка 1 или 2 — кандидат не сформулировал, что значит «совпадает». Решение как у кандидата — понимает суть, но не думает о пустом множестве и объёме данных. Один запрос от справочника, оговорка про уникальность и сам названный пустой эталон — то, что нужно.

Лучший вопрос к любому решению этой задачи: «А если эталон пустой?»

Автор: Maraton1185

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js