1с запрос сгруппировать по месяцам и годам
Запросы в динамических списках
Область применения: управляемое приложение, мобильное приложение.
Методическая рекомендация (полезный совет)
При проектировании динамических списков в формах следует учитывать, что динамические списки предъявляют более высокие требования к скорости выполнения запросов, чем в других случаях (например, в отчетах, в процедурах обработки данных и пр.). Данные в динамических списках непосредственно отображаются пользователю, поэтому скорость вывода и обновления данных является критичной. В данной статье перечислены рекомендации, дополняющие общие сведения по оптимизации запросов (см. группу статей «Оптимизация запросов»).
1. Нужно стараться делать простые запросы для динамических списков. Для этого в первую очередь нужно оптимизировать архитектуру хранения данных, чтобы их было просто отображать в динамических списках.
Пример
В динамическом списке документов-распоряжений на отгрузку нужно вывести состояние отгрузки. Состояние зависит от остатков регистра накопления, и статусов двух документов другого типа.
НЕПРАВИЛЬНО
Пытаться разработать запрос для динамического списка, который будет учитывать всю сложную логику расчета состояния отгрузки.
ПРАВИЛЬНО
Сделать регистр сведений «Состояния отгрузки», в котором хранить уже рассчитанное состояние отгрузки. При этом расчет можно делать или в процессе проведения документов, которые могут влиять на состояние отгрузки, или отдельным регламентным заданием.
2. Необходимо выбрать один из трех режимов работы динамического списка:
- Динамическое считывание данных включено (рекомендуется). Используются запросы, выбирающие записи в количестве приблизительно соответствующем количеству видимых строк в таблице;
- Динамическое считывание данных выключено, задана не виртуальная основная таблица или одна из следующих таблиц: СрезПервых, СрезПоследних, ЗадачиПоИсполнителю, КритерииОтбора, ДвижениеСубконто. Используются запросы, выбирающие по 1000 записей в буфер на сервере, по мере необходимости данные передаются на клиент. Менее эффективно, чем динамическое считывание;
- Динамическое считывание данных выключено, основная таблица не задана. Запрос выполняется «как есть». В буфере накапливаются данные, начиная с 1000 записей. Чем ближе к концу списка, тем больше записей. Можно использовать только для заведомо маленьких выборок.
3. При разработке динамического списка следует учитывать что запрос, который фактически будет сформирован к СУБД, зависит от предопределенных настроек отборов, порядка и группировки СКД. В частности это означает, что необходимо рассмотреть индексирование полей:
- По которым выполняется соединение в запросе;
- На которые наложены условия в запросе;
- Выведенных в качестве быстрых отборов;
- По которым выполняется упорядочивание или предусмотрена группировка;
- По которым ожидается, что пользователь будет часто упорядочивать (группировать).
При этом не следует индексировать все поля подряд «на всякий случай», так как избыточные индексы создают неоправданную нагрузку при записи данных.
4. Настоятельно не рекомендуется использовать конструкции, «утяжеляющие» запрос:
- конструкции РАЗЛИЧНЫЕ и СГРУППИРОВАТЬ ПО;
- конструкции ВЫБОР в предложении ГДЕ или в условиях соединения;
- упорядочивание по полю, полученному при помощи конструкции ВЫБОР, в том числе и пользовательское.
5.1 Соединяться в запросе следует только с небольшим количеством реальных таблиц (в оптимальном варианте в динамическом списке – только одна таблица, и она назначена основной).
Не рекомендуется выполнять соединения:
- с большим количеством реальных таблиц. Ориентироваться стоит на количество не более 4 таблиц;
- с вложенными запросами;
- с виртуальными таблицами.
5.2. Соединение с виртуальными таблицами допустимо в отдельных случаях, если запрос и архитектура данных удовлетворяют ряду условий:
- допустимо соединение с виртуальными таблицами СрезПоследних (СрезПервых), если регистр сведений содержит
заведомо небольшое количество записей. Например, получение текущего курса валют по данным регистра сведений КурсыВалют; - при обращении к виртуальной таблице будут использованы хранимые итоги регистра сведений (см. Разрешение итогов для периодических регистров сведений);
- запрос к виртуальной таблице Остатки будет преобразован платформой в простое чтение хранимой таблицы итогов без группировок (см. Эффективное обращение к виртуальной таблице «Остатки»).
5.3. Временные таблицы в динамических списках следует использовать с учетом требований описанных ниже и в стандарте про использование временных таблиц.
5.3.1. Временные таблицы в динамических списках рекомендуется использовать только тогда, когда они содержат заведомо небольшое количество записей. Иначе их использование неэффективно, т.к. значения временных таблиц в динамическом списке НЕ кешируются, а формируется при каждом считывании данных для заполнения списка.
5.3.2. В частности, если не удается переделать запрос динамического списка, используя виртуальные таблицы с ограничениями (см. п. 5.2), или вообще отказавшись от их использования, то вместо соединения с ними следует использовать соединения с временными таблицами.
5.3.3. Если последний запрос динамического списка выбирает данные только из ранее созданной временной таблицы, то это уже не динамический список и следует перепроектировать его запрос и, скорее всего, метаданные, используемые запросом.
5.4. При учете количества таблиц, участвующих в запросе, необходимо помнить, что обращение к полям «через точку» приводит к неявному соединению с дополнительными таблицами. Подробнее см.: Разыменование ссылочных полей составного типа в языке запросов
5.5. При работе со списком под неполными правами, в которых настроены ограничения доступа к данным (RLS) к таблицам, участвующим в запросе*, также автоматически присоединяются условия ограничения доступа к данным, которые замедляют работу списка. Именно в этом режиме работы следует проверять скорость работы динамических списков.
* примечание: к тем таблицам, к которым предусмотрен RLS в конфигурации.
6. Предусмотреть оптимизацию при работе с полями составного типа
- При использовании в соединениях, отборах, упорядочивании и других конструкциях составного поля, необходимо чтобы состав типов данного поля определялся только ссылочными типами. Подробнее см.: Ограничения на использование реквизитов составного типа;
- Если известно заранее, какого типа должно быть получено поле составного типа, то его необходимо выражать.
7. Запрос динамического списка рекомендуется менять «на лету» на более оптимальный, если это возможно.
Например, если изменение настройки позволяет переписать запрос динамического списка так, что он будет обращаться к другим метаданным, что позволит выполняться ему быстрее.
8. Если применение вышеизложенных рекомендаций не возможно, либо оно не дает должного эффекта, то можно рассмотреть следующие пути оптимизации:
8.1. Отказаться от части возможностей динамического списка.
- От вывода не столь значимой информации, получение которой приводит к усложнению запроса;
- От реализованных сервисных возможностей по группировке списка.
8.2. Осуществлять вывод данных не в динамический список, а в таблицу или дерево значений.
При этом появятся возможности оптимизации недоступные для динамических списков, такие как использование привилегированного режима и т.п..
Данный способ применим, если выполняется одно из условий:
- Исходных данных заведомо мало (десятки-сотни записей).
- Обязательные отборы, накладываемые на список, гарантируют, что данных в один момент времени записей выводится мало.
- Порционность вывода данных организована другими средствами (вручную), например, как в результатах полнотекстового поиска.
9. В случаях, когда в динамическом списке требуется отображение вспомогательных колонок, по которым не требуется отбирать (в том числе через механизмы поиска), сортировать и группировать, и затруднительно, неэффективно или невозможно выполнить получение данных с помощью основного запроса, рекомендуется воспользоваться обработчиком ПриПолученииДанныхНаСервере таблицы управляемой формы. Например, колонки Курс на сегодня , Кратность в списке валют и т.п.
Для эффективной работы обработчика ПриПолученииДанныхНаСервере следует выбирать всю вспомогательную информацию одним запросом сразу для всех отображаемых строк, которые передаются в этот обработчик после выполнения основного запроса динамического списка.
Кроме того, для корректной работы динамического списка требуется явно ограничить выполнение отбора, сортировки и группировки по вспомогательным колонкам с помощью методов динамического списка УстановитьОграниченияИспользованияВГруппировке , УстановитьОграниченияИспользованияВПорядке и УстановитьОграниченияИспользованияВОтборе .
Работаю с Построителем отчетов. Стандартная задача нужно добавить возможности группировать по датам см. сабдж. Получилось сгуппировать только таким образом:
------------
"ВЫБРАТЬ
| ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.ДокументПланирования.ТипЦен КАК ТипЦен,
| СУММА(ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.СтоимостьОборот + ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.НДСОборот) КАК СуммаПогашения,
| ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.Период КАК ПериодД,
| НАЧАЛОПЕРИОДА(ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.Период, МЕСЯЦ) КАК ПериодМ,
| НАЧАЛОПЕРИОДА(ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.Период, ГОД) КАК ПериодГ
|ИЗ
| РегистрНакопления.ПланыПродажПогашениеДебиторскойЗадолженности.Обороты(&НачалоПериода,&КонецПериода , День, ) КАК ПланыПродажПогашениеДебиторскойЗадолженностиОбороты
|
//|ГДЕ
//| ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.Период > &НачалоПериода И
//| ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.Период < &КонецПериода
//|
|СГРУППИРОВАТЬ ПО
| ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.ДокументПланирования.ТипЦен,
| ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.Период
|
|УПОРЯДОЧИТЬ ПО
| ТипЦен
|
|ИТОГИ СУММА(СуммаПогашения) ПО
| ОБЩИЕ,
| ТипЦен,
| ПериодГ КАК ПериодГод,
| ПериодМ КАК ПериодМесяц,
| ПериодД КАК ПериодДень";
---------
Но вот использование функций в запросе НАЧАЛОПЕРИОДА(ПланыПродажПогашениеДебиторскойЗадолженностиОбороты.Период, ГОД) КАК ПериодГ
мне не сильно нравится пробовал делать только в итогах:
ВЫБРАТЬ
РегистрНакопления.ПланыПродажПогашениеДебиторскойЗадолженности.Период КАК Период
ИЗ
РегистрНакопления.ПланыПродажПогашениеДебиторскойЗадолженности.Обороты(&НачалоПериода,&КонецПериода , День, )
ИТОГИ СУММА(СуммаПогашения) ПО
Периодами(Период,Год) КАК ПериодГод,
Периодами(Период,Месяц) КАК ПериодМесяц,
Период КАК ПериодДень";
------------
Но тогда в отчете выведенном построителем, все группировки и год и месяц группируются до дней.
Как быть?
А чем тебе не нравится использовать НАЧАЛОПЕРИОДА, что не так? Мне вот и программистам фирмы 1С нравится ;)
да нравится, просто думал что раз уж в итогах есть функция Периодами() то она для чего то нужна :)
А тогда такой вопрос. Как в построителе отчета в шаблоне сделать так чтобы в группировки по году к примеру выводились в формате ДФ=гггг, а по месяцу ДФ=ММММ, если используешь стандартный шаблон, тот который генирируется построителем отчета?
Наведи порядок в своей работе используя конфигурацию 1C "Управление IT-отделом 8"
Существует несколько способов получить нужные данные.
Непосредственно в запросе
Способ подходит практически для любой ситуации и поэтому наиболее универсален. Единственный,
пожалуй, минус этого способа - если в отчете пользователю не требуется курс, то запрос будет
выбирать избыточные данные.
Вызов СрезПоследних() можно использовать только с передачей в него заранее готового значения даты,
на которую требуется получить значения. Поэтому сабж делается через стыковку нескольких запросов -
основной, к нему стыкуется запрос по регистру сведений с условием по дате и поиском записи с
маскимальной датой (периодом).
В 8.1. вместо обращения к курсам валют удобнее и надежнее использовать временную таблицу с нужными
датами, потому что не во всех организациях ведут курсы валют ежедневно. ;-)
Для общего развития:
Что есть срез последних в платформе?
В зависимости от периодичности регистра (по времени, по позиции регистратора) ВТ разворачивается в
следующий запрос:
1. По времени (год, месяц, . секунда)
2. По позиции регистратора
В данном случае нужно еще раз обернуть выборку
Все это можно увидеть посмотрев технологический журнал с включенным режимом протоколирования
запросов
Система компоновки данных
Данный способ подходит для отчетов. Из очевидных плюсов - если курс (или другие данные) не нужны для
построения отчета, то СКД не будет их получать. Однако быстродействие такого отчета может оказаться
и несколько ниже, чем в первом способе.
Для примера сделаем отчет - список заказов покупателей.
Для этого создадим набор данных "Документы" - запрос:
Для того, чтобы получить информацию о курсах валют, добавим второй набор данных - запрос "Курсы
валют":
Главное здесь - параметры связи. При соединении наборов данных, если указан параметр, СКД передает в подчиненный набор (в нашем случае - запрос "Курсы валют") параметры, указанные в соединении.
Значениями параметров будут значения соответствующих полей набора-источника.
Дополнение периодов в системе компоновки данных
Для некоторых отчетов необходимо получать данные на все периоды в заданном интервале. Например, получать остатки по дням, вне зависимости от того, были ли движения за эти дни. Система компоновки данных позволяет указывать для группировок дополнение периодов с заданной периодичностью в указанном интервале.
Для примера, рассмотрим отчет, который выводит остатки и обороты за указанный период.
Данные будем получать при помощи следующего запроса:
Для отчета будем использовать следующие настройки:
Т.е. в отчет будем выдавать группировку по периоду и диаграмму группировкой по периоду в сериях.
Если мы будем получать отчет с группировкой по периоду без дополнения, то результат отчета будет выглядеть следующим образом:
Как видно, дни, за которые отсутствовали движения, в отчет не выводятся, что не позволяет визуально отслеживать динамику изменения остатков.
Попробуем воспользоваться дополнением периодов, для этого включим у поля группировки тип дополнения День.
Результат отчета с этой настройкой будет выглядеть следующим образом:
В данном результате видно, что остатки выдаются на все дни, даже если в эти дни не было движений.
При необходимости, для поля группировки можно указать интервал, в котором нужно дополнять периоды. Для этого следует ввести даты в колонки "Начальная дата периода" и "Конечная дата периода" поля группировки. При этом дополнение будет происходить не только в интервале дат, полученных из набора данных, но с начальной даты до конечной даты.
Для демонстрации этой возможности воспользуемся отчетом о продажах, в котором будем использовать следующий запрос:
Для примеров будем рассматривать вывод в отчет одной группировки по полю Период.
Результат отчета без дополнения будет выглядеть так:
Результат с дополнением по дням без указания интервала будет выглядеть так:
Т.е. дополнение произошло в интервале, дат, которые были получены из набора данных.
Если у поля группировки установить начальную и конечную дату периода следующим образом:
То дополнение по дням произойдет в указанном интервале и результат отчета будет выглядеть так:
Отметим, что в качестве начальных и конечных дат периода можно использовать не только даты, но и перечисление ТипДополненияПериодаКомпоновкиДанных, а также поле компоновки данных. Для выбора типа следует очистить содержимое поля и воспользоваться кнопкой выбора типа.
Если в качестве начальной и/или конечной дат периода используется поле, то дополнение будет осуществляться до даты, полученной из этого поля. Заметим, что в качестве полей, значение которых будет использоваться для указания начальной или конечной даты периода, можно использовать только поля - параметры и поля отчета - владельца (в случае если дополнение происходит во вложенном отчете). Для примера, воспользуемся в качестве начальной даты полем - параметром - начало периода, а в качестве конечной даты - параметром - конец периода. При этом результат будет дополняться в том периоде, который указан в параметрах данных отчета.
Как видно в данном примере, дополнение произошло в интервале, указанном в параметрах данных.
Если в качестве границы интервала используется тип ТипДополненияПериодаКомпоновкиДанных, то дополнение будет осуществляться до ближайшей границы выбранного типа периода. Так, если в качестве начальной и конечной дат периода выбрать Месяц, то дополнение будет осуществляться с начала месяца первой даты, присутствующей в группировке и до конца месяца последней даты, присутствующей в группировке. Если выбрать в качестве границ выбрать значение Неделя, то периоды будут дополняться с начала недели и до конца недели. Другие типы дополнения отрабатываются аналогично.
Данная возможность особенно полезна для создания отчетов, в которых группировка по периоду вложена в группировку по объемлющему периоду.
Рассмотрим следующую настройку:
В отчет будут выдаваться периоды, сгруппированные по месяцам.
Если для группировки по периоду установить в качестве начальной и конечной даты конкретные даты, то дополнение произойдет в рамках указанного периода, т.е. в отчет выведутся периоды, которые вовсе не находятся в текущей группировке по месяцам.
При дополнении в периоде 01.01.2002 - 31.03.2002 результат может выглядеть следующим образом:
Как видно, группировка по периоду была дополнена в указанном интервале, и в результат попали строки, которые вовсе не относятся к месяцу группировки.
Для того, чтобы в рамках группировки по месяцу дополнение группировки - период происходило только в интервале этого месяца, укажем в качестве начальной и конечной дат дополнения периода тип дополнения периода - Месяц.
Результат будет выглядеть так:
Как видно, дополнение внутри группировки по месяцу произошло только в рамках месяца, что и требовалось.
Читайте также: