Агрегатные функции
В некоторых случаях требуется в самом запросе произвести вычисление значений полей, получить количество найденных записей, произвести поиск максимального значения поля или выполнить иную вычислительную работу. Функции, реализующие эти возможности, называются агрегатными. Агрегатные функции возвращают одно значение для всего поля таблицы. Список агрегатных функций приведен ниже:
Оператор COUNT возвращает количество записей, удовлетворяющих условию запроса.
Оператор SUM суммирует значения записей поля.
Оператор AVG вычисляет среднее значение записей поля.
Оператор МАХ возвращает наибольшее значение данного поля.
Оператор MIN возвращает наименьшее значение данного ноля.
Агрегатные функции используются подобно именам полей в запросе, а настоящие имена полей передаются им как аргументы. С операторами SUM и AVG могут использоваться только числовые поля. С операторами COUNT, MAX и MIN могут использоваться числовые и символьные поля. В случае применения функций МАХ и MIN к символьным полям их значения будут транслированы в ASCII-код. Минимальному значению функции будет соответствовать символ алфавита, находящийся ближе к его началу, максимальному — находящийся ближе к концу. Функция COUNT производит подсчет всех записей. Для того чтобы исключить повторы, следует использовать оператор DISTINCT. Этот оператор располагается перед названием поля, внутри функции COUNT.
Например, оператор
Select count(*) from Pers
подсчитает полное количество записей в таблице Pers, а оператор
Select count(*) from Pers where Dep=’Бухгалтерия’
выдаст число записей сотрудников бухгалтерии. Оператор
Select count(DISTINCT Dep) from Pers
вернет число различных подразделений, упомянутых в поле Dep таблицы Pers. Ниже приведен запрос, выбирающий из таблицы Orders среднее значение веса груза из поля Freight, минимальное значение веса груза, максимальное значение веса груза, его суммарное значение и количество грузов, вес которых составляет более трехсот единиц.
SELECT AVG (Freight) AS Среднее, MIN (Freight) AS Мин, MAX (Freight) AS Макс, SUM (Freight) AS Суммарное, COUNT (Freight) AS Количество FROM Orders WHERE Freight > 300
Например, оператор
Select min(Year_r), max(Year_r), avg(Year_r) from Pers
вернет минимальное, максимальное и среднее значение года рождения, а оператор
Select min(GetDate()-Year_r), max(GetDate()-Year_r), avg(GetDate()-Year_r) from Pers
выдаст аналогичные данные, но относящиеся к возрасту сотрудников,
GetDate() – текущая дата в MS SQL Server.
Для исключения повторов при использовании функций AVG и SUM тоже может быть использован оператор DISTINCT.
При использовании агрегатных характеристик следует учитывать, что в списке возвращаемых значений после ключевого слова Select могут фигурировать или поля, или агрегатные характеристики, но не могут фигурировать и те и другие. Это очевидно, так как оператор может возвращать или множество значений полей, или агрегатные характеристики по таблице. Нельзя например, написать запрос,
Select fam, max(Year_r) from Pers
в котором определяется фамилия самого молодого сотрудника.
Смешение в одном операторе полей и агрегатных характеристик возможно, если использовать группировку записей, задаваемую ключевыми словами Group By.
Оператор GROUP BY используется для определения полей, к которым могут применяться агрегатные функции. В случае, если этот оператор явно не указан, все поля, указанные в выражении SELECT, трактуются как аргументы агрегатных функций. Поля, указанные в качестве параметров оператора GROUP BY, становятся группирующими. Все записи результирующего набора, имеющие одинаковые значения группирующих полей, образуют единую группу. Далее к каждой такой группе будет применена агрегатная функция. Фактически, оператор GROUP BY дает возможность объединять поля и агрегатные функции в едином запросе. Иллюстрирует вышесказанное запрос, отыскивающий города, в которых расположены фирмы, количество этих городов и максимальное значение почтового индекса для фирмы, расположенной в данном городе:
SELECT City, COUNT (*) AS Количество, MAX (PostalCode) AS Почтовый_индекс FROM Customers GROUP BY City
Легко заметить, что поле City не входит в агрегатную функцию в качестве параметра, поэтому оно было объявлено с использованием оператора GROUP BY. В ходе выполнения запроса были выбраны города, и для каждого города было подсчитано количество вхождений.
Этот пример можно усложнить. Можно создать запрос, который получает только те города, которые повторяются в таблице больше двух раз, и при этом в конечный результат не должен включаться город Buenos Aires. Оператор WHERE в данном случае использовать не получится, так как он работает только с отдельными записями, а не с массивами. Придется использовать оператор HAVING, который является аналогом оператора WHERE, но может работать с агрегатными функциями. Сам запрос будет довольно сильно изменен:
SELECT City, COUNT (*) AS Количество, MAX (PostaTCode) AS Почтовый_индекс FROM Customers Where City <> 'Buenos Aires'
GROUP BY City HAVING COUNT (*) >=3
Оператор
Select Dep, Count(*) From Pers Group By Dep
вернет таблицу, в которой будет 2 столбца: столбец с названиями отделов, и столбец, в котором будет отображено число сотрудников в каждом отделе, а оператор
Select Dep, Count(*) From Pers Group By Dep Having Dep!= ‘Бухгалтерия’
вернет строки, относящиеся ко всем отделам, кроме бухгалтерии.
Достарыңызбен бөлісу: |
