Лекции по информатике
  • Регистрация
1 1 1 1 1 1 1 1 1 1 Рейтинг 4.00 (2 Голоса)

Лекция 6 Тема: Запросы. Вычисления и групповые операциию

6.1. Вычисляемые поля.

6.2. Создание выражений с помощью Построителя выражений.

6.3. Обзор встроенных функций СУБД MS Access.

6.4. Итоговые запросы.

6.5. Перекрестные запросы.

Очень часто при формировании набора записей требуется произвести вычисления над данными (определить возраст сотрудника, стоимость заказа, процент от продаж, выделить часть номенклатурного кода и т. п., т. е. вывести информацию, которая не хранится в базе данных) или выполнить определенные операции по непосредственной обработке отбираемых данных.

В QBE СУБД MS Access такие возможности предоставляются через вычисляемые поля и групповые операции.

6.1. Вычисляемые поля

Вычисляемое поле представляет собой выражение, состоящее из операторов (арифметических, сравнения, логических, конкатенации) и операндов. В качестве операндов могут быть использованы константы, встроенные или определенные пользователем функции и идентификаторы, например

Стоимость: Товары! Цена * Количество * (1-Скидка)

КоличествоМужчин: Sum(IIf(Пол = "м"; 1; 0))

ФИО: Фамилия &" "& Left(Имя;1) &". "& Left(Отчество;1) &"."

Стоимость, КоличествоМужчин и ФИО являются именами вычисляемых полей и отображаются в режиме таблицы в заголовке столбца, символ двоеточие исполняет роль разделителя между именем вычисляемого поля и выражением.

Если имя таблицы или поля содержит пробелы, то его идентификатор обязан в выражении заключаться в квадратные скобки, например

Стоимость: Цена*[Количество товара]

Вычисляемое поле создается прямо в бланке QBE путем внесения выражения в ячейку Поле любого свободного столбца. Результаты вычислений, выводящиеся в поле, не запоминаются в базовой таблице. Вычисления снова производятся всякий раз, когда выполняется запрос, поэтому результаты всегда представляют текущее содержимое базы данных.

Обновить вычисленные результаты вручную невозможно.

Для построения сложных выражений в СУБД MS Access входит утилита, называемая Построитель выражений.

6.2. Создание выражений с помощью Построителя выражений

поле для формирования (записи) выраженияПостроитель выражений можно запустить щелкнув по клавише Построить на панели инструментов Конструктор запросов или выбрав в контекстном меню поля бланка запроса QBE команду меню Построить….

список объектов список экземпляров
объекта
список значений
экземпляров

 


 

Рис.6.1. Диалоговое окно Построитель выражений

Рис.6.2. Диалоговое окно Построителя выражений
со сформированным выражением

6.3. Обзор встроенных функций СУБД MS Access

СУБД MS Access содержит более 100 встроенных функций (рис.6.3), которые можно использовать при формировании вычисляемого поля или при задании условия отбора.

Рис.6.3. Диалоговое окно Построителя выражений
со списком встроенных функций

Все встроенные функции в Построителе выражений сгруппированы по функциональному назначению.

При выборе функции в правом окне построителя, в нижней области окна Построителя выражений отображается синтаксис этой функции. Для более детальной справки необходимо щелкнуть по клавише Справка.

Обзор всех встроенных функций выходит за рамки лекции, поэтому рассмотрим наиболее используемые.

Таблица 6.1

Функция

Описание

Функции категории Дата/время

Day(дата)

Возвращает значение дня месяца от 1 до 31

Month(дата)

Возвращает значение месяца от 1 до 12

MonthName(месяц[; флаг])

Возвращает название месяца соответствующего номеру месяца: 1 – январь, 2 – февраль, и т. д. Если значение аргумента флаг Истина, то функция возвращает аббревиатуру месяца: 1 – янв, 2 – фев и т. д.

Year(дата)

Возвращает значение года от 100 до 9999

Weekday(дата[; число])

Если аргумент число не указан, возвращает значение дня недели от 1 (воскресенье) до 7 (суббота). Если аргумент число имеет значение 0, то возвращает значение дня недели от 1 (понедельник) до 7 (воскресенье)

Hour(дата)

Возвращает целое число от 0 од 23, представляющее значение часа

DatePart(интервал; дата)

Возвращает числовое значение в зависимости от значения аргумента интервал:

"q" – квартал (от 1 до 4);

"m" – месяц (от 1 до 12);

"yyyy" – год (от 100 до 9999);

"ww" – неделя (от 1 до 53);

и т. п. (см. справку по функции).

Date()

Возвращает текущую системную дату

Функции категории Проверка

IsNull(переменная)

Возвращает значение Истина если значение переменной (значение поля в источнике данных) равно Null

IsNumeric(переменная)

Возвращает значение Истина если значение переменной (значение поля в источнике данных) имеет один из числовых типов данных

Функции категории Управление

IIf(условие; выр1; выр2)

Возвращает значение выражения выр1 если значение аргумента условие Истина и возвращает значение выражения выр2 если значение аргумента условие Ложь. Данная функция аналогична функции ЕСЛИ в MS Excel.

Продолжение таблицы 6.1

Функция

Описание

Функции категории Текстовые

Left(текст; n)

Возвращает n левых символов аргумента текст

Right(текст; n)

Возвращает n правых символов аргумента текст

Mid(текст; нач_поз[; n])

Возвращает n символов начиная с позиции нач_поз аргумента текст. Если аргумент n не указан, то возвращает все символы до конца строки начиная с позиции нач_поз аргумента текст.

Len(текст)

Возвращает количество символов (длину строки) в аргументе текст

LTrim(текст)

Возвращает строковое значение аргумента текст без начальных пробелов

RTrim(текст)

Возвращает строковое значение аргумента текст без заключительных пробелов

Trim(текст)

Возвращает строковое значение аргумента текст без начальных и заключительных пробелов

Str(число)

Возвращает строковое значение аргумента число

Format(переменная; формат)

Возвращает значение аргумента переменная в формате заданным аргументом формат

6.3. Итоговые запросы

При анализе данных очень часто интересуют не отдельные записи, а итоговые значения по группам данных, например:

-  количество сделок с Партнерами за определенный промежуток времени;

-  средний объем продаж по каждому месяцу за предыдущий год.

Ответы на такие вопросы дает итоговый запрос.

Для вычисления итоговых значений необходимо нажать кнопку Групповые операции на панели инструментов Конструктор запросов, чтобы в бланке QBE появилась строка Групповая операция (после имени таблицы).

По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

Для подведения итогов необходимо заменить установку Группировка на конкретную итоговую функцию. В СУБД MS Access предусмотрено 9 функций (табл. 6.2), обеспечивающих выполнение групповых операций.

Таблица 6.2

Функции категории Статистические

Функция

Назначение

Sum

Возвращает сумму набора значений

Avg

Возвращает среднее арифметическое набора значений

Min

Возвращает наименьшее значение из набора значений

Max

Возвращает наибольшее значение из набора значений

Count

Возвращает количество записей в наборе значений отличных от Null

First

Возвращает первое значение поля в группе

Last

Возвращает последнее значение поля в группе

StDev

Возвращает среднеквадратичное отклонение набора значений

Var

Возвращает дисперсию набора значений

В раскрывающемся списке строки Групповая операция имеется установка Выражение. Данная установка применяется, когда в выражении (строка Поле) используется несколько итоговых функций.

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

Для решения более сложных статистических задач в СУБД MS Access предусмотрен специальный тип запроса – перекрестный запросов.

6.4. Перекрестные запросы

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

Для построения перекрестного запроса достаточно трех полей базового источника. По повторяющимся значениям одного поля формируются названия заголовков строк итоговой (сводной) таблицы (рис.6.4). По повторяющимся значениям другого поля формируются названия заголовков столбцов итоговой (сводной) таблицы. Результаты статистической обработки по третьему полю отображаются в ячейках сводной таблицы (область значений). Пример перекрестного запроса в режиме конструктора представлен на рис.6.5, а результаты выполнения запроса на рис. 6.6.

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

В перекрестном запросе допускается указание условий отбора. Сортировка может проводиться только по полям, размещенным в области заголовков строк.

 

 

Область заголовков столбцов

     
         

Область
заголовков
строк

Область
значений

     
         
         
         
         
         

Рис.6.4. Макет перекрестной таблицы

Рис.6.5. Сформированный перекрестный запрос в QBE

Рис.6.6. Результат выполнения запроса в режиме Таблицы

Вычисления и групповые операции СУБД MS Access - 4.0 out of 5 based on 2 votes

Добавить комментарий


Защитный код
Обновить

По темам:

История Украины

Культурология

Высшая математика

Информатика

Охотоведение

Статистика

География

Военная наука

Английский язык

Генетика

Разное

Технологиеские темы

Украинский язык

Филология

Философия

Химия

Экология

Социология

Физическое воспитание

Растениевосдство

Педагогика

История

Психология

Религиоведение

Плодоводство

Экономические темы

Бухгалтерские темы

Маркетинг

Иностранные языки

Ветеринарная медицина

Технические темы

Землеустройство

Медицинские темы

Творчество

Лесное и парковое хозяйство