- •8.1. Назначение. Основные объекты , предназначенные для организации запросов
- •Выполнение запроса
- •8.2. Язык запросов
- •Поля из вложенных таблиц
- •Псевдонимы источников как/as
- •Запросы к табличным частям
- •Вложенные запросы в списке источников
- •Конструкция соединение...По/join...On
- •Левое внешнее соединение
- •Правое внешнее соединение
- •Полное внешнее соединение
- •Логические операторы и, или, не
- •Параметры в языке запросов
- •Проверка вхождения значения в список (в/in)
- •Проверка ссылочного значения (ссылка/ref)
- •Проверка пустых значений (есть null / is null)
- •Сравнение строк (подобно / like)
- •Упорядочивание по иерархии
- •Упорядочивание во вложенных таблицах
- •Автоупорядочивание /autoorder
- •Агрегатные функции в запросе
- •Общие итоги
- •Итоги по группировкам
- •Итоги по иерархии
- •Несколько итогов в запросе
- •Встроенные функции языка запросов
- •Операции выбора в языке запросов (выбор / case)
- •Приведение типов в языке запросов
- •Литералы и параметры в языке запросов
- •8.4. Обработка результата запроса
- •8.5. Выборка из результата запроса
- •Линейный обход
- •Иерархический обход
- •Обход по группировкам
- •Другие полезные методы
- •8.6. Выгрузка результата запроса
- •8.7. Запросы к регистрам сведений
- •Основная таблица регистра сведений
- •8.8. Запросы к регистрам накопления
- •Основная таблица регистра накопления
- •Сводные таблицы
Автоупорядочивание /autoorder
Ключевое слово АВТОУПОРЯДОЧИВАНИЕ позволяет включить режим автоматического формирования полей для упорядочивания результата запроса. Оно часто используется при построении отчетов.
Автоупорядочивание работает по следующим правилам:
• Если в запросе было указано предложение УПОРЯДОЧИТЬ ПО, то каждая ссылка на таблицу, находящаяся в этом предложении, будет заменена полями, по которым по умолчанию сортируется таблица (для справочников это код или наименование, для документов - дата документа). Если поле для упорядочивания ссылается на иерархический справочник, то будет применена иерархическая сортировка по этому справочнику.
• Если в запросе отсутствует предложение УПОРЯДОЧИТЬ ПО, но есть предложение ИТОГИ, тогда результат запроса будет упорядочен по полям, присутствующим в предложении ИТОГИ после ключевого слова ПО, в той же последовательности; в случае, если итоги рассчитывались по ссылочным полям, то результат запроса будет упорядочен по полям сортировки по умолчанию таблиц, на которые были ссылки.
• Если в запросе отсутствуют предложения УПОРЯДОЧИТЬ ПО и ИТОГИ, но есть предложение СГРУППИРОВАТЬ ПО, тогда результат запроса будет упорядочен по полям, присутствующим в предложении, в той же последовательности и в случае, если группировка велась по ссылочным полям, то по полям сортировки по умолчанию таблиц, на которые были ссылки.
• И наконец, если в запросе отсутствуют предложения УПОРЯДОЧИТЬ ПО, ИТОГИ и СГРУППИРОВАТЬ ПО, результат будет упорядочен по полям сортировки по умолчанию для таблиц, из которых выбираются данные, в порядке их появления в запросе.
Агрегатные функции в запросе
Часто требуется не просто выбрать отдельные записи из базы данных, а получить сводную информацию, например, для ответа на следующие вопросы:
• Каков общий объем продаж за период?
• Какова средняя стоимость заказа в каждом филиале?
• Сколько сотрудников работает в штате?
• Какова наименьшая и наибольшая цена продажи каждого товара?
В системе 1С:Предприятие 8 такие запросы можно создавать с помощью агрегатных функций, группировок и предложения ИМЕЮЩИЕ (HAVING). Ниже будут описаны агрегатные функции языка запросов.
Любая агрегатная функция принимает в качестве аргумента какой-либо столбец, а возвращает единственное значение. Например, агрегатная функция СУММА (SUM) принимает в качестве аргумента столбец чисел и вычисляет его сумму.
В языке запросов 1С:Предприятия 8 существуют следующие агрегатные функции:
СУММА (SUM). Вычисляет сумму всех значений, содержащихся в столбце.
МАКСИМУМ (МАХ). Находит наибольшее значение в столбце.
МИНИМУМ (MIN). Находит наименьшее значение в столбце.
СРЕДНЕЕ (AVG). Вычисляет среднее арифметическое значение по столбцу.
КОЛИЧЕСТВО (COUNT). Подсчитывает количество значений, содержащихся в столбце. Если в качестве параметра данной функции передать звездочку («*»), то функция подсчитает количество строк в таблице результата запроса.
Ниже приведен пример запроса с несколькими агрегатными функциями:
ВЫБРАТЬ
СУММА(Оклад) КАК ФондОплатыТруда,
МИНИМУМ(Оклад) КАК МинОклад,
МАКСИМУМ(Оклад) КАК МаксОклад,
СРЕДНЕЕ(Оклад) КАК СреднийОклад,
КОЛИЧЕСТВО(*) КАК Количество
ИЗ Справочник.Сотрудники
Результат запроса будет содержать всего одну строку:
ФондОплаты Труда |
МинОклад |
Макс Оклад |
Средний Оклад |
Количество |
1500000 |
6000 |
17000 |
9000 |
30 |
Рассмотрим более подробно функцию КОЛИЧЕСТВО / COUNT. Эта функция подсчитывает количество значений параметра, попавших в выборку.
В отличие от других агрегатных функций она допускает три варианта использования:
• Позволяет узнать количество строк в результате запроса. Для этого в качестве параметра функции надо указать звездочку («*»). Это наиболее часто встречающийся вариант использования функции КОЛИЧЕСТВО. Даже если в строке все поля содержат NULL, то такая строка тоже будет посчитана.
• Позволяет подсчитать количество значений указанного поля, не являющихся NULL-значениями. В качестве параметра функции можно указывать ссылки на поля, содержащие значения любого типа, при этом NULL-значения игнорируются.
• Позволяет узнать количество различных значений указанного поля. Для этого перед спецификацией поля надо указать ключевое слово РАЗЛИЧНЫЕ / DISTINCT, при этом NULL-значения игнорируются.
Например, с помощью функции КОЛИЧЕСТВО можно ответить на следующие вопросы:
• Сколько сотрудников, у которых оклад больше заданной величины?
ВЫБРАТЬ КОЛИЧЕСТВО(*) КАК Количество
ИЗ Справочник.Сотрудники
ГДЕ Оклад > &ВыбОклад
• Сколько различных клиентов купили хоть что-нибудь за заданный период?
ВЫБРАТЬ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагент) КАК Количество
ИЗ Документ.РасходнаяНакладная
ГДЕ Дата МЕЖДУ &НачДата И &КонДата
Предложение СГРУППИРОВАТЬ ПО / GROUP BY
Очень часто запрос делается с целью не просто выбрать записи из таблицы, но также сгруппировать их определенным образом. Под словом «сгруппировать» имеется в виду не распределить записи по группам, а свернуть по группировочным полям, вычислив агрегатные функции по каждой группе.
Например, если требуется узнать объем продаж каждого товара за период, тогда в запросе понадобится группировка по товару. Ниже приведен запрос с группировкой к документам РасходнаяНакладная:
ВЫБРАТЬ Номенклатура, СУММА(Сумма) КАК ОбъемПродаж
ИЗ Документ.РасходнаяНакладная.Состав КАК ДокСостав
ГДЕ ДокСостав.Ссылка.Дата МЕЖДУ &НачДата И &КонДата
СГРУППИРОВАТЬ ПО Номенклатура
АВТОУПОРЯДОЧИВАНИЕ
В данном примере использована агрегатная функция СУММА для поля Сумма табличной части Состав. Запрос группирует все продажи по товарам и подсчитывает объем продаж по каждому товару:
Номенклатура |
ОбъемПродаж |
1С:Торговля и Склад 7.7 Проф |
1 540 |
Windows XP Home Edition Russian CD |
1 360 |
Windows XP Home Edition Russian UPG CD |
1 105 |
Windows XP Professional Russian CD |
2480 |
Доставка |
40 |
Инсталляция ПО |
60 |
Клавиатура Apple Pro Keyboards |
5890 |
Клавиатура Keyboard PS/2 |
384 |
Таким образом, в большинстве случаев группировки используются совместно с агрегатными функциями. Если взять пример из предыдущего параграфа и добавить группировку по подразделению, то можно легко детализировать информацию до подразделений и получить ценную информацию для анализа:
ВЫБРАТЬ Подразделение, СУММА(Оклад) КАК ФондОплатыТруда
МИНИМУМ(Оклад) КАК МинОклад,
МАКСИМУМ(Оклад) КАК МаксОклад,
СРЕДНЕЕ(Оклад) КАК СреднийОклад,
КОЛИЧЕСТВО(*) КАК КоличествоЧеловек
ИЗ Справочник.Сотрудники
СГРУППИРОВАТЬ ПО Подразделение
Подразделение |
Фонд Оплаты Труда |
Мин Оклад |
Макс Оклад |
Средний Оклад |
Количество Человек |
Бухгалтерия |
30000 |
6000 |
16000 |
10000 |
9 |
Маркетинг |
40000 |
6500 |
15000 |
11000 |
7 |
Снабжение |
30000 |
7000 |
12000 |
9000 |
8 |
Руководство |
50000 |
9000 |
17000 |
14000 |
6 |
В языке запросов можно группировать данные по нескольким полям, при этом будут подсчитаны агрегатные функции для каждой комбинации группировок:
ВЫБРАТЬ Номенклатура,
ДокСостав.Ссылка.Контрагент КАК Контрагент,
СУММА(Сумма) КАК Продажи
ИЗ Документ.РасходнаяНакладная.Состав КАК ДокСостав
ГДЕ ДокСостав.Ссылка.Дата МЕЖДУ &НачДата И &КонДата
СГРУППИРОВАТЬ ПО Номенклатура, Контрагент
АВТОУПОРЯДОЧИВАНИЕ
Номенклатура |
Контрагент |
Продажи |
1C: Аспект 7.7 |
Алекс-2002 |
270 |
1C: Аспект 7.7 |
Магазин на ул. Алексеева |
270 |
1C: Аспект 7.7 |
Эльбрус |
180 |
1C: Бухгалтерия 7.7 |
Магазин на ул. Алексеева |
210 |
1C: Бухгалтерия 7.7 |
Эльбрус |
140 |
Предложение ИМЕЮЩИЕ / HAVING
Предложение ИМЕЮЩИЕ позволяет накладывать условия на значения агрегатных функций. В других конструкциях языка запросов, например, в предложении ГДЕ, использовать агрегатные функции в условиях нельзя. В условии отбора ИМЕЮЩИЕ можно использовать только агрегатные функции и поля, по которым осуществляется группировка.
Например, необходимо выбрать товары, которых продали не менее 70 штук. Тогда нужно сначала подсчитать объем продаж по каждому товару, а затем применить условие отбора уже к получившимся группам, а не к исходным записям.
Эта задача решается с помощью приведенного ниже запроса:
ВЫБРАТЬ Номенклатура, Сумма (Количество) КАК Продано
ИЗ Документ.РасходнаяНакладная.Состав
СГРУППИРОВАТЬ ПО Номенклатура
ИМЕЮЩИЕ Сумма (Количество) >= 70
Результат запроса будет следующий:
Номенклатура |
Продано |
Клавиатура Apple Pro Keyboards |
79 |
Клавиатура Keyboard PS/2 |
79 |
Клавиатура LK-601 KB-2000 PS/2 |
76 |
Мышь 2-кноп A4Tech PS/2 |
102 |
Мышь Ice Mouse MUS-2 |
89 |
Мышь LOGITECH M-S48 PS/2 |
98 |
Приведем другой пример, когда отбор групп необходим. Выберем клиентов, купивших за заданный период товаров на сумму 10000 и выше:
ВЫБРАТЬ Контрагент, СУММА(Сумма) КАК Куплено
ИЗ РегистрНакопления.Продажи
ГДЕ Период МЕЖДУ &НачДата И &КонДата
СГРУППИРОВАТЬ ПО Контрагент
ИМЕЮЩИЕ СУММА(Сумма) >= 10000
Контрагент |
Куплено |
Автохозяйство №34 |
10 698,6 |
Алекс-2002 |
76 519,2 |
Завод РТИ |
15 007,9 |
Магазин на ул. Алексеева |
63 525,1 |
Эльбрус |
31 084,7 |
Обратите внимание, что ключевое слово ГДЕ применяется здесь только для отбора записей за нужный период. Затем данные группируются по контрагенту с подсчетом суммы проданных товаров, и к получившимся строкам применяется условие ИМЕЮЩИЕ.
Предложение ИТОГИ / TOTALS
Язык запросов системы 1С:Предприятие 8 имеет очень мощную возможность расчета итогов, чего нет в стандартном языке SQL. Данный механизм дает возможность включить в результат запроса дополнительные строки, содержащие общие и промежуточные итоги по заданным полям и группировкам.
