В практике разработчика 1С часто возникает задача анализа динамики цен или поставок. Если найти один последний документ прихода (самый свежий по дате) достаточно просто через использование агрегатной функции MAX, то выборка двух или более последних документов для каждой позиции номенклатуры в рамках одного запроса требует более продвинутых техник. В этой статье мы подробно разберем, как решить эту задачу для платформ 1С 7.7 и 1С 8.x, используя прямые SQL-запросы и стандартные механизмы языка запросов.
Основная сложность заключается в том, что нам нужно выполнить группировку по номенклатуре, но при этом получить не одно значение, а «срез» из двух записей. При использовании стандартного GROUP BY результат схлопывается в одну строку. Если же мы просто ограничим выборку через TOP 2, мы получим всего две записи на весь отчет, а не для каждого товара. Нам необходимо применить механизмы, которые позволяют пронумеровать документы внутри каждой группы товаров и отобрать записи с номерами 1 и 2.
Важный момент: в 1С 7.7 (SQL-версия) дата и время документа хранятся в поле Date_Time_IDDoc таблицы _1JOURN. Это 23-значная строка, которая обеспечивает уникальную хронологическую последовательность. При работе с 1С 8.x мы обычно используем поле Дата (которое включает время) и ссылку на документ для обеспечения уникальности.
Если ваша база 1С 7.7 работает на MS SQL Server 2005 и выше, самым элегантным и быстрым решением будет использование оконной функции ROW_NUMBER() с конструкцией OVER(PARTITION BY ...). Рассмотрим этот метод подробнее.
Суть метода заключается в том, что мы «разрезаем» всю таблицу приходов на секции (partition) по каждой номенклатуре и внутри каждой секции нумеруем строки в порядке убывания даты. Затем мы просто отфильтровываем те строки, где порядковый номер меньше или равен 2.
Проанализируем пример кода для прямого запроса:
SELECT
Res.Номенклатура [Номенклатура $Справочник.Номенклатура],
Res.ЦенаПрихода,
Res.IDDoc [ДокументПрихода $Документ]
FROM (
SELECT
$ДокТЧ.Товар AS Номенклатура,
$ДокТЧ.Цена AS ЦенаПрихода,
Jr.IDDoc AS IDDoc,
ROW_NUMBER() OVER (
PARTITION BY $ДокТЧ.Товар
ORDER BY Jr.Date_Time_IDDoc DESC
) as НомерПрихода
FROM
_1JOURN AS Jr
INNER JOIN
$ДокументСтроки.ПоступлениеТоваров AS ДокТЧ ON Jr.IDDoc = ДокТЧ.IDDoc
WHERE
Jr.Closed = 1 -- Документ проведен
AND Jr.ISMARK = 0 -- Документ не помечен на удаление
) AS Res
WHERE
Res.НомерПрихода <= 2
ORDER BY
Res.Номенклатура, Res.НомерПрихода
В данном запросе мы используем подзапрос, чтобы сначала сформировать нумерацию. Внешний запрос оставляет только последние два прихода. Это решение работает максимально быстро, так как SQL-сервер оптимизирует обход индексов внутри секций.
Если использование оконных функций невозможно, разберем альтернативный подход. Мы можем найти первый (последний) приход через MAX, а затем найти второй приход как MAX от всех приходов, исключая тот, что был найден первым. Рассмотрим, как это можно реализовать через UNION для формирования единой таблицы результатов.
Рассмотрим структуру такого запроса:
-- Выбираем самые последние приходы
SELECT
$ДокТЧ.Товар as Номенклатура,
MAX(Jr.Date_Time_IDDoc) as Позиция
INTO #LastArrivals
FROM _1JOURN as Jr
INNER JOIN $ДокументСтроки.ПоступлениеТоваров as ДокТЧ ON Jr.IDDoc = ДокТЧ.IDDoc
GROUP BY $ДокТЧ.Товар;
-- Выбираем предпоследние приходы (максимум среди тех, кто не попал в #LastArrivals)
SELECT
$ДокТЧ.Товар as Номенклатура,
MAX(Jr.Date_Time_IDDoc) as Позиция
INTO #PrevArrivals
FROM _1JOURN as Jr
INNER JOIN $ДокументСтроки.ПоступлениеТоваров as ДокТЧ ON Jr.IDDoc = ДокТЧ.IDDoc
LEFT JOIN #LastArrivals as LA ON LA.Позиция = Jr.Date_Time_IDDoc
WHERE LA.Позиция IS NULL
GROUP BY $ДокТЧ.Товар;
-- Объединяем результаты
SELECT * FROM #LastArrivals
UNION ALL
SELECT * FROM #PrevArrivals;
Этот метод более трудоемкий для сервера, так как требует нескольких проходов по таблице данных, но он является классическим и работает на любой версии SQL.
В платформе 1С:Предприятие 8.x мы используем язык запросов. Здесь также удобно применять временные таблицы. Разберем по шагам логику, предложенную в обсуждении:
ВТ_Документы и найдем МАКСИМУМ(Дата) для каждой номенклатуры.ВТ_Документы с ВТ_Первая по номенклатуре, но ставим условие, что дата в основной таблице строго меньше даты из первой временной таблицы. Снова берем МАКСИМУМ.ОБЪЕДИНИТЬ ВСЕ для результатов первой и второй таблиц.Посмотрим на пример кода запроса:
ВЫБРАТЬ
ТЧ.Номенклатура КАК Номенклатура,
ТЧ.Ссылка КАК Ссылка,
ТЧ.Ссылка.Дата КАК Дата
ПОМЕСТИТЬ втВсеПриходы
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ТЧ
ГДЕ
ТЧ.Ссылка.Проведен = ИСТИНА;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втВсеПриходы.Номенклатура КАК Номенклатура,
МАКСИМУМ(втВсеПриходы.Дата) КАК МаксДата
ПОМЕСТИТЬ втПоследний
ИЗ
втВсеПриходы КАК втВсеПриходы
СГРУППИРОВАТЬ ПО
втВсеПриходы.Номенклатура;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втВсеПриходы.Номенклатура КАК Номенклатура,
МАКСИМУМ(втВсеПриходы.Дата) КАК ПредПоследняяДата
ПОМЕСТИТЬ втПредПоследний
ИЗ
втВсеПриходы КАК втВсеПриходы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ втПоследний КАК втПоследний
ПО втВсеПриходы.Номенклатура = втПоследний.Номенклатура
И втВсеПриходы.Дата < втПоследний.МаксДата
СГРУППИРОВАТЬ ПО
втВсеПриходы.Номенклатура;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втВсеПриходы.Номенклатура,
втВсеПриходы.Ссылка
ИЗ
втВсеПриходы КАК втВсеПриходы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ втПоследний КАК втПоследний
ПО втВсеПриходы.Номенклатура = втПоследний.Номенклатура
И втВсеПриходы.Дата = втПоследний.МаксДата
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
втВсеПриходы.Номенклатура,
втВсеПриходы.Ссылка
ИЗ
втВсеПриходы КАК втВсеПриходы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ втПредПоследний КАК втПредПоследний
ПО втВсеПриходы.Номенклатура = втПредПоследний.Номенклатура
И втВсеПриходы.Дата = втПредПоследний.ПредПоследняяДата
Выясним причину, почему такие запросы могут работать медленно. Если в базе миллионы строк в табличных частях, то полное сканирование для построения отчета «на лету» может занять много времени. Проанализируем ситуацию: если этот отчет нужен менеджерам ежедневно, стоит рассмотреть следующие улучшения:
1. Ограничение периода: Всегда ограничивайте выборку документов (например, только за последний год). Это позволит SQL-серверу быстрее находить нужные данные, используя индексы по дате.
2. Использование регистров: В 1С 7.7 вместо перебора документов можно использовать таблицу движений регистра (например, ПартииТоваров или Закупки). Движения в регистрах уже проиндексированы по номенклатуре, что делает выборку гораздо более эффективной.
3. Денормализация данных (Подход «Предыдущая цена»): В некоторых высоконагруженных системах программисты идут на хитрость. В справочник Номенклатура добавляют два реквизита: ПоследняяЦенаЗакупки и ПредпоследняяЦенаЗакупки. При проведении документа поступления система сама сдвигает значения: текущая цена становится предпоследней, а цена из нового документа — последней. В этом случае отчет по всем товарам формируется мгновенно, так как данные берутся напрямую из справочника.
Мы рассмотрели несколько способов получения двух последних приходов. Для прямых запросов в 1С 7.7 оптимальным является использование оконной функции ROW_NUMBER(). Для стандартного языка запросов 1С 8 лучше всего подходит каскад временных таблиц с последовательным нахождением максимумов. Выбор метода зависит от ваших технических условий (версия SQL, объем базы данных) и частоты использования данного отчета.