Тема. Функції у запитах.

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

Використана література:

1. О.Ю. Гаєвський Інформатика 7-11 класи. – Київ „А.С.К” 2005.

2. Й.Я. Ривкінд Інформатика 11 клас. – Київ «Генеза» 2018.

3. https://support.office.com

План

1. Обрахунки у запитах.

2. Функції для побудови підсумкових запитів.

3. Побудова перехресних запитів

4. Функції дати та часу у запитах.

Обрахунки у запитах.

Підсумкові запити

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

Відповіді на такі питання дає підсумковий запит. Для обчислення підсумкових значінь потрібно натиснути кнопку „Групповые операции” на панелі інструментів конструктора запитів, щоб у бланку QBE з’явився рядок „Групповая операция”.

Коли натиснути кнопку „Групповые операции” на панелі інструментів, Access використовує установку „Группировка” в рядку „Групповая операція” для любого поля, яке занесене в бланк запиту. Тепер записи по кожному полю групуються, але підсумок не підводиться:

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

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

Avg Вираховує середнє арифметичне всіх значень даного поля в кожній групі. Використовується тільки для числових та грошових полів.

Min Повертає найменше значіння, яке знайдене в цьому полі всередині кожної групи. Для числових полів повертається найменше значення. Для текстових полів – найменше з символьних значень незалежно від регістру.

Max Повертає найбільше значіння, яке знайдене в цьому полі всередині кожної групи. Для числових полів повертається найбільше значення. Для текстових полів – найбільше з символьних значень незалежно від регістру.

Count Повертає число записів, в яких значення даного поля відмінні від Null (пусто).

StDev Вираховує стандартне відхилення всіх значінь даного поля в кожній групі. Ця функція використовується тільки для числових або грошових полів.

Var Вираховує дисперсію значінь даного поля в кожній групі. Ця функція використовується тільки для числових або грошових полів. First Повертає перше значення цього поля в групі. Last Повертає останнє значення цього поля в групі. Приклад: визначення загальної суми всіх покупок:

Кількісні запити

Основним принципом статистичних підрахунків у запитах є функція групування (GroupBy) та обліку (Count). Нехай нам треба взнати кількість записів у таблиці покупок. Для цього використовується Count:

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

Обрахунки в запитах

Можна виконувати обчислення з любими полями таблиці і зробити обчислюваний вираз новим полем в наборі записів. Для цього можна використати любі із вбудованих функцій Access. Окрім цього, поля запиту можуть містити дані, які отримані за допомогою арифметичних операцій над полями таблиці. Обчислюване поле може також містити результат конкатенції (об’єднання) значінь текстових полів або рядкових (текстових) констант. Щоб створити рядкову константу, потрібно заключити текст в подвійні або одинарні лапки. В якості операції конкатенції використовується символ “&”. Наприклад, можна створити поле, яке буде містити результат об’єднання поля Прізвище і поля Ім’я: [Прізвище]& [Ім’я].

В виразах можна використовувати наступні оператори:

+ Додає два арифметичних вирази.

- Віднімає від першого арифметичного виразу другий.

* Перемножує два арифметичних вирази.

/ Ділить перший арифметичний вираз на другий вираз.

\ Заокруглює два арифметичних вирази до цілих значінь і ділить перше на друге. Результат округлюється до цілого.

^ Вираховує степінь першого арифметичного виразу, степінь задається другим арифметичним виразом.

MOD Округлює обидва арифметичних вирази до цілих значінь, ділить перше на друге і повертає залишок.

& Створює текстовий рядок, як результат приєднання другого рядка в кінець першого. Іноді вирази, що вводяться, задовгі для поля у конструкторі. Якщо після клацання в чарунці рядка „Поле” (де ми маємо ввести вираз) натиснути SHIFT+F2, то відкриється вікно „Область ввода”, в якому зручно задавати довгі вирази.

Перехресні запити

Access підтримує особливий тип підсумкових запитів, який називається перехресними запитами. Вони дозволяють побачити обчислювані значення в вигляді перехресної таблиці, яка нагадує електронну. Для побудови перехресного запиту потрібно вибрати „Новый запрос”. В вікні діалогу вибрати команду „Перекрестный запрос”.

Access додасть в бланк запиту рядок „Перекрестная таблица”. В цьому рядку для кожного поля перехресного запиту може бути вибрана одна з чотирьох установок: Ø „Заголовки строк”, Ø „Заголовки столбцов”, Ø „Значение” (яке виводиться в чарунках перехресної таблиці), Ø „Не отображается”. Для перехресного запиту потрібно визначити у крайньому випадку одне поле в якості заголовків рядків, одне для заголовків стовпчиків і одне поле значень. Кожне поле, яке є заголовком стовпчиків, повинно мати в рядку „Групповая операция” установку

„Группировка”. Для поля, яке використовується в якості заголовків рядків, в рядку „Групповая операция” повинна бути встановлена операція „Группировка”, вибрана одна з підсумкових функцій або введений вираз, який містить підсумкову функцію. Для поля з установкою „Значение” вибрати одну з підсумкових функцій або ввести вираз, який використовує підсумкову функцію. Як і в інших типах підсумкових запитів, для відбору даних, які включаються у набір записів, можна використати додаткові поля. Для них необхідно вибрати установку „Условие” в рядку „Групповая операция” і „Не отображается” в рядку „Перекрестная таблица”, а потім ввести умову відбору. Умову відбору можна також задати для любого поля, яке використовується в якості заголовків стовпчиків, а дані можна сортувати по любим полям (по замовчуванню Access сортує заголовки стовпчиків за зростанням).

Обрахунки дати й часу

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

Умови відбору для дат і часу

Microsoft Access зберігає значення дат і часу як числа з плаваючою комою і з подвійною точністю. Значіння з лівого боку від десяткової коми відповідає даті, а дробова частина числа представляє час доби. Щоб повідомити Access про те, що вводиться дата і час, значення вміщується в символи числа (#). Наприклад, #10 Квітень 2003# і #10/04/03# визначають одну і ту саму дату. Access дає декілька функцій, які можна використовувати при завданні умов відбору для дат і часу:

Day (дата). Повертає значення дня місяця в діапазоні від 1 до 31.

Month (дата). Повертає значення місяця року в діапазоні від 1 до 12.

Year (дата). Повертає значення року в діапазоні від 100 до 9999.

Weekday (дата). Повертає значення чисел від 1 (Неділя) до 7 (Субота), які відповідають дням тижня.

Hour (дата). Повертає ціле число від 0 до 23, які представляють значення часу.

DatePart (інтервал, дата). Повертає номер кварталу або номер тижня в залежності від того, який код інтервалу задається (“q” – для визначення кварталу, “ww” – для визначення порядкового номера тижня в році).

Date (). Повертає поточну системну дату. Підставляючи вирази з даними функціями в область умов відбору в конструкторі запитів, можна створювати запити на вибірки за датою чи часом (наприклад, вираз Year ([Дата народження]) >= 1990 поверне нам усіх громадян, що народилися з 1990 року.

Підрахунок часу, що пройшов Нехай в нас є таблиця „Час”, в якій зберігається інформація про час – а саме про початок і завершення певного регулярного процесу:

Запитання для самоконтролю.

1. Як класифікують запити за способом обрахунків, що в них використовуються?

2. Що таке підсумковий запит. Наведіть приклади умов.

3. Перерахуйте функції та їх призначення, що використовуються у підсумковому запиті.

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

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

6. Перерахуйте функції дати та часу?

7. Як знаходиться проміжок часу між двома датами?

8. Опишіть структуру функції DateDiff?

9. Вкажіть види інтервалів у функціях дат.