При разработке систем учета часто возникает задача отслеживания жизненного цикла документов. Классический пример: у документа есть история статусов (создан, принят, согласован), но иногда переход на новый этап может быть отменен или аннулирован. В этом случае система должна «откатиться» к предыдущему действующему статусу, но при этом сохранить информацию о том, что последняя попытка изменения была неудачной.
Сегодня мы разберем интересную задачу по написанию запроса: нам нужно получить список заявок с их актуальным статусом, учитывая флаг «Аннулирован». Если последний статус аннулирован — мы должны показать предыдущий, но пометить строку флагом отмены.
Давайте формализуем условия, с которыми мы будем работать. У нас есть:
Заявка.СтатусыЗаявок, где статусы имеют числовой код, определяющий их порядок (10 — создана, 20 — принята, 30 — согласована, 40 — в работе и т.д.). Чем больше код, тем выше статус.Заявка, Статус, Аннулирован (Булево).Пример данных:
Попытка решить эту задачу одним простым запросом без вложений может привести к сложным конструкциям. Наиболее надежный и понятный способ — использовать временные таблицы и логику сравнения двух максимумов.
Рассмотрим алгоритм действий:
Давайте напишем запрос, следуя нашей логике. Мы будем использовать пакетный запрос с временными таблицами.
Шаг 1. Получаем максимальный статус из всей истории
Сначала выберем максимальный код статуса для каждой заявки, игнорируя флаг отмены. Поместим результат во временную таблицу ВсеСтатусы.
ВЫБРАТЬ
ТаблицаИстории.Заявка КАК Заявка,
МАКСИМУМ(ТаблицаИстории.Статус.Код) КАК МаксКод
ПОМЕСТИТЬ ВсеСтатусы
ИЗ
РегистрСведений.СтатусыЗаявок КАК ТаблицаИстории
СГРУППИРОВАТЬ ПО
ТаблицаИстории.Заявка
;
Шаг 2. Получаем максимальный «живой» статус
Теперь сделаем выборку только тех записей, которые не аннулированы. Это даст нам тот статус, который должен считаться текущим. Поместим это в таблицу ДействующиеСтатусы.
ВЫБРАТЬ
ТаблицаИстории.Заявка КАК Заявка,
МАКСИМУМ(ТаблицаИстории.Статус) КАК АктуальныйСтатус,
МАКСИМУМ(ТаблицаИстории.Статус.Код) КАК АктуальныйКод
ПОМЕСТИТЬ ДействующиеСтатусы
ИЗ
РегистрСведений.СтатусыЗаявок КАК ТаблицаИстории
ГДЕ
НЕ ТаблицаИстории.Аннулирован
СГРУППИРОВАТЬ ПО
ТаблицаИстории.Заявка
;
Обратите внимание: здесь мы выбираем и ссылку на статус, и его код, чтобы потом было удобно сравнивать.
Шаг 3. Финальное соединение и вычисление признака
Теперь объединим эти данные. Основной таблицей будет ДействующиеСтатусы, так как именно этот статус нам нужно вывести пользователю (например, «30», а не аннулированный «40»). К ней присоединим таблицу всех статусов, чтобы проверить, был ли факт аннулирования более позднего этапа.
ВЫБРАТЬ
Действующие.Заявка,
Действующие.АктуальныйСтатус,
// Логика определения флага:
// Если максимальный код в истории больше актуального кода,
// значит была попытка перехода дальше, которая аннулирована.
ВЫБОР
КОГДА ЕСТЬNULL(Все.МаксКод, 0) > ЕСТЬNULL(Действующие.АктуальныйКод, 0)
ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ КАК ПризнакАннулированияПоследнего
ИЗ
ДействующиеСтатусы КАК Действующие
ЛЕВОЕ СОЕДИНЕНИЕ ВсеСтатусы КАК Все
ПО Действующие.Заявка = Все.Заявка
При реализации этого алгоритма необходимо учесть несколько важных моментов, чтобы избежать ошибок в работе программы.
В примере выше мы использовали ЕСТЬNULL. Это критически важно. Рассмотрим ситуацию: заявка создана ошибочно, статус 10 записан и тут же аннулирован. Других статусов нет.
В этом случае подзапрос ДействующиеСтатусы будет пустым для этой заявки. Если мы хотим видеть такую заявку в отчете (с пустым статусом, но с флагом аннулирования), нам нужно поменять местами таблицы в соединении (брать ВсеСтатусы как основную) и использовать ЕСТЬNULL для полей из таблицы действующих статусов.
Если записей в регистре статусов миллионы, чтение физической таблицы дважды (как мы сделали выше) может быть неэффективным. Рекомендуется использовать следующую схему оптимизации:
ВТ_ИсходныеДанные).Заявка и Статус.ВсеСтатусы и ДействующиеСтатусы, выбирая данные уже из ВТ_ИсходныеДанные, а не из физического регистра.Пример оптимизированного подхода:
// 1. Забираем сырые данные во временную таблицу
ВЫБРАТЬ
Т.Заявка,
Т.Статус,
Т.Статус.Код КАК Код,
Т.Аннулирован
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрСведений.СтатусыЗаявок КАК Т
ГДЕ
Т.Заявка В (&СписокЗаявок) // Если есть отбор
ИНДЕКСИРОВАТЬ ПО
Заявка, Код
;
// 2. Группируем для поиска максимумов
ВЫБРАТЬ
Т.Заявка,
МАКСИМУМ(Т.Код) КАК МаксКодВсего,
МАКСИМУМ(ВЫБОР КОГДА НЕ Т.Аннулирован ТОГДА Т.Код ИНАЧЕ 0 КОНЕЦ) КАК МаксКодЖивой
ПОМЕСТИТЬ ВТ_Максимумы
ИЗ
ВТ_Данные КАК Т
СГРУППИРОВАТЬ ПО
Т.Заявка
;
// 3. Соединяем с данными чтобы получить ссылку на статус по коду
ВЫБРАТЬ
М.Заявка,
Т.Статус КАК ТекущийСтатус,
ВЫБОР
КОГДА М.МаксКодВсего > М.МаксКодЖивой ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ КАК АннулированПоследний
ИЗ
ВТ_Максимумы КАК М
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК Т
ПО М.Заявка = Т.Заявка
И М.МаксКодЖивой = Т.Код
Этот вариант (использование вычисления максимума через ВЫБОР внутри агрегатной функции) позволяет сократить количество временных таблиц и сделать запрос более компактным и быстрым.
Хотя мы разобрали, как решить эту задачу запросом, стоит отметить важный момент проектирования. Вычисление «текущего» статуса на лету путем анализа всей истории — операция ресурсоемкая.
В высоконагруженных системах правильным решением будет денормализация данных:
Заявка реквизит ТекущийСтатус.В этом случае отчет будет строиться простым запросом к документам без сложных группировок, что ускорит работу системы в разы.