Функции агрегирования sql: Агрегатные функции SQL — CodeTown.ru

Агрегатные функции SQL — CodeTown.ru

Здравствуйте! Сегодня мы познакомимся с агрегатными функциями в SQL, подробно разберем их работу с данными из таблиц, которые создавали в прошлых уроках.

Общее понятие

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

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

• Суммировать выбранные значения
• Находить среднее арифметическое значений
• Находить минимальное и максимальное из значений

Примеры агрегатных функций SQL

Мы разберем самые часто используемые функции и приведем несколько примеров.

Функция SUM

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

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

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

Следующий код осуществит нужную выборку:

SELECT SUM(amt) 
FROM Orders 
WHERE odate BETWEEN '2016-01-01' and '2016-12-31'; 

В результате получим:

В данном запросе мы использовали функцию SUM, после которой в скобках нужно указать поле для суммирования. Затем мы указали условие в WHERE, которое отобрало строчки только с 2016 годом. На самом деле это условие можно записать по другому, но сейчас важнее агрегатная функция суммирования в SQL.

Функция AVG

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

Вывести среднюю стоимость заказа из таблицы Orders.

И сразу запрос:

SELECT AVG(amt) 
FROM Orders; 

В результате получим:

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

Функции MIN и MAX

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

Вывести максимальное и минимальное значения цены заказа, для тех заказов в которых цена менее 1000.

Получается такой запрос,

SELECT MAX(amt), MIN(amt) 
FROM Orders 
WHERE amt < 1000; 

который выведет:

Также стоит сказать, что в отличие от предыдущих функций, эти 2 могут работать с символьными параметрами, то есть можно написать запрос типа MIN(odate) (в данном случае дата у нас символьная), и тогда нам вернется 2016-01-01.

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

Еще одним важным моментом является то, что мы можем производить некоторые простые математические операции в запросе SELECT, например, такой запрос:

SELECT (MAX(amt) - MIN(amt)) AS 'Разница' 
FROM Orders; 

Вернет такой ответ:

Функция COUNT

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

Теперь разберем пример использования COUNT в SQL:

Подсчитать количество сделанных заказов и количество продавцов в таблице Orders.

SELECT COUNT(*), COUNT(DISTINCT snum) 
FROM Orders; 

Получаем:

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

Оператор GROUP BY

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

Вывести сумму всех заказов для каждого продавца по отдельности.

То есть теперь нам нужно для каждого продавца в таблице Orders выделить поля с ценой заказа и просуммировать. Все это сделает оператор GROUP BY в SQL достаточно легко:

SELECT snum, SUM(amt) AS 'Сумма всех заказов' 
FROM Orders 
GROUP BY snum; 

И в итоге получим:

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

Оператор HAVING

Этот оператор используется как дополнение к предыдущему. Он необходим для того, чтобы ставить условия для выборки данных при группировке. Если условие выполняется то выделяется группа, если нет — то ничего не произойдет. Рассмотрим следующий код:

SELECT snum, SUM(amt) AS 'Сумма всех заказов' 
FROM Orders 
GROUP BY snum 
HAVING MAX(amt) > 1000; 

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

Казалось бы, почему тут не использовать условие WHERE, но SQL так построен, что в таком случае выдаст ошибку, и именно поэтому в SQL есть оператор HAVING.

Примеры на агрегатные функции в SQL

1. Напишите запрос, который сосчитал бы все суммы заказов, выполненных 1 января 2016 года.

SELECT SUM(amt)
FROM Orders
WHERE odate = '2016-01-01';

2. Напишите запрос, который сосчитал бы число различных, отличных от NULL значений поля city в таблице заказчиков.

SELECT COUNT(DISTINCT city)
FROM customers;

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

SELECT cnum, MIN(amt)
FROM orders
GROUP BY cnum;

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

SELECT cname
FROM customers
WHERE cname LIKE 'Г%' ;

5. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.

SELECT city, MAX(rating)
FROM customers
GROUP BY city;

Заключение

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

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

Поделиться ссылкой:

Похожее

PostgreSQL : Документация: 12: 9.20. Агрегатные функции : Компания Postgres Professional

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

Агрегатные функции получают единственный результат из набора входных значений. Встроенные агрегатные функции общего назначения перечислены в Таблице 9.55, а статистические агрегатные функции — в Таблице 9.56. Встроенные внутригрупповые сортирующие агрегатные функции перечислены в Таблице 9.57, встроенные внутригрупповые гипотезирующие — в Таблице 9.58. Группирующие операторы, тесно связанные с агрегатными функциями, перечислены в Таблице 9.59. Особенности синтаксиса агрегатных функцией разъясняются в Подразделе 4.2.7. За дополнительной вводной информацией обратитесь к Разделу 2.7.

Таблица 9.55. Агрегатные функции общего назначения

Следует заметить, что за исключением count, все эти функции возвращают NULL, если для них не была выбрана ни одна строка. В частности, функция sum, не получив строк, возвращает NULL, а не 0, как можно было бы ожидать, и array_agg в этом случае возвращает NULL, а не пустой массив. Если необходимо, подставить в результат 0 или пустой массив вместо NULL можно с помощью функции coalesce.

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

Примечание

Булевы агрегатные функции bool_and и bool_or соответствуют стандартным SQL-агрегатам every и any или some. Что касается any и some, по стандарту их синтаксис допускает некоторую неоднозначность:

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

Здесь ANY можно рассматривать и как объявление подзапроса, и как агрегатную функцию, если этот подзапрос возвращает одну строку с булевым значением. Таким образом, этим агрегатным функциям нельзя было дать стандартные имена.

Примечание

Пользователи с опытом использования других СУБД SQL могут быть недовольны скоростью агрегатной функции count, когда она применяется ко всей таблице. Подобный запрос:

SELECT count(*) FROM sometable;

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

Агрегатные функции array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg и xmlagg так же, как и подобные пользовательские агрегатные функции, выдают разные по содержанию результаты в зависимости от порядка входных значений. По умолчанию порядок не определён, но его можно задать, дополнив вызов агрегатной функции предложением ORDER BY, как описано в Подразделе 4.2.7. Обычно нужного результата также можно добиться, передав для агрегирования результат подзапроса с сортировкой. Например:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

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

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

Таблица 9.56. Агрегатные функции для статистических вычислений

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

Таблица 9.57. Сортирующие агрегатные функции

Все агрегатные функции, перечисленные в Таблице 9.57, игнорируют значения NULL при сортировке данных. Для функций, принимающих параметр дробь, значение этого параметра должно быть от 0 до 1; в противном случае возникает ошибка. Однако, если в этом параметре передаётся NULL, эти функции просто выдают NULL.

Все агрегатные функции, перечисленные в Таблице 9.58, связаны с одноимёнными оконными функциями, определёнными в Разделе 9.21. В каждом случае их результат — значение, которое вернула бы связанная оконная функция для «гипотетической» строки, полученной из аргументов, если бы такая строка была добавлена в сортированную группу строк, которую образуют сортированные_аргументы.

Таблица 9.58. Гипотезирующие агрегатные функции

SQL | Функции (агрегатные и скалярные функции)

Для выполнения операций над данными в SQL есть много встроенных функций, они подразделяются на две категории и далее подкатегоризованы на разные семь функций в каждой категории. Категории:

1. Агрегатные функции:
   Эти функции используются для выполнения операций со значениями столбца, и возвращается одно значение.
   1. AVG ()
   2. COUNT ()
   3. ПЕРВЫЙ()
   4. ПОСЛЕДНИЙ()
   5. МАКСИМУМ()
   6. MIN ()
   7. СУММА ()
2. Скалярные функции:
   Эти функции основаны на вводе пользователем, они также возвращают одно значение.
   1. UCASE ()
   2. LCASE ()
   3. (MID)
   4. LEN ()
   5. КРУГЛЫЙ()
   6. СЕЙЧАС()
   7. ФОРМАТ()

Студенты-Table

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

• AVG () : возвращает среднее значение после расчета по значениям в числовом столбце.
  Синтаксис:

  SELECT AVG(column_name) FROM table_name;
  

  Запросы:

  1. Вычисление средних баллов студентов.

     SELECT AVG(MARKS) AS AvgMarks FROM Students; 
     

     Выход:

  2. Вычисление среднего возраста студентов.

     SELECT AVG(AGE) AS AvgAge FROM Students; 
     

     Выход:

• COUNT (): используется для подсчета количества строк, возвращаемых в инструкции SELECT. Он не может быть использован в MS ACCESS.
  Синтаксис:

  SELECT COUNT(column_name) FROM table_name;
  

  Запросы:

  1. Подсчет общего количества студентов.

     SELECT COUNT(*) AS NumStudents FROM Stuents;
     

     Выход:

  2. Вычисление числа студентов с уникальным / отличным возрастом.

     SELECT COUNT(DISTINCT AGE) AS NumStudents FROM Students;
     

     Выход:

• FIRST (): функция FIRST () возвращает первое значение выбранного столбца.
  Синтаксис:

  SELECT FIRST(column_name) FROM table_name;
  

  Запросы:

  1. Получение отметок первого ученика со стола учеников.

     SELECT FIRST(MARKS) AS MarksFirst FROM Students;
     

     Выход:

  2. Получение возраста первого ученика из таблицы учеников.

     SELECT FIRST(AGE) AS AgeFirst FROM Students;
     

     Выход:

• LAST (): функция LAST () возвращает последнее значение выбранного столбца. Может использоваться только в MS ACCESS.
  Синтаксис:

  SELECT LAST(column_name) FROM table_name;
  

  Запросы:

  1. Извлечение отметок последнего ученика из таблицы «Студенты».

     SELECT LAST(MARKS) AS MarksLast FROM Students;
     

     Выход:

  2. Выбор возраста последнего ученика из таблицы учеников.

     SELECT LAST(AGE) AS AgeLast FROM Students;
     

     Выход:

• MAX (): функция MAX () возвращает максимальное значение выбранного столбца.
  Синтаксис:

  SELECT MAX(column_name) FROM table_name;
  

  Запросы :

  1. Получение максимальных оценок среди студентов из таблицы «Студенты».

     SELECT MAX(MARKS) AS MaxMarks FROM Students;
     

     Выход:

  2. Получение максимального возраста среди студентов из таблицы «Студенты».

     SELECT MAX(AGE) AS MaxAge FROM Students;
     

     Выход:

• MIN (): функция MIN () возвращает минимальное значение выбранного столбца.
  Синтаксис:

  SELECT MIN(column_name) FROM table_name;
  

  Запросы:

  1. Получение минимальных оценок среди студентов из таблицы «Студенты».

     SELECT MIN(MARKS) AS MinMarks FROM Students;
     

     Выход:

  2. Извлечение минимального возраста среди учеников из таблицы учеников.

     SELECT MIN(AGE) AS MinAge FROM Students;
     

     Выход:

• SUM (): функция SUM () возвращает сумму всех значений выбранного столбца.
  Синтаксис:

  SELECT SUM(column_name) FROM table_name;
  

  Запросы:

  1. Выборка суммирования итоговых оценок среди студентов из таблицы «Студенты».

     SELECT SUM(MARKS) AS TotalMarks FROM Students;
     

     Выход:

  2. Выборка суммирования общего возраста среди учеников из таблицы учеников.

     SELECT SUM(AGE) AS TotalAge FROM Students;
     

     Выход:

Скалярные функции

• UCASE () : преобразует значение поля в верхний регистр.
  Синтаксис:

  SELECT UCASE(column_name) FROM table_name;
  

  Запросы:

  1. Преобразование имен учеников из таблицы учеников в верхний регистр.

     SELECT UCASE(NAME) FROM Students;
     

     Выход:

     NAME

     HARSH

     SURESH

     PRATIK

     DHANRAJ

     RAM

• LCASE () : преобразует значение поля в нижний регистр.
  Синтаксис:

  SELECT LCASE(column_name) FROM table_name;
  

  Запросы:

  1. Преобразование имен учеников из таблицы учеников в строчные.

     SELECT LCASE(NAME) FROM Students;
     

     Выход:

     NAME

     harsh

     suresh

     pratik

     dhanraj

     ram

• MID (): функция MID () извлекает текст из текстового поля.
  Синтаксис:

  SELECT MID(column_name,start,length) AS some_name FROM table_name;
  
  specifying length is optional here, and start signifies start position ( starting from 1 )
  

  Запросы:

  1. Получение первых четырех символов имен учеников из таблицы «Студенты».

     SELECT MID(NAME,1,4) FROM Students; 
     

     Выход:

• LEN (): функция LEN () возвращает длину значения в текстовом поле.
  Синтаксис:

  SELECT LENGTH(column_name) FROM table_name;
  

  Запросы:

  1. Получение длины имен студентов из таблицы студентов.

     SELECT LENGTH(NAME) FROM Students;
     

     Выход:

