Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
ЦОО_ИСБУ_Лек3.doc
Скачиваний:
6
Добавлен:
01.07.2025
Размер:
684 Кб
Скачать
☆

Автоупорядочивание /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. Данный механизм дает возможность включить в результат запро­са дополнительные строки, содержащие общие и промежуточные итоги по заданным полям и группировкам.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]