2 апр. 2013 г.

Особенности получения последних документов

Наверняка многие коллеги сталкивались с такой задачей, как получение первых/последних документов в выборке. В зависимости от необходимого результата к реализации решения можно подойти различными способами.

Разберем на примере демо-базы УТ11, найдем последние документы прихода номенклатуры по регистру "ТоварыНаСкладе". Под последним документом в контексте данной задачи может пониматься как документ с наибольшей датой документа, так и последний документ, введенный пользователем.

В ниже приведенных пакетных запросах первый запрос пакета нужен для демонстрации картины в целом, последующие запросы формируют результат.
Для одной номенклатуры все просто, нужно сделать упорядочивание по регистратору и выбрать первый документ.

ВЫБРАТЬ
 ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,
 ТоварыНаСкладахОбороты.Регистратор КАК Регистратор,
 ТоварыНаСкладахОбороты.ВНаличииПриход КАК ВНаличииПриход
ИЗ
 РегистрНакопления.ТоварыНаСкладах.Обороты(, , Регистратор, Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОбороты
ГДЕ
 ТоварыНаСкладахОбороты.ВНаличииПриход > 0

УПОРЯДОЧИТЬ ПО
 Регистратор УБЫВ
 АВТОУПОРЯДОЧИВАНИЕ
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ ПЕРВЫЕ 1
 ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,      
 ТоварыНаСкладахОбороты.Регистратор КАК Регистратор,
 ТоварыНаСкладахОбороты.ВНаличииПриход КАК ВНаличииПриход
ИЗ
 РегистрНакопления.ТоварыНаСкладах.Обороты(, , Регистратор, Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОбороты
ГДЕ
 ТоварыНаСкладахОбороты.ВНаличииПриход > 0

УПОРЯДОЧИТЬ ПО
 Регистратор УБЫВ
 АВТОУПОРЯДОЧИВАНИЕ 


Без автоупорядочивания сортировка произойдет без учета даты документа, по уникальному идентификатору. В этом случае, документы введенные позже приходного ордера ЦУ-33 задним числом встанут в начало выборки. Уберем "АВТОУПОРЯДОЧИВАНИЕ" из запроса и посмотрим результат.


Если воспользоваться методом получения даты создания объекта из GUID, то в первом случае 3339428b-6656-11e0-af2a-0015e9b8c48d создан 14.04.2011, во втором b2c7cfa2-6ca9-11e0-af30-0015e9b8c48d - 22.04.2011. То есть второй способ можно применять, если нужно определить последний документ прихода, введенный пользователем.

В случае, когда нужно определить последние документы для перечня номенклатуры конструкция "ВЫБРАТЬ ПЕРВЫЕ" нам уже не подходит. Поэтому, чтобы свернуть выборку по регистратору, будем использовать функцию МАКСИМУМ.

ВЫБРАТЬ
 ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,
 ТоварыНаСкладахОбороты.Регистратор КАК Регистратор

ИЗ
 РегистрНакопления.ТоварыНаСкладах.Обороты(, , Регистратор, ) КАК ТоварыНаСкладахОбороты
ГДЕ
 ТоварыНаСкладахОбороты.ВНаличииПриход > 0

УПОРЯДОЧИТЬ ПО
 Номенклатура,
 Регистратор УБЫВ
 АВТОУПОРЯДОЧИВАНИЕ
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
 ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,
 МАКСИМУМ(ТоварыНаСкладахОбороты.Регистратор) КАК Регистратор

ИЗ
 РегистрНакопления.ТоварыНаСкладах.Обороты(, , Регистратор, ) КАК ТоварыНаСкладахОбороты
ГДЕ
 ТоварыНаСкладахОбороты.ВНаличииПриход > 0

СГРУППИРОВАТЬ ПО
 ТоварыНаСкладахОбороты.Номенклатура

УПОРЯДОЧИТЬ ПО
 Номенклатура,
 Регистратор УБЫВ
 АВТОУПОРЯДОЧИВАНИЕ
;


Как видно из результата, функция МАКСИМУМ сворачивает выборку по GUID объекта. То есть, этот способ не подходит для случая, когда нам нужно получить последний документ, исходя из даты документа. Получать перечень номенклатуры и потом в цикле для каждой позиции получать последний документ тоже не наш вариант, замедляется быстродействие. Поэтому перепишем запрос, добавим временную таблицу, в которой свернем выборку по максимуму даты регистратора. Эту дату и будем использовать для соединения с основной выборкой. В последнем запросе так же сворачиваем выборку по максимуму регистратора, но теперь мы получим последний введенный документ с учетом последней даты документа.

ВЫБРАТЬ
 ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,
 ТоварыНаСкладахОбороты.Регистратор КАК Регистратор,
 ТоварыНаСкладахОбороты.ВНаличииПриход КАК ВНаличииПриход
ИЗ
 РегистрНакопления.ТоварыНаСкладах.Обороты(, , Регистратор, ) КАК ТоварыНаСкладахОбороты
ГДЕ
 ТоварыНаСкладахОбороты.ВНаличииПриход > 0

УПОРЯДОЧИТЬ ПО
 Номенклатура,
 Регистратор УБЫВ
 АВТОУПОРЯДОЧИВАНИЕ
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
 ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,
 МАКСИМУМ(ТоварыНаСкладахОбороты.Регистратор.Дата) КАК Дата
ПОМЕСТИТЬ времДата
ИЗ
 РегистрНакопления.ТоварыНаСкладах.Обороты(, , Регистратор, ) КАК ТоварыНаСкладахОбороты
ГДЕ
 ТоварыНаСкладахОбороты.ВНаличииПриход > 0

СГРУППИРОВАТЬ ПО
 ТоварыНаСкладахОбороты.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
 ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,
 МАКСИМУМ(ТоварыНаСкладахОбороты.Регистратор) КАК Регистратор
ИЗ
 времДата КАК времДата
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Обороты(, , Регистратор, ) КАК ТоварыНаСкладахОбороты
  ПО времДата.Номенклатура = ТоварыНаСкладахОбороты.Номенклатура
   И времДата.Дата = ТоварыНаСкладахОбороты.Регистратор.Дата
ГДЕ
 ТоварыНаСкладахОбороты.ВНаличииПриход > 0

СГРУППИРОВАТЬ ПО
 ТоварыНаСкладахОбороты.Номенклатура

УПОРЯДОЧИТЬ ПО
 Номенклатура
 АВТОУПОРЯДОЧИВАНИЕ 


UPD: как правильно заметили в комментариях, механизм генерации ГУИД обеспечивает только уникальность, но не возрастающую последовательность. На момент написания статьи проведенные тесты давали необходимый результат. Но гарантии, что выявленная закономерность сохранится в последующих версиях платформы, нет. Поэтому, если необходимо фиксировать момент создания документов, то лучше реализовать свой механизм. Например, с использованием регистра сведений.


12 комментариев:

  1. В последнем запросе таблицы соединяются по дате, так делать нельзя т.к. это будет не правильно работать в случае когда у разных поступлений одинаковая дата и время.

    ОтветитьУдалить
    Ответы
    1. Почему нет? В последнем запросе в случае, когда встретится два и более документа с одинаковым датой/временем, возьмется только один по МАКСИМУМ(ТоварыНаСкладахОбороты.Регистратор), иначе говоря последний введенный документ поступления на эту дату/время.

      Удалить
  2. Получается МАКСИМУМ() выбирает максимальный уникальный идентификатор, который икрементально строится на основании даты создания ссылки ?

    ОтветитьУдалить
    Ответы
    1. В общих чертах, да. По результатам, МАКСИМУМ по ссылке дает ссылку (он же УИД) на последний введенный объект.

      Удалить
    2. Этот комментарий был удален автором.

      Удалить
    3. Михаил Андрияшкин, объекты зачастую мигрируют из одних баз в другие и выгружаются по ссылке, поэтому "МАКСИМУМ по ссылке дает ссылку" не верно.

      Удалить
    4. Согласен! Вчера нужно было отобрать в запросе максимальный документ по дате. В результате пришлось отбирать документы по максимальной дате, а потом по максимальной ссылке. При этом все равно отобрался не последний документ, т.к. документы были созданы загрузкой. И тут уж ничего не поделать.

      Удалить
  3. Этот комментарий был удален автором.

    ОтветитьУдалить
  4. Спасибо, полдня мучался пока не нашел этот пост. Вот что получилось у меня под мою задачу

    ВЫБРАТЬ
    ТоварыОрганизацийОбороты.Организация КАК Организация,
    ТоварыОрганизацийОбороты.АналитикаУчетаНоменклатуры.Склад КАК Склад,
    ТоварыОрганизацийОбороты.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
    ТоварыОрганизацийОбороты.АналитикаУчетаНоменклатуры.Характеристика КАК Характеристика,
    МАКСИМУМ(ТоварыОрганизацийОбороты.Регистратор.Дата) КАК ДатаПоступления
    ПОМЕСТИТЬ ВТ_ДатыПоступлений
    ИЗ
    РегистрНакопления.ТоварыОрганизаций.Обороты(, &КонецПериода, Регистратор, АналитикаУчетаНоменклатуры.Склад В ИЕРАРХИИ (&ГруппаСкладов)) КАК ТоварыОрганизацийОбороты
    ГДЕ
    ТоварыОрганизацийОбороты.Регистратор ССЫЛКА Документ.ПриобретениеТоваровУслуг

    СГРУППИРОВАТЬ ПО
    ТоварыОрганизацийОбороты.Организация,
    ТоварыОрганизацийОбороты.АналитикаУчетаНоменклатуры.Склад,
    ТоварыОрганизацийОбороты.АналитикаУчетаНоменклатуры.Номенклатура,
    ТоварыОрганизацийОбороты.АналитикаУчетаНоменклатуры.Характеристика
    ;

    ////////////////////////////////////////////////////////////////////////////////
    ВЫБРАТЬ
    ВТ_ДатыПоступлений.Организация КАК Организация,
    ВТ_ДатыПоступлений.Склад КАК Склад,
    ВТ_ДатыПоступлений.Номенклатура КАК Номенклатура,
    ВТ_ДатыПоступлений.Характеристика КАК Характеристика,
    ВТ_ДатыПоступлений.ДатаПоступления КАК ДатаПоступления,
    МАКСИМУМ(ТоварыОрганизацийОбороты.Регистратор) КАК Поступление
    ИЗ
    ВТ_ДатыПоступлений КАК ВТ_ДатыПоступлений
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций.Обороты(, &КонецПериода, Регистратор, АналитикаУчетаНоменклатуры.Склад В ИЕРАРХИИ (&ГруппаСкладов)) КАК ТоварыОрганизацийОбороты
    ПО ВТ_ДатыПоступлений.ДатаПоступления = ТоварыОрганизацийОбороты.Регистратор.Дата
    ГДЕ
    ТоварыОрганизацийОбороты.Регистратор ССЫЛКА Документ.ПриобретениеТоваровУслуг

    СГРУППИРОВАТЬ ПО
    ВТ_ДатыПоступлений.Организация,
    ВТ_ДатыПоступлений.Склад,
    ВТ_ДатыПоступлений.Номенклатура,
    ВТ_ДатыПоступлений.Характеристика,
    ВТ_ДатыПоступлений.ДатаПоступления

    УПОРЯДОЧИТЬ ПО
    Организация,
    Склад,
    Номенклатура,
    Характеристика,
    ДатаПоступления УБЫВ,
    Поступление УБЫВ

    Но вот в чем меня терзают смутные сомнения в собственной профпригодности. Как поведет себя этот запрос при большом количестве накопленных данных, не будет ли выполняться по полчаса. В принципе можно исходя из конкретных условий ограничить дату начала выбирания оборотов, но тогда получается некошерно, не все последние поступления будут выбираться.

    ОтветитьУдалить
    Ответы
    1. Запрос явно не оптимален. Выборка из оборотного регистра с периодичностью по регистратору будет всегда читать данные из таблицы движений. В вашем случае все движения регистра по дату окончания. Так же есть связь по полю от составного типа (ПО ВТ_ДатыПоступлений.ДатаПоступления = ТоварыОрганизацийОбороты.Регистратор.Дата) и прочие неоптимальности. Я рекомендую разработать отдельный регистр с подходящей структурой метаданных для ваших целей. Навскиду, это периодический регистр сведений, измерения: Склад / Номенклатура / Характеристика, ресурс - Документ.ПриобретениеТоваровУслуг. В этом случае срез последних по всем измерениям с отбором по складам как раз даст вам последние документы. Если планируется получать данные по большей части на текущую дату, можно еще включить у регистра сведений итоги по срезу последних.

      Удалить
  5. Этот комментарий был удален автором.

    ОтветитьУдалить
  6. Использование конструкции ПЕРВЫЕ совместно с конструкцией АВТОУПОРЯДОЧИВАНИЕ запрещено
    https://its.1c.ru/db/v8std#content:-2145783235:hdoc

    ОтветитьУдалить