Особенность получения среза последних записей в регистре сведений
В тестовой конфигурации у нас есть периодический регистр сведений «ЦеныНоменклатуры» со следующими исходными данными:
На рисунке представлена также структура метаданных регистра. Как мы видим, регистр содержит измерение «Товар» с типом ссылки на справочник «Товары», а также числовой ресурс «Цена» и реквизит «СтараяЦена».
Допустим, в отчете нам нужно получить срез последних записей для товаров и их цен с условием, что старая цена меньше или равно 50.
Два варианта запроса
Сразу скажу, что рассматривать будем правильный и не правильный варианты.Начнем с последнего. Эту ошибку часто делают начинающие программисты. И так, для отчета был написан следующий запрос:
Запрос = Новый Запрос; Запрос.Текст = "ВЫБРАТЬ | ЦеныНоменклатурыСрезПоследних.Период, | ЦеныНоменклатурыСрезПоследних.Товар, | ЦеныНоменклатурыСрезПоследних.Цена, | ЦеныНоменклатурыСрезПоследних.СтараяЦена |ИЗ | РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних |ГДЕ | ЦеныНоменклатурыСрезПоследних.СтараяЦена = 50";
Обратите внимание на условие в секции «ГДЕ». В этом и заключается главная ошибка! Этот запрос не вернет ни одной записи, и вот почему: при использовании виртуальных таблиц, в нашем случае «СрезПоследних», сначала выполняется выборка данных из базы по условиям, описанным в виртуальной таблице, а затем выполняются действия, описанные в тексте запроса (группировки, условия в секции «ГДЕ», сортировка и т.д.).
Поэтому в нашем примере запрос и не возвращает результат. Сначала он получает срез последних, а уже после устанавливает условие на реквизит «СтараяЦена». Вот так это выглядит на схеме:
Чтобы правильно решить задачу, условие на реквизит «СтараяЦена» нужно перенести в условия виртуальной таблицы. Вот так будет выглядеть правильный текст запроса:
Запрос = Новый Запрос; Запрос.Текст = "ВЫБРАТЬ ЦеныНоменклатурыСрезПоследних.Период, ЦеныНоменклатурыСрезПоследних.Товар, ЦеныНоменклатурыСрезПоследних.Цена, ЦеныНоменклатурыСрезПоследних.СтараяЦена ИЗ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, СтараяЦена = 50) КАК ЦеныНоменклатурыСрезПоследних"
Теперь запрос получит правильные данные, поскольку срез последних цен будет получать уже с учетом условия по реквизиту «СтараяЦена».
Результаты
Следует понимать, что описанное выше относится ко всем случаям использования виртуальных таблиц в запросах (для регистров накопления, регистров бухгалтерии, задач и т.д.).
Отсюда также вытекает главное правило по использованию виртуальных таблиц: «используя виртуальную таблицу обязательно устанавливайте параметры отбора непосредственно в виртуальной таблице, иначе запрос будет получать излишние данные, на которые уже потом будут устанавливаться отборы».
Как работает срез последних в регистрах сведений
Здравствуйте, можете мне объяснить что такое в регистре сведений виртуальные таблицы СрезПервых и СрезПоследних и чем они отличаются?
РегистрСведенийМенеджер.<Имя регистра сведений>.СрезПоследних (InformationRegisterManager.<Имя регистра сведений>.SliceLast)
РегистрСведенийМенеджер. <Имя регистра сведений>(InformationRegisterManager.<Имя регистра сведений>)
СрезПоследних (SliceLast)
Синтаксис:
Тип: Дата; МоментВремени; Граница.
Определяет момент времени, заканчивая которым необходимо выбрать записи.
Если параметр не указан, то будут возвращены значения ресурсов самой последней записи регистра.
(необязательный)
Тип: Структура.
Структура, содержащая отбор по измерениям и реквизитам регистра. Имя ключа структуры должно совпадать с именем измерения регистра, заданного в конфигураторе, а значение элемента структуры — задает отбираемое по данному измерению значение.
Структура дополнительно может иметь элементы, соответствующие разделителям регистра с уровнем разделения НезависимоИСовместно, в состав которых входит регистр. Если такой элемент структуры задан для используемого в сеансе разделителя, значение для этого элемента должно совпадать со значением разделителя, иначе будет вызвано исключение.
Если параметр не указан, то отбор не используется.
Возвращаемое значение:
Тип: ТаблицаЗначений.
Таблица значений, заполненная данными найденных записей регистра сведений.
Описание:
Получает наиболее поздние записи регистра, соответствующие установленным в параметрах метода значениям ключевых полей. Записи подбираются для каждого сочетания из всех имеющихся значений измерений регистра.
Сервер, толстый клиент, внешнее соединение, мобильное приложение(сервер).
Примечание:
Применим только для периодических регистров сведений.
Пример:
МаркетингЦен = РегистрыСведений.МаркетингЦен;
ТекущиеЦеныКонкурентов = МаркетингЦен.СрезПоследних(ТекущаяДата());
РегистрСведенийМенеджер.<Имя регистра сведений>.СрезПервых (InformationRegisterManager.<Имя регистра сведений>.SliceFirst)
РегистрСведенийМенеджер. <Имя регистра сведений>(InformationRegisterManager.<Имя регистра сведений>)
СрезПервых (SliceFirst)
Синтаксис:
Тип: Дата; МоментВремени; Граница.
Определяет момент времени, начиная с которого необходимо выбрать записи.
Если параметр не указан, то будут получены записи без ограничения по времени.
(необязательный)
Тип: Структура.
Структура, содержащая отбор по измерениям и реквизитам регистра. Имя ключа структуры должно совпадать с именем измерения регистра, заданного в конфигураторе, а значение элемента структуры — задает отбираемое по данному измерению значение.
Структура дополнительно может иметь элементы, соответствующие разделителям регистра с уровнем разделения НезависимоИСовместно, в состав которых входит регистр. Если такой элемент структуры задан для используемого в сеансе разделителя, значение для этого элемента должно совпадать со значением разделителя, иначе будет вызвано исключение.
Если параметр не указан, то отбор не используется.
Возвращаемое значение:
Тип: ТаблицаЗначений.
Таблица значений, заполненная данными найденных записей регистра сведений.
Описание:
Получает наиболее ранние записи регистра, соответствующие установленным в параметрах метода значениям ключевых полей. Записи подбираются для каждого сочетания из всех имеющихся значений измерений регистра.
Сервер, толстый клиент, внешнее соединение, мобильное приложение(сервер).
Примечание:
Применим только для периодических регистров сведений.
Пример:
МаркетингЦен = РегистрыСведений.МаркетингЦен;
ОбновленныеСегодняЦены = МаркетингЦен.СрезПервых(ТекущаяДата());
Срез последних в регистре сведений на каждую дату
3. Регистр сведений «Учетная политика организаций»
Периодичность: в пределах года
Режим записи: независимый
Постановка задачи: необходимо в запросе получить список документов «Счет на оплату» и по каждому документу получить «Размер оптовой надбавки», которая хранится в регистре сведений «Учетная политика организаций» в разрезе организаций. Учетная политика может меняться раз в год.
Сложность заключается в том, что при получении среза последних в регистр сведений мы не можем передать список дат. Можно передать только одно значение.
Решение задачи:
Для решения будем использовать следующий запрос:
Пояснение:
1. Получаем список документов «Счет на оплату»
2. Для каждого документа «Счет на оплату» делаем левое соединение с регистром сведений «Учетная политика организаций». При этом в регистре сведений ищем все записи с датой, меньшей или равной дате документа, и организацией, равной организации в документе. Таким образом получаем несколько записей по регистру сведений. Дальше группируем результат и берем максимальную запись по периоду в регистре. Таким образом эмулируем срез последних. На дату каждого документа получаем самую последнюю запись в регистре сведений и помещаем результат во временную таблицу «ВТ_СрезПоследнихНаКаждуюДату_1»:
3. Дальше выбираем данные из временной таблицы «ВТ_СрезПоследнихНаКаждуюДату_1» и снова делаем левое соединение с регистром сведений «Учетная политика организаций» по организации и по периоду, который мы получили во временной таблице «ВТ_СрезПоследнихНаКаждуюДату_1». Таким образом в одном запросе получаем срез последних на каждую дату документа:
Ссылка для скачивания:
Регистры сведений. История одного «велосипеда»
В свое время, когда я достаточно много занимался собеседованиями кандидатов на должность разработчика 1С, я нашел для себя пару-тройку совсем простых вопросов. Послушав ответы на эти вопросы можно было моментально представить себе уровень кандидата.
Один из вопросов звучал безобидно и просто: «Что такое регистр сведений?» Тем удивительнее было наблюдать, как многие буквально спотыкались об него. Впечатление было такое, как будто человек шел, шел и не заметил стеклянную дверь. Тогда-то я впервые задумался о том, что не так с этим изобретением.
На момент разработки принципиальной схемы будущей платформы 1С:Предприятие 8, уже существовала хорошо проработанная и стройная теория реляционных баз данных. Ее основные понятия: записи (кортежи), таблицы (отношения), индексы, ключи были прекрасно «подогнаны» друг к другу. Все логично и ничего лишнего. Одна лишь проблема. Все это было несколько «абстрактно» для простого человека. Поэтому идея «обернуть» понятия таблиц и связей типа один-ко-многим во что-нибудь более близкое простому человеку сработала на «ура». Назвав таблицы с реквизитом типа «Дата» документами, а таблицы без такового справочниками, создатели получили эффект, наверное, больший, чем сами ожидали. В самом деле, каждый легко мог представить себе что такое справочник и что такое документ. Потому что раньше так или иначе имел с ними дело. В одночасье базы данных стали близкими для широкого круга.
Но все это произошло до появления восьмой версии, да и в общем-то и до появления 1С как таковой. Разработчики восьмерки, стремясь всенепременно изобрести что-нибудь свое, выделили в отдельный класс то, что в сущности является всего-лишь одной из возможных опций таблицы (или справочника, если вам так удобнее называть). Всякая запись (кортеж) служит для отображения связей между сущностями. Но характер этих связей может быть разным. Иногда это одна основная сущность и множество сущностей, подчиненных основной. Например, основная сущность «товар», а «наименование», «модель», «размер», «цвет» — это все сущности-атрибуты, подчиненные основной. Еще пример: «контрагент», как основная сущность, и «наименование», «адрес», «телефон», как подчиненные.
В реальном мире есть как отношения подчинения, так и отношения равноправия. Поэтому в одной записи могут оказаться несколько сущностей с равными правами. Например, если мы хотим отражать в базе данных информацию о том, какие товары по каким ценам мы берем у тех или иных поставщиков (контрагентов), тогда у нас появляются записи вида: «товар», «контрагент», «цена». Здесь «цена» несомненно подчиненная сущность. Но подчинена она уже не какой-то одной а сразу двум другим сущностям.
В теории (да и в практике тоже) баз данных это обыгрывается элементарно. Первичный ключ может быть простым, если у нас отношения подчинения (первый случай) или составным, если у нас отношения равноправия (второй случай). Это решение логично, изящно и настолько просто, что попытки найти здесь «свой путь», несомненно должны проходить по разряду «изобретений велосипеда».
Создатели платформы 1С:Предприятие 8 могли бы просто дать возможность указывать явным образом первичный ключ в справочнике, но они пошли по пути «велосипедостроительства» и создали отдельный класс для таблиц с составным первичным ключом, назвав его регистром сведений. Тут можно возразить, что я только что хвалил создание новых классов. Наличие поля типа «дата» тоже, по сути, опция. Почему же тогда нельзя создать новый класс по опции составного первичного ключа? Тут все дело в результате. «Справочники» и «документы» понятны всем практически «с лету». Это я вам, как преподаватель с двадцатилетним стажем, могу со всей ответственностью заявить. А вот «регистры» окутаны туманом. А «регистры сведений» непроницаемым туманом. Нет, человек с некоторым уровнем развития всяко может догадаться, что «регистр сведений» — это некое место, где хранятся «сведения». Но его следующим, законным вопросом будет: а что мы тогда храним в «документах» и «справочниках»? Не «сведения»? А что?
А все дело в том, что «регистры» уже существовали в предыдущей версии. Такое ощущение, что разработчики восьмерки, отважившись на создание нового класса, ровно на этом месте исчерпали весь запас своей креативности. Новый класс они зачем-то отнесли к семейству «регистров», переименовав тру-регистры в «регистры накопления», а неофитов назвав «регистрами сведений». Более того. У тру-регистров (у регистров накопления) поля разделены на «измерения» и «ресурсы». Что по сути есть поля группировки и поля агрегирования (суммирования). У регистров сведений не стали придумывать ничего нового (конечно! зачем плодить сущности без нужды! ха-ха!) и также разделили поля на «измерения» и «ресурсы». Только здесь «измерения» — это и есть составной уникальный ключ, а «ресурсы» это все прочее. Так в одном случае набор «измерений» уникален и в этом его предназначение, а в другом наборы «измерений» повторяются и в этом тоже заключается предназначение, но уже другое. Одни «ресурсы» суммируются и вообще похожи на ресурсы реального мира, которые могут накапливаться, а могут исчерпываться. А другие «ресурсы». ну, мы просто экономим слова, говорят нам разработчики. Кажется, если бы мы решили все запутать, у нас вряд ли получилось бы лучше, чем у них. Но и это еще не все.
Есть такая задача, которая называется «получение последних значений». Например, у вас в базе имеется следующая информация о закупочных ценах:
01.01.2019, ООО Ромашка, Ложка, 150 р.
01.02.2019, ООО Ромашка, Вилка, 120 р.
01.03.2019, ООО Ромашка, Вилка, 125 р.
01.02.2020, ООО Незабудка, Ложка, 165 р.
01.03.2020, ООО Незабудка, Ложка, 167 р.
01.08.2021, ООО Василек, Ложка, 190 р.
01.08.2021, ООО Василек, Вилка, 155 р.
01.08.2021, ООО Одуванчик, Ложка, 191 р.
Тогда последние цены конкретных товаров у конкретных поставщиков будут следующие:
01.01.2019, ООО Ромашка, Ложка, 150 р.
01.03.2019, ООО Ромашка, Вилка, 125 р.
01.03.2020, ООО Незабудка, Ложка, 167 р.
01.08.2021, ООО Василек, Ложка, 190 р.
01.08.2021, ООО Василек, Вилка, 155 р.
01.08.2021, ООО Одуванчик, Ложка, 191 р.
А последние цены просто товаров, без учета поставщиков:
01.08.2021, Ложка, 190 р.
01.08.2021, Вилка, 155 р.
01.08.2021, Ложка, 191 р.
Получается такое в результате достаточно нехитрой операции группировки и получения максимальных дат, а затем соединения с исходной таблицей. Может применяться к любому набору данных, в котором есть поле типа «Дата».
Разработчики восьмерки почему-то решили, что единственным местом, откуда эта операция может вызываться должен быть как раз регистр сведений. Только не обычный в их понимании, а особенный, который они выделили в отдельный подкласс и назвали «периодическим». Кавычки здесь более чем уместны, потому что никаких периодов там нет. Разработчики воспользовались словом, не вполне отдавая себе отчет в том, что оно означает. Но, по сравнению с остальным, это, в сущности, мелочь. «Периодический» регистр сведений отличается от обычного тем, что в состав первичного ключа помимо измерений входит т.н. «период» (который, конечно не период, а просто поле типа «Дата»).
Идея разработчиков заключалась в том, что последние записи должны быть уникальными. И надо сказать, что они верно поняли задачу. Но лучше бы они ее не решали. Если верить Эйнштейну (а у нас нет оснований ему не верить, раз мы пользуемся его формулами практически каждый день), то в реальном мире никакие два события не происходят в точности одновременно. Поэтому, имея поле типа «Дата», которое отмечает точку на временной оси, мы уже имеем уникальность. Достаточно позаботиться о том, чтобы ваша учетная система соответствовала реальному миру в фундаментальных аспектах (а время это именно такой аспект) и создать механизм обеспечивающий уникальность значения типа «Дата» как минимум в рамках информационной базы. Тогда наш результат из вышеприведенного примера естественным образом избавился бы от дублирующих значений:
01.08.2021 00:00:00 78364732678365738465734, Вилка, 155 р.
01.08.2021,00:00:00 78364732678365753438478, Ложка, 191 р.
Но разработчики посчитали, что достаточно будет хранить даты с точностью до секунды. И перед ними встала другая задача. Какой результат выдавать, в случае если запрос к их регистру сведений строится по неполному набору ключевых полей, как это было показано выше. Можно было бы выдавать результат:
01.08.2021, Ложка, 190 р.
01.08.2021, Вилка, 155 р.
01.08.2021, Ложка, 191 р.
Но это внезапно ломало концепцию уникальности последних записей. Можно было бы вызывать исключительную ситуацию при попытке построить запрос к регистру сведений. И, повторюсь, все-таки лучшим решением было обеспечение уникальности значений типа «Дата». Что же сделали разработчики, столкнувшись с очередной трудностью? А ничего! Вот просто ничего. В текущей реализации запрос к регистру сведений по неполному набору «измерений» вернет:
01.01.2019, Ложка, 150 р.
01.03.2019, Вилка, 125 р.
01.03.2020, Ложка, 167 р.
01.08.2021, Ложка, 190 р.
01.08.2021, Вилка, 155 р.
01.08.2021, Ложка, 191 р.
Как видите, результат не только потерял уникальность, он еще и перестал выдавать последние записи. Он лишен вообще какого-либо смысла. Такое ощущение, что малые дети начали что-то делать, потом у них возникли трудности, они расстроились и бросили все, как есть.
На самом деле все это не так смешно, как я здесь описываю. Потому что в реальности происходит следующее. Вы сталкиваетесь с задачей получения последних записей по неполному набору «измерений». Открываете документацию. В описании виртуальной таблицы среза последних читаете:
«Предназначена для получения наиболее поздних записей регистра сведений на указанную дату (включительно). Включает только активные записи. По каждой комбинации измерений будет найдена наиболее поздняя запись, но не более поздняя, чем указанная дата.»
Делаете вывод, что ваш запрос вернет вам последние записи, а в результате получаете бессмыслицу. Проблема здесь не только в том, что вы теряете время. Хотя и это тоже важно, если учесть, что разработчиков в 1С десятки тысяч. Гораздо серьезнее следующее. Если вы полностью доверяете разработчикам платформы, то вы не станете дотошно проверять результат и можете элементарно пропустить эту ошибку. А если вы уже обожглись на чем-то другом и теперь проверяете все досконально, то и тут нет ничего хорошего. Потому что невозможно нормально работать, если знаешь, что от разработчиков платформы можно в любой момент ожидать чего угодно.
В какой мере были знакомы архитекторы будущей 1С:Предприятие 8 с теорией баз данных мы теперь наверное и не узнаем. Я допускаю крайний вариант — ни в какой. Это было время пионеров. Можно было предложить практически что угодно. В том числе и очевидно нелепые решения. И это проходило, потому что пользователям было трудно дать оценку нового для них продукта, а конкуренты остались где-то далеко позади. Нам же остается утешать себя тем, что зато сегодня мы имеем очень хороший пример очень плохой архитектуры.