Запити на вибірку з параметрами

Групування даних та використання агрегатних функцій

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

Таблица 5.4. Ітогові (агрегатні) функції

Функція	Результат	Дозволені типи полів для обробки
Sum	Сума значень групи записів	Числовий, Дата/час, Грошовий, Лічильник
Avg	Середнє значення групи записів	Числовий, Дата/час, Грошовий, Лічильник
Min	Найменьше значення групи записів	Текстовий, Числовий, Дата/час, Грошовий, Лічильник
Max	Найбільше значення групи записів	Текстовий,Числовий, Дата/час, Грошовий, Лічильник
Count	Кількість значень групи записів (без врахування пустих значень).	Текстовий, Числовий, Дата/час, Грошовий, Лічильник, Логічний, Об’єкт OLE

Наприклад, у запиті "Продажи товаров в 1997" бази даних "Борей" використовується групування за полями "Категория", "Марка", "Квартал" з сумуванням за полем "ПродажиТоваров".

Для визначення способу групування записів та обчислення агрегатних функцій у режимі Конструктор використовується рядок Групповая операция. Для відображення рядку Групповая операция необхідно натиснути кнопку Групповые операции панелі інструментів Конструктор запросов. У рядку Групповая операция для кожного поля запиту необхідно у списку обрати одне із значень:

- Группировка – якщо за полем необхідно здійснити групування,

- Выражение – якщо необхідно здійснити обчислення виразу для групи записів,

- Условие – якщо необхідно визначити умову на обчислене агрегатне значення,

- назву агрегатної функції.

Запит з параметром – це запит, для виконання якого необхідно вказати значення одного або декількох параметрів. Значення параметру може бути визначено у стандартному вікні Введите значение параметра (см. рис. 5.14) або у формі (формі даних чи діалоговій формі). Зокрема, запит з параметром дозволяє застосувати багато разів умову для різних значень параметру або параметрів. Запити на вибірку з параметрами часто використовуються як основа для звітів.

Рис.5.14. Вікно введення параметра

Ім’я параметра береться у квадратні дужки та використовується при побудові умовного виразу запиту. Ім’я параметра буде відображатись у запрошенні ввести значення параметру. Наприклад, запит Продажи по сотрудникам и странам виконується для значень початкової дати та кінцевої дати, які вводяться користувачем. Умова на значення поля ДатаИсполнения має вигляд

Between [Начальная дата] And [Конечная дата]

де Начальная дата, Конечная дата – імена параметрів. Запит Продажи по сотрудникам и странам є джерелом даних для звіту Продажи по сотрудникам и странам. Таким чином, стає можливим формування звіту про продажі товарів для різних звітних періодів.

Для введення значення параметра з діалогової форми або форми даних необхідно при створенні запиту в якості умови відбору вказати назву елемента управління форми, в яке буде введено значення параметру. Наприклад, запит Выбор счета бази даних Борей використовується для вибірки поточного рахунку форми Заказы (рис.5.15).

Рисунок 5.15. Запит з параметром Выбор счета у режимі конструктора

ЗАПИТИ НА ВИБІРКУ ДАНИХ З ДЕКІЛЬКОХ ТАБЛИЦЬ

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

- з кожної таблиці здійснюється відбір визначених полів або обчислення обчислюваних полів;

- здійснюється об’єднання сформованих динамічних наборів записів відповідно до зв’язків, які визначені між таблицями;

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

Операція об’єднання записів декількох таблиць або динамічних наборів записів є одною з найважливіших у реляційній алгебрі [4]. В залежності від типу зв’язків, які встановлено між таблицями, розрізняють декілька способів об’єднання:

· повне об’єднання (декартовий добуток), коли між таблицями зв’язки не встановлено;

· внутрішнє об’єднання - цей тип використовується за замовченням, коли одне з полів зв’язку є ключовим;

· зовнішнє об’єднання.

Повне об’єднання (декартовий добуток) таблиць (відношень) є найпростішим способом об’єднання, коли всі записи першої таблиці комбінуються з усіма записами з другої таблиці. Таким чином, якщо у першій таблиці було m записів, а у другій n записів, то до результуючий набір буде містити m*n записів, кожна з яких містить усі обрані поля вихідних таблиць. Наприклад, повне об’єднання таблиць Поставщики та Клиенты буде містити 2639 = 91*29 записів. Такия спосіб об’єднання використовують, якщо необхідно сформувати перелік можливих комбінацій, що задовольняють певній умові. Наприклад, визначити перелік можливих поставок у межах однієї країни, тобто перелік постачальників та клієнтів з однієї країни (див.рис. 5.16). Якщо до запиту додати усі таблиці, за допомогою яких встановлено зв’язок між таблицями Поставщики та Клиенты (а саме таблиці Товары, Заказано, Заказы), то до результуючого набору буде включено записи, що відображають інформацію про поставки, які дійсно мали місце в межах однієї країни. Результуючий набір першого запиту у базі даних Борей містить 149 записів, а другого - 124.

Рис. 5.16. Запит на визначення переліку можливих поставок у межах однієї країни у режимі Конструктор

Найчастіше у запиті на вибірку необхідно отримати об’єднання таблиць, між якими існує зв’язок типу «один до багатьох». При цьому лівою таблицею називається та з таблиць, у якій поле зв’язку є ключовим, інша таблиця називається правою таблицею. Таким чином, ліва таблиця знаходиться на стороні „один” зв’язку, а права таблиця знаходиться на стороні „багато”. Внутрішнє об’єднання здійснюється з перевіркою умови на значення полів, за якими встановлено зв’язок: до результуючого набору додаються лише ті комбінації записів, для яких значення у пов’язаних полях і лівої і правої таблиць не є порожніми та співпадають.

Використовується також і зовнішнє об’єднання таблиць:

· ліве зовнішнє об’єднання - до результуючого набору додаються усі записи з першої (лівої) таблиці та з ними комбінуються лише ті записи другої (правої) таблиці, для яких значення у пов’язаних полях співпадають;

· праве зовнішнє об’єднання - до результуючого набору додаються усі записи з другої (правої) таблиці та з ними комбінуються лише ті записи першої (лівої) таблиці, для яких значення у пов’язаних полях співпадають.

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

Наприклад, результат внутрішнього об’єднання таблиць Клиенты та Заказы буде містити перелік клієнтів (всього записів– 830), які дійсно здійснювали замовлення, тобто значення КодКлиента яких присутнє як в таблиці Клиенты, так і в таблиці Заказы. До результуючого набору (всього записів– 832) виконання запиту з лівим зовнішнім об’єднанням будуть включені в тому числі і клієнти (КодКлиента – PARIS, FISSA), які не здійснювали замовлення (значення поля КодЗаказа для цих записів буде порожнім).

Рис. 5.17. Фрагмент результуючого набору виконання лівого зовнішнього об’’днання таблиць Клиенты та Заказы (Режим таблицы)

В режимі Конструктор спосіб об’єднання визначається типом зв’язку між таблицями. Повне об’єднання виконується для непов’язаних таблиць. Якщо таблиці пов’язані за посередництвом інших таблиць або ланцюга інших таблиць, то для виконання внутрішнього або зовнішнього об’єднання вони усі мають бути додані до запиту. Внутрішнє об’єднання виконується для таблиць, що пов’язані звичайним зв’язком „один до багатьох”. Зовнішнє об’єднання виконується для таблиць, що пов’язані за допомогою орієнтованого зв’язку зі стрілкою (див. рис. 5.18).

а) внутрішнє об’єднання

б) ліве зовнішнє об’єднання

в) праве зовнішнє об’єднання

Рис. 5.18. Типы объединения таблиц

Зміна типу зв’язку здійснюється у діалоговому вікні "Параметры объединения", яке можна відкрити, натиснувши два раза ліву кнопку миші на зв’язку, або за допомогою команди "Параметры объединения" контекстного меню зв’язку. У цьому вікні можна визначити тип об’єднання:

1) Об’єднання тільки тих записів, у яких пов’язані поля обох таблиць співпадають (внутішнє об’єднання);

2) Об’єднання всіх записів з лівої таблиці та тільки тих записів з правої таблиці, у яких значення пов’язаних полів співпадають (ліве зовнішнє об’єднання);

3) Об’єднання всіх записів з правої таблиці та тільки тих записів з лівої таблиці, у яких значення пов’язаних полів співпадають (праве зовнішнє об’єднання).

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

1. запустити майстер побудови запитів

2. На першому кроці обрати поле "Цена" з таблиці "Товары", поле "Категория" из таблиці "Типы" та поле "Страна" з таблиці "Поставщики"

3. на другому кроці встановити перемикач у положення "итоговый", а у діалоговому вікні "Итоги" встановити прапорець для агрегатної функції обчислення середнього значення "Avg"

Результат виконання запиту показано на рис. 5.19.

Дата добавления: 2015-05-24