Обобщение данных с помощью агрегатных функций


Содержание

Глава. 6 ОБОБЩЕНИЕ ДАННЫХ С ПОМОЩЬЮ АГРЕГАТНЫХ ФУНКЦИЙ

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

ЧТО ТАКОЕ АГРЕГАТНЫЕ ФУНКЦИИ?

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

Вот список этих функций:

COUNT выдаёт количество строк или не-NULL значений полей, которые выбрал запрос. SUM выдаёт арифметическую сумму всех выбранных значений данного поля. AVG выдаёт усреднение всех выбранных значений данного поля. MAX выдаёт наибольшее из всех выбранных значений данного поля. MIN выдаёт наименьшее из всех выбранных значений данного поля.

КАК ИСПОЛЬЗОВАТЬ АГРЕГАТНЫЕ ФУНКЦИИ?

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

Только числовые поля могут использоваться с SUM и AVG.

С функциями COUNT, MAX и MIN могут использоваться и числовые, и символьные поля.
При использовании с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII, который должен сообщать, что MIN будет означать первое, а MAX последнее значение в алфавитном порядке (алфавитное упорядочивание обсуждается более подробно в Главе 4).

Чтобы найти SUM всех наших покупок в таблице Заказов, мы можем ввести следующий запрос, с выводом на Рисунке 6.1:

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

Нахождение усреднённой суммы — похожая операция (вывод следующего запроса показан на Рисунке 6.2):

СПЕЦИАЛЬНЫЙ АТРИБУТ COUNT

Функция COUNT несколько отличается от всех остальных. Она считает число значений в данном столбце или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT, чтобы производить счёт чисел различных значений в данном поле. Мы могли бы использовать её, например, чтобы сосчитать количество продавцов, описанных в настоящее время в таблице Заказов (вывод показан на Рисунке 6.3):

ИСПОЛЬЗОВАНИЕ DISTINCT

Обратите внимание в вышеупомянутом примере, что DISTINCT, сопровождаемый именем поля, с которым он применяется, помещён в круглые скобки, но не сразу после SELECT, как раньше. Такого использования DISTINCT с COUNT, применяемого к индивидуальным столбцам, требует стандарт ANSI, но большое количество программ не предъявляют такого требования.

Вы можете выполнять несколько подсчётов (COUNT) в полях с помощью DISTINCT в одиночном запросе, что, как мы видели в Главе 3, не выполнялось, когда вы выбирали строки с помощью DISTINCT.
DISTINCT может использоваться таким образом с любой агрегатной функцией, но наиболее часто он используется с COUNT. С MAX и MIN это просто не будет иметь никакого эффекта, а SUM и AVG вы обычно применяете для включения повторяемых значений, так как они эффективнее общих и средних значений всех столбцов.

ИСПОЛЬЗОВАНИЕ COUNT СО СТРОКАМИ, А НЕ ЗНАЧЕНИЯМИ

Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звёздочкой вместо имени поля, как в следующем примере, вывод из которого показан на Рисунке 6.4:

COUNT со звёздочкой включает и NULL, и дубликаты; по этой причине DISTINCT не может быть использован. DISTINCT может производить более высокие числа, чем COUNT особого поля, который удаляет все

строки, имеющие избыточные или NULL-данные в этом поле. DISTINCT неприменим c COUNT (*), потому что он не имеет никакого действия в хорошо разработанной и поддерживаемой БД. В такой БД не должно быть ни таких строк, которые являлись бы полностью пустыми, ни дубликатов (первые не содержат никаких данных, а последние полностью избыточны). Если всё-таки имеются полностью пустые или избыточные строки, вы, вероятно, не захотите, чтобы COUNT скрыл от вас эту информацию.

ВКЛЮЧЕНИЕ ДУБЛИКАТОВ В АГРЕГАТНЫЕ ФУНКЦИИ

Агрегатные функции могут также (в большинстве реализаций) использовать аргумент ALL, который помещается перед именем поля, подобно DISTINCT, но означает противоположное: включать дубликаты. ANSI технически не позволяет этого для COUNT, но многие реализации ослабляют это ограничение.

Различия между ALL и * при использовании с COUNT:

  • ALL использует имя поля как аргумент.
  • ALL не может подсчитать NULL-значения.

Пока * является единственным аргументом который включает NULL-значения, и только он используется с COUNT; функции, кроме COUNT, игнорируют NULL-значения в любом случае.

Следующая команда подсчитает (COUNT) число не-NULL-значений в поле rating в таблице Заказчиков (включая повторения):

АГРЕГАТЫ, ПОСТРОЕННЫЕ НА СКАЛЯРНОМ ВЫРАЖЕНИИ

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

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

Вы можете найти наибольший неуплаченный баланс следующим образом:

Для каждой строки таблицы этот запрос будет складывать blnc и amt для данного заказчика и выбирать самое большое значение, которое он найдёт. Конечно, пока заказчики могут иметь несколько заказов, их неуплаченный баланс оценивается отдельно для каждого заказа. Возможно, заказ с более поздней датой будет иметь самый большой неуплаченный баланс. Иначе старый баланс должен быть выбран, как в запросе выше.

Фактически имеется большое количество ситуаций в SQL, где можно использовать скалярные выражения с полями или вместо полей, как вы увидите в Главе 7.

ПРЕДЛОЖЕНИЕ GROUP BY

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

Например, предположим, что вы хотите найти наибольшую сумму продажи, полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX (amt) из таблицы Заказов для каждого значения поля snum. GROUP BY, однако, позволит вам поместить всё в одну команду:

Вывод для этого запроса показан на Рисунке 6.5.

GROUP BY применяет агрегатные функции, независимо от серий групп, которые определяются с помощью значения поля в целом. В этом случае каждая группа состоит из всех строк с тем же самым значением поля snum, и MAX функция применяется отдельно для каждой такой группы. Это значение поля, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода так же, как это делает агрегатная функция. Результатом является совместимость, которая позволяет агрегатам и полям объединяться таким образом.

Вы можете также использовать GROUP BY с несколькими полями. Совершенствуя вышеупомянутый пример, предположим, что вы хотите увидеть наибольшую сумму продаж, получаемую каждым продавцом каждый день. Чтобы сделать это, вы должны сгруппировать таблицу Заказов по датам продавцов и применить функцию MAX к каждой такой группе:

Вывод для этого запроса показан на Рисунке 6.6.

Конечно же, пустые группы в дни, когда текущий продавец не имел заказов, не будут показаны в выводе.

ПРЕДЛОЖЕНИЕ HAVING


Предположим, что в предыдущем примере вы хотели бы увидеть только максимальную сумму приобретений, значение которой выше $3000.00. Вы не сможете использовать агрегатную функцию в предложении WHERE (если вы не используете подзапрос, описанный позже), потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции оцениваются в терминах групп строк. Это означает, что вы не сможете сделать что-нибудь подобно следующему:

Это будет отклонением от строгой интерпретации ANSI. Чтобы увидеть максимальную стоимость приобретений свыше $3000.00, вы можете использовать предложение HAVING.

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

Правильной командой будет следующая:

Вывод для этого запроса показан на Рисунке 6. 7.

Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении SELECT, состоящем из команд, использующих GROUP BY. Они должны иметь одно значение на группу вывода.

Следующая команда будет запрещена:

Поле оdate не может быть вызвано предложением HAVING, потому что оно может иметь (и действительно имеет) больше чем одно значение на группу вывода. Чтобы избегать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос (вывод показан на Рисунке 6.8):

Поскольку поля odate нет, не может быть и выбранных полей, и значение этих данных меньше, чем в некоторых других примерах. Вывод должен, вероятно, включать что-нибудь такое, что говорит: «это — самые большие заказы на 3 октября». В Главе 7 мы покажем, как вставлять текст в ваш вывод.

Как говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически ссылки на агрегатные функции — наиболее общие, но и поля, выбранные с помощью GROUP BY, также допустимы. Например, мы хотим увидеть наибольшие заказы для Serres и Rifkin:

Вывод для этого запроса показан на Рисунке 6.9.

НЕ ДЕЛАЙТЕ ВЛОЖЕННЫХ АГРЕГАТОВ

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

то ваша команда будет, вероятно, отклонена. (Некоторые реализации не предписывают этого ограничения, что выгодно, потому что вложенные агрегаты могут быть очень полезны, даже если они и несколько проблематичны.) В вышеупомянутой команде, например, SUM должен применяться к каждой группе поля odate, а MAX — ко всем группам, производящим одиночное значение для всех групп. Однако предложение GROUP BY подразумевает, что должна иметься одна строка вывода для каждой группы поля odate.

РЕЗЮМЕ

Теперь вы используете запросы несколько по-иному. Способность получать, а не просто размещать значения, очень мощна. Это означает, что вы не обязательно должны следить за определённой информацией, если вы можете сформулировать запрос так, чтобы её получить. Запрос будет давать вам поминутные результаты, в то время как таблица общего или среднего значений будет хороша только некоторое время после её модификации. Это не должно наводить на мысль, что агрегатные функции могут полностью вытеснить потребность в отслеживании информации, такой, например, как эта.

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

Объединенные вместе, эти особенности делают возможным производить агрегаты, основанные на чётко определённых подмножествах значений в поле. Затем вы можете определять другое условие для исключения определенных результатов групп с предложением HAVING.

Теперь, когда вы стали знатоком того, как запрос производит значения, мы покажем вам, в Главе 7, чт́о вы можете делать со значениями, которые он производит.

Агрегатные функции SQL — SUM, MIN, MAX, AVG, COUNT

Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.

Функция SQL SUM

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Для получения суммы размеров всех заработных плат используем следующий запрос:

Этот запрос вернёт значение 287664,63.

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

Пример 2. Вывести сумму комиссионных, получаемых всеми сотрудниками с должностью Clerk.

Илон Маск рекомендует:  DateUtils в Delphi

Функция SQL MIN

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

Пример 3. База данных и таблица — те же, что и в примере 1.

Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения. В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.

Функция SQL MAX

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

Пример 5. База данных и таблица — те же, что и в предыдущих примерах.

Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения.

Пример 6. Вновь работаем с двумя таблицами — Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц).

Функция SQL AVG


Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.

Пример 7. База данных и таблица — те же, что и в предыдущих примерах.

Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос:

Результатом будет значение 6,33

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

Пример 8. Работаем с одной таблицей — Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.

Функция SQL COUNT

Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) . то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) . то результатом будет количество всех записей (строк) таблицы.

Пример 9. База данных и таблица — те же, что и в предыдущих примерах.

Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm — не NULL, вернёт следующий запрос:

Результатом будет значение 11.

Пример 10. База данных и таблица — те же, что и в предыдущих примерах.

Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT:

Результатом будет значение 17.

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

Пример 11. Работаем с одной таблицей — Staff. Вывести число сотрудников в отделе планирования (Plains).

Агрегатные функции вместе с SQL GROUP BY (группировкой)

Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору.

Пример 12. Есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts — о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money — о денежных суммах, вырученных за подачу объявлений.

Category Part Units Money
Транспорт Автомашины 110 17600
Недвижимость Квартиры 89 18690
Недвижимость Дачи 57 11970
Транспорт Мотоциклы 131 20960
Стройматериалы Доски 68 7140
Электротехника Телевизоры 127 8255
Электротехника Холодильники 137 8905
Стройматериалы Регипс 112 11760
Досуг Книги 96 6240
Недвижимость Дома 47 9870
Досуг Музыка 117 7605
Досуг Игры 41 2665

Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос:

Результатом будет следующая таблица:

Category Money
Досуг 16510
Недвижимость 40530
Стройматериалы 18900
Транспорт 38560
Электротехника 17160

Пример 13. База данных и таблица — та же, что в предыдущем примере.

Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было подано наибольшее число объявлений. Пишем следующий запрос:

Результатом будет следующая таблица:

Category Part Maximum
Досуг Музыка 117
Недвижимость Квартиры 89
Стройматериалы Регипс 112
Транспорт Мотоциклы 131
Электротехника Холодильники 137

Итоговые и индивидуальные значения в одной таблице можно получить объединением результатов запросов с помощью оператора UNION.

Обобщение данных с помощью агрегатных функций

В ЭТОЙ ГЛАВЕ ВЫ ПЕРЕЙДЕТЕ ОТ ПРОСТОГО использования запросов к извлечению значений из базы данных и определению, как вы можете использовать эти значения, чтобы получить из них информацию. Это делается с помощью агрегатных или общих функций, которые берут группы значений из поля и сводят их до одиночного значения. Вы узнаете, как использовать эти функции, как определить группы значений, к которым они будут применяться, и как определить, какие группы выбираются для вывода. Вы будете также видеть, при каких условиях вы сможете объединить значения поля с этой полученной информацией в одиночном запросе.

Что такое агрегатные функции?

Запросы могут производить обобщенное групповое значение полей точно также как и значение одного поля. Это делает с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций:

COUNT производит номера строк или не NULL значения полей, которые выбрал запрос.

SUM производит арифметическую сумму всех выбранных значений данного поля.

AVG производит усреднение всех выбранных значений данного поля.

MAX производит наибольшее из всех выбранных значений данного поля.

MIN производит наименьшее из всех выбранных значений данного поля.

Как использовать агрегатные функции?

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

С COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля. Когда они используются с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII, который должен сообщать, что MIN будет означать первое, а MAX последнее значение в алфавитном порядке (выдача алфавитного упорядочения обсуждается более подробно в Главе 4).

Чтобы найти SUM всех наших покупок в таблицы Заказов, мы можем ввести следующий запрос, с его выводом в Рисунке 6.1:

SELECT SUM (amt)
FROM Orders;

Рисунок 6.1. Выбор суммы.

Это, конечно, отличается от выбора поля, при котором возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Из-за этого, агрегатные функции и поля не могут выбираться одновременно, пока не будет использовано предложение GROUP BY (описанное далее). Нахождение усредненной суммы — это похожая операция (вывод следующего запроса показывается в Рисунке 6.2):


SELECT AVG (amt)
FROM Orders;

Рисунок 6.2. Выбор среднего.

Специальные атрибуты COUNT

Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце, или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT, чтобы производить счет чисел различных значений в данном поле. Мы могли бы использовать ее, например, чтобы сосчитать номера продавцов в настоящее время описанных в таблице Заказов (вывод показывается в Рисунке 6.3):

SELECT COUNT (DISTINCT snum)
FROM Orders;

Обратите внимание в вышеупомянутом примере, что DISTINCT, сопровождаемый именем поля с которым он применяется, помещен в круглые скобки, но не сразу после SELECT, как раньше. Этого использования DISTINCT с COUNT применяемого к индивидуальным столбцам, требует стандарт ANSI, но большое количество программ не предъявляют к ним такого требования.

Рисунок 6.3: Подсчет значений поля

Вы можете выбирать многочисленные счета (COUNT) из полей с помощью DISTINCT в одиночном запросе который, как мы видели в Главе 3, не выполнялся когда вы выбирали строки с помощью DISTINCT. DISTINCT может использоваться, таким образом, с любой функцией агрегата, но наиболее часто он используется с COUNT. С MAXиMIN это просто не будет иметь никакого эффекта, а SUMиAVG вы обычно применяете для включения повторяемых значений, так как они законно эффективнее общих и средних значений всех столбцов.

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

Агрегатные функции в SQL позволяют забирать обобщающую информацию из группы строк и проводить систематизацию данных. Список агрегатных функций приведен в таблице 8.7. Агрегатные функции почти во всех реализациях SQL носят одинаковые имена. Различие в наименование для Oracle дано через косую черту.

Таблица 8.7. Агрегатные функции

Функция Описание
AVG(X) = AVG(ALLX) AVG(DISTINCTX) Вычисляет среднее значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты
COUNT(*) COUNT(X) = COUNT(ALL X) COUNT(DISTINCT X) Вычисляет число итемов. При указании * всегда возвращается число строк в таблице. Указание DISTINCT подавляет дубликаты
MAX(X) = MAX(ALL X) MAX(DISTINCT X) Вычисляет максимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTI NCT подавляет дубликаты
MIN(X) = MIN(ALLX) MIN(DISTINCTX) Вычисляет минимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты
SUM(X) = SUM(ALL X) SU INDISTINCT X) Вычисляет сумму значений аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубли каты
STDDEV([DISTINCT|ALL| X) Вычисляет стандартное отклонение на множестве значений аргумента, который может быть выражением любого типа. Нуль-значения игно­рируются, ключевое слово DISTINCT подавляет дубликаты
VARIANCE([DIST1NCT|ALL1 X) Вычисляет квадрат дисперсии

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

вы узнаете итоговую сумму зарплаты по организации, а из запроса

SELECT AVG(SAL), STODEV(SAL)

— среднюю зарплату no организации и ее разброс (дисперсию).

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

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

Предположим, что вы хотите найти минимальные и максимальные оклады служащих в подразделениях, тогда вы можете написать

SELECT DEPNO, MIN(SAL), MAX(SAL)

Предложение GROUP BY должно следовать после предложения WHERE, если последнее присутствует в команде SELECT. Каждая строка результирующей таблицы относится к одной группе строк. Число групп определяется числом различных значений в колонке группировки (в данном случае DEPNO). Агрегатные функции применяются к каждой группе как к отдельному множеству.

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

SELECT DNAME, JOB, SUM(SAL), C0UNT(*), AVG(SAL)

FROM EMPLOYEE, DEPARTAMENT

GROUP BY DNAME, JOB;

Функции SUM( ), C0UNT( ), AVG( ) вычисляют суммы, число строк в группе и среднее значение в группе строк.

В SQL можно задавать условия поиска для группы строк. Для этого в команде SELECT существует предложение HAVING, которое должно следовать за предложением GROUP BY. HAVING задает условие поиска для группы строк.

Допустим, что вам необходимо получить ответ на тот же вопрос, что и в предыдущем примере, но при этом каждая группа должна состоять не менее чем из двух сотрудников.

SELECT DNAME, JOB, SUM(SAL), AVG(SAL)

FROM EMPLOYEE, DEPARTAMENT

WHERE EMPLOYEE.DEPNO=DEPARTAMENT. DEPNO

GROUP BY DNAME, JOB

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

Таким образом, вы познакомились с различными вариантами использования команды SQL SELECT.

Язык SQL. Формирование запросов к базе данных

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

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

Например, сгруппируем отношение R1 по значению столбца Дисциплина . Мы получим 4 группы, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Оценка .

Это делается с помощью агрегатных функций . Агрегатные функции вычисляют одиночное значение для всей группы таблицы. Список этих функций представлен в табл. 5.7.

Таблица 5.7. Агрегатные функции
Функция Результат
COUNT Количество строк или непустых значений полей, которые выбрал запрос
SUM Сумма всех выбранных значений данного поля
AVG Среднеарифметическое значение всех выбранных значений данного поля
MIN Наименьшее из всех выбранных значений данного поля
MAX Наибольшее из всех выбранных значений данного поля
R1
ФИО Дисциплина Оценка
Группа 1 Петров Ф. И. Базы данных 5
Сидоров К. А. Базы данных 4
Миронов А. В. Базы данных 2
Степанова К. Е. Базы данных 2
Крылова Т. С. Базы данных 5
Владимиров В. А. Базы данных 5
Группа 2 Сидоров К. А. Теория информации 4
Степанова К. Е. Теория информации 2
Крылова Т. С. Теория информации 5
Миронов А. В. Теория информации Null
Группа 3 Трофимов П. А. Сети и телекоммуникации 4
Иванова Е. А. Сети и телекоммуникации 5
Уткина Н. В. Сети и телекоммуникации 5
Группа 4 Владимиров В. А. Английский язык 4
Трофимов П. А. Английский язык 5
Иванова Е. А. Английский язык 3
Петров Ф. И. Английский язык 5

Агрегатные функции используются подобно именам полей в операторе SELECT , но с одним исключением: они берут имя поля как аргумент . С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT , MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.

Илон Маск рекомендует:  Атрибут media в HTML

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

Дисциплина COUNT(*)
Базы данных 6
Теория информации 4
Сети и телекоммуникации 3
Английский язык 4


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

Дисциплина COUNT(*)
Базы данных 6
Теория информации 3
Сети и телекоммуникации 3
Английский язык 4

В этом случае строка со студентом

Миронов А. В. Теория информации Null

не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины » Теория информации » будет на 1 меньше.

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

Обратившись снова к базе данных «Сессия» (таблицы R1, R2, R3 ), найдем количество успешно сданных экзаменов:

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

Дисциплина COUNT(DISTINCT R1 .Оценка)
Базы данных 3
Теория информации 3
Сети и телекоммуникации 2
Английский язык 3

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

Дисциплина COUNT(*) AVR (Oцeнкa)
Базы данных 6 3.83
Теория информации 3 3.67
Сети и телекоммуникации 3 4.66
Английский язык 4 4.25

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

Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT . Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT , так и в выражении условия обработки сформированных групп HAVING . В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций , могут быть использованы для вывода соответствующих результатов или для условия отбора групп.

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

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

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

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

GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и функция SUM применяется отдельно для каждой такой группы, то есть суммарный остаток на счетах подсчитывается отдельно для каждого филиала. Значение поля, к которому применяется GROUP BY , имеет, по определению, только одно значение на группу вывода, как и результат работы агрегатной функции. Поэтому мы можем совместить в одном запросе агрегат и поле . Вы можете также использовать GROUP BY с несколькими полями.

Предположим, что мы хотели бы увидеть только те суммарные значения остатков на счетах, которые превышают $5000. Чтобы увидеть суммарные остатки свыше $5000, необходимо использовать предложение HAVING . Предложение HAVING определяет критерии, используемые, чтобы удалять определенные группы из вывода, точно так же как предложение WHERE делает это для индивидуальных строк.

Правильной командой будет следующая:

Аргументы в предложении HAVING подчиняются тем же самым правилам, что и в предложении SELECT , где используется GROUP BY . Они должны иметь одно значение на группу вывода.

Следующая команда будет запрещена:

Поле ДатаОткрытия не может быть использовано в предложении HAVING , потому что оно может иметь больше чем одно значение на группу вывода. Чтобы избежать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля, выбранные GROUP BY . Имеется правильный способ сделать вышеупомянутый запрос :

Смысл данного запроса следующий: найти сумму остатков по каждому филиалу счетов, открытых 27 декабря 1999 года.

Как и говорилось ранее, HAVING может использовать только аргументы, которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и поля, выбранные с помощью GROUP BY , также допустимы. Например, мы хотим увидеть суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и Урюпинске:

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING , прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY . Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT , SUM , AVG , MIN и MAX , вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING : если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

Результатом выполнения раздела HAVING является сгруппированная таблица , содержащая только те группы строк, для которых результат вычисления условия поиска есть TRUE . В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY , то результатом его выполнения будет либо пустая таблица , либо результат выполнения предыдущих разделов табличного выражения , рассматриваемый как одна группа без столбцов группирования.

Понимание SQL (12 стр.)

SQL Execution Log

SELECT * FROM Salespeople WHERE sname LIKE ‘ P 1% ‘;

snum

sname

city

comm

Таблица 5.8: SELECT использует LIKE с подчеркиванием (_)

А что же Вы будете делать если вам нужно искать знак процента или знак подчеркивания в строке? В LIKE предикате, вы можете определить любой одиночный символ как символ ESC. Символ ESC используется сразу перед процентом или подчеркиванием в предикате, и означает что процент или подчеркивание будет интерпретироваться как символ а не как групповой символ. Например, мы могли бы найти наш sname столбец где присутствует подчеркивание, следующим образом:

WHERE sname LIKE ‘%/_%’ESCAPE’/’;

С этими данными не будет никакого вывода, потому что мы не включили никакого подчеркивания в имя нашего продавца. Предложение ESCAPE определяет ‘/ ‘ как символ ESC. Символ ESC используемый в LIKE строке, сопровождается знаком процента, знаком подчеркивания, или знаком ESCAPE, который будет искаться в столбце, а не обрабатываться как групповой символ. Символ ESC должен быть одиночным символом и применяться только к одиночному символу сразу после него.

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

Как упомянуто выше, символ ESC может также использоваться самостоятельно. Другими словами, если вы будете искать столбец с вашим символом ESC, вы просто вводите его дважды. Во-первых это будет означать что символ ESC «берет следующий символ буквально как символ», и во-вторых что символ ESC самостоятелен.

Имеется предыдущий пример который пересмотрен чтобы искать местонахождение строки ‘_/’ в sname столбце:

WHERE sname LIKE ‘ % /_ / / %’ESCAPE’/’;

Снова не будет никакого вывода с такими данными. Строка сравнивается с содержанием любой последовательности символов (%), сопровождаемых символом подчеркивания (/_ ), символом ESC (// ), и любой последовательностью символов в конце строки (% ).


РАБОТА С НУЛЕВЫМИ( NULL ) ЗНАЧЕНИЯМИ

Часто, будут иметься записи в таблице которые не имеют никаких значений для каждого поля, например потому что информация не завершена, или потому что это поле просто не заполнялось. SQL учитывает такой вариант, позволяя вам вводить значение NULL(ПУСТОЙ) в поле, вместо значения. Когда значение поля равно NULL, это означает, что программа базы данных специально промаркировала это поле как не имеющее никакого значения для этой строки (или записи). Это отличается от просто назначения полю, значения нуля или пробела, которые база данных будет обрабатывать также как и любое другое значение. Точно также, как NULL не является техническим значением, оно не имеет и типа данных. Оно может помещаться в любой тип поля. Тем ни менее, NULL в SQL часто упоминается как нуль.

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

Вы можете ввести строку для заказчика со значением NULL в поле snum и заполнить это поле значением позже, когда продавец будет назначен.

Так как NULL указывает на отсутствие значения, вы не можете знать каков будет результат любого сравнения с использованием NULL. Когда NULL сравнивается с любым значением, даже с другим таким же NULL, результат будет ни верным ни неверным, он — неизвестен. Неизвестный Булев, вообще ведет себя также как неверная строка, которая произведя неизвестное значение в предикате не будет выбрана запросом — имейте ввиду что в то время как NOT(неверное) — равнятся верно, NOT (неизвестное) — равняется неизвестно.

Следовательно, выражение типа ‘city=NULL’ или ‘city IN (NULL)’ будет неизвестно, независимо от значения city.

Часто вы должны делать различия между неверно и неизвестно — между строками содержащими значения столбцов которые не соответствуют условию предиката и которые содержат NULL в столбцах. По этой причине, SQL предоставляет специальный оператор IS, который используется с ключевым словом NULL, для размещения значения NULL.

Найдем все записи в нашей таблице Заказчиков с NULL значениями в city столбце:

WHERE city IS NULL;

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

ИСПОЛЬЗОВАНИЕ NOT СО СПЕЦИАЛЬНЫМИ ОПЕРАТОРАМИ

Специальные операторы которые мы изучали в этой главе могут немедленно предшествовать Булеву NOT.

Он противоположен реляционным операторам, которые должны иметь оператор NOT — вводимым выражением. Например, если мы хотим устранить NULL из нашего вывода, мы будем использовать NOT чтобы изменить на противоположное значение предиката:

WHERE city NOT NULL;

При отсутствии значений NULL( как в нашем случае ), будет выведена вся таблица Заказчиков. Аналогично можно ввести следующее

WHERE NOT city IS NULL;

— что также приемлемо.

Мы можем также использовать NOT с IN:

WHERE city NOT IN (‘London’, ‘San Jose’ );

А это — другой способ подобного же выражения

WHERE NOT city IN (‘London’, ‘ San Jose’ );

Вывод для этого запроса показывается в Таблице 5.9.

Таким же способом Вы можете использовать NOT BETWEEN и NOT LIKE.

SQL Execution Log

SELECT * FROM Salespeople WHERE sity NOT IN

(‘London’, ‘San Jose’);

snum

sname

city

comm

Таблица 5. 9: Использование NOT с IN

Теперь вы можете создавать предикаты в терминах связей специально определенных SQL. Вы можете искать значения в определенном диапазоне (BETWEEN) или в числовом наборе (IN), или вы можете искать символьные значения которые соответствуют тексту внутри параметров (LIKE).

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

Это уже тема Главы 6.

Напишите два запроса которые могли бы вывести все порядки на 3 или 4 Октября 1990

* Напишите запрос который выберет всех заказчиков обслуживаемых продавцами Peel или Motika. (Подсказка: из наших типовых таблиц, поле snum связывает вторую таблицу с первой )

* Напишите запрос, который может вывести всех заказчиков чьи имена начинаются с буквы попадающей в диапазон от A до G.

* Напишите запрос который выберет всех пользователей чьи имена начинаются с буквы C.

* Напишите запрос который выберет все порядки имеющие нулевые значения или NULL в поле amt(сумма).

Глава 6. ОБОБЩЕНИЕ ДАННЫХ С ПОМОЩЬЮ АГРЕГАТНЫХ ФУНКЦИЙ

В ЭТОЙ ГЛАВЕ, ВЫ ПЕРЕЙДЕТЕ ОТ ПРОСТОГО использования запросов к извлечению значений из базы данных и определению, как вы можете использовать эти значения чтобы получить из них информацию. Это делается с помощью агрегатных или общих функций которые берут группы значений из поля и сводят их до одиночного значения. Вы узнаете как использовать эти функции, как определить группы значений к которым они будут применяться, и как определить какие группы выбираются для вывода. Вы будете также видеть при каких условиях вы сможете объединить значения поля с этой полученной информацией в одиночном запросе.

ЧТО ТАКОЕ АГРЕГАТНЫЕ ФУНКЦИИ?

Запросы могут производить обобщенное групповое значение полей точно также как и значение одного поля. Это делает с помощью агрегатых функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций:

* COUNT производит номера строк или не-NULL значения полей которые выбрал запрос.


* SUM производит арифметическую сумму всех выбранных значений данного поля.

* AVG производит усреднение всех выбранных значений данного поля.

* MAX производит наибольшее из всех выбранных значений данного поля.

* MIN производит наименьшее из всех выбранных значений данного поля.

Запросы с обобщением

Читайте также:

  1. Агрегатные функции, вложенные запросы в операторе выбора.
  2. Вложенные запросы. Примеры.
  3. Групповые запросы.
  4. Запросы
  5. Запросы
  6. Запросы
  7. Запросы
  8. Запросы
  9. Запросы
  10. Запросы SQL
  11. Запросы А6-3-7 — А6-3-8
  12. ЗАПРОСЫ В СУБД ACCESS
Илон Маск рекомендует:  Процедуры работы с динамической памятью

Задание критериев отбора

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

При задании критериев отбора можно также использовать логические операторы (AND, OR, NOT), операторы between и like, операторы сравнения (>, =, =”5” or

Дата добавления: 2015-05-09 ; Просмотров: 433 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Лекция №7, Обобщение данных с помощью агрегатных функций

Код роботи: 4516

Вид роботи: Лекція

Предмет: База даних (БД) (База данных (БД))

Тема: №7, Обобщение данных с помощью агрегатных функций

Кількість сторінок: 16

Дата виконання: 2020

Мова написання: російська

Ціна: 100 грн

1. Обобщение данных с помощью агрегатных функций

2. Агрегаты, построенные на скалярном выражении

3. Предложение GROUP BY

4. Предложение HAVING

5. Скалярное выражение на основе выбранных полей

6. Упорядочение вывода полей

7. Объединения нескольких таблиц в запросе

8. Создание обьединения

9. Объединение таблиц через справочную целостность

10. Объединения таблиц по равенству значений

Вложенные операторы в агрегатных функциях T-SQL

Дополнительные варианты фильтрации

Как вы помните, различные элементы определения окна (секционирование, упорядочение и кадрирование) по сути являются различными вариантами фильтрации. Существуют другие потребности в фильтрации, которые эти определения не в состоянии удовлетворить. Некоторые из этих потребностей удается удовлетворить с помощью предложения FILTER, которое не было реализовано в SQL Server 2012. Есть также попытки решить эту проблему за счет внесения предложений по расширению стандарта, которые, я надеюсь, так или иначе появятся в стандарте и SQL Server.

Начну с предложения FILTER. В стандарте определено, что в функциях агрегирования это предложение позволяет фильтровать набор строк, к которому применяется агрегирование, с использованием предиката, формат этого предложения выглядит так:

В качестве примера приведу запрос, вычисляющий разницу между текущим количеством и среднемесячным количеством для сотрудника до текущей даты (не месяца текущей строки):

SQL Server 2012 пока не поддерживает предложение FILTER. Честно говоря, я не знаю СУБД, которая поддерживала его. Если вам нужна такая возможность, существует довольно простое альтернативное решение — использовать в качестве входных данных для функции агрегирования выражение CASE:

Вот полный запрос, который решает ту же задачу:

Чего все еще не хватает в стандарте (начиная с версии SQL 2008) и SQL Server 2012, так это возможности ссылаться на элементы текущей строки для целей фильтрации. Это можно было бы применять в предложении FILTER, в альтернативном решении с использованием выражения CASE, а также в других случаях, в которых нужна фильтрация.

Для демонстрации этой потребности представьте на секундочку, что на элемент текущей строки можно ссылаться с помощью префикса $current_row. А теперь представим себе, что нужно написать запрос представления Sales.OrderValues, который бы вычислял для каждого заказа разницу между значением текущего заказа и средним значением для определенного сотрудника для всех клиентов кроме того клиента, которому принадлежит этот заказ. Эта задача решается следующим запросом с предложением FILTER:

В качестве альтернативы можно воспользоваться выражением CASE:

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

Предложение по улучшению

Есть очень интересные предложения по расширению стандарта для удовлетворения этой и других потребностей. Одним из примеров являются сравнительные оконные функции. Подробнее о предложении можно узнать из блога Тома Кайта по адресу Comparative Window Functions.


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

Паттерны в последовательностях строк определяются с применением семантики, похожей на регулярные выражения. Этот механизм может применяться для определения табличного выражения, а также для фильтрации строк в определении окна. Он также может использоваться в технологиях потоковой передачи данных, например с StreamInsight в SQL Server, а также в запросах, которые работают с неперемещаемыми данными. Вот ссылка на предоставленный для всеобщего доступа документ: http://www.softwareworkshop.com/h2/SQL-RPR-review-paper.pdf. Прежде чем читать этот документ, я предлагаю освободить голову от лишних мыслей и хорошенько взбодриться кофе. Это непросто чтение, но идея исключительно интересна и я надеюсь, что она пробьет себе путь в стандарт SQL и будет использоваться не только для данных в движении, но и для неактивных данных.

Ключевое слово DISTINCT в функциях агрегирования

SQL Server 2012 не поддерживает параметр DISTINCT в оконных функциях агрегирования. Представьте, что вам нужно запрашивать представление Sales.OrderValues и получить для каждого заказа число конкретных клиентов, с которыми работал текущий сотрудник с начала и до текущей даты. Вам нужно выполнить такой запрос:

Но поскольку этот запрос не поддерживается, нужно искать обходное решение. Один из вариантов — прибегнуть к помощи функции ROW_NUMBER. Я расскажу о ней подробнее чуть попозже, а пока достаточно будет сказать, что она возвращает уникальное целое значение для каждой строки секции, начиная с единицы и с шагом 1, в соответствии с определением упорядочения в окне. С помощью функции ROW_NUMBER можно назначить строкам номера, секционированные по empid и custid и упорядоченные по orderdate. Это означает, что строки с номером 1 относятся к первому случаю работы сотрудника с данным клиентом при упорядочении заказов по датам. Используя выражение CASE можно вернуть значение custid, только если номер строки равен 1, а в противном случае вернуть NULL. Вот запрос, реализующий описанную логику, с результатом его работы:

Заметьте, что для каждого сотрудника возвращается только первое значение cust >обобщенного табличного значения (CTE) на основе предыдущего запроса, а затем применении агрегирования текущего числа строк к результату выражения CASE:

Вложенные агрегаты

На данный момент вы знаете, что есть групповые и оконные агрегаты. Как уже говорилось, функции при этом используются одинаковые, но контекст разный. Групповые агрегаты работают на основе групп строк, определенных предложением GROUP BY и возвращают одно значение на группу. Оконные агрегаты действуют на основе окон строк и возвращают одно значение для каждой строки в базовом запросе. Вспомните рассказ о логической обработке запросов из статьи Запросы. Напомню порядок, в котором в соответствии с концепцией должны обрабатываться различные предложения запросов:

Групповые агрегаты используются, когда запрос является групповым, и они разрешены в фазах, которые обрабатываются после определения групп, а именно, начиная с фазы 4 и далее. Помните, что в результате запроса каждая группа представлена только одной строкой. Оконные агрегаты разрешены, начиная с фазы 5 и последующих, потому что они работают на основе строк базового запроса — после фазы HAVING.

Агрегаты двух типов — даже при том, что они используют те же имена функций и логику вычислений — работают в разных контекстах. Вернусь к важному моменту, который я хотел отметить в этом разделе: что, если нужно просуммировать значение, сгруппированное по идентификатору сотрудника, и одновременно агрегировать все эти суммы по всем сотрудникам?

Это совершенно легальный, но на первый взгляд странный подход — применять оконный агрегат к окну, содержащему строки с атрибутами, полученными с применением групповых агрегатов. Я сказал «странный», потому что на первый взгляд выражение SUM(SUM(val)) в запросе выглядит неуместным. Но оно имеет право на существование. Посмотрите на запрос, который решает поставленную задачу:

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

Будет проще увидеть логику вложенных агрегатов, если анализ запроса разбить да два этапа. На первом вычисляется групповой агрегат:

Этот результат можно считать начальной точкой для дальнейшего оконной агрегации. Таким образом, можно применить агрегат SUM к выражению, представленному псевдонимом emptotal. К сожалению нельзя применить его непосредственно к псевдониму по причинам, изложенным ранее (помните принцип «все сразу»?). Но его можно применить к базовому выражению так: SUM(SUM(val)) OVER(. ) и можно считать, что это SUM(emptotal) OVER(. ). Таким образом получаем следующее:

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

Посмотрим на другой пример сложностей, связанных с оконными и групповыми функциями. Следующая задача является вариацией запроса, приведенного в предыдущей статье. Надо создать запрос таблицы Sales.Orders, возникающий для каждого сотрудника точные даты заказов и точные имена клиентов, с которыми работал текущий сотрудник с начала и до текущей даты. Первая попытка реализации:

Но при выполнении запроса вы получаете следующую ошибку:

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

Ясно, что ответ отрицательный. Атрибут distinct_custid в списке SELECT неверен, потому что не содержится ни в агрегирующей функции, ни в предложении GROUP BY, и примерно об этом говорится в сообщении об ошибке. Что вам нужно сделать, так это применить оконный агрегат SUM с кадром, реализующим принцип нарастающего итога, к групповому агрегату COUNT, который считает конкретные вхождения:

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

Обобщение данных с помощью агрегатных функций

В ЭТОЙ ГЛАВЕ ВЫ ПЕРЕЙДЕТЕ ОТ ПРОСТОГО использования запросов к извлечению значений из базы данных и определению, как вы можете использовать эти значения, чтобы получить из них информацию. Это делается с помощью агрегатных или общих функций, которые берут группы значений из поля и сводят их до одиночного значения. Вы узнаете, как использовать эти функции, как определить группы значений, к которым они будут применяться, и как определить, какие группы выбираются для вывода. Вы будете также видеть, при каких условиях вы сможете объединить значения поля с этой полученной информацией в одиночном запросе.

Что такое агрегатные функции?

Запросы могут производить обобщенное групповое значение полей точно также как и значение одного поля. Это делает с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций:

COUNT производит номера строк или не NULL значения полей, которые выбрал запрос.

SUM производит арифметическую сумму всех выбранных значений данного поля.

AVG производит усреднение всех выбранных значений данного поля.

MAX производит наибольшее из всех выбранных значений данного поля.

MIN производит наименьшее из всех выбранных значений данного поля.

Как использовать агрегатные функции?

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

С COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля. Когда они используются с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII, который должен сообщать, что MIN будет означать первое, а MAX последнее значение в алфавитном порядке (выдача алфавитного упорядочения обсуждается более подробно в Главе 4).

Чтобы найти SUM всех наших покупок в таблицы Заказов, мы можем ввести следующий запрос, с его выводом в Рисунке 6.1:

SELECT SUM (amt)
FROM Orders;

Рисунок 6.1. Выбор суммы.

Это, конечно, отличается от выбора поля, при котором возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Из-за этого, агрегатные функции и поля не могут выбираться одновременно, пока не будет использовано предложение GROUP BY (описанное далее). Нахождение усредненной суммы — это похожая операция (вывод следующего запроса показывается в Рисунке 6.2):

SELECT AVG (amt)
FROM Orders;

Рисунок 6.2. Выбор среднего.

Специальные атрибуты COUNT

Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце, или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT, чтобы производить счет чисел различных значений в данном поле. Мы могли бы использовать ее, например, чтобы сосчитать номера продавцов в настоящее время описанных в таблице Заказов (вывод показывается в Рисунке 6.3):

SELECT COUNT (DISTINCT snum)
FROM Orders;

Обратите внимание в вышеупомянутом примере, что DISTINCT, сопровождаемый именем поля с которым он применяется, помещен в круглые скобки, но не сразу после SELECT, как раньше. Этого использования DISTINCT с COUNT применяемого к индивидуальным столбцам, требует стандарт ANSI, но большое количество программ не предъявляют к ним такого требования.

Рисунок 6.3: Подсчет значений поля

Вы можете выбирать многочисленные счета (COUNT) из полей с помощью DISTINCT в одиночном запросе который, как мы видели в Главе 3, не выполнялся когда вы выбирали строки с помощью DISTINCT. DISTINCT может использоваться, таким образом, с любой функцией агрегата, но наиболее часто он используется с COUNT. С MAXиMIN это просто не будет иметь никакого эффекта, а SUMиAVG вы обычно применяете для включения повторяемых значений, так как они законно эффективнее общих и средних значений всех столбцов.

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL