Sql max min: | SQL | SQL-tutorial.ru

Функции min, Max (Microsoft Access SQL)

• 
  09/21/2018
• Чтение занимает 2 мин

В этой статье

Область применения: Access 2013 | Access 2016Applies to: Access 2013 | Access 2016

Возвращает минимальное или максимальное значение из набора значений, содержащегося в указанном поле запроса.Return the minimum or maximum of a set of values contained in a specified field on a query.

СинтаксисSyntax

Min (выражение)Min(expr)

Max (выражение)Max(expr)

Заполнитель expr представляет строковое выражение, определяющее поле, содержащее данные, которые необходимо оценить, или выражение, которое выполняет вычисления с использованием данных в этом поле. The expr placeholder represents a string expression identifying the field that contains the data you want to evaluate or an expression that performs a calculation using the data in that field. Операнды в expr могут включать имя поля таблицы, константу или функцию (которая может быть встроенной или пользовательской, но не одной из других статистических функций SQL).Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate functions).

ПримечанияRemarks

Функции min и Max можно использовать для определения минимальных и наибольших значений в поле на основе заданной статистической схемы или группирования.You can use Min and Max to determine the smallest and largest values in a field based on the specified aggregation, or grouping. Например, вы можете использовать эти функции для возврата минимальной и максимальной стоимости доставки. For example, you could use these functions to return the lowest and highest freight cost. Если объединение не задано, используется таблица целиком.If there is no aggregation specified, the entire table is used.

Можно использовать min и Max в выражении запроса и в свойстве SQL объекта QueryDef или при создании объекта Recordset на основе SQL запроса.You can use Min and Max in a query expression and in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.

ПримерExample

В этом примере таблица Orders используется для возврата минимальной и максимальной стоимости доставки для заказов, отправляемых в Соединенное Королевство.This example uses the Orders table to return the lowest and highest freight charges for orders shipped to the United Kingdom.

В этом примере вызывается процедура EnumFields, которую можно найти в примере инструкции SELECT. This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub MinMaxX() 
 
    Dim dbs As Database, rst As Recordset 
 
    ' Modify this line to include the path to Northwind 
    ' on your computer. 
    Set dbs = OpenDatabase("Northwind.mdb") 
     
    ' Return the lowest and highest freight charges for  
    ' orders shipped to the United Kingdom. 
    Set rst = dbs.OpenRecordset("SELECT " _  
        & "Min(Freight) AS [Low Freight], " _ 
        & "Max(Freight)AS [High Freight] " _ 
        & "FROM Orders WHERE ShipCountry = 'UK';") 
     
    ' Populate the Recordset. 
    rst.MoveLast 
     
    ' Call EnumFields to print the contents of the  
    ' Recordset. Pass the Recordset object and desired 
    ' field width. 
    EnumFields rst, 12 
 
    dbs.Close 
 
End Sub 

Об участникахAbout the contributors

Ссылка предоставлена сообществом UtterAccess.Link provided by the UtterAccess community.

UtterAccess — это премиальный вики-портал и форум, посвященный Microsoft Access.UtterAccess is the premier Microsoft Access wiki and help forum.

См. такжеSee also

Поддержка и обратная связьSupport and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи?Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Min и max функции sql. Агрегатные функции SQL

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

Функция SQL SUM

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

SELECT SUM
(ИМЯ_СТОЛБЦА) …

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

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

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

SELECT SUM
(Salary)
FROM
Staff

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

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

Функция SQL MIN

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

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

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

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

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

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

Функция SQL MAX

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

Пример 5.

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

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

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

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

Функция SQL AVG

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

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

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

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

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

Функция SQL COUNT

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

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

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

SELECT COUNT
(Comm)
FROM
Staff

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

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

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

SELECT COUNT
(*)
FROM
Staff

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

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

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

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

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

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

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

SELECT
Category, SUM
(Money) AS
Money
FROM
Ads
GROUP BY
Category

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

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

SELECT
Category, Part, MAX
(Units) AS
Maximum
FROM
Ads
GROUP BY
Category

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

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

Реляционные базы данных и язык SQL

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

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN
и MAX

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

Пример.

Найти минимальную и максимальную цену на персональные компьютеры:

Пример.

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

Пример.

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

Пример.

Найти количество имеющихся различных моделей, выпускаемых производителем А.
Запрос похож на предыдущий, в котором требовалось определить общее число моделей,
выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC
(т. е. имеющихся в продаже).

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

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

производителем, то потребуется использовать предложение GROUP BY
, синтаксически
следующего после предложения WHERE
.

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

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

В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все
строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT
вычисляются количество значений и средние значения цены для каждой группы. Результатом
выполнения запроса будет следующая таблица:

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

Существует несколько определенных правил выполнения агрегатных функций
:

• Если в результате выполнения запроса не получено ни одной строки
  (или не одной строки
  для данной группы), то исходные данные для вычисления любой из агрегатных функций
  отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а
  результатом всех других функций — NULL.
• Аргумент
  агрегатной функции не может сам содержать агрегатные функции
  (функция от
  функции). Т.е. в одном запросе нельзя, скажем, получить максимум средних значений.
• Результат выполнения функции COUNT есть целое число
  (INTEGER). Другие агрегатные
  функции наследуют типы данных обрабатываемых значений.
• Если при выполнении функции SUM был получен результат, превышающий максимальное
  значение используемого типа данных, возникает ошибка
  .

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

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

Шаги этого этапа изучения SQL запросов призваны продемонстрировать тот факт, что SQL умеет не только делать сложные выборки и сортировать данные, но и вычислять результаты математических функций, выполнять преобразование текста, группировать записи и т.п. Точнее все это умеет не SQL, а , его поддерживающие. SQL своими стандартами только формулирует требования к этим самым СУБД.

Шаг 15. Функции SUM, AVG, MIN, MAX, COUNT…

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

SELECT AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНИЙ СТАЖ СОТРУДНИКОВ] FROM D_STAFF

SQL функция AVG.

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

Перечисленные выше функции для определения своего значения используют результат запроса целиком. Такие функции называются агрегатными

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

SELECT S_NAME, LEN(D_STAFF.S_NAME) AS [ДЛИНА] FROM D_STAFF

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

SELECT MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

SQL функция MAX.

Ну и в заключении все вместе.

SELECT
SUM(D_STAFF.S_EXPERIENCE) AS [СУММА],
AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНЕЕ],
MIN(D_STAFF.S_EXPERIENCE) AS [МИНИМУМ],
MAX(D_STAFF.S_EXPERIENCE) AS [МАКСИМУМ],
COUNT(*) AS [КОЛИЧЕСТВО ЗАПИСЕЙ],
MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА]
FROM D_STAFF

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

Обратите внимание на аргумент функции COUNT. Я указал в качестве аргумента (*), поскольку хочу получиться именно общее число записей. Если указать, например COUNT(S_NAME), то результатом будет число непустых значений S_NAME (S_NAME IS NOT NULL). Можно было бы написать COUNT(DISTINCT S_NAME) и получить количество уникальных значений S_NAME, но MS Access такой вариант, к сожалению, не поддерживает. В нашем примере COUNT(S_NAME) и COUNT(*) дают абсолютно одинаковый результат.

Шаг 16. Преобразование текста

Часто, текстовые значения заполняются пользователями программного обеспечения по-разному: кто пишет Ф.И.О. с заглавной буквы, кто нет; кто-то пишет все заглавными буквами. Многие отчетные формы требуют унифицированного подхода, да и не только отчетные формы. Для решения этой задачи в SQL есть две функции UCASE и LCASE. Пример запроса и результат его обработки приведены ниже:

SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF

SQL функции UCASE и LCASE.

Шаг 17. SQL и работа со строками

Есть еще такая замечательная функция MID, которая поможет вам решить задачу выделения части строки из всего значения текстового поля. Здесь также лучшим комментарием будет пример – пример «издевательств» над наименованиями профилей пользователей.

SELECT UCASE(MID(P_NAME,3,5)) FROM D_PROFILE

Суперпозиция SQL функций UCASE и MID.

Мы “вырезали” из значений наименований профилей по 5 символов, начиная с 3-го, и получили кучу повторяющегося “мусора”. Для того чтобы оставить только уникальные значения будем использовать ключевое слово DISTINCT.

SELECT DISTINCT UCASE(MID(P_NAME,3,5)) AS FROM D_PROFILE

Выбор уникальных значений агрегатной функции.

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

SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE

Использование SQL функции LEN.

Шаг 18. Использование SQL функций в критерии отбора записей. Оператор HAVING

Разобравшись с функциями, практически сразу возникает вопрос, как их можно использовать в критериях отбора записей? Некоторые функции, а именно те, которые не являются агрегатными — использовать достаточно легко. Вот, например, список сотрудников, чье Ф.И.О. более 25 символов.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

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

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

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

Для таких случаев в SQL ввели ключевое слово HAVING, которое поможет нам решить проблему с должностями и сотрудниками.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION HAVING COUNT(S_POSITION)>1

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

Шаг 19. Группировка данных в результатах SQL запроса оператором GROUP BY

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

SELECT S_POSITION FROM D_STAFF

А это два варианта, позволяющие вывести только уникальные значения S_POSITION.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION

SELECT DISTINCT S_POSITION FROM D_STAFF

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

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ] FROM D_STAFF_PROFILE GROUP BY PROFILE_ID

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

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

SELECT
S.S_POSITION AS ,
S.S_NAME AS [СОТРУДНИК],
COUNT(SP.STAFF_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ В ТАБЛИЦЕ D_STAFF_PROFILE]
FROM D_STAFF S, D_STAFF_PROFILE SP
WHERE S.XD_IID=SP.STAFF_ID
GROUP BY S.S_POSITION, S.S_NAME

Группировка строк результата SQL запроса по нескольким полям.

Оконные функции – то, что должен знать каждый T-SQL программист. Часть 2.

9 декабря 2018 г.

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

Напомню, окно – это набор строк, по которым производится вычисление функции. Инструкция OVER разбивает весь набор строк на отдельные группы – окна согласно заданному условию.

Поговорим о типах оконных функций. Выделяют три группы по назначению:

• Агрегатные функции: SUM(), MAX(), MIN(), AVG(). COUNT(). Эти функции возвращают значение, полученное путем арифметических вычислений;
• Функции ранжирования: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(). Позволяют получить порядковые номера записей в окне;
• Функции смещения: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Возвращают значение из другой строки окна.

Для демонстрации работы функций я буду использовать простую таблицу:

1
2
3
4
5
6
7

CREATE TABLE ForWindowFunc (ID INT, GroupId INT, Amount INT)
GO
 
INSERT INTO ForWindowFunc (ID, GroupId, Amount)
VALUES(1, 1, 100), (1, 1, 200), (1, 2, 150),
 (2, 1, 100), (2, 1, 300), (2, 2, 200), (2, 2, 50),
 (3, 1, 150), (3, 2, 200), (3, 2, 10);

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

SUM()

Функция SUM() работает также как и обычная агрегатная функция – суммирует все значения заданного столбца в наборе данных. Однако, благодаря инструкции OVER() мы разбиваем набор данных на окна. Суммирование производится внутри окон согласно порядку, заданному в предложении ORDER BY. Давайте посмотрим на простой пример — сумма по трем группам.

1
2
3

SELECT ID,
 Amount,
 SUM(Amount) OVER (ORDER BY id) AS SUM FROM ForWindowFunc

Для удобства окна выделены разным цветом. Все значения в окне имеют одинаковую сумму – сумму всех Amount в окне.

Давайте добавим еще один столбец в выборку и изменим инструкцию OVER:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
SUM(Amount) OVER (Partition BY id ORDER BY id, GroupId) AS SUM
FROM ForWindowFunc

Как видите, теперь каждое окно разделено на группы благодаря полю GroupId. Каждая группа теперь имеет свою сумму.

А теперь, сделаем нарастающий итог внутри каждого окна:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
SUM(Amount) OVER (Partition BY id ORDER BY id, GroupId, Amount) AS SUM
FROM ForWindowFunc

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

AVG()

Эта функция рассчитывает среднее значение. Ее можно применять с предложениями Partition by и Order by.

1
2
3
4

SELECT ID,
Amount,
AVG(Amount) OVER (Partition BY id ORDER BY id) AS AVG
FROM ForWindowFunc

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

Поведение этой функции похоже на SUM().

MIN()

Из названия функции понятно, что она возвращает минимальное значение в окне.

1
2
3
4

SELECT ID,
Amount,
MIN(Amount) OVER (Partition BY id ORDER BY id) AS MIN
FROM ForWindowFunc

Как вы видите, в столбце Min, выводится минимальное значение Amount в окне.

MAX()

Функция MAX работает аналогично MIN, только выдает максимальное значение поля в окне:

1
2
3
4

SELECT ID,
Amount,
MAX(Amount) OVER (Partition BY id ORDER BY id) AS MAX
FROM ForWindowFunc

Все предельно понятно. В первой группе максимальный Amount – 200, во второй 300, а в третьей – 200.

COUNT()

Эта функция возвращает количество строк в окне.

1
2
3

SELECT ID,
 Amount,
 COUNT(Amount) OVER (Partition BY id ORDER BY id) AS COUNT FROM ForWindowFunc

Усложним запрос, добавим поле GroupId.

1
2
3
4
5

SELECT ID,
GroupId,
 Amount,
 COUNT(Amount) OVER (Partition BY id ORDER BY id, GroupId) AS COUNT
FROM ForWindowFunc

В этом случае интереснее. Давайте рассмотрим первое окно. Для первой и второй строки количество записей составило 2. Но для третьей строки значение уже равно 3. У нас получилось накопление количества по группам наподобие накопительной суммы.

Если же мы все-таки хотим количество в каждой группе, то GroupId нужно добавить в предложение Partition by.

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
COUNT(Amount) OVER (Partition BY id, GroupId) AS COUNT
FROM ForWindowFunc

Функции ранжирования

RANK()/DENSE_RANK()

Функция RANK() возвращает порядковый номер текущей строки в окне. Однако, есть особенность. Если в предложении Order By попадется несколько равнозначных для правила строки, то все они будут считаться текущей строкой. Таким образом функцию RANK() нужно использовать для ранжирования, а не нумерации строк. Хотя, если правильно задать Order by, то можно нумеровать и физические строки. Например:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
RANK() OVER (Partition BY id ORDER BY id, GroupId, Amount) AS RANK
FROM ForWindowFunc

А вот случай с одинаковыми строками в контексте Order by:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
RANK() OVER (Partition BY id ORDER BY id, GroupId) AS RANK
FROM ForWindowFunc

Интересно, что третья строка в первом окне имеет ранг 3, хотя предыдущие две строки отнесены к первому рангу. Не самая понятная логика. В этом случае лучше использовать DENSE_RANK().

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
DENSE_RANK() OVER (Partition BY id ORDER BY id, GroupId) AS DENSE_RANK
FROM ForWindowFunc

Вот теперь все, как и должно быть. DENSE_RANK() не пропускает ранги если предыдущий ранг содержит несколько строк.

Функции RANK() и DENSE_RANK() не требуют указания поля в скобках.

ROW_NUMBER()

Функция ROW_NUMBER () отображает номер текущей строки в окне. Как и предыдущие две функции, ROW_NUMBER () не требует указания поля в круглых скобках.

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
ROW_NUMBER() OVER (Partition BY id ORDER BY id, GroupId, Amount) AS ROW_NUMBER
FROM ForWindowFunc

В запросе мы использовали Partition by для разделения набора данных на группы. Здесь все понятно и не должно вызвать вопросов.

Если вы не используете Partition by, то получите сквозную нумерацию по всему набору данных:

1
2
3
4
5

SELECT ID,
GroupId,
Amount,
ROW_NUMBER() OVER (ORDER BY id, GroupId, Amount) AS ROW_NUMBER
FROM ForWindowFunc

Фактически отсутствие предложения Partition by говорит от том, что весь набор данных является окном.

NTILE()

Функция NTILE() позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках, а предложение ORDER BY определяет, какой столбец используется для определения группы.

К примеруЭто означает, что, если у вас есть 100 строк, и вы хотите создать 4 квартили на основе указанного поля значений, вы можете сделать это легко и посмотреть, сколько строк попадает в каждый квартиль.

Давайте посмотрим пример. В приведенном ниже запросе мы указали, что хотим создать четыре квартили на основе суммы заказа. Затем мы хотим увидеть, сколько заказов попадает в каждый квартиль.

NTILE создает группы на основе следующей формулы:

Количество строк в каждой группе = количество строк в наборе / количество указанных групп

Вот наш пример: в запросе указано всего 10 строк и 4 плитки, поэтому количество строк в каждой плите будет 2,5 (10/4). Поскольку число строк должно быть целым числом, а не десятичным. SQL engine назначит 3 строки для первых двух групп и 2 строки для оставшихся двух групп.

1
2
3

SELECT Amount,
NTILE(4) OVER(ORDER BY amount) AS Ntile
FROM ForWindowFunc

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

Функции смещения

LAG() и LEAD()

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

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

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

1
2
3

SELECT id, Amount,
LAG(Amount) OVER(ORDER BY id, amount) AS Lag
FROM ForWindowFunc

В первой строке значение поля Lag навно Null потому, что для этой строки нет предыдущего значения Amount. Для всех последующих строк, берется значение Amount из предыдущей строки.

Функция LEAD работает аналогично, только в другую сторону – берет значение из следующей строки.

1
2
3

SELECT id, Amount,
LEAD(Amount,2) OVER(ORDER BY id, amount) AS Lag
FROM ForWindowFunc

Как вы видите, в запросе мы передаем в функцию LEAD параметр 2. Это значит, что мы получаем второе от текущего значение Amount. Для последних двух строк значение Null, т.к. для них нет следующих значений.

FIRST_VALUE() и LAST_VALUE()

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

1
2
3

SELECT id, Amount,
FIRST_VALUE(Amount) OVER(Partition BY Id ORDER BY Id, amount) AS FIRST
FROM ForWindowFunc

Здесь мы получили первое значение по каждому окну.

А теперь получим первое значение по всему набору данных:

1
2
3

SELECT id, Amount,
FIRST_VALUE(Amount) OVER(ORDER BY Id, amount) AS FIRST
FROM ForWindowFunc

Мы убрали предложение Partition из запроса, тем самым мы определили весь набор данных окном.

А теперь посмотрим на работу функции LAST_VALUE:

1
2
3

SELECT id, Amount,
LAST_VALUE(Amount) OVER(ORDER BY id) AS LAST
FROM ForWindowFunc

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

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

Выберите max, min, последнюю строку для каждой группы в SQL без подзапроса

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

Одно из самых популярных применений для подвыборов в SQL – это когда нужно выбрать первую, последнюю, максимальную или минимальную строку для каждой группы в таблице.  Например, как бы вы реализовали SQL-запрос, который должен получать сотрудников с максимальной зарплатой для каждого отдела из таблицы сотрудников? На самом деле, получение самой зарплаты довольно просто, но это становится более сложным, когда вы хотите получить имя сотрудника (данные строки) вместе с максимальной зарплатой.

Давайте посмотрим на таблицу:

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

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

Теперь давайте рассмотрим меньшие части этого требования:
Найти всех сотрудников:

select * from employees

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

1. присоединиться к таблице слева, используя поле роли.
2. добавить условие, чтобы заработная плата была самой высокой.

SELECT
empl1.*, empl2.salary
FROM
employees AS empl1
LEFT OUTER JOIN
employees AS empl2 ON empl2.role = empl1.role
AND empl2.salary > empl1.salary

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

Поэтому следующим простым шагом будет просто отфильтровать все остальные строки, добавив еще одно условие:

SELECT
empl1. *, empl2.salary
FROM
employees AS empl1
LEFT OUTER JOIN
employees AS empl2 ON empl2.role = empl1.role
AND empl2.salary > empl1.salary
WHERE
empl2.salary IS NULL;

И конечный результат:

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

SQL SELECT MIN, MAX Функции

Автор: Чайтанья Сингх | Сохранено в: SQL

Функции SQL MIN () и MAX () используются для нахождения наименьшего и наибольшего значений столбца соответственно.

МИН (имя_столбца): Возвращает наименьшее значение столбца.
MAX (имя_столбца): Возвращает наибольшее значение столбца.

Синтаксис функции MIN ()

 ВЫБРАТЬ МИН (имя_столбца)
FROM table_name
ГДЕ условие; 

Синтаксис функции MAX ()

 SELECT MAX (имя_столбца)
FROM table_name
ГДЕ условие; 

SQL MIN () и MAX () для определенного столбца

Стол: Заказы

 ORDER_NO ORDER_DATE CUSTOMER_ID ВЫПЛАЧЕННАЯ СУММА
-------- ---------- ----------- ------ ------
100123 10-НОЯ-18 70010 5000 2000
100223 15-AUG-18 70099 7000 4000
100544 17-ИЮЛ-18 70013 4000 1000
100700 20-ЯНВ-18 70101 9000 1000
100928 24-дек-18 70745 18000 11000
 

Найдите минимальную сумму заказа в таблице Заказы:

 ВЫБРАТЬ МИН. (СУММА)
ОТ Заказы; 

Выход:

 МИН (СУММА)
-----------
    4000
 

Найдите максимальную стоимость заказа из таблицы Заказы:

 ВЫБРАТЬ МАКС.  (СУММА)
ОТ Заказы; 

Выход:

 МАКС. (СУММА)
-----------
    18000
 

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

 ВЫБРАТЬ МАКС. (СУММА) КАК LargestOrder
ОТ Заказы; 

Выход:

 Крупнейший заказ
-----------
    18000
 

SQL MIN () и MAX () со сложением и вычитанием двух столбцов

Стол: Заказы

 ORDER_NO ORDER_DATE CUSTOMER_ID ВЫПЛАЧЕННАЯ СУММА
-------- ---------- ----------- ------ ------
100123 10-НОЯ-18 70010 5000 2000
100223 15-AUG-18 70099 7000 4000
100544 17-ИЮЛ-18 70013 4000 1000
100700 20-ЯНВ-18 70101 9000 1000
100928 24-дек-18 70745 18000 11000
 

Найдите наименьшее значение (AMOUNT-PAID) из таблицы «Заказы»:

 ВЫБЕРИТЕ МИН. (СУММА - ОПЛАЧЕНО) КАК MIN_DUE
ОТ Заказы; 

Выход:

 MIN_DUE
--------
3000 

Найдите наибольшее значение (AMOUNT-PAID) из таблицы «Заказы»:

 ВЫБРАТЬ МАКС.  (СУММА - ОПЛАЧЕНО) КАК MAX_DUE
ОТ Заказы; 

Выход:

 MAX_DUE
--------
8000 

SQL SELECT MIN () и MAX () для значения даты

Стол: Заказы

 ORDER_NO ORDER_DATE CUSTOMER_ID ВЫПЛАЧЕННАЯ СУММА
-------- ---------- ----------- ------ ------
100123 10-НОЯ-18 70010 5000 2000
100223 15-AUG-18 70099 7000 4000
100544 17-ИЮЛ-18 70013 4000 1000
100700 20-ЯНВ-18 70101 9000 1000
100928 24-дек-18 70745 18000 11000
 

Найти самую старую дату заказа:

 ВЫБЕРИТЕ МИН (ORDER_DATE) как «дату самого старого заказа»
ОТ заказов; 

Выход:

 Дата самого раннего заказа
-----------------
20-ЯНВ-18 

Найти дату последнего заказа:

 ВЫБРАТЬ МАКС. (ORDER_DATE) КАК «Последняя дата заказа»
ОТ заказов; 

Выход:

 Последняя дата заказа
-----------------
24-дек-18 

Агрегатная функция SQL Server MAX ()

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

В этой статье мы рассмотрим использование агрегатной функции MAX () и предоставим несколько примеров MAX ().
использование функции.

Использование

Агрегатная функция SQL MAX () используется для возврата максимального значения из предоставленного числового выражения или
максимальное значение в последовательности сортировки из предоставленного символьного выражения.

Синтаксис

Синтаксис T-SQL для агрегатной функции MAX () выглядит так:

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

Выражение здесь может быть передано в агрегатную функцию MAX () может быть любым числом, символом,
столбец таблицы uniqueidentifier или DateTime, учитывая, что тип данных BIT не может использоваться с MAX ()
агрегатная функция.

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

Значение, возвращаемое агрегатной функцией SQL MAX (), будет таким же, как выражение Expression .
тип данных, и возвращаемое значение будет NULL, если во входном выражении нет строк для чтения.

Примеры агрегатной функции SQL MAX ()

Для демонстрации агрегатной функции SQL MAX () давайте создадим новую тестовую таблицу, содержащую столбцы с
различные типы данных, такие как INT, DECIMAL, NVARCHAR, DATETIME и BIT, с использованием CREATE TABLE T-SQL
заявление ниже:

Как только таблица будет создана, мы заполним эту таблицу данными тестирования из AdventureWorks2017 Microsoft.
тестирование таблиц базы данных с помощью приведенного ниже сценария T-SQL:

Теперь тестовая среда готова с таблицей из 316 записей.

Чтобы получить максимальное значение столбца идентификатора, в котором отображается последний добавленный сотрудник, и использовать его для дальнейшего
анализа или операций, мы будем использовать агрегатную функцию SQL MAX () в операторе SELECT, как в T-SQL
заявление ниже:

И возвращаемое значение в нашей демонстрации здесь будет 316, что является последним добавленным идентификатором сотрудника, как показано ниже:

Другой сценарий, где нам нужно получить имя сотрудника, который находится в конце алфавитного порядка. Этого можно добиться, применив агрегатную функцию MAX () к символьному столбцу EmpName, где MAX ()
функция отсортирует значения столбца EmpName на основе сопоставления столбцов и вернет последнее значение, как в
Инструкция T-SQL ниже:

В нашем случае оператор SELECT с агрегатной функцией SQL MAX () вернет последнего сотрудника после
сортировка имен сотрудников.Вы можете представить это как сортировку имен по убыванию, а затем получение имени первого сотрудника,
как показано ниже:

Агрегатную функцию SQL MAX () также можно использовать с типом данных DateTime, где она будет сортировать DateTime
values ​​и вернуть последнее значение из отсортированных результатов. В нашем сценарии нам нужно получить самого молодого сотрудника с
ближайшая дата рождения. Для этого мы передадим столбец EmpDateOfBirth в агрегатную функцию MAX (), как
в инструкции T-SQL SELECT ниже:

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

Обновление шкалы заработной платы сотрудников можно проверить с помощью различных видов расчетов и агрегирования.
операции, включая агрегатную функцию SQL MAX (). Это можно сделать, передав столбец EmpSalary в MAX ()
функция, которая отсортирует значения заработной платы всех сотрудников и вернет наибольшее значение из столбца, как в инструкции T-SQL Select ниже:

Выполнение предыдущего оператора SELECT с агрегатной функцией MAX () будет аналогично возвращенному результату
от сортировки значений зарплат сотрудников по убыванию и возврата первой зарплаты с наибольшим значением, как показано
ниже:

Если мы попытаемся применить агрегатную функцию SQL MAX () к столбцу EmpIsActive с типом данных BIT, как в
Инструкция T-SQL SELECT ниже:

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

Агрегатную функцию SQL MAX () также можно использовать в предложении WHERE простого запроса SELECT. Это может быть
достигается путем сравнения определенного значения столбца с результатом, возвращенным подзапросом, который вернул максимальное
значение этого столбца, как показано в запросе T-SQL SELECT ниже:

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

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

И результат, который можно легко сравнить вместе, будет следующим:

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

И результат выполнения предыдущего запроса будет таким:

Давайте добавим новый столбец в демонстрационную таблицу без заполнения какого-либо значения в этом столбце, используя ALTER TABLE T-SQL
заявление ниже:

После добавления нового столбца, если мы попытаемся выполнить приведенный ниже запрос, чтобы получить максимальное значение допуска от всей компании
сотрудники, использующие агрегатную функцию SQL MAX ():

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

Заключение

Из приведенных выше примеров ясно, как мы можем использовать агрегатную функцию SQL MAX () для достижения конкретной цели в
разные сценарии, с разными типами данных и в любом запросе T-SQL.

Ахмад Ясин (Ahmad Yaseen) — инженер Microsoft по большим данным с глубокими знаниями и опытом в областях SQL BI, администрирования баз данных SQL Server и разработки.

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

Кроме того, он вносит свои советы по SQL во многие блоги.

Посмотреть все сообщения от Ahmad Yaseen

Последние сообщения от Ahmad Yaseen (посмотреть все)

7.Изучите SQL AVG / MIN / MAX

На этот раз мы рассмотрим некоторые запросы, которые помогают управлять числовыми данными, рассмотренными в предыдущем руководстве. Мы будем использовать AVG / MIN / MAX , чтобы увидеть среднее, минимальное и максимальное значения.

🆕 🌟
📌 AVG: найти среднее значение числового столбца
📌 MAX: найти наибольшее значение в выбранном столбце
📌 MIN: найти наименьшее значение в выбранном столбце

#Table содержания

Запрос 1.Нахождение средней длины пленки в таблице пленок

  ВЫБРАТЬ СРЕДНЮЮ (длину) ОТ пленки;
ВЫБЕРИТЕ СРЕДНЕЕ (длина) КАК среднее значение, СУММ (длина) / КОЛИЧЕСТВО (длина) КАК "сумма / количество" ОТ фильма;  

Запрос 2. Нахождение минимального / максимального значения столбца «сумма» в таблице платежей

  ВЫБРАТЬ МИН (сумма) КАК Минимум, МАКС (сумма) КАК Максимум ИЗ платежа;  

# AVG

Q1. Определение средней длины пленки в таблице фильмов

SELECT AVG (имя столбца)

Мы нашли общую длину всех фильмов в предыдущем уроке.Теперь мы собираемся найти среднее значение длины пленки, используя AVG . Он имеет тот же шаблон, что и COUNT , поэтому вы можете написать AVG после SELECT и имени столбца в круглых скобках. Мы также собираемся переименовать столбцы, чтобы навести порядок, например:

  ВЫБРАТЬ СРЕДНЕЕ (длина) В среднем ОТ пленки;  

💡Это даст вам значение 115,2720.

Мы также можем получить среднее значение, используя запросы SUM и COUNT , которые мы изучили в прошлый раз. Поскольку среднее — это значение, которое делит общую сумму на количество, мы можем написать наш запрос следующим образом:

  ВЫБРАТЬ СУММ (длина) / СЧЁТ (длина) КАК «сумма / количество» ОТ фильма;  

💡 Появится то же значение 115,2720!

Давайте сделаем еще один шаг и поместим оба оператора в один и тот же запрос для сравнения:

  ВЫБРАТЬ СРЕДНЕЕ (длина) КАК среднее, СУММ (длина) / КОЛИЧЕСТВО (длина) КАК «сумма / счет» ОТ фильма;  

Вот и все, два разных способа получить среднее значение!

# MIN / MAX

Q2.Нахождение минимального / максимального значения столбца «сумма» в таблице платежей

SELECT MIN (имя столбца) SELECT MAX (имя столбца)

Давайте проверим таблицу платежей, чтобы найти минимальные / максимальные значения.
Мы используем эту таблицу впервые, поэтому взгляните на то, что находится внутри столбцов, как мы делали в предыдущем руководстве: (payment_id, customer_id, staff_id, rental_id, amount, payment_date, last_update)

🔑 Вы можете использовать ВЫБРАТЬ * ИЗ ОПЛАТЫ; для быстрого просмотра.

Здесь вы увидите значения 2,99, 0,99 и 5,99 в столбце суммы. Давайте использовать MIN и MAX , чтобы найти минимальное и максимальное значения. Напишите MIN или MAX после SELECT и имени столбца в круглых скобках. Как это:

⏬ ВЫБЕРИТЕ МИН (сумму) ОТ платежа;
⏫ ВЫБРАТЬ МАКС. (Сумма) ОТ платежа;

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

  SELECT MIN (количество) AS Minimum, MAX (количество ) КАК Максимум ОТ платежа;  

Теперь, когда мы узнали, как использовать AVG , MIN и MAX , давайте рассмотрим примерный вопрос.

# Время практики

📰 На этот раз у нас есть легкий вариант для вас. Можете ли вы найти среднее, минимальное значение
и максимальное значение replacement_cost в таблице Film?

👍 Попробуйте сами, прежде чем проверять ответ ниже ~

Получили ли вы значения 9,99 для минимума и 29,99 для максимального? Как насчет среднего? Прокрутите вниз, чтобы проверить предоставленный нами ответ!