При построении аналитических отчетов в 1С часто возникает задача сегментации клиентской базы. Типичный пример: необходимо вывести детальные продажи (в разрезе месяцев, филиалов или торговых точек) только по тем покупателям, которые совершили более одной покупки за весь анализируемый период (например, за год). При этом «разовых» покупателей из финального отчета нужно полностью исключить.
Сложность заключается в том, что условие отбора (общее количество покупок > 1 за год) должно применяться ко всему периоду в целом, в то время как результат мы хотим видеть с высокой детализацией (по месяцам). Если попытаться решить задачу «в лоб» через обычную группировку с использованием секции ИМЕЮЩИЕ, СУБД отсечет покупателей, у которых не было повторных продаж в пределах каждого конкретного месяца, что приведет к искажению данных.
В этой статье мы подробно разберем, как правильно спроектировать такой запрос, избежав типичных ошибок производительности, откажемся от медленных вложенных запросов и оптимизируем работу с СУБД по стандартам разработки фирмы «1С».
Рассмотрим, с какими проблемами сталкивается разработчик при попытке написать такой запрос с использованием вложенных таблиц и текстовых реквизитов:
Документ.РасходнаяНакладная — это грубейшая методическая ошибка. Физические таблицы документов содержат огромное количество избыточных полей. При выполнении запроса СУБД вынуждена сканировать «тяжелые» индексы и осуществлять неявные соединения табличной части с шапкой. В реальных базах это приводит к критическому падению скорости работы и транзакционным блокировкам.Покупатель.Телефон) для связи таблиц — крайне неэффективное решение. Строковые типы данных имеют переменную длину, их сравнение на уровне СУБД происходит медленно из-за посимвольного анализа и учета кодировок (collation). Кроме того, строковые данные часто содержат дубли, пробелы или дефисы, из-за чего один и тот же контрагент может не связаться при соединении таблиц.Для получения точного результата и обеспечения максимальной производительности мы применим механизм временных таблиц (ВТ). Наш пакетный запрос будет состоять из трех последовательных шагов. Такой подход позволяет прочесть исходные данные из базы данных ровно один раз.
Рассмотрим по шагам алгоритм работы нашего оптимального запроса:
Сначала мы выберем все продажи за интересующий нас период с наложением необходимых фильтров (по организации, пометке удаления, периоду и т.д.) и поместим их в первую временную таблицу. Вместо тяжелых физических таблиц документов мы задействуем виртуальную таблицу оборотов регистра накопления (например, Продажи.Обороты).
Проанализируем пример кода для первого шага пакета:
ВЫБРАТЬ
ПродажиОбороты.ДоговорКонтрагента.Владелец КАК Покупатель,
ПродажиОбороты.Организация КАК Фирма,
НАЧАЛОПЕРИОДА(ПродажиОбороты.Период, МЕСЯЦ) КАК Месяц,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ПродажиОбороты.Регистратор) КАК КоличествоПокупокЗаМесяц
ПОМЕСТИТЬ ВТ_ВсеПродажи
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоГода, &КонецГода, Регистратор, Организация.Город = &Город) КАК ПродажиОбороты
ГДЕ
НЕ ПродажиОбороты.ДоговорКонтрагента.Владелец.Телефон ЕСТЬ NULL
СГРУППИРОВАТЬ ПО
ПродажиОбороты.ДоговорКонтрагента.Владелец,
ПродажиОбороты.Организация,
НАЧАЛОПЕРИОДА(ПродажиОбороты.Период, МЕСЯЦ)
ИНДЕКСИРОВАТЬ ПО
Покупатель
;
Важный момент оптимизации: Обратите внимание на инструкцию ИНДЕКСИРОВАТЬ ПО Покупатель в конце первого запроса. Так как в последующих запросах пакета мы будем связывать таблицы по полю покупателя, наличие индекса позволит СУБД выполнить соединение мгновенно (используя быстрый алгоритм Merge Join или Index Seek вместо медленного полного сканирования таблицы).
Теперь нам нужно определить список «лояльных» покупателей, у которых зафиксировано более одной покупки за весь выбранный год. Источником данных для этого шага будет служить уже не тяжелая таблица базы данных, а созданная нами на предыдущем шаге легкая таблица ВТ_ВсеПродажи, которая уже находится в оперативной памяти или tempdb СУБД.
Рассмотрим запрос для второго шага:
ВЫБРАТЬ
ВТ_ВсеПродажи.Покупатель КАК Покупатель
ПОМЕСТИТЬ ВТ_ПокупателиСПовторнымиПокупками
ИЗ
ВТ_ВсеПродажи КАК ВТ_ВсеПродажи
СГРУППИРОВАТЬ ПО
ВТ_ВсеПродажи.Покупатель
ИМЕЮЩИЕ
СУММА(ВТ_ВсеПродажи.КоличествоПокупокЗаМесяц) > 1
ИНДЕКСИРОВАТЬ ПО
Покупатель
;
В этом запросе мы агрегируем данные по покупателю, складываем количество его покупок по всем месяцам и отсекаем тех, у кого суммарный показатель равен 1. Результат помещаем во вторую временную таблицу и также обязательно индексируем ее.
На последнем шаге мы объединяем детальные данные из первой таблицы ВТ_ВсеПродажи с фильтрующим списком покупателей из ВТ_ПокупателиСПовторнымиПокупками. Связывание выполняем типом ВНУТРЕННЕЕ СОЕДИНЕНИЕ по ссылочному полю Покупатель. Именно на этом этапе мы можем безопасно получить строковое представление телефона покупателя для вывода в отчет.
Посмотрим на финальный запрос пакета:
ВЫБРАТЬ
ВТ_ВсеПродажи.Месяц КАК Месяц,
ВТ_ВсеПродажи.Фирма КАК Фирма,
ВТ_ВсеПродажи.Покупатель.Телефон КАК ПокупательТелефон,
ВТ_ВсеПродажи.Покупатель КАК ПокупательСсылка,
ВТ_ВсеПродажи.КоличествоПокупокЗаМесяц КАК КоличествоПокупок
ИЗ
ВТ_ВсеПродажи КАК ВТ_ВсеПродажи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ПокупателиСПовторнымиПокупками КАК ВТ_ФильтрПокупателей
ПО ВТ_ВсеПродажи.Покупатель = ВТ_ФильтрПокупателей.Покупатель
УПОРЯДОЧИТЬ ПО
Месяц,
Фирма,
ПокупательСсылка
Разберем преимущества предложенной архитектуры пакета запросов:
Покупатель, которое имеет ссылочный тип (в СУБД это тип uniqueidentifier или GUID размером всего 16 байт). Сравнение таких полей происходит на аппаратном уровне процессора СУБД практически мгновенно, в отличие от ресурсоемкого сравнения строк с номерами телефонов.Применяя этот подход в своей практике разработки, вы обеспечите стабильную и быструю работу аналитических отчетов даже на высоконагруженных базах данных 1С с миллионами записей.