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

Содержание

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

  • Чтение занимает 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 — о денежных суммах, вырученных за подачу объявлений.

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

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

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, можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price
FROM PC
GROUP BY model;

В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средние значения цены для каждой группы. Результатом выполнения запроса будет следующая таблица:
model Qty_model Avg_price
1121 3 850.0
1232 4 425.0
1233 3 843.33333333333337
1260 1 350.0

Если бы в 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.

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

 

ID

Amount

Sum

1

100

450

1

200

450

1

150

450

2

100

650

2

300

650

2

200

650

2

50

650

3

150

360

3

200

360

3

10

360

Для удобства окна выделены разным цветом. Все значения в окне имеют одинаковую сумму – сумму всех 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

 

ID

GroupId

Amount

Sum

1

1

100

300

1

1

200

300

1

2

150

450

2

1

100

400

2

1

300

400

2

2

200

650

2

2

50

650

3

1

150

150

3

2

200

360

3

2

10

360

Как видите, теперь каждое окно разделено на группы благодаря полю 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

 

ID

Amount

Sum

1

100

100

1

200

300

1

150

450

2

100

100

2

300

400

2

50

450

2

200

650

3

150

150

3

10

160

3

200

360

Поле 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

 

ID

Amount

AVG

1

100

150

1

200

150

1

150

150

2

100

162

2

300

162

2

200

162

2

50

162

3

150

120

3

200

120

3

10

120

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

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

MIN()

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

1
2
3
4
SELECT ID,
Amount,
MIN(Amount) OVER (Partition BY id ORDER BY id) AS MIN
FROM ForWindowFunc

 

ID

Amount

Min

1

100

100

1

200

100

1

150

100

2

100

50

2

300

50

2

200

50

2

50

50

3

150

10

3

200

10

3

10

10

Как вы видите, в столбце 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

 

ID

Amount

Max

1

100

200

1

200

200

1

150

200

2

100

300

2

300

300

2

200

300

2

50

300

3

150

200

3

200

200

3

10

200

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

 

COUNT()

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

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

 

ID

Amount

Count

1

100

3

1

200

3

1

150

3

2

100

4

2

300

4

2

200

4

2

50

4

3

150

3

3

200

3

3

10

3

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

1
2
3
4
5
SELECT ID,
GroupId,
 Amount,
 COUNT(Amount) OVER (Partition BY id ORDER BY id, GroupId) AS COUNT
FROM ForWindowFunc

 

ID

GroupId

Amount

Count

1

1

100

2

1

1

200

2

1

2

150

3

2

1

100

2

2

1

300

2

2

2

200

4

2

2

50

4

3

1

150

1

3

2

200

3

3

2

10

3

В этом случае интереснее. Давайте рассмотрим первое окно. Для первой и второй строки количество записей составило 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

 

ID

GroupId

Amount

Count

1

1

100

2

1

1

200

2

1

2

150

1

2

1

100

2

2

1

300

2

2

2

200

2

2

2

50

2

3

1

150

1

3

2

200

2

3

2

10

2

 

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

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

 

ID

GroupId

Amount

RANK

1

1

100

1

1

1

200

2

1

2

150

3

2

1

100

1

2

1

300

2

2

2

50

3

2

2

200

4

3

1

150

1

3

2

10

2

3

2

200

3

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

 

1
2
3
4
5
SELECT ID,
GroupId,
Amount,
RANK() OVER (Partition BY id ORDER BY id, GroupId) AS RANK
FROM ForWindowFunc

 

ID

GroupId

Amount

RANK

1

1

100

1

1

1

200

1

1

2

150

3

2

1

100

1

2

1

300

1

2

2

200

3

2

2

50

3

3

1

150

1

3

2

200

2

3

2

10

2

Интересно, что третья строка в первом окне имеет ранг 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

 

ID

GroupId

Amount

DENSE_RANK

1

1

100

1

1

1

200

1

1

2

150

2

2

1

100

1

2

1

300

1

2

2

200

2

2

2

50

2

3

1

150

1

3

2

200

2

3

2

10

2

Вот теперь все, как и должно быть. 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

 

ID

GroupId

Amount

ROW_NUMBER

1

1

100

1

1

1

200

2

1

2

150

3

2

1

100

1

2

1

300

2

2

2

50

3

2

2

200

4

3

1

150

1

3

2

10

2

3

2

200

3

В запросе мы использовали 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

 

ID

GroupId

Amount

ROW_NUMBER

1

1

100

1

1

1

200

2

1

2

150

3

2

1

100

4

2

1

300

5

2

2

50

6

2

2

200

7

3

1

150

8

3

2

10

9

3

2

200

10

Фактически отсутствие предложения 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

Ntile

10

1

50

1

100

1

100

2

150

2

150

2

200

3

200

3

200

4

300

4

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

 

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

LAG() и LEAD()

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

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

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

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

 

id

Amount

Lag

1

100

NULL

1

150

100

1

200

150

2

50

200

2

100

50

2

200

100

2

300

200

3

10

300

3

150

10

3

200

150

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

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

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

 

id

Amount

Lag

1

100

200

1

150

50

1

200

100

2

50

200

2

100

300

2

200

10

2

300

150

3

10

200

3

150

NULL

3

200

NULL

Как вы видите, в запросе мы передаем в функцию 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

 

id

Amount

First

1

100

100

1

150

100

1

200

100

2

50

50

2

100

50

2

200

50

2

300

50

3

10

10

3

150

10

3

200

10

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

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

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

 

id

Amount

First

1

100

100

1

150

100

1

200

100

2

50

100

2

100

100

2

200

100

2

300

100

3

10

100

3

150

100

3

200

100

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

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

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

 

id

Amount

Last

1

100

150

1

200

150

1

150

150

2

100

50

2

300

50

2

200

50

2

50

50

3

150

10

3

200

10

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

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

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

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

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

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

Name Salary Role
David 130,000 Software Engineer
AndreyEx 175,000 DevOps Engineer
Marina 120,000 Software Engineer
Anton 130,000 DevOps Engineer
Alice 110,000 Software Engineer
Maxim 95,000 DevOps Engineer

 

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

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

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

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 для объединенного столбца окладов.

Name Salary Role Salary (joint)
David 130,000 Software Engineer NULL
AndreyEx 175,000 DevOps Engineer NULL
Marina 120,000 Software Engineer 130,000
Anton 130,000 DevOps Engineer 175,000
Alice 110,000 Software Engineer 120,000
Alice 110,000 Software Engineer 130,000
Maxim 95,000 DevOps Engineer 130,000
Maxim 95,000 DevOps Engineer 175,000

 

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

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;

 

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

David 130,000 Software Engineer NULL
AndreyEx 175,000 DevOps Engineer 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 () выглядит так:

MAX (выражение [ALL | DISTINCT])

Где опция 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 заявление ниже:

Активный бит)

CREATE TABLE MAXDemo

(ID INT IDENTITY PRIMARY KEY,

EmpName NVARCHAR (100),

EmpDateOfBirth datetime,

EmpSalary Decimal (6,2),

EmpIs

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

ВСТАВИТЬ В MAXDemo

ВЫБРАТЬ E.[FName] + E. [SeName] + E. [ThName] + E. [LName] AS EmpName,

E.BirthDate AS EmpDateOfBirth,

EP.Rate AS EmpSalary,

E.SalariedFlag AS EmpIsActive

FROM [HumanResources]. [Сотрудник] E

JOIN [HumanResources]. [EmployeePayHistory] EP

ON E.BusinessEntityID = EP.BusinessEntityID

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

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

ВЫБРАТЬ MAX (ID) AS MaxID

ИЗ MAXDemo

GO

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

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

ВЫБРАТЬ MAX (EmpName) AS LastEmp

ИЗ MAXDemo

GO

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

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

ВЫБРАТЬ MAX (EmpDateOfBirth) AS YoungestEmp

ИЗ MAXDemo

GO

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

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

ВЫБРАТЬ MAX (EmpSalary) AS LargestSal

ИЗ MAXDemo

GO

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

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

ВЫБРАТЬ MAX (EmpIsActive) AS MaxActive

ИЗ MAXDemo

GO

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

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

ВЫБРАТЬ * ИЗ MAXDemo

ГДЕ EmpSalary = (ВЫБРАТЬ MAX (EmpSalary) FROM MAXDemo)

И EmpIsActive = 1

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

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

ВЫБРАТЬ ТОП 10 ID, EmpName, EmpDateOfBirth,

YoungEmp = (ВЫБРАТЬ МАКС. (EmpDateOfBirth) ИЗ MAXDemo), EmpSalary,

LargeSalary = (ВЫБРАТЬ МАКСИМАЛЬНУЮ (EmpSalary) FROM MAXDemo 1 =

ОТ MAXDemo)

ОТ MAXDemo

ГРУППА ПО ID, EmpName, EmpDateOfBirth, EmpSalary

ЗАКАЗАТЬ ПО EmpSalary DESC

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

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

ВЫБРАТЬ ТОП 10 ID, EmpName, EmpDateOfBirth,

YoungEmp = (ВЫБРАТЬ МАКС. (EmpDateOfBirth) ИЗ MAXDemo), EmpSalary,

LargeSalary = (ВЫБРАТЬ МАКСИМАЛЬНУЮ (EmpSalary) FROM MAXDemo 1 =

ОТ MAXDemo)

ОТ MAXDemo

ГРУППА ПО ID, EmpName, EmpDateOfBirth, EmpSalary

HAVING ((SELECT MAX (EmpSalary) FROM MAXDemo) — EmpSalary)> 100

ORDER BY EmpSalary DESC

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

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

ALTER TABLE MAXDemo ADD AllowanceValue INT NULL

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

ВЫБРАТЬ МАКС. (Значение допуска) как макс. Допустимое значение ИЗ MAXDemo

Возвращаемый результат будет 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 для максимального? Как насчет среднего? Прокрутите вниз, чтобы проверить предоставленный нами ответ!

  ВЫБРАТЬ СРЕДНЕЕ (стоимость_замены), МИН (стоимость_замены), МАКС (стоимость_замены) ОТ пленки;  

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

  ВЫБРАТЬ СРЕДНЮЮ (стоимость_замены) КАК Средняя_Стоимость, МИН (стоимость_замены) КАК Минимальная_Стоимость, МАКС (стоимость_замещения) КАК Максимальная_Стоимость ОТ пленки;  

Функция SQL MIN () — w3resource

MIN () функция

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

Синтаксис:

 MIN ([ALL | DISTINCT] выражение)
 

MySQL, PostgreSQL и SQL Server

Все вышеперечисленные платформы поддерживают синтаксис SQL MIN.

Синтаксис DB2 и Oracle:

 MIN ([ALL | DISTINCT] выражение) OVER (window_clause)
 

Параметры:

Имя Описание
ВСЕ Применяется ко всем значениям.
DISTINCT Рассмотрим каждое уникальное значение. DISTINCT не имеет смысла с функцией MIN.
выражение Выражение, состоящее из одной константы, переменной, скалярной функции или имени столбца или любой комбинации арифметических, побитовых и строковых операторов. MIN можно использовать с числовыми, символьными столбцами и столбцами типа datetime, но не с битовыми столбцами. Агрегатные функции и подзапросы не допускаются.

Синтаксическая диаграмма — функция MIN ()


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

Образец таблицы: заказы

Чтобы получить минимальное или минимальное значение ‘ord_amout’ из таблицы заказов, можно использовать следующий оператор SQL:

  ВЫБРАТЬ МИН (количество_орд)
ОТ заказов;  

Выход:

 МИН (ORD_AMOUNT)
---------------
            500
 

ВЫБРАТЬ МИН. (Open_amt + receive_amt) ОТ заказчика;

Выход:

 МИН (OPENING_AMT + RECEIVE_AMT)
----------------------------
                        8000
 

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

Образец таблицы: заказы

Чтобы получить минимальное или наименьшее значение ‘ord_date’ из таблицы ‘orders’, можно использовать следующий оператор SQL:

  ВЫБРАТЬ МИН. (Порядковая_дата) КАК «Мин. Дата»
ОТ заказов;
  

Выход:

 Мин. Дата
---------
08-ЯНВ-08
 

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

См. Нашу базу данных моделей

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

Практические упражнения по SQL

Хотите улучшить статью выше? Публикуйте свои заметки / комментарии / примеры через Disqus.

Назад: Макс. Количество, имея
Далее: Мин. Группировать по, упорядочивать по

MAX (Transact-SQL) — SQL Server

  • 2 минуты на чтение

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL AzureAzure SQL Managed InstanceAzure Synapse Analytics Параллельное хранилище данных

Возвращает максимальное значение в выражении.

Соглашения о синтаксисе Transact-SQL

Синтаксис

  - Синтаксис функции агрегирования
MAX ([ALL | DISTINCT] выражение)
  
- Синтаксис аналитических функций
MAX ([ALL] выражение) OVER ([] [])
  

Аргументы

ВСЕ
Применяет агрегатную функцию ко всем значениям. ВСЕ по умолчанию.

DISTINCT
Указывает, что учитывается каждое уникальное значение.DISTINCT не имеет смысла с MAX и доступен только для совместимости с ISO.

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

Для получения дополнительной информации см. Выражения (Transact-SQL).

OVER ( [ partition_by_clause ] order_by_clause )
partition_by_clause делит набор результатов, созданный предложением FROM, на разделы, к которым применяется функция. Если не указано, функция обрабатывает все строки набора результатов запроса как одну группу. order_by_clause определяет логический порядок, в котором выполняется операция. order_by_clause является обязательным. Дополнительные сведения см. В разделе Предложение OVER (Transact-SQL).

Типы возврата

Возвращает значение, такое же как выражение .

Замечания

MAX игнорирует любые нулевые значения.

MAX возвращает NULL, если нет строки для выбора.

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

MAX является детерминированной функцией при использовании без предложений OVER и ORDER BY. Он недетерминирован, если указан в предложениях OVER и ORDER BY. Для получения дополнительной информации см. Детерминированные и недетерминированные функции.

Примеры

A. Простой пример

В следующем примере возвращается самая высокая (максимальная) ставка налога в базе данных AdventureWorks2012.

  ВЫБРАТЬ МАКС. (Ставка налога)
ОТ Sales.SalesTaxRate;
ИДТИ
  

Вот результат.

  -------------------
19,60
Предупреждение, нулевое значение исключено из агрегата.
 
(Затронута 1 строка)
  

B. Использование предложения OVER

В следующем примере используются функции MIN, MAX, AVG и COUNT с предложением OVER, чтобы предоставить агрегированные значения для каждого отдела в HumanResources.Таблица отдела в базе данных AdventureWorks2012.

  ВЫБРАТЬ ОТЛИЧНОЕ имя
       , МИН (ставка) ВЫШЕ (РАЗДЕЛЕНИЕ ПО EDH.DepartmentID) КАК МИНИМАЛЬНАЯ ЗАПЛАТА
       , МАКС. (Ставка) ПРЕВЫШАЕТ (РАЗДЕЛЕНИЕ ПО ИД.  Отдела) КАК Макс. Зарплата
       , AVG (Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
       , COUNT (edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
ОТ HumanResources.EmployeePayHistory AS eph
ПРИСОЕДИНЯЙТЕСЬ HumanResources.EmployeeDepartmentHistory AS edh
     ON eph.BusinessEntityID = edh.BusinessEntityID
ПРИСОЕДИНЯЙТЕСЬ HumanResources. Отдел AS d
 ON d.DepartmentID = edh.DepartmentID
ГДЕ edh.EndDate ЕСТЬ NULL
ЗАКАЗАТЬ ПО Имя;
  

Вот результат.

  Имя MinSalary MaxSalary AvgSalary EmployeesPerDept
----------------------------- --------------------- --------------------- --------------------- -------- --------
Документооборот 10.25 17.7885 14,3884 5
Инженерное дело 32,6923 63,4615 40,1442 6
Исполнительный 39.06 125.50 68.3034 4
Оборудование и техническое обслуживание 9,25 24,0385 13,0316 7
Финансы 13,4615 43,2692 23,935 10
Человеческие ресурсы 13,9423 27.1394 18.0248 6
Информационные услуги 27.4038 50.4808 34. 1586 10
Маркетинг 13,4615 37,50 18,4318 11
Производство 6.50 84.1346 13.5537 195
Производственный контроль 8,62 24,5192 16,7746 8
Закупка 9,86 30.00 18.0202 14
Обеспечение качества 10,5769 28,8462 15,4647 6
Исследования и разработки 40,8654 50,4808 43,6731 4
Продажи 23.0769 72.1154 29.9719 18
Отгрузка и получение 9,00 19,2308 10,8718 6
Конструкция инструмента 8,62 29.8462 23,5054 6
  
 (Затронуто 16 рядов)
  

C. Использование MAX с символьными данными

В следующем примере возвращается имя базы данных, которое отсортировано как фамилия по алфавиту. В примере используется WHERE database_id <5 , чтобы рассматривать только системные базы данных.

  ВЫБРАТЬ МАКС. (Имя) ИЗ sys.databases WHERE database_id <5;
  

Последняя системная база данных - tempdb .

См. Также

Агрегатные функции (Transact-SQL)
Предложение OVER (Transact-SQL)

Функции SQL min () и max ()

Здравствуйте! В этом руководстве мы собираемся обсудить использование и работу функций SQL min () и max () .

Итак, перейдем к теме.

Функция SQL MIN ()

Функция SQL min () используется для нахождения минимального значения / минимального значения в данном столбце. Синтаксис использования функции приведен ниже.

ВЫБРАТЬ МИН (столбец)
Из таблицы
ГДЕ условие;
 

Где, столбец и Таблица - это имя столбца и имя таблицы соответственно, для которых нам нужно найти минимальное значение. Условие WHERE используется для сужения набора значений на основе условия .

Давайте посмотрим на пример, чтобы лучше понять.

Пример : В таблице ниже приведены оценки 3 разных студентов по 3 различным предметам (математика, естественные науки и английский) из 100.

87134 9013
Roll Имя Математика Наука Английский
1 Джон 95 89 90
2 Кейт 78 90 91 Alex
3 89
Таблица - Оценки

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

ВЫБЕРИТЕ МИН (математика)
ОТ Марок;
 

Выход:

78
 

Пример - Для расчета общего минимального количества баллов, которое студент набрал из 300.

ВЫБЕРИТЕ МИН (математика + наука + английский)
ОТ Марок;
 

Выход:

259
 

Мы получаем желаемый результат, так как Roll 2 получил наименьшие общие оценки (78 + 90 + 91 = 259 ).

Функция SQL MAX ()

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

ВЫБРАТЬ МАКС. (Столбец)
Из таблицы
ГДЕ условие;
 

Аналогично функции min () , здесь также столбец и Таблица - это имя столбца и имя таблицы соответственно, для которых должно быть вычислено максимальное значение. Условие WHERE используется для сужения набора значений на основе условия .

Пример - Рассматривая ту же таблицу Marks , используемую для функции min () , давайте на этот раз попробуем вычислить максимальные баллы, которые студент набрал за English .

SELECT MAX (английский)
ОТ Марок;
 

Выход:

91
 

Пример - Для расчета максимальных оценок, которые студент набрал из 300 из по всем предметам.

ВЫБЕРИТЕ МАКС. (Математика + наука + английский)
ОТ Марок;
 

Выход:

274
 

Из таблицы «Оценки» видно, что Roll 1 имеет самые высокие общие оценки (95 + 90 + 89 = 274 ). Значит, вывод оправдан.

SQL MIN () и MAX () с псевдонимом

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

Примечание: Эти псевдонимы - временные имена, данные для удобства пользователя.

1. Псевдоним с MIN ()

Давайте посмотрим на пример.

ВЫБРАТЬ МИН. (Наука) КАК Min_Sci_Marks
ОТ Марок;
 

Вывод:

Как видите, на этот раз имя столбца изменено (временно).

2. Псевдоним с MAX ()

Аналогично для функции max () ,

ВЫБРАТЬ MAX (наука) AS Max_Sci_Marks
ОТ Марок;
 

Вывод:

Подведение итогов

Итак, на сегодня все. Надеюсь, у вас есть четкое представление о функциях SQL min () и max () . Мы рекомендуем пройти по ссылкам, приведенным в разделе ссылок для получения дополнительной информации.

Вы также можете ознакомиться с нашим учебным пособием по SQL.

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

Ссылки

Оптимизация MIN / MAX - База знаний MariaDB

Мин. / Макс. Оптимизация без GROUP BY

MariaDB и MySQL могут оптимизировать функции MIN () и MAX () для поиска одной строки в следующих случаях:

  • В SELECT используется только одна таблица.
  • У вас есть только константы MIN () и MAX () в части SELECT .
  • Аргумент MIN () и MAX () - это простая ссылка на столбец, которая является частью ключа.
  • Нет предложения WHERE или WHERE используется с константой для всех частей префикса ключа перед аргументом для MIN () / MAX () .
  • Если аргумент используется в предложении WHERE , его можно сравнить с константой с < или <= в случае MAX () и с > или > = в корпус MIN () .

Вот несколько примеров, чтобы прояснить это.В этом случае мы предполагаем, что есть индекс по столбцам (a, b, c)

 ВЫБЕРИТЕ МИН (а), МАКС (а) из t1
ВЫБРАТЬ МИН (b) ИЗ t1, ГДЕ a = const
ВЫБЕРИТЕ МИН (b), МАКС (b) ОТ t1, ГДЕ a = const
ВЫБРАТЬ МАКС. (C) ИЗ t1, ГДЕ a = const И b = const
ВЫБРАТЬ МАКС. (B) ИЗ t1, ГДЕ a = const И b  const
ВЫБЕРИТЕ MIN (b) ИЗ t1, ГДЕ a = const И b МЕЖДУ const И const
ВЫБРАТЬ МАКС (b) ИЗ t1, ГДЕ a = const И b МЕЖДУ const И const
 
  • Вместо a = const можно использовать условие IS NULL .

Вышеупомянутая оптимизация также работает для подзапросов:

 ВЫБРАТЬ x из t2, где y = (ВЫБРАТЬ МИН (b) ИЗ t1, ГДЕ a = const)
 

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

 выберите min (t1.key_part_1), max (t2. key_part_1) из t1, t2
 

Мин. / Макс. Оптимизация с GROUP BY

MariaDB и MySQL поддерживают свободное сканирование индекса, которое может ускорить определенные запросы GROUP BY .Основная идея заключается в том, что при сканировании индекса BTREE (наиболее распространенный тип индекса для механизмов хранения MariaDB) мы можем перепрыгивать через идентичные значения для любого префикса ключа и, таким образом, значительно ускорять сканирование.

Свободное сканирование возможно в следующих случаях:

  • В запросе используется только одна таблица.
  • Часть GROUP BY использует только индексированные столбцы в том же порядке, что и в индексе.
  • Единственными агрегированными функциями в части SELECT являются функции MIN () и MAX () , и все они используют один и тот же столбец, который является следующей частью индекса после используемых столбцов GROUP BY .
  • Частично проиндексированные столбцы нельзя использовать (например, индексировать только 10 символов столбца VARCHAR (20) ).

Свободное сканирование будет применяться к вашему запросу, если EXPLAIN показывает Использование индекса для группировки по в столбце Extra . В этом случае оптимизатор выполнит только одну дополнительную выборку строки для вычисления значения MIN () или MAX () для каждого уникального префикса ключа.

В следующих примерах предполагается, что таблица t1 имеет индекс (a, b, c) .

 ВЫБЕРИТЕ a, b, MIN (c), MAX (c) ИЗ t1 ГРУППЫ ПО a, b
 

См. Также

.

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

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

Theme: Overlay by Kaira Extra Text
Cape Town, South Africa