При решении учетных задач нередко возникает необходимость сопоставить текущее и предыдущее состояние объекта: например, сравнить новый оклад сотрудника с его предыдущим тарифом, вывести изменение курса валюты относительно предшествующей даты или отследить динамику цен номенклатуры. В классическом SQL для этого применяются оконные функции LAG() или ROW_NUMBER() OVER (...), однако в языке запросов платформы 1С:Предприятие таких конструкций нет. Кроме того, язык запросов 1С не поддерживает конструкцию ВЫБРАТЬ ПЕРВЫЕ 1 внутри коррелированного подзапроса с отбором по полям внешней таблицы.
Разберем подробнее практические и производительные способы получения последней и предпоследней записи для каждого объекта.
Рассмотрим классический подход на уровне языка запросов, который подходит для произвольных источников данных (таблиц документов, регистров накопления или переданных в запрос таблиц значений). Логика решения строится по следующим шагам:
МАКСИМУМ().NULL.Посмотрим на пример текста запроса:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВТ_Исходная.Сотрудник КАК Сотрудник
ПОМЕСТИТЬ ВТ_СписокСотрудников
ИЗ
ВТ_Исходная КАК ВТ_Исходная
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Исходная.Сотрудник КАК Сотрудник,
МАКСИМУМ(ВТ_Исходная.Период) КАК Период
ПОМЕСТИТЬ ВТ_ПоследниеДаты
ИЗ
ВТ_Исходная КАК ВТ_Исходная
СГРУППИРОВАТЬ ПО
ВТ_Исходная.Сотрудник
ИНДЕКСИРОВАТЬ ПО
Сотрудник,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Исходная.Сотрудник КАК Сотрудник,
ВТ_Исходная.Тариф КАК Тариф,
ВТ_Исходная.Период КАК Период
ПОМЕСТИТЬ ВТ_ПоследниеТарифы
ИЗ
ВТ_ПоследниеДаты КАК ВТ_ПоследниеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Исходная КАК ВТ_Исходная
ПО ВТ_ПоследниеДаты.Сотрудник = ВТ_Исходная.Сотрудник
И ВТ_ПоследниеДаты.Период = ВТ_Исходная.Период
ИНДЕКСИРОВАТЬ ПО
Сотрудник,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Исходная.Сотрудник КАК Сотрудник,
МАКСИМУМ(ВТ_Исходная.Период) КАК Период
ПОМЕСТИТЬ ВТ_ПредпоследниеДаты
ИЗ
ВТ_Исходная КАК ВТ_Исходная
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПоследниеТарифы КАК ВТ_ПоследниеТарифы
ПО ВТ_Исходная.Сотрудник = ВТ_ПоследниеТарифы.Сотрудник
И ВТ_Исходная.Период = ВТ_ПоследниеТарифы.Период
ГДЕ
ВТ_ПоследниеТарифы.Тариф ЕСТЬ NULL
СГРУППИРОВАТЬ ПО
ВТ_Исходная.Сотрудник
ИНДЕКСИРОВАТЬ ПО
Сотрудник,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Исходная.Сотрудник КАК Сотрудник,
ВТ_Исходная.Тариф КАК Тариф,
ВТ_Исходная.Период КАК Период
ПОМЕСТИТЬ ВТ_ПредпоследниеТарифы
ИЗ
ВТ_ПредпоследниеДаты КАК ВТ_ПредпоследниеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Исходная КАК ВТ_Исходная
ПО ВТ_ПредпоследниеДаты.Сотрудник = ВТ_Исходная.Сотрудник
И ВТ_ПредпоследниеДаты.Период = ВТ_Исходная.Период
ИНДЕКСИРОВАТЬ ПО
Сотрудник
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_СписокСотрудников.Сотрудник КАК Сотрудник,
ЕСТЬNULL(ВТ_ПредпоследниеТарифы.Тариф, 0) КАК СтарыйТариф,
ВТ_ПредпоследниеТарифы.Период КАК СтараяДата,
ВТ_ПоследниеТарифы.Тариф КАК НовыйТариф,
ВТ_ПоследниеТарифы.Период КАК НоваяДата
ИЗ
ВТ_СписокСотрудников КАК ВТ_СписокСотрудников
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПредпоследниеТарифы КАК ВТ_ПредпоследниеТарифы
ПО ВТ_СписокСотрудников.Сотрудник = ВТ_ПредпоследниеТарифы.Сотрудник
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПоследниеТарифы КАК ВТ_ПоследниеТарифы
ПО ВТ_СписокСотрудников.Сотрудник = ВТ_ПоследниеТарифы.Сотрудник
Если отчет строится в системе компоновки данных, задачу можно решить без усложнения текста запроса с помощью встроенного языка выражений СКД.
Разберем по шагам настройку схемы компоновки:
ПредыдущийТариф. В выражении поля используем функцию:
ВычислитьВыражение("Тариф", , "Группировка", "Предыдущая")
Если данные хранятся в периодическом регистре сведений (например, курсы валют или плановые начисления), мы можем использовать возможности виртуальной таблицы СрезПоследних:
РегистрСведений.КурсыВалют.СрезПоследних(&Период, ). В выборку обязательно включаем поле Период.СрезПоследних, но в параметре среза передаем дату, вычисленную как ДОБАВИТЬКДАТЕ(ПериодПоследнейЗаписи, СЕКУНДА, -1).Этот способ работает быстро, так как платформа 1С использует встроенные оптимизированные механизмы получения срезов на стороне СУБД.
Проанализируем универсальный способ нумерации строк средствами одного запроса. Метод удобен, когда нужно отобрать N-ю запись от конца:
Т1.Сотрудник = Т2.Сотрудник И Т1.Период < Т2.Период.Т1 и вычисляем количество: КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Период) КАК КоличествоБолееПоздних.КоличествоБолееПоздних = 0 — текущая последняя запись.КоличествоБолееПоздних = 1 — предпоследняя запись.Если данные уже выгружены в объект ТаблицаЗначений, программный обход часто выполняется быстрее сложных множественных соединений на стороне СУБД:
// Сортируем таблицу: сначала сотрудник, затем даты по убыванию
ТЗ_Данные.Сортировать("Сотрудник Возр, Период Убыв");
ТЗ_Результат = Новый ТаблицаЗначений;
ТЗ_Результат.Колонки.Добавить("Сотрудник");
ТЗ_Результат.Колонки.Добавить("НовыйТариф");
ТЗ_Результат.Колонки.Добавить("НоваяДата");
ТЗ_Результат.Колонки.Добавить("СтарыйТариф");
ТЗ_Результат.Колонки.Добавить("СтараяДата");
ТекущийСотрудник = Неопределено;
СчетчикЗаписей = 0;
СтрокаРезультата = Неопределено;
Для Каждого Стр Из ТЗ_Данные Цикл
Если Стр.Сотрудник <> ТекущийСотрудник Тогда
ТекущийСотрудник = Стр.Сотрудник;
СчетчикЗаписей = 1;
СтрокаРезультата = ТЗ_Результат.Добавить();
СтрокаРезультата.Сотрудник = Стр.Сотрудник;
СтрокаРезультата.НовыйТариф = Стр.Тариф;
СтрокаРезультата.НоваяДата = Стр.Период;
Иначе
СчетчикЗаписей = СчетчикЗаписей + 1;
Если СчетчикЗаписей = 2 Тогда
СтрокаРезультата.СтарыйТариф = Стр.Тариф;
СтрокаРезультата.СтараяДата = Стр.Период;
КонецЕсли;
КонецЕсли;
КонецЦикла;
При реализации запросов обратите внимание на следующие моменты:
ИНДЕКСИРОВАТЬ ПО для полей связи (Сотрудник, Период). Без индексов СУБД будет выполнять полное сканирование (Table Scan), что приведет к замедлению работы на больших объемах данных.NULL. Всегда используйте ЛЕВОЕ СОЕДИНЕНИЕ и функцию ЕСТЬNULL(), чтобы такие объекты не исчезли из итоговой выборки.Регистратор или НомерСтроки).