• ROUND (): функция ROUND () используется для округления числового поля до указанного числа десятичных знаков. ПРИМЕЧАНИЕ: Многие системы баз данных приняли стандарт IEEE 754 для арифметических операций, который говорит, что при округлении любого числового .5 оно приводит к до ближайшего четного целого числа, то есть 5,5 и 6,5 оба округляются до 6.

  Синтаксис:

  SELECT ROUND(column_name,decimals) FROM table_name; 
  
  decimals- number of decimals to be fetched.
  

  Запросы:

  1. Получение максимальных оценок среди студентов из таблицы «Студенты».

     SELECT ROUND(MARKS,0) FROM table_name; 
     

     Выход:

• NOW (): функция NOW () возвращает текущую системную дату и время.
  Синтаксис:

  SELECT NOW() FROM table_name;
  

  Запросы:

  1. Получение текущего системного времени.

     SELECT NAME, NOW() AS DateTime FROM Students; 
     

     Выход:

     NAME	DateTime
     HARSH	1/13/2017 1:30:11 PM
     SURESH	1/13/2017 1:30:11 PM
     PRATIK	1/13/2017 1:30:11 PM
     DHANRAJ	1/13/2017 1:30:11 PM
     RAM	1/13/2017 1:30:11 PM

• FORMAT (): функция FORMAT () используется для форматирования отображения поля.
  Синтаксис:

  SELECT FORMAT(column_name,format) FROM table_name; 
  

  Запросы:

  1. Форматирование текущей даты в формате «ГГГГ-ММ-ДД».

     SELECT NAME, FORMAT(Now(),'YYYY-MM-DD') AS Date FROM Students; 
     

     Выход:

     NAME	Date
     HARSH	2017-01-13
     SURESH	2017-01-13
     PRATIK	2017-01-13
     DHANRAJ	2017-01-13
     RAM	2017-01-13

Эта статья предоставлена Пратиком Агарвалом . Если вы как GeeksforGeeks и хотели бы внести свой вклад, вы также можете написать статью с помощью contribute.geeksforgeeks.org или по почте статьи [email protected]. Смотрите свою статью, появляющуюся на главной странице GeeksforGeeks, и помогите другим вундеркиндам.

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

Рекомендуемые посты:

SQL | Функции (агрегатные и скалярные функции)

0.00 (0%) 0 votes

Функции агрегации данных · Loginom Help

4.4. GROUP BY и агрегатные функции SQL

РЕЗЮМЕ

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

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

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

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

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

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

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

Чтобы найти SUM всех окладов в таблице ОТДЕЛ_СОТРУДНИК (рис. 2.3) надо ввести следующий запрос:

FROM Отдел_ Сотрудники;

И на экране увидим результат: 46800 (в таблице будет один столбец с именем СУММА).

Подсчет среднего значения по окладам также прост:

SELECT AVG ((Оклад))

FROM Отдел_ Сотрудники;

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

В таблице восемь строк, в которых находятся различные значения окладов.

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

Следующее предложение позволяет определить число подразделений на

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

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

SELECT COUNT (*)

FROM Отдел_ Сотрудники;

COUNT (*) подсчитывает все без исключения строки таблицы.

DISTINCT не применим c COUNT (*).

Предположим, что таблица ВЕДОМОСТЬ_ОПЛАТЫ (рис. 2.4) имеет еще один столбец, который хранит сумму произведенных вычетов (поле Вычет) для каждой строки ведомости. Тогда если Вас интересует вся сумма, то содержимое столбца Сумма и Вычет надо сложить.

Если же Вас интересует максимальная сумма с учетом вычетов, содержащаяся в ведомости, то это можно сделать с помощью следующего предложения:

SELECT MAX (Сумма + Вычет)

FROM Ведомость_ оплаты;

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

ПРЕДЛОЖЕНИЕ GROUP BY (перекомпоновка, порядок)

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

Например, предположим, что Вы хотите определить, сколько сотрудников находятся в каждом отделе (ответ приведен на рис. 4.21):

SELECT Ид_Отд, COUNT (DISTINCT Ид_Отд) AS Кол-во_сотрудников

Рис. 4.21. Запрос с группировкой

В результате выполнения предложения GROUP BY остаются только уникальные значения столбцов, по умолчанию отсортированные по возрастанию. В этом аспекте предложение GROUP BY отличается от предложения ORDER BY тем, что последнее хотя и сортирует записи по возрастанию, но не удаляет повторяющиеся значения. В приведенном примере запрос группирует строки таблицы по значениям столбца Ид_Отд (по номерам отделов). Строки с одинаковыми номерами отделов сначала объединяются в группы, но при этом для каждой группы отображается только одна строка. Во втором столбце выводится количество строк в каждой группе, т.е. число сотрудников в каждом отделе.

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

Пусть, например, таблица ВЕДОМОСТЬ_ОПЛАТЫ имеет вид рис. 4.22 и мы хотим определить максимальную сумму, выплаченную по ведомости каждому сотруднику.

В результате получим.

Рис. 4.23. Агрегатная функция с AS

Группировка может быть осуществлена и по нескольким атрибутам: SELECT Ид_сотр, Дата, MAX ((Сумма))

FROM Ведомость1

GROUP BY Ид_сотр, Дата;

Результат:

Рис. 4.24. Группировка по нескольким атрибутам

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

4.5. Использование фразы HAVING

Фраза HAVING играет такую же роль для групп, что и фраза WHERE для строк: она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение

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

Например, пусть надо выдать количественный состав всех отделов (рис. 2.3), исключая отдел с номером 3.

SELECT Ид_Отд, COUNT (DISTINCT Ид_Отд) AS Кол-во _сотрудников

Рис. 4.25. Запрос с группировкой и ограничением

Последним элементом при вычислении табличного выражения используется раздел HAVING (если он присутствует). Синтаксис этого раздела следующий:

<having clause> ::=

HAVING <search condition>

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

Условие поиска раздела HAVING строится по тем же синтаксическим правилам, что и условие поиска раздела WHERE, и может включать те же самые предикаты.

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

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

Пусть запрос вида (в качестве базовой таблицы см. рис. 4.22):

SELECT Ид_сотр, Дата, MAX ((Сумма))

FROM Ведомость1

GROUP BY Ид_сотр, Дата;

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

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

Следующее предложение будет неправильным:

SELECT Ид_сотр, Дата, MAX (Сумма)

FROM Ведомость1

WHERE MAX ((Сумма)) > 1000 GROUP BY Ид_сотр, Дата;

Правильным предложением будет:

SELECT Ид_сотр, Дата, MAX ((Сумма))

Агрегатные функции в sql и переименование столбцов

На уроке будет рассмотрена тема sql переименование столбца (полей) при помощи служебного слова AS; также рассмотрена тема агрегатные функции в sql. Будут разобраны конкретные примеры запросов

Переименование полей AS

Имена столбцов в запросах можно переименовывать. Это придает результатам более читабельный вид.

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

Синтаксис:

SELECT <имя поля> AS <псевдоним> FROM …

SELECT <имя поля> AS <псевдоним> FROM …

Рассмотрим пример переименования в SQL:

Пример БД «Институт»: Вывести фамилии учителей и их зарплаты, для тех преподавателей, у которых зарплата ниже 15000, переименовать поле zarplata на «низкая_зарплата»

✍ Решение: 

1
2
3

SELECT name, zarplata AS низкая_зарплата
  FROM teachers
  WHERE zarplata<15000;

SELECT name, zarplata AS низкая_зарплата
FROM teachers
WHERE zarplata<15000;

Результат:

Переименование столбцов в SQL часто необходимо при вычислении значений, связанных с несколькими полями таблицы. Рассмотрим пример:

Пример БД «Институт»: Из таблицы teachers вывести поле name и вычислить сумму зарплаты и премии, назвав поле «зарплата_премия»

✍ Решение: 

1
2

SELECT name, (zarplata+premia) AS zarplata_premia 
  FROM teachers;

SELECT name, (zarplata+premia) AS zarplata_premia
FROM teachers;

Результат:

SQL As 2_1. БД Институт. Вывести фамилии учителей и разницу между их зарплатой и премией. Назвать вывод «зарплата_минус_премия»

Задание 2_1. БД «Компьютерные курсы». Из таблицы Личные данные вывести значения полей Код студента, Word, Excel, Access и вычислить среднее значение по полям Word, Excel, Access, назвав поле «Среднее»

SQL As 2_2. БД Компьютерный магазин. Вывести объем оперативной памяти в Килобайтах (из Мб получить Кб). Выводить и исходное и получившееся значение

Задание 2_2. БД «Компьютерные курсы». Вывести номера телефонов и оценки по Word студентов, успеваемость по курсу Word которых ниже 4 баллов, переименовать поле Word на Низкая успеваемость

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

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

Все агрегатные функции возвращают единственное значение.

Функции COUNT, MIN и MAX применимы к любым типам данных.

Функции SUM и AVG используются только для числовых полей.
Между функциями COUNT(*) и COUNT() есть разница: вторая при подсчете не учитывает NULL-значения.

Важно: при работе с агрегатными функциями в SQL используется служебное слово AS

Пример БД «Институт»: Получить значение самой большой зарплаты среди учителей, вывести итог как «макс_зп»

✍ Решение: 

SELECT MAX(zarplata) AS макс_зп
  FROM teachers;

SELECT MAX(zarplata) AS макс_зп
FROM teachers;

Результаты:

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

Пример: БД Компьютерный магазин. Найти имеющееся в наличии количество компьютеров, выпущенных производителем Америка

✍ Решение: 

1
2
3
4
5
6
7
8

SELECT COUNT( * ) 
FROM  `pc` 
WHERE  `Номер` 
IN (
  SELECT  `Номер` 
  FROM product
  WHERE Производитель =  "Америка"
)

SELECT COUNT( * )
FROM `pc`
WHERE `Номер`
IN (
SELECT `Номер`
FROM product
WHERE Производитель = «Америка»
)

Agr func 2_2. БД Институт. Вывести минимальную и максимальную зарплату учителей

Agr func 2_3. БД Институт. Выберите название курса, уроки по которому не проводились и не запланированы проводиться. Дополните код:

1
2
3
4
5
6

SELECT  `title` 
FROM  `courses` 
WHERE  `title` NOT 
IN (
   ...
)

SELECT `title`
FROM `courses`
WHERE `title` NOT
IN (
…
)

Agr func 2_4. БД Институт. Измените предыдущее задание: Посчитайте количество тех курсов, уроки по которым не проводились и не запланированы проводиться. Выводите результат с именем «нет_уроков»

Предложение GROUP BY в SQL

Оператор group by в sql обычно используется совместно с агрегатными функциями.

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

Рассмотрим пример с таблицей lessons:

Пример:

1. Выдавать количество проведенных уроков учителем Иванов из таблицы lessons (порядковый номер Иванова 1 (tid)).

SELECT COUNT(tid) AS Иванов FROM lessons WHERE tid=1

SELECT count(tid) as Иванов
from lessons
WHERE tid=1

Результат:

2. Выдавать количество проведенных уроков учителем Иванов по разным курсам из таблицы lessons

SELECT course, COUNT( tid ) AS Иванов FROM lessons WHERE tid =1 GROUP BY course

SELECT course, COUNT( tid ) AS Иванов
FROM lessons
WHERE tid =1
GROUP BY course

Результат:

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

При этом стоит учесть, что при группировке по полю, содержащему NULL-значения, все такие записи попадут в одну группу.

SQL group by 2_5. БД Компьютерный магазин. Для различных типов принтеров определить их среднюю стоимость и количество (т.е. отдельно по лазерным, струйным и матричным). Использовать агрегатные функции AVG(поле) и COUNT(поле). Результат должен выглядеть так:

SQL group by 2_5. БД Институт. Посчитать количество уроков, проведенных одним и тем же учителем. Результат должен выглядеть так:

Задание 2_5. БД «Компьютерные курсы». Вывести количество человек в каждой группе и количество человек на каждом курсе из таблицы Список. Назвать вычисляемые поля «кол_во_в_гр» и «кол_во_на_курс»

Оператор Having SQL

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

Пример: БД Компьютерный магазин. Посчитать среднюю цену компьютеров с одинаковой скоростью процессора. Выполнить подсчет только для тех групп, средняя цена которых меньше 30000.

✍ Решение: 

SELECT AVG(`Цена`) ,`Скорость`
FROM  `pc` 
GROUP BY `Скорость` 
HAVING AVG(`Цена`) <30000

SELECT AVG(`Цена`) ,`Скорость`
FROM `pc`
GROUP BY `Скорость`
HAVING AVG(`Цена`) <30000

Результат:

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

Having SQL 2_6. БД Компьютерный магазин. Для различных типов принтеров определить их среднюю стоимость (т.е. отдельно по лазерным, струйным и матричным). Вести подсчет только если средняя стоимость

Having SQL 2_6. БД Институт
Посчитать количество уроков, проведенных одним и тем же учителем. Выдавать значение только для тех учителей, у которых уроков больше двух.

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

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

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

Общий формат унарной агрегатной функции следующий:

имя_функции([АLL | DISTINCT] выражение)

где DISTINCT указывает, что функция должна рассматривать только различные значения аргумента, а ALL — все значения, включая повторяющиеся (этот вариант используется по умолчанию). Например, функция AVG с ключевым словом DISTINCT для строк столбца со значениями 1, 1, 1 и 3 вернет 2, а при наличии ключевого слова ALL вернет 1,5.

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

Функция COUNT имеет два формата. В первом случае возвращается количество строк входной таблицы, во втором случае — количество значений аргумента во входной таблице:

• COUNT(*)
• COUNT([DISTINCT | ALL] выражение)

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

Запрос: Количество видов продукции, информация о которых имеется в базе данных.

SELECT COUNT(*) AS ‘Количество видов продукции’

FROM Product

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

Запрос: Количество различных имен, содержащихся в таблице Customer.

SELECT COUNT(DISTINCT FNAME)

FROM Customer

Использование остальных унарных агрегатных функции аналогично COUNT за тем исключением, что для функций MIN и MAX использование ключевых слов DISTINCT и ALL не имеет смысла. С функциями COUNT, MAX и MIN кроме числовых могут использоваться и символьные поля. Если аргумент агрегатной функции не содержит значений, функция COUNT возвращает 0, а все остальные — значение NULL.

Запрос: Дата последнего заказа до 1 сентября 2010 года.

SELECT MAX(OrdDate)

FROM [Order]

WHERE OrdDate’1.09.2010′

Задание для самостоятельной работы: Сформулируйте на языке SQL запросы на выборку следующих данных:

• Суммарная стоимость всех заказов;
• Количество различных городов, содержащихся в таблице Customer.

Еще записи по теме

SUM, AVG, MAX, MIN, COUNT, DISTINCT

• Home

• Testing

  • • Back
    • Agile Testing
    • BugZilla
    • Cucumber
    • 9000 Testing Database 9000L
    • Назад
    • JUnit
    • LoadRunner
    • Ручное тестирование
    • Мобильное тестирование
    • Mantis
    • Почтальон
    • QTP
    • Назад
    • 00030003 Центр контроля качества
    • 0003 Центр контроля качества
    • SoapUI
    • Управление тестированием
    • TestLink

• SAP

  • • Назад
    • ABAP 9000 4
    • APO
    • Начинающий
    • Basis
    • BODS
    • BI
    • BPC
    • CO
    • Назад
    • CRM
    • Crystal Reports
    • FICO
    • 000
    • 000 HRM
    • 000
    • 000 HRM Заработная плата
    • Назад
    • PI / PO
    • PP
    • SD
    • SAPUI5
    • Безопасность
    • Менеджер решений
    • Successfactors
    • Учебники SAP
  • Apache
  • AngularJS
  • ASP.Net
  • C
  • C #
  • C ++
  • CodeIgniter
  • СУБД
  • JavaScript
  • Назад
  • Java
  • JSP
  • Kotlin
  • Linux
  • Linux
  • Kotlin
  • Linux

  js

  • Perl
  • Назад
  • PHP
  • PL / SQL
  • PostgreSQL
  • Python
  • ReactJS
  • Ruby & Rails
  • Scala
  • SQL

  000

  • SQL

  000

  0003 SQL

  000

  0003 SQL

  000

  • UML
  • VB.Net
  • VBScript
  • Веб-службы
  • WPF

  
 ВЫБРАТЬ СРЕДНЮЮ (зарплату) 
 
 ОТ сотрудника; 
  

  
 ВЫБРАТЬ СРЕДНЮЮ (зарплату) 
 ОТ сотрудника 
 
 ГДЕ title = «Программист»; 
 
  

  
 ВЫБРАТЬ Счетчик (*) 
 ОТ сотрудника;