При разработке отчетов и механизмов интеграции часто возникает задача: выбрать из информационной базы ровно один итоговый документ определенного типа за каждый день заданного интервала дат, а также получить его реквизиты (например, Ответственный, СуммаДокумента или ссылку).
На первый взгляд задача кажется простой — достаточно сгруппировать данные по началу дня и вычислить максимум по полю Дата. Однако на практике такой подход скрывает подводные камни. Разберем типичные ошибки, проанализируем краевые ситуации и рассмотрим правильные варианты реализации выборки.
Посмотрим на классический вариант запроса с вложенной таблицей, который часто пишут начинающие разработчики:
ВЫБРАТЬ
Реализация.Ответственный КАК Ответственный,
Реализация.Ссылка КАК Ссылка,
ВложенныйЗапрос.ДатаДня КАК ДатаДня
ИЗ
(ВЫБРАТЬ
НАЧАЛОПЕРИОДА(РеализацияТоваровУслуг.Дата, ДЕНЬ) КАК ДатаДня,
МАКСИМУМ(РеализацияТоваровУслуг.Дата) КАК МаксДата
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ГДЕ
РеализацияТоваровУслуг.Проведен
И РеализацияТоваровУслуг.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(РеализацияТоваровУслуг.Дата, ДЕНЬ)) КАК ВложенныйЗапрос
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализация
ПО НАЧАЛОПЕРИОДА(Реализация.Дата, ДЕНЬ) = ВложенныйЗапрос.ДатаДня
И Реализация.Дата = ВложенныйЗапрос.МаксДата
Проанализируем две ключевые проблемы этой конструкции:
Реализация.Дата = ВложенныйЗапрос.МаксДата вернет все эти документы. В результате за один день в выборку попадет несколько строк вместо одной.ВНУТРЕННЕЕ СОЕДИНЕНИЕ лишает СУБД возможности эффективно использовать индексы, что приводит к сканированию всей таблицы при росте объема базы данных.Чтобы гарантировать выборку строго одного документа на каждый день и исключить неоднозначность при совпадении времени вплоть до секунды, применим двухэтапный поиск максимума: сначала определяем максимальную дату, затем максимальную ссылку на эту дату, после чего получаем необходимые реквизиты.
Разберем по шагам текст запроса с использованием временных таблиц и индексации:
// Шаг 1: Получаем максимальную дату документа за каждый день
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(Реализация.Дата, ДЕНЬ) КАК ПериодДень,
МАКСИМУМ(Реализация.Дата) КАК МаксДата
ПОМЕСТИТЬ ВТ_МаксДаты
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
Реализация.Проведен
И Реализация.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(Реализация.Дата, ДЕНЬ)
ИНДЕКСИРОВАТЬ ПО
ПериодДень,
МаксДата
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Устраняем дубли по времени — выбираем максимальную ссылку за максимальную дату
ВЫБРАТЬ
ВТ_МаксДаты.ПериодДень КАК ПериодДень,
МАКСИМУМ(Реализация.Ссылка) КАК Ссылка
ПОМЕСТИТЬ ВТ_ПоследниеСсылки
ИЗ
ВТ_МаксДаты КАК ВТ_МаксДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализация
ПО Реализация.Дата = ВТ_МаксДаты.МаксДата
И (НАЧАЛОПЕРИОДА(Реализация.Дата, ДЕНЬ) = ВТ_МаксДаты.ПериодДень)
ГДЕ
Реализация.Проведен
СГРУППИРОВАТЬ ПО
ВТ_МаксДаты.ПериодДень
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Получаем итоговые данные документа по выбранной ссылке
ВЫБРАТЬ
ВТ_ПоследниеСсылки.ПериодДень КАК ДатаДня,
Реализация.Ссылка КАК Документ,
Реализация.Дата КАК ДатаПроведения,
Реализация.Ответственный КАК Ответственный,
Реализация.СуммаДокумента КАК СуммаДокумента
ИЗ
ВТ_ПоследниеСсылки КАК ВТ_ПоследниеСсылки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализация
ПО ВТ_ПоследниеСсылки.Ссылка = Реализация.Ссылка
УПОРЯДОЧИТЬ ПО
ДатаДня
В этом решении использование МАКСИМУМ(Реализация.Ссылка) гарантирует выборку ровно одного документа (по уникальному идентификатору UUID), даже если несколько документов имеют абсолютно одинаковый реквизит Дата.
В современных версиях платформы 1С:Предприятие появилась поддержка аналитических функций. С их помощью задачу можно решить лаконично в рамках одного запроса с подзапросом, используя нумерацию строк внутри секции.
Рассмотрим пример с функцией НОМЕР_СТРОКИ() OVER(...):
ВЫБРАТЬ
Подзапрос.ДатаДня КАК ДатаДня,
Подзапрос.Ссылка КАК Документ,
Подзапрос.Дата КАК Дата,
Подзапрос.Ответственный КАК Ответственный
ИЗ
(ВЫБРАТЬ
НАЧАЛОПЕРИОДА(Реализация.Дата, ДЕНЬ) КАК ДатаДня,
Реализация.Ссылка КАК Ссылка,
Реализация.Дата КАК Дата,
Реализация.Ответственный КАК Ответственный,
НОМЕР_СТРОКИ() OVER (
PARTITION BY НАЧАЛОПЕРИОДА(Реализация.Дата, ДЕНЬ)
ORDER BY Реализация.Дата УБЫВ, Реализация.Ссылка УБЫВ
) КАК НомерСтрокиВРамкахДня
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
Реализация.Проведен
И Реализация.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания) КАК Подзапрос
ГДЕ
Подзапрос.НомерСтрокиВРамкахДня = 1
УПОРЯДОЧИТЬ ПО
Подзапрос.ДатаДня
Конструкция PARTITION BY делит набор записей на группы по дням, а предложение ORDER BY Реализация.Дата УБЫВ, Реализация.Ссылка УБЫВ присваивает первой (самой поздней) строке номер 1. Внешний фильтр ГДЕ НомерСтрокиВРамкахДня = 1 отсекает все остальные строки за день.
Если задача решается для построения пользовательского отчета в Системе компоновки данных, писать сложные запросы вовсе не обязательно. Достаточно использовать возможности настроек схемы:
Ссылка, Дата, Ответственный и вычисляемое выражение НАЧАЛОПЕРИОДА(Дата, ДЕНЬ) КАК ПериодДень.ПериодДень.Дата Убыв, Ссылка Убыв.Обратите внимание на два нюанса при работе с документами:
Ответственный в документе заполняется при создании или вручную и может не совпадать с пользователем, который фактически провел документ. Точную информацию об авторе проведения и реальном времени транзакции можно получить либо через журнал регистрации, либо через стандартный механизм История данных (если он включен для данного объекта метаданных).ГДЕ Документ.Проведен = ИСТИНА на самом первом этапе выборки данных во временную таблицу.