Встроенные функции

 

Рассмотрим вопросы такого типа:

Каковы максимальная и минимальная почасовые ставки? Каково сред­нее число дней работы служащих на здании 435? Каково общее число дней, отведенных на штукатурные работы на здании 312? Сколько всего разных специальностей?

Для ответа на эти вопросы требуются статистических функции, которые рассматривают множество строк таблицы и выдают одно значение. В SQL есть пять таких функций, называемых встроенными функциями или функ­циями множества. Это функции SUM (сумма), AVG (среднее), COUNT (количество), МАХ (максимум) и MIN (минимум).

Встроенная функция (функция множества). Статистическая функция, оперирующая множеством строк: SUM (сумма), AVG (среднее), COUNT (количество), МАХ (максимум), MIN (минимум).

Запрос: Каковы максимальная и минимальная почасовые ставки?

 

SELECT MAX(HRLY_RATE), MIN(HRLY_RATE)

FROM WORKER

Результат:17.40, 8.20

 

Функции MAX и MIN оперируют одним столбцом таблицы. Они выби­рают максимальное или минимальное значение, соответственно, из этого столбца. Формулировка нашего запроса не содержит команды WHERE. Для большинства запросов это может быть не так, как показывает наш следую­щий пример.

Запрос:Каково среднее число дней работы служащих на здании 435?

SELECT AVG(NUM_DAYS)

FROM ASSIGNMENT

WHERE BLDG_ID =435

Результат: 12.33

Для того чтобы подсчитать искомое среднее, нужно рассматривать только те строки таблицы ASSIGNMENT, которые относятся к зданию 435. Как всегда в SQL, фраза WHERE сводит рассмотрение только к таким строкам.

Запрос:Каково общее число дней, отведенных на штукатурные работы на здании 312?

SELECT SUM(NUM_DAYS)

FROM ASSIGNMENT, WORKER

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND

SKILL_TYPE = 'Штукатур' AND

BLDG_ID = 312

Результат: 27

В решении используется соединение таблиц ASSIGNMENT и WORKER. Это необходимо, так как SKILL_TYPE находится в таблице WORKER, a BLDG_ID - в таблице ASSIGNMENT.

Запрос: Сколько всего разных специальностей?

SELECT COUNT (DISTINCT SKILL_TYPE)

FROM WORKER

Результат: 4

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

DISTINCT. Оператор, исключающий повторяющиеся строки.

Функции SUM и AVG должны использоваться только с числовыми столбцами. Другие функции могут использоваться и с числовыми, и с сим­вольными данными. Все функции, кроме COUNT, можно использовать с вы­числяемыми выражениями. Например:

Запрос: Какова средняя недельная зарплата?

SELECT AVG (40 * HRLY_RATE)

FROM WORKER

Результат: 509.14

COUNT может ссылаться на строку целиком, а не на отдельныйстолбец:

Запрос: Сколько зданий имеют уровень качества З?

SELECT COUNT (*)

FROM BUILDING WHERE

QLTY_LEVEL = 3

Результат: 3

Как показывают все эти примеры, если в команде SELECT стоит встро­енная функция, то больше в этой команде SELECT ничего стоять не может. Единственное исключение из этого правила связано с фразой GROUP BY, которую мы сейчас рассмотрим.

Задание 4. Выполните следующие запросы к базе данных «Перевозки».

 

  1. Каков средний вес груза, отправляющегося в Атланту?
  2. Сколько грузов отправил клиент 433?
  3. Какие города в базе данных имеют наибольшую и наименьшую чис­ленность населения? 5. Каков общий вес грузов, перевозимых машиной №82?
  4. Дать список клиентов, все грузы которых имеют вес свыше 25 фунтов
  5. Перечислить клиентов, отправлявших все свои грузы в один и тот же город. (Замечание. Этот город может быть, а может и не быть общим для всех таких клиентов).