Разное

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

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

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

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

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

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

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

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

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

Функция SUM

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

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

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

onum amt odate cnum snum
1001 128 2016-01-01 9 4
1002 1800 2016-04-10 10 7
1003 348 2017-04-08 2 1
1004 500 2016-06-07 3 3
1005 499 2017-12-04 5 4
1006 320 2016-03-03 5 4
1007 80 2017-09-02 7 1
1008 780 2016-03-07 1 3
1009 560 2017-10-07 3 7
1010 900 2016-01-08 6 8

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

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

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

  • С ключевым словом DISTINCT, для того, чтобы подсчитать количество не повторяющихся значений
  • С использованием «*», для того, чтобы подсчитать количество всех выбранных значений
  • Теперь разберем пример использования 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; 
    

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

    snum Сумма всех заказов
    1 428
    3 1280
    4 947
    7 2360
    8 900

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

    Оператор HAVING

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

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

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

    snum Сумма всех заказов
    7 2360

    Казалось бы, почему тут не использовать условие 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. Агрегатные функции общего назначения

    Функция Типы аргумента Тип результата Частичный режим Описание
    array_agg(выражение) любой тип не массива массив элементов с типом аргумента Нет входные значения, включая NULL, объединяются в массив
    array_agg(выражение) любой тип массива тот же, что и тип аргумента Нет входные массивы собираются в массив большей размерности (они должны иметь одну размерность и не могут быть пустыми или равны NULL)
    avg(выражение) smallint, int, bigint, real, double precision, numeric или interval numeric для любых целочисленных аргументов, double precision для аргументов с плавающей точкой, в противном случае тип данных аргумента Да арифметическое среднее для всех входных значений, отличных от NULL
    bit_and(выражение) smallint, int, bigint или bit тот же, что и тип аргумента Да побитовое И для всех входных значений, не равных NULL, или NULL, если таких нет
    bit_or(выражение) smallint, int, bigint или bit тот же, что и тип аргумента Да побитовое ИЛИ для всех входных значений, не равных NULL, или NULL, если таких нет
    bool_and(выражение) bool bool Да true, если все входные значения равны true, и false в противном случае
    bool_or(выражение) bool bool Да true, если хотя бы одно входное значение равно true, и false в противном случае
    count(*)   bigint Да количество входных строк
    count(выражение) any bigint Да количество входных строк, для которых значение выражения не равно NULL
    every(выражение) bool bool Да синоним bool_and
    json_agg(выражение) any json Нет агрегирует значения, включая NULL, в виде массива JSON
    jsonb_agg(выражение) any jsonb Нет агрегирует значения, включая NULL, в виде массива JSON
    json_object_agg(имя, значение) (any, any) json Нет агрегирует пары имя/значение в виде объекта JSON (NULL допускается в значениях, но не в именах)
    jsonb_object_agg(имя, значение) (any, any) jsonb Нет агрегирует пары имя/значение в виде объекта JSON (NULL допускается в значениях, но не в именах)
    max(выражение) любой числовой, строковый, сетевой тип или тип даты/времени, либо массив этих типов тот же, что и тип аргумента Да максимальное значение выражения среди всех входных данных, отличных от NULL
    min(выражение) любой числовой, строковый, сетевой тип или тип даты/времени, либо массив этих типов тот же, что и тип аргумента Да минимальное значение выражения среди всех входных данных, отличных от NULL
    string_agg(выражение, разделитель) (text, text) или (bytea, bytea) тот же, что и типы аргументов Нет входные данные (исключая NULL) складываются в строку через заданный разделитель
    sum(выражение) smallint, int, bigint, real, double precision, numeric, interval или money bigint для аргументов smallint или int, numeric для аргументов bigint, и тип аргумента в остальных случаях Да сумма значений выражения по всем входным данным, отличным от NULL
    xmlagg(выражение) xml xml Нет соединение XML-значений, отличных от NULL (см. также Подраздел 9.14.1.7)

    Следует заметить, что за исключением 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. Агрегатные функции для статистических вычислений

    Функция Тип аргумента Тип результата Частичный режим Описание
    corr(Y, X) double precision double precision Да коэффициент корреляции
    covar_pop(Y, X) double precision double precision Да ковариация совокупности
    covar_samp(Y, X) double precision double precision Да ковариация выборки
    regr_avgx(Y, X) double precision double precision Да среднее независимой переменной (sum(X)/N)
    regr_avgy(Y, X) double precision double precision Да среднее зависимой переменной (sum(Y)/N)
    regr_count(Y, X) double precision bigint Да число входных строк, в которых оба выражения не NULL
    regr_intercept(Y, X) double precision double precision Да пересечение с осью OY линии, полученной методом наименьших квадратов по данным (X, Y)
    regr_r2(Y, X) double precision double precision Да квадрат коэффициента корреляции
    regr_slope(Y, X) double precision double precision Да наклон линии, полученной методом наименьших квадратов по данным (X, Y)
    regr_sxx(Y, X) double precision double precision Да sum(X^2) - sum(X)^2/N («сумма квадратов» независимой переменной)
    regr_sxy(Y, X) double precision double precision Да sum(X*Y) - sum(X) * sum(Y)/N («сумма произведений» независимых и зависимых переменных)
    regr_syy(Y, X) double precision double precision Да sum(Y^2) - sum(Y)^2/N («сумма квадратов» зависимой переменной)
    stddev(выражение) smallint, int, bigint, real, double precision или numeric double precision для аргументов с плавающей точкой, numeric для остальных Да сохранившийся синоним stddev_samp
    stddev_pop(выражение) smallint, int, bigint, real, double precision или numeric double precision для аргументов с плавающей точкой, numeric для остальных Да стандартное отклонение по генеральной совокупности входных значений
    stddev_samp(выражение) smallint, int, bigint, real, double precision или numeric double precision для аргументов с плавающей точкой, numeric для остальных Да стандартное отклонение по выборке входных значений
    variance(выражение) smallint, int, bigint, real, double precision или numeric double precision для аргументов с плавающей точкой, numeric для остальных Да сохранившийся синоним var_samp
    var_pop(выражение) smallint, int, bigint, real, double precision или numeric double precision для аргументов с плавающей точкой, numeric для остальных Да дисперсия для генеральной совокупности входных значений (квадрат стандартного отклонения)
    var_samp(выражение) smallint, int, bigint, real, double precision или numeric double precision для аргументов с плавающей точкой, numeric для остальных Да дисперсия по выборке для входных значений (квадрат отклонения по выборке)

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

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

    Функция Тип непосредственного аргумента Тип агрегированного аргумента Тип результата Частичный режим Описание
    mode() WITHIN GROUP (ORDER BY выражение_сортировки) любой сортируемый тип тот же, что у выражения сортировки Нет возвращает значение, наиболее часто встречающееся во входных данных (если одинаково часто встречаются несколько значений, произвольно выбирается первое из них)
    percentile_cont(дробь) WITHIN GROUP (ORDER BY выражение_сортировки) double precision double precision или interval тот же, что у выражения сортировки Нет непрерывный процентиль: возвращает значение, соответствующее заданной дроби по порядку, интерполируя соседние входные значения, если необходимо
    percentile_cont(дроби) WITHIN GROUP (ORDER BY выражение_сортировки) double precision[] double precision или interval массив типа выражения сортировки Нет множественный непрерывный процентиль: возвращает массив результатов, соответствующих форме параметра дроби (для каждого элемента не NULL подставляется значение, соответствующее данному процентилю)
    percentile_disc(дробь) WITHIN GROUP (ORDER BY выражение_сортировки) double precision любой сортируемый тип тот же, что у выражения сортировки Нет дискретный процентиль: возвращает первое значение из входных данных, позиция которого по порядку равна или превосходит указанную дробь
    percentile_disc(дроби) WITHIN GROUP (ORDER BY выражение_сортировки) double precision[] любой сортируемый тип массив типа выражения сортировки Нет множественный дискретный процентиль: возвращает массив результатов, соответствующих форме параметра дроби (для каждого элемента не NULL подставляется входное значение, соответствующее данному процентилю)

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

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

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

    Функция Тип непосредственного аргумента Тип агрегированного аргумента Тип результата Частичный режим Описание
    rank(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы) VARIADIC "any" VARIADIC "any" bigint Нет ранг гипотетической строки, с пропусками повторяющихся строк
    dense_rank(аргументы) WITHIN GROUP (ORDER BY сортированные_аргументы) VARIADIC "any" VARIADIC

    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

    Сумма Суммирует значение всех ячеек в столбце.
    Количество Показывает общее количество записей в наборе данных, в том числе пустые записи и записи без значения (<null>).
    Минимум Выводит наименьшее значение по столбцу. Пустые записи в агрегации не участвуют, кроме типа Строковый, где пустая запись является наименьшей. Записи с <null> в агрегации не участвуют вовсе.
    Максимум Выводит наибольшее значение по столбцу. Для Строкового типа порядок к максимуму такой: «Пустое значение → Символы → Цифры → латинский алфавит в нижнем регистре → Латинский в верхнем → Русский в нижнем → Русский в верхнем регистре».
    Среднее Выводит среднее значение всех ячеек столбца. В расчетах участвуют те поля, которые не пустые, и не <null>.
    Медиана Выводит значение медианы по столбцу. Участвующие в агрегации поля как у агрегации Среднее.
    Стандартное отклонение Выводит среднеквадратическое отклонение по столбцу. Участвующие в агрегации поля как у агрегации Среднее.
    Количество уникальных Показывает количество уникальных значений записей. При подсчете не учитываются записи с пустым значением, со значением <null> или значением не соответствующим типу данных. Кроме типа данных Строковый где не учитываются записи только со значением <null>.
    Количество пропусков Показывает количество записей с пустыми значениями, со значением <null> или со значениями не соответствующими типу данных. Кроме типа данных Строковый, где пропусками считаются записи со значением <null>.
    Первый Выводит значение первой записи в наборе данных.
    Последний                               Выводит значение последней записи наборе данных.

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

    Ид_Сотр.

    Фамилия

    Имя

    Отчество

    Год_рожд.

    Пол

     

     

     

     

     

     

    2

    Петров

    Иван

    Иванович

    1949

    М

     

     

     

     

     

     

    3

    Сидоров

    Петр

    Петрович

    1947

    М

     

     

     

     

     

     

    4

    Панов

    Антон

    Михайлович

    1975

    М

     

     

     

     

     

     

    5

    Петухов

    Виктор

    Борисович

    1940

    М

     

     

     

     

     

     

    7

    Петрова

    Нина

    Николаевна

    1970

    Ж

     

     

     

     

     

     

    8

    Сидорова

    Екатерина

    Ивановна

    1970

    Ж

     

     

     

     

     

     

    9

    Никитин

    Валентин

    Сергеевич

    1952

    М

     

     

     

     

     

     

    11

    Попов

    Анатолий

    Михайлович

    1947

    М

     

     

     

     

     

     

     

    Рис. 4.20. Использование LIKE «^[Д-М]% «

     

    РЕЗЮМЕ

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

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

    COUNT

    — число значений в столбце,

    SUM

    — арифметическая сумма значений в столбце,

    AVG

    — арифметическое среднее значение в столбце,

    MAX

    — самое большое значение в столбце,

    MIN

    — самое маленькое значение в столбце.

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

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

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

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

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

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

    SELECT SUM ((Оклад))

    AS СУММА

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

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

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

    SELECT AVG ((Оклад))

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

    И на экране увидим результат:

    3342.85

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

    SELECT COUNT (DISTINCT

    ОКЛАД)

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

     

    Результат: 8.

     

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

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

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

    предприятии:

     

    SELECT COUNT (DISTINCT

    Ид_Отд)

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

     

    Результат: 3.

     

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

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

    SELECT COUNT (*)

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

    Ответ будет:

    11.

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

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

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

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

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

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

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

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

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

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

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

    FROM

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

     

    WHERE

    Дата_ увольнения

    NOT NULL

    GROUP BY

     

    Ид_Отд;

     

     

     

     

     

     

     

    Ид_Отд

    Кол-во_сотрудников

     

     

     

     

     

     

    1

    5

     

     

     

     

     

     

    2

    4

     

     

     

     

     

     

    3

    1

     

     

     

     

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

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

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

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

    Ид_сотр

    Ид_Отд

    Дата

    Сумма

    Вид_оплаты

     

     

     

     

     

    1

    1

    02.03.03

    1200

     

     

     

     

     

     

    2

    1

    01.03.03

    1200

     

     

     

     

     

     

    3

    1

    01.03.03

    1000

     

     

     

     

     

     

    1

    1

    01.04.03

    1200

     

     

     

     

     

     

    1

    1

    01.03.03

    800

     

     

     

     

     

     

    2

    1

    02.04.03

    2000

     

     

     

     

     

     

     

    Рис. 4.22. Состояние таблицы Ведомость1

    SELECT

    ИД_Сотр, MAX (Сумма) AS «Сумма максимальная»

    FROM

    Ведомость1

    GROUP

    BY ИД_Сотр;

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

    Ид_сотр

    Сумма_максимальная

     

     

    3

    1000

     

     

    1

    1200

     

     

    2

    2000

     

     

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

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

    FROM Ведомость1

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

    Результат:

    Ид_сотр

    Дата

     

     

     

     

    1

    01.03.03

    800

     

     

     

    1

    02.03.03

    1200

     

     

     

    1

    01.04.03

    1200

     

     

     

    2

    01.03.03

    1200

     

     

     

    2

    02.04.03

    2000

     

     

     

    2

    01.03.03

    1000

     

     

     

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

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

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

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

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

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

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

    FROM

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

    WHERE

    Дата_ увольнения

     

    NOT NULL

    GROUP BY

     

    Ид_Отд

    HAVING Ид_Отд < > 3;

     

     

     

     

     

     

     

    Ид_Отд

     

    Кол_во_сотрудников

     

     

     

     

     

     

     

    1

     

    5

     

     

     

     

     

     

     

    2

     

    4

     

     

     

     

     

    Рис. 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(*) Возвращает количество строк таблицы.
    COUNT(имя поля) Возвращает количество значений в указанном столбце.
    SUM(имя поля) Возвращает сумму значений в указанном столбце.
    AVG(имя поля) Возвращает среднее значение в указанном столбце.
    MIN(имя поля) Возвращает минимальное значение в указанном столбце.
    MAX(имя поля) Возвращает максимальное значение в указанном столбце.

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

    Функции 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)).
    2. SELECT COUNT(tid) AS Иванов 
      FROM lessons
      WHERE tid=1

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

      Результат:

    3. Выдавать количество проведенных уроков учителем Иванов по разным курсам из таблицы lessons
    4. 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
  • Обязательно учите!

      • Назад
      • Бухгалтерский учет
      • Алгоритмы
      • Android
      • Блокчейн
      • Business Analyst
      • Создание веб-сайта
      • CCNA
      • Облачные вычисления
      • 00030003 COBOL 9000 Compiler
          9000 Встроенные системы

        • 00030002 9000 Compiler 9000
        • Ethical Hacking
        • Учебники по Excel
        • Программирование на Go
        • IoT
        • ITIL
        • Jenkins
        • MIS
        • Сеть
        • Операционная система
        • Назад
        • Управление проектами Обзоры
        • Salesforce
        • SEO
        • Разработка программного обеспечения
        • VB A
    • Big Data

        • Назад
        • AWS
        • BigData
        • Cassandra
        • Cognos
        • Хранилище данных
        • 0003

        • HBOps
        • 0003

        • HBOps
        • 0003

        • MicroStrategy

    .

    Агрегатных функций | SQLCourse — Расширенное онлайн-обучение SQL

    МИН. возвращает наименьшее значение в заданном столбце
    МАКС возвращает наибольшее значение в данном столбце
    СУМ возвращает сумму числовых значений в заданном столбце
    СРЕДНЕЕ возвращает среднее значение данного столбца
    СЧЕТ возвращает общее количество значений в данном столбце
    СЧЕТ (*) возвращает количество строк в таблице

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

      

    ВЫБРАТЬ СРЕДНЮЮ (зарплату)

    ОТ сотрудника;

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

    Другой пример:

      
    ВЫБРАТЬ СРЕДНЮЮ (зарплату)

    ОТ сотрудника

    ГДЕ title = «Программист»;

    Эта инструкция вернет среднюю зарплату для всех сотрудников, чья должность равна «Программист»

    Пример:

      
    ВЫБРАТЬ Счетчик (*)

    ОТ сотрудника;

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

    Упражнения для повторения

    1. Выберите максимальную цену любого заказанного товара в таблице items_ordered. Подсказка: выберите только максимальную цену.
    2. Выберите среднюю цену всех заказанных товаров, приобретенных в декабре.
    3. Каково общее количество строк в таблице items_ordered?
    4. Для всех палаток, которые были заказаны в таблице items_ordered, какова цена самой низкой палатки? Подсказка: ваш запрос должен возвращать только цену.

    Ответы на эти упражнения

    Введите здесь выражение SQL:

    .

    встроенных агрегатных функций

    встроенных агрегатных функций

    Небольшой. Быстрый. Надежный.
    Выберите любые три.

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

    вызов агрегатной функции:
    скрыть

    Показанные ниже агрегатные функции доступны по умолчанию. Дополнительные
    агрегатные функции, написанные на C, могут быть добавлены с помощью
    sqlite3_create_function ()
    API.

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

    Если предоставляется предложение FILTER, то только строки, для которых expr является
    true включены в совокупность.

    средн. ( X )

    Функция avg ()
    возвращает среднее значение всех отличных от NULL X в пределах
    группа.Значения String и BLOB, не похожие на числа,
    интерпретируется как 0.
    Результатом avg () всегда является значение с плавающей запятой, пока
    at есть хотя бы один вход, отличный от NULL, даже если все
    входы — целые числа. Результат avg () равен NULL тогда и только тогда, когда
    нет входов, отличных от NULL.

    количество ( X )
    количество (*)

    Функция count (X) возвращает
    счет количества раз
    что X не равно NULL в группе.Функция count (*)
    (без аргументов) возвращает общее количество строк в группе.

    group_concat ( X )
    group_concat ( X , Y )

    Функция group_concat () возвращает
    строка, которая является конкатенацией
    все значения X , отличные от NULL. Если присутствует параметр Y , то
    он используется как разделитель
    между экземплярами X . Запятая («,») используется как разделитель
    если Y опущено.Порядок составных элементов:
    произвольно.

    макс. ( X )

    Агрегатная функция max ()
    возвращает максимальное значение всех значений в группе.
    Максимальное значение — это значение, которое будет возвращено последним в
    ЗАКАЗАТЬ в том же столбце. Aggregate max () возвращает NULL
    тогда и только тогда, когда в группе нет значений, отличных от NULL.

    мин ( X )

    Агрегатная функция min ()
    возвращает минимальное значение, отличное от NULL, для всех значений в группе.Минимальное значение — это первое значение, отличное от NULL, которое может появиться
    в ORDER BY столбца.
    Aggregate min () возвращает NULL тогда и только тогда, когда нет отличных от NULL
    ценности в группе.

    сумма ( X )
    итого ( X )

    Агрегатные функции sum () и total ()
    вернуть сумму всех ненулевых значений в группе.
    Если нет входных строк, отличных от NULL, тогда sum () возвращает
    NULL, но total () возвращает 0,0.
    NULL обычно не является полезным результатом для суммы без строк
    но стандарт SQL требует этого, и большинство других
    Механизмы баз данных SQL реализуют sum () таким образом, поэтому SQLite делает это в
    таким же образом, чтобы быть совместимым.Нестандартная функция total ()
    предоставляется как удобный способ решения этой проблемы проектирования
    на языке SQL.

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

    Sum () выдаст исключение «целочисленное переполнение», если все входные данные
    целые числа или NULL
    и целочисленное переполнение происходит в любой момент во время вычисления.Total () никогда не вызывает целочисленного переполнения.

    .

  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *