→ Угруповання у запиті. Угруповання у запиті Використання конструктора запитів

Угруповання у запиті. Угруповання у запиті Використання конструктора запитів

Даний механізм запиту служить для розрахунку показників вибраних групувальних полів.

Наприклад, для вирішення завдань: визначити суму всіх продажів контрагента за період, максимальний чи середній чек.

швидкий перехід

Основний синтаксис:

ВИБРАТИ<ГруппировочныеПоля>, <СУММА(<РасчетноеПоле>)>
З<Источник>
ЗГРУПУВАТИ ПО
<ГруппировочныеПоля>

Виходить, що в директиві ЗГРУПУВАТИ ПО, дублюються всі поля з ВИБРАТИ, крім тих за якими проводиться розрахунок. При додаванні поля в конструкторі це робиться автоматично, якщо руками відстежуйте самостійно, інакше буде помилка: "Поле не входить до групи".

Зауважу, що примітивні значення (число, дата, рядок) або реквізити вже присутніх у групуванні посилань додавати не обов'язково.

Також можуть взагалі відсутні агрегатні функції, тоді запит буде працювати начебто використовується ВИБРАТИ РІЗНІ.

//Цей запит отримає кількість унікальних номенклатур, які проходили по кожному складу в системі
ВИБРАТИ
ЗалишкиТМЦ.Склад,
КІЛЬКІСТЬ(РІЗНІ ЗАЛИШКИТМЦ.Номенклатура) ЯК Номенклатура,
1,
ДАТАВРЕМЯ(2017,1,12),
ЗалишкиТМЦ.Склад.Найменування
З
РегістрНакопичення. Залишки ТМЦ ЯК Залишки ТМЦ

ЗГРУПУВАТИ ПО
ЗалишкиТМЦ.Склад

Функції угруповання (агрегатні)

  • СУМА (<Поле>)
  • КІЛЬКІСТЬ(<Поле>)
  • КІЛЬКІСТЬ (РІЗНІ<Поле>)
  • МАКСИМУМ (<Поле>)
  • МІНІМУМ (<Поле>)
  • СЕРЕДНІЙ(<Поле>)

Інші можливості угруповання

Допускається використання математичних операторів над функціями угруповання (+,*,/,-), а також конструкція ВИБІР і поверх агрегату та всередині (залежно від потреб)

ВИБРАТИ
,
СУМА(ЗалишкиТМЦОбороти.СуммаОборот) ЯК СуммаОборот,
СУМА(ЗалишкиТМЦОбороти.КількістьОборот) ЯК КількістьОборот,
ВИБІР
КОЛИ СУМА(ЗалишкиТМЦОбороти.КількістьОборот) = 0
ТОДІ 0
Інакше СУМА(ЗалишкиТМЦОбороти.СумаОборот) / СУМА(ЗалишкиТМЦОбороти.КількістьОборот)
КІНЕЦЬ ЯК Середня ціна
З

ЗГРУПУВАТИ ПО
ЗалишкиТМЦОбороти.Номенклатура

Приклад запиту без функцій:
ВИБРАТИ
ЗалишкиТМЦОбороти.Номенклатура
З
РегістрНакопичення. Залишки ТМЦ.

ЗГРУПУВАТИ ПО
ЗалишкиТМЦОбороти.Номенклатура

Можна використовувати агрегати над таблицями, що з'єднуються, але рекомендується поверх використовувати функцію ЄNULL

ВИБРАТИ
СпрНоменклатура.Посилання,
Є NULL (СУМА (Залишки ТМЦОбороти. Сума Оборот), 0) ЯК Сума
З
Довідник.Номенклатура ЯК СпрНоменклатура
ЛІВОЕ З'ЄДНАННЯ РегістрНакопичення. Залишки ТМЦ.
ПО (ЗалишкиТМЦОбороти.Номенклатура = СпрНоменклатура.Посилання)

ЗГРУПУВАТИ ПО

СпрНоменклатура.Посилання

Угруповання в конструкторі запиту

Виготовляється на однойменній вкладці.

  • З доступних полів вибираються групові поля, а також підсумововані поля (для кожного вибирається функція підсумовування).
  • У середині є кнопки додавання або можливе перетягування мишею
  • Конструктор зазвичай сам вибирає функцію (обов'язково перевіряйте його дії)

У цій статті ми хочемо обговорити з Вами всі функції мови запитів 1с, а також конструкції мови запитів. Чим відрізняється функція від конструкції? Функція викликається з дужками та можливими параметрами у них, а конструкція пишеться без дужок. Безумовно всі конструкції та функції мови запитів 1сроблять процес отримання даних гнучким та багатофункціональним. Дані функції та конструкції застосовні до полів запиту, а деякі також застосовні за умов.

Функції мови запитів 1с

Оскільки зрозумілий опис функцій мови запитів 1страпляється набагато рідше, ніж опис конструкцій, ми вирішили почати розглядати саме функції. Тепер давайте розберемо кожну окремо, описавши її призначення, синтаксис та приклад використання, отже:

1. Функція ДАТА ЧАС- Ця функція створює константне поле з типом "Дата".

Синтаксис: ДАТА ЧАС(<Год>,<Месяц>,<День>,<Час>,<Минута>,<Секунда>)

Приклад використання:

2. Функція Різноманітність- Повертає різницю двох дат в одному з вимірювань (рік, місяць, день, година, хвилина, секунда). Вимірювання передається у параметрі.

Синтаксис: РІЗНІСТЬДАТ(<Дата1>, <Дата2>, <Тип>)

Приклад використання:

Запит.Текст = "ВИБРАТИ | РІЗНЕСТЬДАТ(ДАТАВРЕМЯ(2015, 4, 17), ДАТАВРЕМЯ(2015, 2, 1), ДЕНЬ) | ЯК Колводнів";

3. Функція ЗНАЧЕННЯ- задає константне поле з наперед визначеним записом з бази даних, також можна отримати порожнє посилання будь-якого типу.

Синтаксис: ЗНАЧЕННЯ(<Имя>)

Приклад використання:

Запит.Текст = "ВИБРАТИ //зумовлений елемент | ЗНАЧЕННЯ(Довідник.Валюти.Долар) ЯК Долар, //порожнє посилання | ФізОсоба) ЯК ФізОбличчя, / / ​​зумовлений рахунок |

4. Функція ВИБІР- Перед нами аналог конструкції ЯКЩО який використовується в коді, тільки ця використовується в запитах 1С.

Синтаксис: ВИБІР КОЛИ<Выражение>ТОДІ<Выражение>Інакше<Выражение>КІНЕЦЬ

Приклад використання:

Запрос.Текст = //якщо сума більше 7500, тоді має бути знижка 300 рублів, //тому якщо умова спрацьовує то функція //повертає Сума - 300 //інакше запит поверне просто Сума "ВИБРАТИ | ВИБІР | КОЛИ ТЧ Надходження.Сума > 7500 | ТОДІ ТЧНадходження.Сума - 300 | Інакше ТЧНадходження.Сума |

5. Функція ВИРАЗИТИ- дозволяє виразити константне поле певним типом.

Синтаксис: ВИРАЗИТИ(НазваПоля ЯК НазваТипу)

Приклад використання:

Запит.Текст = "ВИБРАТИ РІЗНІ | Продажі.Реєстратор.Номер, | ВИБІР | КОЛИ Продажі.Реєстратор ПОСИЛАННЯ Документ.Видаткова | реалізація | ТОДИ ВИРАЗИТИ(Продажи.Реєстратор ЯК Документ.Реалізація) | КІНЕЦЬ | ... | КІНЕЦЬ ЯК Номер | З | РеєстрНакопичення.Закупівлі ЯК Закупівлі";

Є ще варіант використання функції ВИРАЗИТИ в полях змішаних типах, де такі зустрічаються? Найпростіший приклад це "Реєстратор" у будь-якого регістру. То навіщо нам може знадобитися уточнювати тип у реєстраторі? Давайте розглянемо ситуацію, коли ми з реєстратора вибираємо поле "Номер", з якої таблиці буде обраний номер? Правильна відповідь із усіх! Тому щоб наш запит працював швидко, слід вказувати явний тип за допомогою функції ВИРАЗИТИ

Приклад використання:

Запит.Текст = "ВИБРАТИ | ВИРАЗИТИ(Номенклатура.Коментар ЯК Рядок(300)) ЯК Коментар, | ВИРАЗИТИ(Номенклатура.Сума ЯК Число(15,2)) ЯК Сума |З | Довідник.Номенклатура ЯК

6. Функція ISNULL(альтернативне написання Є NULL) - якщо поле має тип NULL, воно замінюється на другий параметр функції.

Синтаксис: Є NULL (<Поле>, <ПодставляемоеЗначение>)

Приклад використання:

Також відзначимо що тип NULL бажано ЗАВЖДИ замінювати якесь значення, т.к. порівняння з типом NULL завжди дає брехня навіть якщо ви порівнюєте NULL з NULL. Найчастіше значення NULL утворюються внаслідок з'єднання таблиць (всі види з'єднань крім внутрішнього).

Запит.Текст = //Вибираємо всю номенклатуру і залишки по ній //якщо залишокв але якоїсь номенклатури немає то буде поле //NULL яке заміниться значенням 0 "ВИБРАТИ | Ном.Посилання, | Залишок |З |Довідник.Номенклатура ЯК Ном |Ліве З'єднання РеєстрНакопичення.ТовариНаСкладах.Залишки ЯК ТовариНаСкладахЗалишки |ПЗ (ТовариНаСкладахЗалишки.Номенклатура = Ном.Посилання)";

7. Функція ПРЕДСТАВЛЕННЯ- дозволяє одержати подання поля запиту.

Синтаксис: ПРЕДСТАВЛЕННЯ(<НаименованиеПоля>)

Приклад використання:

Запит.Текст = "ВИБРАТИ | ПРЕДСТАВЛЕННЯ(ВільніЗалишкиЗалишки.Номенклатура) ЯК Номенклатура, | ПРЕДСТАВЛЕННЯ(ВільніЗалишкиЗалишки.Склад) ЯК Склад, |

Конструкції у мові запитів 1с

Вище ми розглянули з Вами функції мови запитів 1с, тепер настав час розглянути конструкції у мові запитів 1с, вони не менш важливі та корисні, приступаємо.

1. Конструкція ПОСИЛАННЯ- являє собою логічний оператор перевірки типу посилання. Найчастіше зустрічається під час перевірки поля складеного типу на конкретний тип. Синтаксис: ПОСИЛАННЯ<Имя таблицы>

Приклад використання:

Запит.Текст = //якщо тип значення реєстратора документ Прибуткова, //тоді запит поверне "Надходження товарів", інакше "Реалізація товарів" "ВИБРАТИ | ВИБІР | КОЛИ Залишки.Реєстратор ПОСИЛАННЯ Документ. ""Витрата"" | КІНЕЦЬ ЯК Види руху | З | Реєстр Накопичення.

2. Конструкція МІЖ- Цей оператор перевіряє чи входить значення у вказаний діапазон.

Синтаксис: МІЖ<Выражение>І<Выражение>

Приклад використання:

Запрос.Текст = //отримаємо всю номенклатуру код якої лежить в діапазоні від 1 до 100 "ВИБРАТИ | Номенклатура. Посилання | З | Довідник. Номенклатура ЯК Номенклатура | ДЕ | Номенклатура.

3. Конструкція В та В ІЄРАРХІЇ- перевіряють чи знаходиться значення в списку, що передається (як списку можуть передаватися масиви, таблиці значень і т.д.). Оператор В ІЄРАРХІЇ дозволяє переглядати ієрархію (приклад використання План Рахунків).

Синтаксис: В(<СписокЗначений>), В ІЄРАРХІЇ(<СписокЗначений>)

Приклад використання:

Запрос.Текст = // вибираємо всі субрахунки рахунку "ВИБРАТИ | Госпрозрахунковий. Посилання ЯК Рахунок | З | План Рахунок.Госпрозрахунковий ЯК Хозрозрахунковий | ДЕ | Госпрозрахунковий.

4. Конструкція ПОДІБНО- Ця функція дозволяє нам порівнювати рядок із шаблоном рядка.

Синтаксис: ПОДІБНО "<ТекстШаблона>"

Варіанти шаблону рядка:

% - Послідовність, що містить будь-яку кількість довільних символів.

Один символ довільний.

[...] - будь-який одиночний символ, або послідовність символів із перелічених усередині квадратних дужок. У перерахуванні можуть задаватися діапазони, наприклад a-z, що означають довільний символ, що входить до діапазону, включаючи кінці діапазону.

[^...] - будь-який одиночний символ, або послідовність символів з перелічених усередині квадратних дужок крім тих, які перелічені за значком заперечення.

Приклад використання:

Запрос.Текст = //знайдемо всю номенклатуру яка містить корінь ТАБУР і починається //або з маленької або з великої літери т "ВИБРАТИ | Номенклатура.Посилання |З | Довідник.Номенклатура ЯК Номенклатура |ДЕ | Товари.Найменування ПОДІБНО ""[Тт ]абур%""";

5. Конструкція ДОЗВОЛЕНІ- цей оператор дозволяє вибрати лише записи з БД, куди викликає має право читання. Ці права налаштовуються лише на рівні записів (RLS).

Синтаксис: ДОЗВОЛЕНІ пишеться після ключового слова ВИБРАТИ

Приклад використання:

Запит.Текст = "ВИБРАТИ ДОЗВОЛЕНІ | Контрагенти.Посилання |З | Довідник.Контрагенти ЯК Контрагенти";

6. Конструкція РІЗНІ- дозволяє вибрати записи, у яких відсутні повторні записи.

Синтаксис: РІЗНІ пишеться після ключового слова ВИБРАТИ

Приклад використання:

Запрос.Текст = // вибирає записи на які є права у читача "ВИБРАТИ РІЗНІ | Контрагенти. Найменування | З | Довідник. Контрагенти ЯК Контрагенти";

Також Конструкція РІЗНІ може використовуватися з оператором ДОЗВОЛЕНІ та іншими операторами.

Приклад використання:

Запрос.Текст = // вибирає різні записи на які є права у читача "ВИБРАТИ ДОЗВОЛЕНІ РІЗНІ | Контрагенти. Найменування | З | Довідник. Контрагенти ЯК Контрагенти";

7. Конструкція ПЕРШІ- вибирає вказану в параметрі кількість записів із результату запиту.

Синтаксис: ПЕРШІ<число>

Приклад використання:

Запит. Текст = // Вибирають перші 4 номери ВМД з довідника "ВИБРАТИ ПЕРШІ 4 | Номери ВМД. Посилання | З | Довідник.

8. Конструкція ДЛЯ ЗМІНИ- дозволяє заблокувати таблицю, працює лише у транзакціях (актуально лише автоматичних блокувань).

Синтаксис: ДЛЯ ЗМІНИ<НаименованиеТаблицы>

Приклад використання:

Запит.Текст = "ОБРАТИ |

9. Конструкція ПОРЯДОЧИТИ ПО- впорядковує дані щодо певного поля. Якщо полем є посилання, то при установці прапора Автоупорядкуваннябуде відбуватися сортування за поданням посилання, якщо прапор вимкнено, то посилання сортуються за старшинством адреси посилання в пам'яті.

Синтаксис: ВПОРЯДКУВАТИ ЗА<НаименованиеПоля>Автоупорядкування

Приклад використання:

Запит.Текст = "ВИБРАТИ | Вільні Залишки Залишки. Номенклатура ЯК Номенклатура, | Вільні Залишки Залишки. ЧИВАННЯ";

10. Конструкція ЗГРУПУВАТИ ПО- використовується для угрупування рядків запиту за певними полями. Числові поля повинні використовуватись із будь-якою агрегатною функцією.

Синтаксис: ЗГРУПУВАТИ ПО<НаименованиеПоля1>, .... , <НаименованиеПоляN>

Приклад використання:

Запит.Текст = "ВИБРАТИ | ТовариНа Складах.Номенклатура ЯК Номенклатура, | ТовариНаСкладах.Склад, | кладах.Склад";

11. Конструкція МАЮЧІ- дозволяє застосувати агрегатну функцію умови вибірки даних, схожа на конструкцію ДДЕ.

Синтаксис: МАЮЧІ<агрегатная функция с условием>

Приклад використання:

Запит.Текст = // вибирає згруповані записи де поле У наявності більше 3 " ВИБРАТИ | Товари на складах. Номенклатура ЯК Номенклатура, | Товари на складах. склад, | СУМА (Товари на складах. | ТовариНаСкладах.Номенклатура, | ТовариНаСкладах.Склад | |МІЮЧІ | СУМА(ТовариНаСкладах.ВНаявності) > 3" ;

12. Конструкція ІНДЕКСУВАТИ ПО- Використовується для індексації поле запиту. Запит з індексацією довше виконується, але прискорює пошук індексованих полів. Можна використовувати лише у віртуальних таблицях.

Синтаксис: ІНДЕКСУВАТИ ПО<Поле1, ... , ПолеN>

Приклад використання:

Запрос.Текст = " ВИБРАТИ | Тз. Найменування ОС, | Тз. Номер Папки, | Тз. КодОС, | Тз. Термін, | Тз. Тип | ПОМІСТИТИ Дані Тз | .КодОС";

13. Конструкція ДЕ- дозволяє накласти умову будь-які поля вибірки. В результаті потраплять записи, що тільки задовольняють умові.

Синтаксис: ДЕ<Условие1 ОператорЛогСоединения УсловиеN>

Приклад використання:

Запит.Текст = //вибираються всі записи у яких КомпенсаціяЗалишок<>0 і //СуммаДляРозчКомпОстаток > 100 " ВИБРАТИ | КомпенсаціяРПостатки.Контрагент, | КомпенсаціяРПостатки.Дитина, | КомпенсаціяРпостатки.КомпенсаціяЗалишок, | КомпенсаціяРпостач. Статті ЯК КомпенсаціяРПОстатки |ДЕ |КомпенсаціяРПОстатки.КомпенсаціяЗалишок<>0 | І КомпенсаціяРПОстатки.СуммаДляРозчКомпОстаток > 100" ;

14. Конструкція ПІДСУМКИ... ЗА ЗАГАЛЬНІ- застосовується для підрахунку результатів, у конструкції вказуються поля якими вважатимуться підсумки і агрегатні функції які застосовуються до підсумковим полям. При використанні підсумків на кожне поле наступне після конструкції ПІДСУМКИ проводиться групування даних. Є необов'язкова конструкція ЗАГАЛЬНІ, її використання також забезпечує додаткове угруповання. Приклад результату запиту ви побачите нижче.

Синтаксис: ПІДСУМКИ<АгрегатнаяФункция1, ... , АгрегатнаяФункцияN>ПЗ<ОБЩИЕ> <Поле1, ... , ПолеN>

Приклад використання:

Запит.Текст = "ВИБРАТИ | Розрахунки.ДоговірКонтрагенту.ВидДоговіру ЯК ВидДоговіру, | Розрахунки.ДоговірКонтрагенту ЯК Договір, | Розрахунки.Контрагент, | | СУМА (Залишок) |ПО | ЗАГАЛЬНІ, | ВидДоговору ";

На малюнку обведені угруповання які утворилися в ході виконання запиту, найвище відноситься до секції ЗАГАЛЬНІ, а друге до поля ДоговірКонтрагентуВідДоговіру.

; Вкладені запити (у розробці).

Надходження товарів та послуг.

Нові вкладки: Угруповання

Теоретична частина уроку №5

Угруповання у запиті — це об'єднання кількох рядків вибірки в один, при цьому використовується два види полів:

  • Групувальні. Рядки, в яких набір цих полів набуває однакових значень, згортаються один рядок;
  • Груповані. Для згорнутих рядків значення цих полів розраховуються за допомогою агрегатних функцій:
    • Сума. Значення поля сумується за всіма згрупованими рядками;
    • Середнє. Розраховується середнє значення поля серед згрупованих рядків;
    • Максимум. Обчислюється максимальне значення поля серед згрупованих рядків;
    • Мінімум. Обчислюється мінімальне значення поля серед згрупованих рядків;
    • Кількість. Обчислюється кількість згрупованих рядків, незалежно від того якого поля застосовується дана агрегатна функція;
    • Кількість різних. Обчислюється кількість різних значень даного поля за всіма згрупованими рядками.

Приклад 1. Нехай ми маємо таблицю з двома полями: Фрукті Кількість. Згрупувати рядки з однаковими фруктами та порахувати загальну кількість по кожному фрукту.

За умовами прикладу поле Фрукт- групувальне, а поле Кількість- групується за допомогою агрегатної функції Сума.

Після угруповання наша таблиця буде виглядати так:

Приклад 2. На складі фрукти зберігаються у ящиках, коробках тощо. Оскільки фрукти приходили різними партіями, вартість них може різнитися. Порахувати максимальні ціни фруктів кожного виду тари.

Таким чином, у нас є таблиця з трьома полями Фрукт, Тара, Ціна.Необхідно згрупувати рядки по полях Фрукті Тара, а по полю Цінапорахувати Максимум.

У цьому прикладі у нас два групувальні поля і одне груповане. Після угруповання наша таблиця буде виглядати так:

Тепер подивимося, як реалізовано механізм угруповання у конструкторі запитів 1с. У конструкторі перейдемо на вкладку Угруповання, Вона складається з трьох розділів:

  • Поля (червона рамка). У цьому розділі представлені всі поля, доступні для угруповання. Це поля таблиць вибраних на вкладці Таблиці та поля. Їх необхідно розподілити між наступними двома розділами;
  • Групувальні поля(зелена рамка). У цей розділ необхідно перетягнути (мишею або за допомогою кнопок) поля, за якими буде проводитися угруповання;
  • Груповані поля(Синя рамка). У цей розділ необхідно перетягнути (мишею або кнопками) поля, значення яких будуть розраховуватися за допомогою агрегатних функцій. Даний розділ являє собою таблицю, в першій колонці якої знаходиться поле, що групується, а в другій вибирається зі списку агрегатна функція.

Слід зазначити, що якщо ви використовуєте угруповання у запиті, то вам необхідно розподілити усі вибрані на запит поля між розділами групувальнихі групуються. Якщо ви цього не зробите, то поля, що залишилися, автоматично перейдуть у розділ групуються.Виняток становлять лише поля створені вручну і які містять полів таблиць (рядкові чи числові, наприклад). Їх включати в угруповання не обов'язково, оскільки їх значення однаково всім рядків вибірки.

Практична частина уроку №5

Розберемо розв'язання задачі, наведеної на початку уроку. Нагадаю умови:

Завдання: отримати суми надходжень за поточний рік з розбивкою по місяцях. Для отримання даних використовувати проведені документи Надходження товарів та послуг.

  • Створимо новий запит;
  • Запустимо конструктор запитів;
  • Виберемо таблицю НадходженняТоварівПослугз гілки Документи;
  • З таблиці НадходженняТоварівПослугвиберемо поля Датаі Сума документа;
  • Виділимо поле Датата натиснемо кнопку Змінити поточний елемент, щоб відкрити його в редакторі довільних виразів;

  • В розділі Функції мови запитівзнайдемо гілку Функції роботи з датами;
  • З цієї гілки перетягнемо в розділ виразів функцію Місяцьта замінимо параметр Датана обране нами поле із датою надходження;

  • Натисніть кнопку ОК, у нижній частині редактора довільних виразів;
  • Перейдемо на вкладку Об'єднання / Псевдонімита встановимо для поля з номером місяця псевдонім Місяць;

  • Перейдемо на вкладку Умови;
  • В розділі Поля, розкриємо гілку НадходженняТоварівПослугза допомогою кнопки "+";
  • Знайдемо реквізит Датаі перетягнемо його в розділ умови, виберемо оператор порівняння міжі вкажемо параметри початку та закінчення року, наприклад Початок рокуі Кінець року;
  • З розділу Поляперетягнемо реквізит Проведено, поставимо у рядку з умовою прапор Довільнеі зітремо зайву частину умови «= &Проведено»;
  • Перейдемо на вкладку Угруповання;
  • У розділ групувальних полівперетягнемо поле МІСЯЦЬ (Надходження Товарів Послуг. Дата), а розділ групованих полів — НадходженняТоварівПослуг.СумаДокумента. За замовчуванням установиться агрегатна функція Сума, у разі вона нас цілком влаштовує;


  • Написання запиту завершено, натисніть кнопку ОК, щоб закрити конструктор запитів;

У результаті ми отримаємо запит з наступним текстом.

ПитанняОбхід запиту по угрупованням, як збираються дані у запиті з конструкцією ПІДСУМКИ ПЗ. Припустимо, потрібно організувати цикл, у якому має формуватися масив замовлень, у межах Контрагентів і Адреси доставки. Як це зробити?
ВідповідьПишемо запит:

1С (Код)

Запит .Текст = "ВИБРАТИ // Адреса доставки укладаємо "ВИРАЗИТИ", т.к. у метаданих Адреса доставки - рядок необмеженої довжини і якщо її не перетворити на рядок обмеженої довжини, //то її буде неможливо використовувати в конструкції "ПІДСУМКИ ПО" // і ми отримаємо повідомлення про помилку виду " Неприпустиме поле для угруповання" |З |ДЕ | усЗамовленняНаВідвантаження.Проведено | |Упорядкувати за |ПІДСУМКИ ПО | Контрагент, | Адреса доставки";

Запит< spanclass="k" > . < /span>Текст< spanclass="k" > =< /span>

< spanclass="s" >"ВИБРАТИ

| усЗамовленняНаВідвантаження. Посилання ЯК Посилання,

| усСтатусДокументів.Статус ЯК Статус,

">// Адреса доставки укладаємо в "ВИРАЗИТИ ", тому що в метаданих Адреса Доставки - рядок необмеженої довжини і якщо її не перетворити на рядок обмеженої довжини,

//то її буде неможливо використовувати в конструкції"ПІДСУМКИ ПО"

// і ми отримаємо повідомлення про помилку виду"< strong>Неприпустиме поле для угруповання< /strong> "

| ВИРАЗИТИ(усЗамовленняНаВідвантаження.АдресДоставки ЯК РЯДКУ(350)) ЯК АдресаДоставки

|З

| РеєстрВідомостей.усСтатусДокументів ЯК усСтатусДокументів

| ЛІВОЕ З'ЄДНАННЯ Документ.усЗамовленняНаВідвантаження ЯК усЗамовленняНаВідвантаження

| ПО усСтатусДокументів.Документ = усЗамовленняНаВідвантаження.Посилання

|ДЕ

| усЗамовленняНа відвантаження. Проведено

|

|Упорядкувати за

| усСтатусДокументів.Статус.Порядок

|ПІДСУМКИ ПО

| Контрагент,

| АдресаДоставки"< /span> < spanclass="k" > ; < /span>

Результатом цього запиту буде набір даних приблизно такого вигляду:

Контрагент 1

  • АдресаДоставки1

Контрагент 1

  • АдресаДоставки2

Контрагент2

  • АдресаДоставки3

Тепер нам потрібно отримати три масиви:
- Замовлення 1, замовлення 2, замовлення 3
- Замовлення 4, замовлення 5
- Замовлення 6
Для отримання цих масивів потрібно організувати обхід наведеного вище запиту «Угруповання з ієрархією»:

1С (Код)

СпосібВибірки =ОбхідРезультатуЗапиту .За угрупуванням з ієрархією ;Результат =Запит .Виконати();ВибіркаПершогоРівня =Результат .Вибрати (СпосібВибірки ); БувайВибіркаПершогоРівня .Наступний () Цикл ВибіркаДругогорівня =ВибіркаПершогоРівня .Вибрати (СпосібВибірки );мсвЗамовлень .Очистити (); БувайВибіркаДругогорівня .Наступний () Цикл // усередині кожного рівня ієрархії замовлення впорядковані: "Створено", "У роботу", "Ведуться роботи", "Готов до відвантаження" (саме в такому порядку порядок встановлений у перерахуванні)ВибіркаТретьогоРівня =ВибіркаДругогорівня .Вибрати (ОбхідРезультатуЗапиту .Прямий ); БувайВибіркаТретьогоРівня .Наступний () Цикл // усередині кожного рівня ієрархії замовлення впорядковані: "Створено", "У роботу", "Ведуться роботи", "Готов до відвантаження" (саме в такому порядку порядок встановлений у перерахуванні)мсвЗамовлень .Додати (ВибіркаТретьогоРівня .Посилання ); КінецьЦикл;Сформувати Відбір За замовленням (мсвЗамовлень ); КінецьЦикл; КінецьЦикл;

СпосібВибірки< spanclass="k" > =< /span>ОбхідРезультатуЗапиту< spanclass="k" > . < /span>За угрупуванням з ієрархією< spanclass="k" > ; < /span>

Результат< spanclass="k" > =< /span>Запит< spanclass="k" > . < /span> < spanclass="k" >Виконати< /span> < spanclass="k" > (< /span> < spanclass="k" > ) < /span> < spanclass="k" > ; < /span>

ВибіркаПершогоРівня< spanclass="k" > =< /span>Результат< spanclass="k" > . < /span>Вибрати< spanclass="k" > (< /span>СпосібВибірки< spanclass="k" > ) < /span> < spanclass="k" > ; < /span>

< spanclass="k" >Бувай< /span>ВибіркаПершогоРівня< spanclass="k" > . < /span>Наступний< spanclass="k" > (< /span> < spanclass="k" > ) < /span> < spanclass="k" >Цикл< /span> < spanclass="c" > // усередині кожного рівня ієрархії замовлення впорядковані: "Створено", "У роботу", "Ведуться роботи", "Готов до відвантаження" (саме в такому порядку порядок встановлений у перерахуванні)

ВибіркаДругогорівня< spanclass="k" > =< /span>ВибіркаПершогоРівня< spanclass="k" > . < /span>Вибрати< spanclass="k" > (< /span>СпосібВибірки< spanclass="k" > ) < /span> < spanclass="k" > ; < /span>

мсвЗамовлень< spanclass="k" > . < /span>Очистити< spanclass="k" > (< /span> < spanclass="k" > ) < /span> < spanclass="k" > ; < /span> < spanclass="c" > // Очистили перед додаванням першого до списку

< spanclass="k" >Бувай< /span>ВибіркаДругогорівня< spanclass="k" > . < /span>Наступний< spanclass="k" > (< /span> < spanclass="k" > ) < /span> < spanclass="k" >Цикл< /span> < spanclass="c" > // усередині кожного рівня ієрархії замовлення впорядковані: "Створено", "У роботу", "Ведуться роботи", "Готов до відвантаження" (саме в такому порядку порядок встановлений у перерахуванні)

ВибіркаТретьогоРівня< spanclass="k" > =< /span>ВибіркаДругогорівня< spanclass="k" > . < /span>Вибрати< spanclass="k" > (< /span>ОбхідРезультатуЗапиту< spanclass="k" > . < /span>Прямий< spanclass="k" > ) < /span> < spanclass="k" > ; < /span> < spanclass="c" > // параметр можна не передавати, за замовчуванням спосіб вибірки прямої

< spanclass="k" >Бувай< /span>ВибіркаТретьогоРівня< spanclass="k" > . < /span>Наступний< spanclass="k" > (< /span> < spanclass="k" > ) < /span> < spanclass="k" >Цикл< /span> < spanclass="c" > // усередині кожного рівня ієрархії замовлення впорядковані: "Створено", "У роботу", "Ведуться роботи", "Готов до відвантаження" (саме в такому порядку порядок встановлений у перерахуванні)

мсвЗамовлень< spanclass="k" > . < /span>Додати< spanclass="k" > (< /span>ВибіркаТретьогоРівня< spanclass="k" > . < /span>Посилання< spanclass="k" > ) < /span> < spanclass="k" > ; < /span>

< spanclass="k" >КінецьЦикл< /span> < spanclass="k" > ; < /span>

Сформувати Відбір За замовленням< spanclass="k" > (< /span>мсвЗамовлень< spanclass="k" > ) < /span> < spanclass="k" > ; < /span> < spanclass="c" > // Тут ми по черзі отримаємо в циклі необхідні масиви: [замовлення 1, замовлення 2, замовлення 3], потім [замовлення 4, замовлення 5] та [замовлення 6]

< spanclass="k" >КінецьЦикл< /span> < spanclass="k" > ; < /span>

< spanclass="k" >КінецьЦикл< /span> < spanclass="k" > ; < /span>

Примітка. Конструкція «ПОРЯДОЧИТИ ПО УСТАТУ Документів. Статус. Порядок» буде спрацьовувати всередині кожного масиву, тобто. замовлення будуть упорядковані за статусом у кожному з отриманих масивів.

Довідка щодо розрахунку підсумків запиту з опису в довідці Конфігуратора

Розрахунок підсумків запиту

Пропозиція ПІДСУМКИ дозволяє визначити, розрахунок яких підсумків необхідний у запиті. Під час розрахунку результатів обчислюються значення агрегатних функцій за вибірками з однаковими значеннями полів – контрольних точок. Підсумки додаються до результату запиту як підсумкові рядки.

Порядок розрахунку підсумків запиту описується відповідно до таких правил:

<Описание итогов>

<Итоги> [<Список агрегатных функций>]

<Итоги>

ПІДСУМКИ [<Список итоговых_полей>] ПО [ЗАГАЛЬНІ]<Список контрольных точек>

<Список итоговых_полей>

<Итоговое_поле> [,<Список_итоговых_полей> [, …]]

<Итоговое_поле>

<Агрегатная_функция> | <Выражение>[[ЯК]<Псевдоним_поля>]

<Список контрольных точек>

<Контрольная точка> [, <Контрольная точка> [, …]]

<Контрольная точка>

<Выражение>[[ТІЛЬКИ] ІЄРАРХІЯ] | [ПЕРІОДАМИ(Секунда | Хвилина | Година | День | Тиждень | Місяць | Квартал | Рік | Декада | Півріччя
[,<Литерал типа DATE> | <Идентификатор параметра>]
[,<Литерал типа DATE> | <Идентификатор параметра>])] [[ЯК] Псевдонім поля]

Опис підсумків починається з обов'язкового ключового слова ПІДСУМКИ.

<Список агрегатных функций>містить список агрегатних функцій, які потрібно розраховувати в підсумках. Агрегатні функції розглядаються у .

Крім загальних результатів, можна задати розрахунок результатів по контрольним точкам. Для цього після обов'язкового ключового слова необхідно вказати<Список контрольных точек>. Кожна<Контрольная точка>містить<Выражение>, що обчислюється під час виконання запиту. По кожній комбінації значень цих виразів будуть розраховані та додані до результату запиту підсумкові рядки.

Якщо контрольна точка є полем — субконто регістра бухгалтерії, то при отриманні підсумків цієї контрольної точки будуть ігноруватися значення NULL.

Агрегатні функції мови запитів

У мові запитів передбачені агрегатні функції, що використовуються при групуванні результатів запиту та підрахунку підсумків. Агрегатні функції призначені для узагальнення значень вказаного параметра. Визначено такі агрегатні функції:

<Агрегатная функция>

СУМА (<Выражение>) |
СЕРЕДНЕ (<Выражение>) |
МІНІМУМ (<Выражение>) |
МАКСИМУМ (<Выражение>) |
КІЛЬКІСТЬ ([РІЗНІ]<Выражение> | *)

Приклад:

Вибрати
Накладна.Номенклатура.Найменування,
Сума (Накладна.Сума) Як Сума,
Середнє (Накладна.Сума) Як Середнє,
Максимум (Накладна.Сума) Як Максимум,
Мінімум (Накладна.Сума) Як Мінімум,
Кількість (Накладна.Сума) Як Кільк

З

Згрупувати По
Номенклатура.

Підсумки Загальні

Результат запиту:

Найменування Сума Середнє Максимум Мінімум Кільк
265 955,45 12 511,12 40 000,23 555 8
Штани дитячі 28 500,22 9 500,07 15 000 3 000 3
Сорочка «Ковбойка» 24 000 8 000 16 000 4 000 3
Джинси жіночі 63 555 6 355,5 30 000 555 10
Светер Дитячий 6 400 6 400 6 400 6 400 1
Раковина «Лілія» 60 000,23 30 000,115 40 000,23 20 000 2
Мийдодір «Акваріум» 65 500 21 833,33 40 000 8 000 3
Змішувач «Ультра» 15 000 15 000 15 000 15 000 1
Кухонний комбайн Крупс 3 000 3 000 3 000 3 000 1

Агрегатні функції можуть використовуватися в списку полів вибірки, пропозиціях МАЮЧІ, ПІДСУМКИ, ПОРЯДОЧИТИ ПО.

Агрегатна функція СУМА

Функція обчислює арифметичну суму всіх значень поля, що потрапили у вибірку.

Як параметр функції можна вказувати лише поля, що містять числове значення.

Якщо поле не може містити числових значень, застосування функції СУМА до такого поля викликає помилку. Якщо поле може містити числові значення (має складовий тип даних), то ця функція може бути застосована до такого поля. Але якщо серед значень поля у вибірці зустрінеться нечислове значення (крім значень NULL), це викликає помилку.

Агрегатна функція СЕРЕДНЕ

Функція обчислює середнє значення всіх значень поля, що потрапили у вибірку.

Як параметр функції можна вказувати лише посилання на поля, що містять числове значення.

Якщо поле не може містити числових значень, то застосування функції СЕРЕДНЕ до такого поля викликає помилку. Якщо поле може містити числові значення (має складовий тип даних), то ця функція може бути застосована до такого поля. Але якщо серед значень поля у вибірці зустрінеться нечислове значення (крім значень NULL), це викликає помилку.

Агрегатна функція МІНІМУМ

Функція обчислює мінімальне значення зі всіх значень поля, що потрапили у вибірку.

При визначенні мінімального значення застосовують правила порівняння значень, описані в .

Агрегатна функція МАКСИМУМ

Функція обчислює максимальне значення з усіх значень поля, що потрапили у вибірку.

Як параметр функції можна вказувати посилання на поля, що містять значення будь-якого типу.

При визначенні максимального значення застосовують правила порівняння значень, описані в .

Агрегатна функція КІЛЬКІСТЬ

Функція підраховує кількість значень параметра, що потрапили у вибірку. На відміну від інших агрегатних функцій функція КІЛЬКІСТЬ допускає три способи використання.

  • По-перше, функція дозволяє підрахувати кількість значень вказаного поля, не рівних NULL.
  • По-друге, функція дозволяє підрахувати кількість різних значень вказаного поля, не рівних NULL. Для цього перед специфікацією поля треба зазначити ключове слово РІЗНІ.
  • По-третє, функція дозволяє підрахувати кількість рядків у результаті запиту. Для цього як параметр функції треба вказати зірочку «*».

Як параметр функції можна вказувати посилання на поля, що містять значення будь-якого типу.

Приклад:

ВИБРАТИ
Кількість (*) Як Всього,
Кількість (Різні Накладна.Номенклатура) Як Різні

З
Документ.ВитратнаНакладна.Склад Як Накладна

Результат запиту:

Усього Різні
24 8

Розрахунок загальних підсумків

Для розрахунку підсумків по всій таблиці у пропозиції ПІДСУМКИ слід вказати слово ЗАГАЛЬНІ. У цьому випадку буде обчислено значення агрегатних функцій для всіх записів таблиці.

Приклад:



ПІДСУМКИ СУМА(Кількість) ЗАГАЛЬНІ

Результат запиту:

Товар Кількість Номер Одержувач
238
Кран 10 РНК-1 Урюпінськторг
Кран 8 РНК-3 Маг. «Господар»
Кран 44 РНК-4 Маг. "Гігант"
Кран 22 РНК-5 Урюпінськторг
Вантус 5 РНК-1 Урюпінськторг
Вантус 1 РНК-3 Маг. «Господар»
Вантус 14 РНК-4 Маг. "Гігант"
Стіл 1 РНК-1 Урюпінськторг
Стіл 15 РНК-2 Маг. «Меблі»
Стіл 10 РНК-4 Маг. "Гігант"
Стілець 55 РНК-2 Маг. «Меблі»
Стілець 5 РНК-3 Маг. «Господар»
Стілець 32 РНК-4 Маг. "Гігант"
Стілець 16 РНК-5 Урюпінськторг

Логічні вирази у мові запитів

У мові запитів у операціях вибору та умовах відборів використовуються логічні висловлювання:

<Логическое выражение>

<Выражение> |
<Выражение> <Операция сравнения> <Выражение> |
<Выражение>[НЕ] В [ІЄРАРХІЇ] (<Список значений> | <Массив значений>) |
<Выражение>[НЕ] В [ІЄРАРХІЇ] (<Описание запроса>) |
<Выражение>[НЕ] МІЖ<Выражение>І<Выражение> |
<Выражение>Є [НЕ] NULL |
<Выражение>ПОСИЛАННЯ<Имя таблицы> |
<Выражение>[НЕ] подібно<Литерал типа СТРОКА>
[СПЕЦСИМВОЛ<Литерал типа СТРОКА>]

<Операция сравнения>

> | < | = | >= | <= | <>

<Список значений>

<Выражение>[, <Выражение> [, …]]

Логічним виразом може бути:

У порівнянні значень використовуються правила порівняння значень, описані нижче.

Правила порівняння значень

Оскільки мови запитів можуть порівнюватися значення різних типів, визначено правила, якими виконується порівняння двох значень. Ці правила використовуються для:

  • порівняння значень в операторах порівняння;
  • визначення максимального та мінімального значень в агрегатних функціях МІНІМУМ та МАКСИМУМ;
  • упорядкування записів результату запиту відповідно до порядку, заданого у реченні УПОРОДОЧИТИ ПО.

Якщо типи значень відрізняються один від одного, то відносини між значеннями визначаються на підставі пріоритету типів:

  • тип NULL (найнижчий);
  • тип Бульово;
  • тип Число;
  • тип Дата;
  • тип Рядок;
  • типи посилань.

Відносини між різними типами посилань визначаються на основі внутрішніх посилальних номерів таблиць, відповідних тому чи іншому типу.

Якщо типи даних збігаються, то проводиться порівняння значень за такими правилами:

  • у типу Бульова значення ІСТИНА більше значення БРЕХНЯ;
  • у типу Число звичайні правила порівняння для чисел;
  • у типу Дата більш ранні дати менше пізніших;
  • у типу Рядок порівняння проводиться відповідно до встановлених національних особливостей бази даних без урахування кінцевих прогалин;
  • типи посилань порівнюються на основі своїх значень (номери запису і т. п.);
  • не допускається порівняння полів необмеженої довжини (рядки необмеженої довжини, СховищеЗначення, поле ТипЗначення з таблиці планів видів характеристик).

Важливо!Будь-яка операція порівняння двох значень, у якій бере участь хоча б одне значення NULL, дає результат, аналогічний значенню БРЕХНЯ.

Оператор перевірки збігу значення

Форма оператора В для перевірки збігу з одним із перерахованих

Оператор дозволяє перевірити, чи збігається значення виразу, зазначеного праворуч від нього, з одним із значень, описаних зліва. Якщо збігається хоча б з одним – результатом оператора буде ІСТИНА, інакше – БРЕХНЯ. Застосування НЕ змінює дію оператора на зворотне. Порівняння значень проводиться за правилами, описаними у .

Приклад:

Вибрати

Де
Довідник.Номенклатура.Батько.Найменування
В («Побутова техніка», «Оргтехніка»)

Форма оператора для перевірки приналежності по ієрархії

Для довідників перевірка може здійснюватися і належність з ієрархії. Результатом оператора В ІЄРАРХІЇ буде ІСТИНА, якщо значення виразу зліва є посиланням на елемент довідника і входить до множини значень праворуч або ієрархічно належить якійсь групі, що міститься в цій множині:

Приклад:

// Як параметр Група у запит передається посилання
/ / На якусь групу довідника Номенклатура.

Вибрати
Довідник.Номенклатура.Найменування

Як безліч значень, на збіг з якими виконується перевірка, може фігурувати і результат запиту. У цьому випадку праворуч від оператора необхідно вказати опис запиту.

Приклад:

Вибрати
Довідник.Номенклатура.Найменування

Форма оператора для перевірки збігу значення з одним з результату запиту

Прикладом застосування цього оператора може бути таке:

Приклад:


ВИБРАТИ
Товари.Найменування
З
Довідник.Товари ЯК
ДЕ
Товари.Посилання В
ВИБРАТИ
РасхНаклСклад.Товар
З

)

Результат запиту:

Для отримання протилежного результату, тобто якщо потрібно визначити, що значення не збігається з жодним з результату запиту, запит виглядає наступним чином:

Приклад:

// Вибрати назви товарів, які були присутні у видаткових накладних
ВИБРАТИ
Товари.Найменування
З
Довідник.Товари ЯК
ДЕ
Товари.Посилання НЕ В
ВИБРАТИ
РасхНаклСклад.Товар
З
Документ.РозхНакл.Склад ЯК РасхНаклСклад
)

Результат запиту:

Зауважимо, що з запиту операції можливо звернення до полів таблиць, які зустрічалися в зовнішньому запиті до появи операції.

Приклад:

// Вибрати назви товарів, які були присутні у видаткових накладних
ВИБРАТИ
Товари.Найменування
З
Довідник.Товари ЯК
ДЕ
Товари.Посилання В
ВИБРАТИ
РасхНаклСклад.Товар
З
Документ.РозхНакл.Склад ЯК РасхНаклСклад
ДЕ
РасхНаклСостав.Товар = Товари.Посилання
)

Результат запиту:

Оператор перевірки значення значення в діапазон

Оператор МІЖ дозволяє перевірити, чи входить значення виразу, вказаного праворуч від нього, в діапазон, вказаний зліва (разом із межами діапазону). Якщо входить – результатом оператора буде ІСТИНА, інакше – БРЕХНЯ. Застосування НЕ змінює дію оператора на зворотне. Порівняння значень проводиться за правилами, описаними у .

Приклад:

Вибрати


Де
Довідник.Номенклатура.ЗакупівельнаЦіна МІЖ 100 І 1000

Оператор перевірки значення NULL

Оператор Є NULL дозволяє перевірити значення виразу ліворуч від нього на NULL. Якщо значення дорівнює NULL – результатом оператора буде ІСТИНА, інакше – БРЕХНЯ. Застосування НЕ змінює дію оператора на зворотне.

Приклад:

Вибрати
Довідник.Номенклатура.Найменування,
Довідник.Номенклатура.ЗакупівельнаЦіна
Де
Довідник.Номенклатура.ЗакупівельнаЦіна Є NULL

Оператор перевірки значення посилання

Оператор ПОСИЛАННЯ дозволяє перевірити, чи є значення виразу, вказаного праворуч від нього, посиланням на таблицю, вказану зліва. Якщо так – результатом оператора буде ІСТИНА, інакше – БРЕХНЯ. Розіменування таблиць описано в параграфі «Розім'янення полів».

Приклад:

Вибрати
Довідник.Номенклатура.Найменування,
Довідник. Номенклатура. Одиниця Вимірювання
Де
Довідник.Номенклатура.ОдиницяВимірювання Посилання Довідник.ОдиниціВимірювання

Оператор перевірки рядка на зразок шаблону

Оператор подібно дозволяє порівняти значення виразу, вказаного зліва від нього, з рядком шаблону, вказаним праворуч. Значення виразу має мати рядок. Якщо значення виразу задовольняє шаблон - результатом оператора буде ІСТИНА, інакше - БРЕХНЯ.

Наступні символи у рядку шаблону є службовими і мають сенс, відмінний від символу рядка:

  • % (відсоток): послідовність, яка містить будь-яку кількість довільних символів
  • _ (підкреслення): один довільний символ
  • […] (у квадратних дужках один або кілька символів): будь-який одиночний символ із перелічених усередині квадратних дужок
    У перерахуванні можуть зустрічатися діапазони, наприклад, a-z, що означають довільний символ, що входить в діапазон, включаючи кінці діапазону.
  • [^…] (у квадратних дужках значок заперечення, за яким слідує один або кілька символів): будь-який одиночний символ, крім тих, які перелічені слідом за значком заперечення

Будь-який інший символ означає сам себе і не несе жодного додаткового навантаження.

Якщо як самого себе необхідно записати один із перелічених символів, то йому має передувати<Спецсимвол>. Сам<Спецсимвол>(будь-який відповідний символ) визначається в цьому ж операторі після ключового слова СПЕЦСИМВОЛ.

Наприклад, шаблон "%АБВ[абвг]_абв%" СПЕЦСИМВОЛ "" означає підрядок, що складається з послідовності символів:
літери А; літери Б; літери У; однієї цифри; однією з букв а, б, або г; символ підкреслення; літери а; літери б; літери ст.

Причому перед цією послідовністю може бути довільний набір символів.

Підсумки з ієрархії

Є можливість розрахувати підсумки з ієрархії. Для цього після імені поля, для якого обчислюються підсумки, необхідно вказати ключове слово ІЄРАРХІЯ. В результаті будуть розраховані підсумки контрольних точок і підсумки ієрархії для контрольних точок.

Приклад:

ВИБРАТИ Док.Товар, Док.Кількість, Док.Посилання.Номер, Док.Посилання.Отримувач
З Документ.РозхНакл.Склад Як Док
ВПОРЯДОЧИТИ ЗА Док.Товар
ПІДСУМКИ СУМА(Кількість) ПО Док.Товар ІЄРАРХІЯ

Результат запиту:

Товар Кількість Номер Одержувач
Сантехніка 104
Кран 84
Кран 10 РНК-1 Урюпінськторг
Кран 8 РНК-3 Маг. "Господар"
Кран 44 РНК-4 Маг. "Гігант"
Кран 22 РНК-5 Урюпінськторг
Вантус 20
Вантус 5 РНК-1 Урюпінськторг
Вантус 1 РНК-3 Маг. "Господар"
Вантус 14 РНК-4 Маг. "Гігант"
Меблі 134
Стіл 26
Стіл 1 РНК-1 Урюпінськторг
Стіл 15 РНК-2 Маг. "Меблі"
Стіл 10 РНК-4 Маг. "Гігант"
Стілець 108
Стілець 55 РНК-2 Маг. "Меблі"
Стілець 5 РНК-3 Маг. "Господар"
Стілець 32 РНК-4 Маг. "Гігант"
Стілець 16 РНК-5 Урюпінськторг

Приклад:

ВИБРАТИ Док.Товар, Док.Кількість, Док.Посилання.Номер, Док.Посилання.Отримувач
З Документ.РозхНакл.Склад Док
ВПОРЯДОЧИТИ ЗА Док.Товар
ПІДСУМКИ СУМА(Кількість) ПО Док.Товар ТІЛЬКИ ІЄРАРХІЯ

Результат запиту:

Товар Кількість Номер Одержувач
Сантехніка 104
Кран 10 РНК-1 Урюпінськторг
Кран 8 РНК-3 Маг. "Господар"
Кран 44 РНК-4 Маг. "Гігант"
Кран 22 РНК-5 Урюпінськторг
Вантус 5 РНК-1 Урюпінськторг
Вантус 1 РНК-3 Маг. "Господар"
Вантус 14 РНК-4 Маг. "Гігант"
Меблі 134
Стіл 1 РНК-1 Урюпінськторг
Стіл 15 РНК-2 Маг. "Меблі"
Стіл 10 РНК-4 Маг. "Гігант"
Стілець 55 РНК-2 Маг. "Меблі"
Стілець 5 РНК-3 Маг. "Господар"
Стілець 32 РНК-4 Маг. "Гігант"
Стілець 16 РНК-5 Урюпінськторг
Posted in , Tagged #

У минулому уроці було розглянуто фільтрацію результатів запиту. За допомогою пропозиції ДЕзадавали умови відбору з вихідних таблиць, щоб у запиті оброблялися ті записи, котрим умова истинно. Причому умова відбору могло бути як простим, і досить складним логічним виразом. Мета такої обробки - максимально обмежити обсяг одержуваних у результуючому наборі даних. Що далі робити з безліччю даних, які обмежити не вдалося, оскільки вони за всіма параметрами потрібні?

1. Навіщо групують дані?

Можливість мати у своєму розпорядженні всі дані в системі, безсумнівно, - запорука ефективного управління. Проте, як ми переконалися, всі дані всіх таблиць бази даних користувачам не потрібні. Та й потрібні дані існують не просто так, а для чогось.

Але навіть цілком коректно з цією метою відібрані дані аналізувати складно: їх буде занадто багато. Усі дані контролювати теж неможливо, а керувати ними - просто неможливо. Недарма стверджують, що суть управлінського обліку зводиться до проблеми певного агрегування (групування) інформації.

Перша причина угруповання - зменшити кількість інформації, звівши її до однорідних груп. Наприклад, суми продажу та покупок зводяться до груп, якими служать бухгалтерські рахунки. Потім за цими вже зведеними показниками формуються більші показники - балансові групи, різні звітні показники. Проте менше, ніж рахунком, уже не оперують.

План рахунків змінювали не так давно не в останню чергу для того, щоб без додаткового поділу отримувати прийняті в міжнародній практиці показники. Модні зараз різні системи ключових індикаторів також є агрегованими показниками, як правило, досить високого рівня.

Друга причина - нормування показників. Стандартні показники (рентабельність, оборотність тощо) можна вимірювати (наприклад, у кількісному вираженні) та порівнювати з ними.

Третя причина - спрощення аналітичних завданьза рахунок зниження розмірності. Якщо зафіксувати хоча б один вимір, складність зменшиться на порядок. Наприклад, вибрати марку та аналізувати тільки пропозиції всередині неї, хоча практично на конкурентних ринках доведеться аналізувати принаймні кілька марок.

Це не вичерпний список (є ще й гарна візуальна вистава та ін.). Але зрозуміло, що групувати інформацію важливо і потрібно, і робити це можна різними способами

2. Що таке угруповання?

Угруповання - це процес об'єднання в логічному порядку стовпців з значеннями, що повторюються. на Мал. 1представлений фрагмент таблиці товарів, яка може бути згрупована за будь-яким полем. Наприклад, за кольором корпусу - поряд виявляться прилади в корпусах одного кольору, або країна-виробник - тоді ми отримаємо інше угруповання, де поряд виявляться товари однієї країни. На малюнку блакитним кольором виділено товари у сріблястих корпусах; жовтим – товари виробництва Тайваню. Зрозуміло, це різні угруповання, і вони можуть і не перетинатися. Потрібного нам товару виробництва Тайваню може і не бути у сріблястому корпусі.

3. Як здійснюється угруповання?

Угруповання даних - це зрозумілий та технічно досить простий процес.

Вихідні дані в запиті можуть бути згруповані за однаковими значеннями полів і до них можна застосувати різні агрегатні функції. Групуються записи, які містять однакові значення групувального поля. Припустимо, поле Батько- і буде згруповано весь посуд, алкоголь тощо, поле Колір- і згруповано все продане за кольорами тощо).

Якщо застосувати ще й агрегатну функцію, результат поверне кількість наявних апаратів у сріблястих корпусах, у чорних корпусах і т. д. за всіма групами, які будуть визначатися кольором.

Для ілюстрації цього прикладу малюнку 1можна застосувати функцію КІЛЬКІСТЬ (Коліркорпусу). Рядки в результаті запиту будуть містити результати обчислення вказаних агрегатних функцій (СУМА, КІЛЬКІСТЬ, МІНІМУМ, МАКСИМУМ, СЕРЕДНЕ), розраховані (згруповані) за даними вихідних шпальт.

Агрегатні функції вказують у списку полів вибірки у реченнях ЗГРУПУВАТИ ПОПри цьому обов'язково потрібно вказати список полів, за якими необхідно проводити угруповання. Якщо не вказати угруповання, за замовчуванням буде одне число (для текстових полів - кількість, для числових - сума всіх записів).

4. Місце у запиті пропозиції ЗГРУПУВАТИ ПО

Щоб створювати запити, варто знати, куди потрапляють візуально вказані параметри при трансляції їх конструктором. Ось місце речення ЗГРУПУВАТИ ПОсеред інших пропозицій запиту.

Як бачите, воно слідує після пропозиції ДЕта перед пропозицією ВПОРЯДОЧИТИ(якщо такі є у запиті).

5. Приклад створення запиту з групуванням за допомогою конструктора запитів «1С:Підприємства 8.0»

За допомогою запиту, створюваного конструктором, ми хочемо вибрати назву проданих за певний період товарів, виручку та прибуток за їх продажами, згрупувати їх за товарними групами та отримати суму виручки та середній прибуток за кожною товарною групою.

Скористаємось запитом, який ми вже створили. Нижче наведено його текст (блок у зеленій рамці), який ми вже написали, і змінювати його не будемо. Копію попереднього запиту можна зробити у вікні Сервіс - Консоль Звітів, користуючись інструментами верхньої панелі, та викликати на редагування за допомогою кнопки F2.

Конструктор запитів викликається за допомогою кнопки нижньої панелі інструментів вікна редагування запиту.

Повернімося ще до тієї частини тексту, яку ми вже створили. Ми вибрали поля під назвою проданої номенклатури, суму продажу (назвали її виручкою). Скористалися можливістю отримати відразу результат обчислення полів, що містять виручку та собівартість (назвали цю різницю прибутком). Все це зроблено за допомогою закладок Таблиці та поля, Псевдонімиконструктора запитів «1С:Підприємства». Вибір полів зроблено з таблиць довідника номенклатура та регістру накопичення продажу компанії.

На закладці Зв'язкивстановлено з'єднання таблиць. Звичайно, для даного запиту ми могли б обійтися і однією таблицею, якщо віддали б перевагу іншому угрупованню. Але оскільки завдання передбачає розділити товари за групами, а відомості про належність до групи (тобто про поле Батько) перебувають у таблиці самого довідника, але з регістру накопичення, доводиться з'єднувати таблиці. Період вибірки ми задавали через передачу параметрів віртуальної таблиці (це одна з важливих особливостей конструктора обмеження вибірки, яку ми також розглядали).

Тепер ми можемо розпочати угруповання. Для цього використовуються закладки Угрупованняі Підсумки.

На закладці Угрупованнями вибираємо поле, яким будемо групувати, - полем Батько (Мал. 2). Інші поля (текст запиту в блакитній рамці) з представленням елементів поставить конструктор. І нижче в Підсумованому полі (Мал. 3) ми поставимо агрегатні функції, значення яких нам потрібні - суму виручки за групою та середній прибуток за продажами.

Що тут важливе? Те, що групувати можна лише по полях, які вказані у списку вибірки. Це цілком логічно: якщо значення поля не обрано, вони невідомі, і не можна здійснити угруповання. Якщо ж ми хочемо накласти згодом умову значення групувального поля пропозицією МАЮЧІ(наприклад, порахували середню оборотність і вибрали лише ті групи, за якими цей показник нижчий за норму), обов'язково повинні бути угруповання за такими полями. Цю умову необхідно дотримуватись, навіть якщо не потрібно бачити ці поля у підсумках.

Це теж зрозуміло: спочатку потрібно згрупувати та розрахувати, а лише потім можна побачити, чи відповідає результат якимсь умовам. Повідомлення про такі помилки ви отримаєте під час перевірки запиту, але краще відразу мати це на увазі.

Тепер перейдемо до закладки Підсумки. Тут ми вкажемо групувальні поля, які хочемо бачити в підсумках ( Мал. 4). В даному випадку це теж поле Батько. Коли одним із джерел даних вказано таблицю довідника, дуже часто однією з угруповань буде його ієрархія.

Далі вказуються агрегатні функції, значення яких потрібно отримати, - а ми хочемо знати суму виручки за групою та середній прибуток ( Мал. 5). Як параметри цієї закладки перетворені на текст запиту, видно у жовтому блоці тексту запиту.

На закладці Порядокми вказали сортування по полю Виторгза зменшенням. Зазвичай, коли нас цікавлять якісь показники, вдалим буває сортування за числовими полями. Якщо цього не зробити, буде прийнято сортування першої з об'єднаних таблиць (а це таблиця довідника «Номенклатура»). Найменування товарів будуть відсортовані за абеткою.

Скрізь у підсумках, угрупованнях та сортуваннях, якщо ми кваліфікували стовпці (дали їм псевдоніми – товар, виручка, прибуток), можна використовувати кваліфіковані імена. Тепер можна натиснути кнопку ОКу конструкторі - буде виконано синтаксичну перевірку, і якщо все нормально, можна виконати запит.

Якщо ми хочемо накласти умову на групи, потрібно накласти умови на значення агрегатних функцій групувальних полів за допомогою пропозиції МАЮЧІ(слідує безпосередньо за пропозицією ЗГРУПУВАТИ). Наприклад, МАЮЧІ СЕРЕДНІ(Прибуток) > 500 - і в наборі не буде жодної групи, в якій середній за групою прибуток не досяг 500,01 грн.

на малюнку 6представлено те, що в нас вийшло в результаті (фрагмент таблиці, зробленої за даними демонстраційної версії конфігурації «Управління торгівлею для України 8.0» ред. 2.1, щоб можна було відтворити результати).

Запит такої складності цілком підходить для роботи. Більше того, якщо додати сюди ще одне поле – рентабельність товарних запасів (прибуток, поділений на середньоденний залишок товарів за період), до таблиці будуть включені основні показники, з якими працюють менеджери з управління запасами. Для цього в запит потрібно додати ще таблицю «Залишки Товарів Компанії», з'єднати таблиці та знову пройти всі закладки.

ВИБРАТИ<поля1,поля2>

З<таблица1,таблица2>

ДЕ<условия>

ЗГРУПУВАТИ ПО<поля1,поля2>

ВПОРЯДКУВАТИ ЗА<поля1, поля2>

ПІДСУМКИ<фукция1,функция2>

ПЗ<поле1,поля2>

СпрНоменклатура.Представлення,

ПродажКомпаніїОбороти. СумаПродажіОборот

ЯК Виторг,

ПродажіКомпаніїОборо-ти.СумаПродажіОборот ПродажіКомпаніїОбороти. СобівартістьОбіг ЯК Прибуток

З Довідник. Номенклатура

ЯК СпрНоменклатура

ВНУТРІШНЯ З'ЄДНАННЯ-гістрНакопичення.ПродажіКомпанії.Обороти(&ДатаНа-чала, &ДатаКінця,)

ЯК ПродажіКомпанії Обороти

ПО ПродажіКомпаніїОбо-роти.Номенклатура = СпрНоменклатура.

ЗГРУПУВАТИ ПО

СпрНоменклатура.Батько,

СпрНоменклатура.Представлення

ВПОРЯДКУВАТИ ЗА

 

 
Це цікаво: