Ростелеком ТВ 

Применение агрегатных функций и вложенных запросов в операторе выбора. Групповые операции в запросах Access

Группировка и агрегирование данных совершаются с помощью функций, определенных в MySQL. Опишем некоторые из них:

AVG (столбец) – средняя величина значений в указанном столбце

COUNT (столбец) – число ненулевых значений в указанном столбце. Если перед именем столбца указать DISTINCT , то выдается только количество неодинаковых значений;

MIN (столбец) – минимальное значение в столбце;

MAX (столбец) – максимальное значение в столбце;

SUM (столбец) – сумма значений в столбце.

Агрегатные функции можно использовать в запросе SELECT в качестве информации для вывода. Например, чтобы определить количество водителей в автопарке, можно подать запрос

mysql>SELECT COUNT (id) FROM drivers;

Чтобы узнать, сколько водителей работало за отчетное время, следует подать команду

mysql>SELECT COUNT (DISTINCT driver_number) FROM timetable;

Функция COUNT(*) определит число строк в таблице, но если указать COUNT(field) – определить число значений в поле field, то значения NULL в этом поле учтены не будут.

Предложение GROUP BY позволяет группировать записи, вошедшие в результаты запроса. Это дает возможность объединять поля и агрегатные функции в одном предложении SELECT.

Подсчитаем, сколько автомобилей имеется в таксопарке, сгруппировав данные по моделям:

mysql>SELECT model, COUNT(model) FROM cars GROUP by model;

Каждая группа состоит из строк с одинаковым значением поля model. результат этого запроса представлен ниже:

Таблицы – это неупорядоченные наборы данных, и выборка из них не обязательно получается в какой-то определенной последовательности. Для изменения порядка вывода результирующих данных используется конструкция ORDER BY.

Упорядочим сведения о водителях по дате рождения:

mysql>SELECT * FROM drivers ORDER BY birth;

В полученной выборке список откроют старшие водители. Если требуется упорядочить данные по убыванию, следует использовать ключевое слово DESC (от англ. Descending - по убыванию):

mysql>SELECT * FROM drivers ORDER BY birth DESC;

Конструкция LIMIT служит для указания, сколько строк результата следует отображать. Если нужно найти двух самых пожилых водителей, то можно применить команду



mysql>SELECT * FROM drivers ORDER BY birth LIMIT 2;

Можно также использовать два параметра: номер строки, с которой следует начать, и количество строк.

mysql>SELECT family_name From drivers LIMIT 2, 3 ;

Последний запрос можно интерпретировать так: "Выбрать фамилии водителей
и в результате отобразить три строки, начиная со строки 2". Нумерация строк начинается с нуля.

Запросы к двум и более таблицам

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

придется оперировать именами столбцов из разных таблиц. Следует учесть, что полное имя столбца состоит из имени базы данных, имени таблицы и собственно имени столбца, например, taxi. cars . color.

Часто имена таблиц заменяют короткими псевдонимами (alias), указывая их в запросе после ключевого слова from. Псевдонимы для таблиц являются стандартной частью языка SQL и обычно состоят из одной буквы. Задать псевдоним можно с помощью ключевого слова as:



mysql> SELECT color FROM cars AS c;

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

Между таблицами существуют связи по определенным столбцам. Столбец cars.id связывает таблицу cars с таблицей timetable через столбец timetable.car_number. Аналогично строится связь между таблицей drivers и timetable.

Объединение осуществляется по столбцам, образующим связь. При этом вы­бираются одинаковые значения полей, по которым осуществляется связь между таблицами:

timetable. car_number=cars . id

timetable. driver_number=drivers . id

Определим дату (данные из столбца timetable.use_on) и фамилию водителя (drivers.famiiy_name), который водил машину определенного цвета

mysql> SELECT t.use_on, d.family_name, c.color

-> FROM timetable AS t, cars AS c, drivers AS d

-> WHERE t.car_number=c.id AND t.driver_number=d.id;

Выборка данных происходит из таблиц timetable, cars и drivers, для кото­рых определены псевдонимы, сокращающие запись запроса и облегчающие его редактирование. Выбираются только те строки из таблиц timetable и cars, в которых указан одинаковый учетный номер автомобиля. А из таб­лиц timetable и drivers выбираются те строки, в которых значения учетного номера водителя одинаковы.

Команды обновления и удаления данных в таблицах

Если необходимо изменить значение поля, то следует использовать команду update. В команде нужно указать имя таблицы и использовать предложение set, определяющее требуемое изменение. Например, исправим дату выпуска автомобиля "Renault":

mysql>UPDATE cars SET madein="2004" WHERE model="Renault";

Можно изменить значения поля во всем столбце, например, при переоценке товаров. Увеличим цены на все товары на 10% с учетом инфляции:

mysql>UPDATE goods SET price = price * 1.1;

Удалить целые строки из таблицы можно с помощью команды delete. Уда­лим некоторые строки из таблицы cars в связи с продажей старых машин, изготовленных до 2006 г.:

mysql>DELETE FROM cars where madein<=2005;

Можно удалить и все записи из таблицы командой

mysql>DELETE FROM cars;

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

Чтобы очистить таблицу и одновременно сбросить счетчик, нужно использовать команду

mysql>TRUNCATE cars;

Удалить таблицу можно командой drop table:

mysql>DROP TABLE timetable;

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

Сегодня поговорим на тему «Групповые операции в запросах Access». Групповые операции в запросах Access позволяют выделить группы записей с одинаковыми значениями в указанных полях и вычислить итоговые данные для каждой из групп по другим полям, используя одну из статистических функций. Статистические функции применимы, прежде всего, к полям с типом данных Числовой, Денежный, Дата/время.
В Access предусматривается девять статистических функций:

  • Sum - сумма значений некоторого поля для группы;
  • Avg - среднее от всех значений поля в группе;
  • Max, Min - максимальное, минимальное значение поля в группе;
  • Count - число значений поля в группе без учета пустых значений;
  • StDev - среднеквадратичное отклонение от среднего значения поля в группе;
  • Var - дисперсия значений поля в группе;
  • First и Last - значение поля из первой или последней записи в группе.

Результат с использованием групповых операций содержит по одной записи для каждой группы. В запрос, прежде всего, включаются , по которым производится группировка, и поля, для которых выполняются статистические функции. Кроме этих полей в запрос могут включаться поля, по которым задаются условия отбора.
Рассмотрим конструирование однотабличного запроса с групповой операцией на примере таблицы ПОСТАВКА_ПЛАН.

Запрос с функцией Sum

Задача. Определите, какое суммарное количество каждого из товаров должно быть поставлено покупателям по договорам. Все данные о запланированном к по-ставке количестве товара указаны в таблице ПОСТАВКА_ПЛАН.

  1. Создайте в режиме конструктора запрос на выборку из таблицы ПОСТАВКА_ПЛАН.
  2. Из списка таблицы перетащите в бланк запроса поле КОД_ТОВ ― код товара. По этому полю будет производиться группировка записей таблицы.
  3. Перетащите в бланк запроса поле КОЛ_ПОСТ, по которому будет подсчитываться суммарное количество каждого из товаров, заказанных во всех договорах.
  4. Выполните команду Итоги (Totals) из группы Показать или скрыть (Show/Hide). В бланке запроса появится новая строка Групповая операция (Total) со значением Группировка (Group By) в обоих полях запроса.
  5. В столбце КОЛ_ПОСТ замените слово Группировка (Group By) на функцию Sum. Для этого вызовите список и выберите эту функцию. Бланк запроса примет вид, показанный на рис. 4.11.
  6. Для отображения результата запроса (рис. 4.12) щелкните на кнопке Выполнить (Run) в группе Результаты (Results).
  7. Замените подпись поля Sum-КОЛ_ПОСТ на Заказано товаров. Для этого перейдите в режим конструктора, в бланке запроса установите курсор мыши на поле КОЛ_ПОСТ и нажмите правую кнопку. В контекстном меню выберите Свойства (Properties). В окне Свойства поля (Field Properties) введите в строке Подпись (Caption) - Заказано товаров. Для открытия окна свойств может быть выполнена команда Страница свойств (Property Sheet) в группе Показать или скрыть (Show/Hide).


  1. Сохраните под именем Заказано товаров.
  2. Чтобы подсчитать количество товаров, заказанных в каждом месяце, выполните группировку по двум полям: КОД_ТОВ и СРОК_ПОСТ, в котором хранится месяц поставки (рис. 4.13).
  3. Чтобы подсчитать количество товаров, заказанных в заданном месяце, предыдущий запрос дополните вводом параметра запроса в условие отбора (рис. 4.14).



Запрос с функцией Count

Задача. Определите, сколько раз отгружался товар по каждому из договоров. Факт отгрузки фиксируется в таблице НАКЛАДНАЯ.

  1. Создайте запрос на выборку на основе таблицы НАКЛАДНАЯ.
  2. Из списка полей таблицы НАКЛАДНАЯ перетащите в бланк запроса поле НОМ_ДОГ. По этому полю должна производиться группировка.
  3. По сути, смысл задачи сводится к подсчету в таблице числа строк с одинаковым номером договора, поэтому неважно по какому полю будет вычисляться функция Count. Перетащите в бланк запроса любое поле, например опять НОМ_ДОГ.
  4. Выполните команду Итоги (Totals) из группы Показать или скрыть (Show/Hide). Замените слово Группировка (Group By) в одном из столбцов с именем НОМ_ДОГ на функцию Count. Бланк запроса примет вид, показанный на рис. 4.15.

Особенности функции COUNT.

Наименование параметра Значение
Тема статьи: Особенности функции COUNT.
Рубрика (тематическая категория) Торговля

Агрегатные функции.

Итоговые запросы на чтение

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

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

Агрегатами называют группы строк, следовательно, агрегатными функциями называют функции, аргументами которых являются группы строк.

Агрегаты могут представлять собой всœе строки таблицы или группы строк, созданные предложением GROUP BY (будет рассмотрено в п.13.7.2).

Агрегатная функция получает в качестве аргумента выражение, содержащее, по крайней мере, один столбец таблицы, а в качестве результата возвращает одно значение. Τᴀᴋᴎᴍ ᴏϬᴩᴀᴈᴏᴍ, агрегатные функции позволяют выполнять операции над значениями сразу целого столбца таблицы или нескольких таблиц.

В SQL имеется пять стандартных агрегатных функций: SUM, AVG, MIN, MAX, COUNT.

SUM(<выражение>) вычисляет сумму всœех значений в выражении;

AVG(<выражение>) вычисляет среднее всœех значений в выражении;

MIN(<выражение>) находит наименьшее среди всœех значений в выражении;

MAX(<выражение>) находит наибольшее среди всœех значений в выражении;

COUNT(<выражение>) подсчитывает количество значений в выражении.

В качестве выражения чаще всœего выступает имя столбца таблицы.

Функция COUNT(*) подсчитывает количество строк (ᴛ.ᴇ. учитывает и NULL).

Функция COUNT(DISTINCT <выражение>) подсчитывает количество разных значений в выражении.

Примечание 1. В Microsoft Access конструкция COUNT(DISTINCT <выражение>) не работает!!! Данная проблема решается применением вложенного запроса в предложении FROM.

Ограничения на использование агрегатных функций:

Агрегатные функции нельзя использовать в предложении WHERE (оно работает для одной строки, а не для агрегата);

Агрегатные функции нельзя вкладывать друг в друга (при крайне важно сти можно использовать вложенные запросы);

В предложении SELECT нельзя одновременно использоваться агрегатные функции и обычные имена столбцов (если только по этим столбцам не указана группировка GROUP BY)

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

Пример 29 . Вычислить суммарную стоимость всœех вызовов.

SELECT SUM(STOIM)

FROM VYZOVY

Пример 30 . Вычислить средний возраст для контактов.

SELECT AVG(VOZRAST)

FROM KONTAKTY

Пример 31 . Вычислить минимальную и максимальную длительность исходящих вызовов.

SELECT MIN(DLIT), MAX(DLIT)

FROM VYZOVY V, TIPY_VYZ T

WHERE (V.TIP_ID=T.ID) AND (T.NAZV="Исходящий")

Пример 32 . Вывести дату и время самого первого вызова.

SELECT MIN(VREMYA)

FROM VYZOVY

Пример 33 . Вычислить количество контактов старше 30 лет.

SELECT COUNT(ID)

FROM KONTAKTY

WHERE VOZRAST>30

SELECT COUNT(*)

FROM KONTAKTY

WHERE VOZRAST>30

Пример 34 . Вычислить количество мелодий, уже назначенных для какого-либо контакта.

Не работает в ACCESS:

SELECT COUNT(DISTINCT MEL_ID)

FROM KONTAKTY

Работает в ACCESS:

SELECT COUNT(MEL_ID)

FROM (SELECT DISTINCT MEL_ID

FROM KONTAKTY)

Особенности функции COUNT. - понятие и виды. Классификация и особенности категории "Особенности функции COUNT." 2017, 2018.

  • - Seven billion and counting worksheet A

    Дополнительный список литературы Литература DeGolyer E., Vance H., Bibliography of the Petroleum Industry, Bull. 83, A. and. M. College of Texas, College Station, Texas, 725 p., 1944. Dunbar С. Р., Dunbar L., A Selected List of Periodicals, Serials, and Books Dealing with Petroleum and Allied Subjects, Department of Conservation, New Orleans, Louisiana, 218, p., 1939. Economic Geology Publishing Company, Urbana, Illinois,... .


  • - The System of Petroleum Education in Our Country

    Our country has always paid great attention to the education of petroleum engineers. Soon after the October Revolution in September 1918 the Soviet Government published the Decree about the organization of the Moscow Mining Academy. Later in 1930 a separate petroleum institute singled out from this Academy and we call this institute the Russian State Gubkin University of Oil and Gas. Since that time the level of petroleum education has grown manyfold. At present the system of petroleum... .


  • - PETROLEUM EDUCATION IN OUR COUNTRY

    ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ» Кафедра иностранных языков УЧЕБНИК АНГЛИЙСКОГО ЯЗЫКА ПО ШИРОКОМУ ПРОФИЛЮ НЕФТЯНОГО ВУЗА Уфа 2012 Учебное пособие состоит из 16 уроков. Каждый урок включает активный словарь, лексические, грамматические,... .


  • - In Our Country

    The history of petroleum engineering education in our country is inseparable from the history of the foundation and development of its oil and gas industries. In tsarist Russia the petroleum industry was concentrated almost exclusively in the Caucasus. On July 2, 1918 head of the government signed the decree on the oil industry nationalization (they didn`t produce gaseous raw materials in Russia and there was no gas industry there before the revolution). But during the hard times of the Civil... .


  • - Management accounting

    1. Модель. 2. Требования к информации. 3. Сбор информации. 4. Активация модели и интерпретация результатов. 5. Представление результатов модели. G = Д-З Gпр = Д(В)пр – Зпр – продуктовая прибыль МД – маржинальный доход. МД = В – Зпеременые, (В – выручка, З-затраты) Модель... .


  • - Exercise 2. Imagine that you work as an accountant in a big enterprise. Answer the following questions.

    1. Do you total the accounts at the end of the fiscal year? 2. What are your usual activities at the end of the fiscal year? 3. When do you prepare a financial statement? 4. Why should you have more assets than liabilities? 5. Does the accounting department work on taxes? 6. What is the difference between your gross salary and net salary? 7. Is the difference between gross salary and net salary big in this country? What does this difference depend on? Exercise 3. Make up the... .


  • - Функция COUNT

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


  • - Счётчик Гейгера (Geiger Counter)

    Стоимость: 300 Это небольшое полезное устройство не только отображает количество рад, находящееся в человеке, но и замеряет радиационный фон окружающей среды. Бывает полезным, когда надо узнать, опасен ли громадный светящийся кратер впереди. Вес: 5 фнт... .


  • - Отсчет (Countdown)

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


    • Цель работы: научиться формировать на языке SQL простейшие запросы к базе данных, использовать в запросах выражения, включающие в себя арифметические операции, функции для работы со строками и датами, агрегатные функции.

    Запрос на выборку всей таблицы . В лабораторной работе № 10 такой запрос уже был сформирован средствами Access. Можно упростить вид запроса, если вместо запроса

    SELECT Заказы.* FROM Заказы;

    написать запрос

    SELECT * FROM Заказы

    Язык SQL позволяет опускать имя таблицы перед именем поля в тех случаях, когда в запросе используется одна таблица, или имя поля не повторяется в нескольких таблицах в многотабличном запросе.

    Создание запросов на SQL в Access начинается вызовом конструктора запросов. Для этого в окне базы данных нужно выбрать объект «Запросы», пункт меню «Создать» и в окне «Новый запрос» пункт «Конструктор». Далее выберите таблицу «Заказы» и перейдите в режим SQL. Переход в режим SQL: меню Access ВидРежим SQL.

    Зокончите формирование запроса и выполните его.

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

    SELECT КодЗаказа AS Заказ, НазваниеПолучателя AS Получатель, АдресПолучателя AS Адрес, ДатаИсполнения AS Дата FROM Заказы ORDER BY НазваниеПолучателя ASC;

    В примере КодЗаказа, НазваниеПолучателя, АдресПолучателя и ДатаИсполнения – имена полей в таблице «Заказы». При выводе результатов запроса на экран дисплея имена полей будут заменены соответствующими псевдонимами, указанными после словаAS .

    Предложение

    ORDER BY НазваниеПолучателя ASC

    служит для сортировки отобранных записей по возрастанию (т.е. в алфавитном порядке) значения поля НазваниеПолучателя. Если нужно сортировать по убыванию, то вместо ASC нужно использовать DESC (сокращение от descending).

    Сформируйте и выполните этот запрос.

    Вывод записей без дублирования. Сформируйте и выполните следующий запрос

    SELECT НазваниеПолучателя AS Получатель FROM Заказы ORDER BY НазваниеПолучателя DESC.

    Названия получателей многократно повторяются, так как выбраны все записи таблицы. Чтобы не было дублирования записей, добавьте в запрос после слова SELECT слово DISTINCT. Иногда в СУБД режим DISTINCT установлен по умолчанию. Для вывода всех записей в этом случае после слова SELECT вставляется слово ALL.

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

    Умножение. Сформируйте запрос на вывод из таблицы «Заказано» кода товара, цены, количества и общей стоимости заказанного товара. Запрос выглядит так:

    SELECT КодТовара,Цена,Количество,Цена*Количество AS Стоимость FROM Заказано;

    Самостоятельно дополните запрос стоимостью со скидкой.

    Использование функций. ФункцияSTR() предназначена для преобразования в текстовый тип. Для вывода на экран дисплея стоимости товара в тысячах рублей с указанием единицы измерения служит следующий запрос:

    SELECT КодТовара,str (Цена*Количество/1000)+" тыс. руб" AS Стоимость FROM Заказано;

    Для того чтобы в колонке «Стоимость» печатались число и текст, нужно преобразовать число в текстовый тип и объединить с текстом "тыс. руб.". Для преобразования служат функция str(<выражение числового типа>) и операция слияния «+» (конкатенация).

    Сформируйте запрос, в котором из таблицы «Заказы» выбираются 5 полей и результат выводится в две колонки. В первую колонку выводится поле «КодЗаказа», а в колонке с псевдонимом «Адрес клиента» объединены следующие поля: ИндексПолучателя, СтранаПолучателя, ГородПолучателя, НазваниеПолучателя.

    Не забудьте поставить между объединяемыми полями адреса запятую с пробелом. Результат запроса (показаны две первые строки) должен иметь вид:

    Функция выделения части даты DATEPART(). Познакомьтесь с описанием этой функции в справке Access (Содержание, раздел «Справочник по языку Visual Basic», пункт «Functions», буква D).

    Определите с помощью запроса к таблице «Заказы», за какие годы были поставки товаров.

    Агрегатные функции. (В Access они называются статистическими). Подсчитаем общее количество записей в таблице «Заказы» и количество записей содержащих данные в поле «ОбластьПолучателя», то есть, количество записей с непустым полем «ОбластьПолучателя». Для этого выполним следующий запрос:

    SELECT count(*),count(ОбластьПолучателя) FROM Заказы;

    В запросе используется агрегатная функция COUNT(). Используя агрегатные функции MAX(), MIN() и AVG(), составьте запрос для подсчёта максимальной минимальной и средней цены товара в таблице «Товары».

    Используя агрегатную функцию SUM(), составьте запрос для подсчёта общей стоимости доставки всех заказанных товаров в таблице «Заказы».

    Сохраните все созданные Вами запросы и покажите их преподавателю.