Sql max group by примеры: GROUP BY | SQL | SQL-tutorial.ru

Содержание

Sql max group by примеры

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

* COUNT — производит номера строк или не-NULL значения полей которые выбрал запрос.

* SUM — производит арифметическую сумму всех выбранных значений данного пол.

* AVG — производит усреднение всех выбранных значений данного пол.

* MAX — производит наибольшее из всех выбранных значений данного пол.

* MIN — производит наименьшее из всех выбранных значений данного пол.

КАК ИСПОЛЬЗОВАТЬ АГРЕГАТНЫЕ ФУНКЦИИ ?

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

Чтобы найти SUM всех наших покупок в таблицы Порядков, мы можем ввести следующий запрос, с его выводом в Рисунке 6.1: Рисунок 6.1: Выбор суммы

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

СПЕЦИАЛЬНЫЕ АТРИБУТЫ COUNT

ИСПОЛЬЗОВАНИЕ DISTINCT

Обратите внимание в вышеупомянутом примере, что DISTINCT, сопровождаемый именем пол с которым он применяется, помещен в круглые скобки, но не сразу после SELECT, как раньше. Этого использования DISTINCT с COUNT применяемого к индивидуальным столбцам, требует стандарт ANSI, но большое количество программ не предъявляют к ним такого требования. Рисунок 6.3: Подсчет значений пол

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

ИСПОЛЬЗОВАНИЕ COUNT СО СТРОКАМИ, А НЕ ЗНАЧЕНИЯМИ

Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звездочкой вместо имени пол, как например в следующем примере, вывод из которого показан на Рисунке 6.4: COUNT со звездочкой включает и NULL и дубликаты, по этой причине DISTINCT не может быть использован. DISTINCT может производить более высокие номера чем COUNT особого пол, который удаляет все Рисунок 6. 4: Подсчет строк вместо значений

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

ВКЛЮЧЕНИЕ ДУБЛИКАТОВ В АГРЕГАТНЫЕ ФУНКЦИИ

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

* ALL использует имя_поля как аргумент.

* ALL не может подсчитать значения NULL.

Пока * является единственным аргументом который включает NULL значения, и он используется только с COUNT; функции отличные от COUNT игнорируют значения NULL в любом случае. Следующая команда подсчитает(COUNT) число не-NULL значений в поле rating в таблице Заказчиков ( включая повторения ):

АГРЕГАТЫ ПОСТРОЕННЫЕ НА СКАЛЯРНОМ ВЫРАЖЕНИИ

ПРЕДЛОЖЕНИЕ GROUP BY

Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого пол, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять пол и агрегатные функции в едином предложении SELECT. Например, предположим что вы хотите найти наибольшую сумму приобретений полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX (amt) из таблицы Порядков для каждого значения пол snum. GROUP BY, однако, позволит Вам поместить их все в одну команду: Вывод для этого запроса показывается в Рисунке 6.5. Рисунок 6.5: Нахождение максимальной суммы продажи у каждого продавца

GROUP BY применяет агрегатные функции независимо от серий групп которые определяются с помощью значения поля в целом. В этом случае, каждая группа состоит из всех строк с тем же самым значением пол snum, и MAX функция применяется отдельно для каждой такой группы. Это значение пол, к которому применяется GROUP BY, имеет, по определению, только одно значение на группу вывода, также как это делает агрегатная функция. Результатом является совместимость которая позволяет агрегатам и полям объединяться таким образом. Вы можете также использовать GROUP BY с многочисленными полями. Совершенству вышеупомянутый пример далее, предположим что вы хотите увидеть наибольшую сумму приобретений получаемую каждым продавцом каждый день. Чтобы сделать это, вы должны сгруппировать таблицу Порядков по датам продавцов, и применить функцию MAX к каждой такой группе, подобно этому: Вывод для этого запроса показывается в Рисунке 6.6. Рисунок 6.6: Нахождение наибольшей суммы приобретений на каждый день

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

ПРЕДЛОЖЕНИЕ HAVING

Предположим, что в предыдущем примере, вы хотели бы увидеть только максимальную сумму приобретений значение которой выше $3000.00. Вы не сможете использовать агрегатную функцию в предложении WHERE ( если вы не используете подзапрос, описанный позже ), потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции оцениваются в терминах групп строк. Это означает что вы не сможете сделать что-нибудь подобно следующему: Это будет отклонением от строгой интерпретации ANSI. Чтобы увидеть максимальную стоимость приобретений свыше $3000.00, вы можете использовать предложение HAVING. Предложение HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк. Правильной командой будет следующая: Вывод для этого запроса показывается в Рисунке 6. 7. Рисунок 6. 7: Удаление групп агрегатных значений

Аргументы в предложении HAVING следуют тем же самым правилам что и в предложении SELECT, состоящей из команд использующих GROUP BY. Они должны иметь одно значение на группу вывода. Следующая команда будет запрещена: Поле оdate не может быть вызвано предложением HAVING, потому что оно может иметь ( и действительно имеет ) больше чем одно значение на группу вывода. Чтобы избегать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос( вывод показывается в Рисунке 6.8 ): Рисунок 6.8: Максимальное значение суммы приобретений у каждого продавца на 3 Октября

Поскольку пол odate нет, не может быть и выбранных полей, значение этих данных меньше чем в некоторых других примерах. Вывод должен вероятно включать что-нибудь такое что говорит — » это — самые большие порядки на 3 Октября.» В Главе 7, мы покажем как вставлять текст в ваш вывод. Как и говорилось ранее, HAVING может использовать только аргументы которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции — наиболее общие, но и пол выбранные с помощью GROUP BY также допустимы. Например, мы хотим увидеть наибольшие порядки для Serres и Rifkin: Вывод для этого запроса показывается в Рисунке 6.9. Рисунок 6. 9: Использование HAVING с GROUP BY полями

НЕ ДЕЛАЙТЕ ВЛОЖЕННЫХ АГРЕГАТОВ

РЕЗЮМЕ

РАБОТА С SQL

1. Напишите запрос который сосчитал бы все суммы приобретений на 3 Октября.

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

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

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

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

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

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

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

Оператор GROUP BY имеет следующий синтаксис:

Группировка по одному столбцу без агрегатных функций

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

В примерах работаем с базой данных библиотеки и ее таблицей «Книга в пользовании» (BOOKINUSE).

AuthorTitlePubyearInv_NoUser_ID
ТолстойВойна и мир20052865
ЧеховВишневый сад20001731
ЧеховИзбранные рассказы201119120
ЧеховВишневый сад1991565
Ильф и ПетровДвенадцать стульев1985331
ЧеховРанние рассказы200117131
МаяковскийПоэмы19832120
ПастернакДоктор Живаго200669120
Толстой
Воскресенье
20067747
ТолстойАнна Каренина19897205
ПушкинКапитанская дочка20042547
ГогольПьесы20078147
ЧеховИзбранные рассказы19874205
ПушкинСочинения, т.11984647
ПастернакИзбранное200013718
ПушкинСочинения, т.219848205

Пример 1. Вывести авторов выданных книг, сгруппировав их. Пишем следующий запрос:

Этот запрос вернёт следующий результат:

Author
Гоголь
Ильф и Петров
Маяковский
Пастернак
Пушкин
Толстой
Чехов

Как видим, в таблице стало меньше строк, так как фамилии авторов остались каждая по одной.

В следующем примере увидим, что оператор GROUP BY не следует путать с оператором ORDER BY и поймём, чем эти операторы отличаются друг от друга.

Пример 2. Вывести авторов и названия выданных книг, сгруппировав по авторам. Пишем следующий запрос:

Этот запрос вернёт следующий результат:

AuthorTitle
ГогольПьесы
Ильф и ПетровДвенадцать стульев
МаяковскийПоэмы
ПастернакДоктор Живаго
ПушкинКапитанская дочка
ТолстойВойна и мир
ЧеховВишнёвый сад

Как видим, в таблице каждому автору соответствует лишь одна книга, причём та, которая в таблице BOOKINUSE является первой по порядку записей.

Если бы нам требовалось вывести все книги, причём авторы должны были бы следовать не «вразброс», а по порядку: сначала Гоголь и все его книги, затем другие авторы и все их книги, то мы применили бы не оператор GROUP BY, а оператор ORDER BY.

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

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

Пример 3. Вывести авторов, названия выданных книг, ID пользователя и инвентарный номер выданной книги. Сгруппировать по авторам, ID пользователя и инвентарному номеру. Запрос будет следующим:

Этот запрос вернёт следующий результат:

AuthorTitleUser_IDInv_no
ГогольПьесы4781
Ильф и ПетровДвенадцать стульев313
МаяковскийПоэмы1202
ПастернакИзбранное18137
ПастернакДоктор Живаго12069
ПушкинКапитанская дочка4725
ПушкинСочинения, т.1476
ПушкинСочинения, т.22058
ТолстойВоскресенье4777
ТолстойВойна и мир6528
ТолстойАнна Каренина2057
ЧеховВишневый сад3119
ЧеховРанние рассказы31171
ЧеховВишневый сад655
ЧеховИзбранные рассказы12019
ЧеховИзбранные рассказы2054

Как видим, в результирующей таблице присутствуют все книги всех авторов, причём авторы следуют по порядку, как если бы мы применили оператор ORDER BY. Кроме того, видно, что записи сгруппированы и по второму указанному столбцу — User_ID. Так, у автора Пушкина сначала перечисляются книги, выданные пользователю с User_ID 47, а затем — 205. У автора Чехова сначала перечисляются книги, выданные пользователю с User_ID 31, а затем — с другими номерами. Третий столбец, по которому происходит группировка — Inv_no — добавлен только для того, чтобы в результирующей таблице выводились все строки, соответствующие значениям ранее перечисленных столбцов для группировки, а не только уникальные.

Группировка с агрегатными функциями

Агрегатные функции COUNT, SUM, AVG, MAX, MIN служат для вычисления соответствующего агрегатного значения ко всему набору строк, для которых некоторый столбец — общий.

Пример 4. Вывести количество выданных книг каждого автора. Запрос будет следующим:

Результатом выполнения запроса будет следующая таблица:

AuthorInUse
Гоголь1
Ильф и Петров1
Маяковский1
Пастернак2
Пушкин3
Толстой3
Чехов5

Пример 5. Вывести количество книг, выданных каждому пользователю. Запрос будет следующим:

Результатом выполнения запроса будет следующая таблица:

Max() function with Group by

In this page we are discussing, how the GROUP BY clause along with the SQL MAX() can be used to find the maximum value of a column over each group.

Example:

Sample table: agents

To get data of ‘working_area’ and maximum ‘commission’ for the agents of each ‘working_area’ from the ‘agents’ table with the following condition —

1. the ‘working_area’ should come in a group,

the following SQL statement can be used:

SQL max() with group by and order by

To get data of ‘cust_city’, ‘cust_country’ and maximum ‘outstanding_amt’ from the customer table with the following conditions —

1. the combination of ‘cust_country’ and ‘cust_city’ should make a group,

2. the group should be arranged in alphabetical order,

the following SQL statement can be used:

Sample table: customer

Pictorial Presentation:

SQL max() with group by on two columns

To get data of ‘cust_city’, ‘cust_country’ and maximum ‘outstanding_amt’ from the ‘customer’ table with the following condition —

1. the combination of ‘cust_country’ and ‘cust_city’ column should make a group,

the following SQL statement can be used :

Sample table: customer

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Here is a slide presentation of all aggregate functions.

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

SQL запрос: group by, вычислите разницу между max и min в группе и group by снова



Мой DB содержит userID в первом столбце и timestamp какого-то действия (входа в систему или нажатия кнопки). Первая запись (userID, time) — это всегда вход пользователя в систему, иногда есть и вторая, что означает, что пользователь также нажал кнопку-но это не всегда так. Никогда не бывает больше 2 строк с одним и тем же userID.

"userID" "time"
"0" "1526474096000"
"1" "1526474097000"
"2" "1526474098000"
"3" "1526474099000"
"4" "1526474100000"
"1" "1526474106000"
"0" "1526474107000"

Например, здесь только пользователи с ID 0 и 1 нажали на кнопку.
Я хочу вычислить гистограмму (или какое-то другое приближение) разницы во времени, потому что слишком много пользователей, чтобы мой RAM импортировал разницу во времени для каждого пользователя.
Мой запрос SQL выглядит следующим образом (n-это размер ячейки гистограммы):

SELECT COUNT(*), (MAX(time)-MIN(time)/n) as time_difference 
FROM table_name  
GROUP BY userID, time_difference

Это не работает, потому что «aggregate functions are not allowed in the GROUP BY clause».

sql sqlite group-by max min
Поделиться Источник mihagazvoda     16 мая 2018 в 17:48

2 ответа


  • Min, Max, Group By и DateDiff

    Я пытаюсь оптимизировать множество операторов MySQL в один, но столкнулся с проблемой. MySQL запрос: SELECT companyid, COUNT(*) AS total FROM timesheet WHERE userId=’1′ AND DATEDIFF(MIN(startDate), MAX(endDate)) <= 77 GROUP BY companyid HAVING total = 11 То, что я пытаюсь сделать, — это выбрать…

  • SQL MIN() & GROUP BY

    Я ищу более элегантное решение проблемы, с которой я сталкиваюсь с MySQL MIN(), используемым в сочетании с GROUP BY. У меня есть два стола: продукты (id, group_id) product_attributes (product_id, attribute_id, значение) Я хочу выбрать самый дешевый металл из каждой группы металлов (attribute_id =…



1

Ты пытаешься это сделать?

SELECT userID, (MAX(time)-MIN(time)/n) as time_difference 
FROM table_name  
GROUP BY userID 

Поделиться Daniel Marcus     16 мая 2018 в 17:50



1

Просто используйте подзапрос. Я не совсем уверен, что вы пытаетесь сделать с этим «/n», но это, по крайней мере, устраняет вашу синтаксическую ошибку.

SELECT time_Difference,
       count(*)
  FROM (SELECT (MAX(time)-MIN(time)/n) as time_difference 
          FROM table_name  
         GROUP 
            BY userID
       ) TMP
 GROUP
    BY time_difference;

Поделиться Error_2646     16 мая 2018 в 18:04


Похожие вопросы:


SQL: MAX(id) в подзапросе предложения where с group by

У меня есть запрос и подзапрос в запросе, он должен дать строку дат различных строк в группе (group by). Эта работа: теперь я хочу получить эту строку в отчете с другими строками с тем же max(id),…


Преобразование vb.net Linq в c# Group by, с суммой & max & min

Я пытаюсь преобразовать запрос VB.NET Linq в C# Я не уверен, как сделать group by и select Sum, Max & Min in one line statement in C#, можно ли это сделать в c# и если да, то указатель на то,…


использование min и max в предложении group by

Я хочу ниже вывода в oracle sql. У меня есть данные в таблице, как показано ниже : id start_date end_date assignment number 1 2.02.2014 15.02.2014 10 2 25.02.2014 30.02.2014 20 3 26.03.2014…


Min, Max, Group By и DateDiff

Я пытаюсь оптимизировать множество операторов MySQL в один, но столкнулся с проблемой. MySQL запрос: SELECT companyid, COUNT(*) AS total FROM timesheet WHERE userId=’1′ AND DATEDIFF(MIN(startDate),…


SQL MIN() & GROUP BY

Я ищу более элегантное решение проблемы, с которой я сталкиваюсь с MySQL MIN(), используемым в сочетании с GROUP BY. У меня есть два стола: продукты (id, group_id) product_attributes (product_id,…


MySQL GROUP BY AVG MAX MIN — > логическая ошибка

Я изменил несколько примеров кода скрипта, но все равно не смог решить проблему ошибки ‘Boolean’ То, что я пытаюсь сделать, — это просто получить возраст AVR, MIN и MAX лет только для 7 областей…


Min и Max в одном запросе Group by

Следующий запрос не выполняется, если я включаю MAX (col1) в предложение select SELECT AVG(col1), MIN(col1), —ΜΑΧ(col1), COUNT(*), col2 FROM view GROUP BY col2; как я могу переписать это, чтобы…


Объясните group by запрос

Я очень новичок в sql и базе данных. Наткнулся на этот запрос: SELECT cast(min(dt_from) as varchar(12))+cast(max(dt_to) as varchar (12)) FROM view1 GROUP BY dt_from, dt_to Когда я исключаю group by,…


Разница между GROUP BY имея и SELECT MAX() GROUP BY

Я новичок в SQL и запутываюсь в разнице в следующих двух запросах: Выберите MAX(версия), * FROM таблица WHERE primary_key = @key GROUP BY местоположение SELECT версия, * FROM таблица WHERE…


MYSQL max() и group by error:only_full_group_by

У меня есть вопрос о запросе MySQL, который регистрирует ошибки с момента обновления MySQL-5.7. Ошибка-это only_full_group_by, который будет произнесен на stackoverflow. Во многих ответах говорится,…

Оконные функции в SQL — что это и зачем они нужны

Многие разработчики, даже давно знакомые с SQL, не понимают оконные функции, считая их какой-то особой магией для избранных. И, хотя реализация оконных функций поддерживается с SQL Server 2005, кто-то до сих пор «копипастит» их со StackOverflow, не вдаваясь в детали. Этой статьёй мы попытаемся развенчать миф о неприступности этой функциональности SQL и покажем несколько примеров работы оконных функций на реальном датасете.

Почему не GROUP BY и не JOIN

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

Примечание Оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Для простоты понимания можно считать, что SQL сначала выполняет весь запрос (кроме сортировки и limit), а уже потом считает значения окна.

Окей, с GROUP BY разобрались. Но в SQL практически всегда можно пойти несколькими путями. К примеру, может возникнуть желание использовать подзапросы или JOIN. Конечно, JOIN по производительности предпочтительнее подзапросов, а производительность конструкций JOIN и OVER окажется одинаковой. Но OVER даёт больше свободы, чем жёсткий JOIN. Да и объём кода в итоге окажется гораздо меньше.

Для начала

Оконные функции начинаются с оператора OVER и настраиваются с помощью трёх других операторов: PARTITION BY, ORDER BY и ROWS. Про ORDER BY, PARTITION BY и его вспомогательные операторы LAG, LEAD, RANK мы расскажем подробнее.
Все примеры будут основаны на датасете олимпийских медалистов от Datacamp. Таблица называется summer_medals и содержит результаты Олимпиад с 1896 по 2010:

ROW_NUMBER и ORDER BY

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

SELECT
athlete,
event,
ROW_NUMBER() OVER() AS row_number
FROM Summer_Medals
ORDER BY row_number ASC;


Каждая пара «спортсмен — вид спорта» получила номер, причём к этим номерам можно обращаться по имени row_number.
ROW_NUMBER можно объединить с ORDER BY, чтобы определить, в каком порядке строки будут нумероваться. Выберем с помощью DISTINCT все имеющиеся виды спорта и пронумеруем их в алфавитном порядке:

SELECT
sport,
ROW_NUMBER() OVER(ORDER BY sport ASC) AS Row_N
FROM (
SELECT DISTINCT sport
FROM Summer_Medals
) AS sports
ORDER BY sport ASC;

PARTITION BY и LAG, LEAD и RANK

PARTITION BY позволяет сгруппировать строки по значению определённого столбца. Это полезно, если данные логически делятся на какие-то категории и нужно что-то сделать с данной строкой с учётом других строк той же группы (скажем, сравнить теннисиста с остальными теннисистами, но не с бегунами или пловцами). Этот оператор работает только с оконными функциями типа LAG, LEAD, RANK и т. д.

LAG

Функция LAG берёт строку и возвращает ту, которая шла перед ней. Например, мы хотим найти всех олимпийских чемпионов по теннису (мужчин и женщин отдельно), начиная с 2004 года, и для каждого из них выяснить, кто был предыдущим чемпионом.
Решение этой задачи требует нескольких шагов. Сначала надо создать табличное выражение, которое сохранит результат запроса «чемпионы по теннису с 2004 года» как временную именованную структуру для дальнейшего анализа. А затем разделить их по полу и выбрать предыдущего чемпиона с помощью LAG:

– Табличное выражение ищет теннисных чемпионов и выбирает нужные столбцы
WITH Tennis_Gold AS (
SELECT
Athlete,
Gender,
Year,
Country
FROM
Summer_Medals
WHERE
Year >= 2004 AND
Sport = 'Tennis' AND
event = 'Singles' AND
Medal = 'Gold')
– Оконная функция разделяет по полу и берёт чемпиона из предыдущей строки
SELECT
Athlete as Champion,
Gender,
Year,
LAG(Athlete) OVER (PARTITION BY gender
ORDER BY Year ASC) AS Last_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;


Функция PARTITION BY в таблице вернула сначала всех мужчин, потом всех женщин. Для победителей 2008 и 2012 года приведён предыдущий чемпион; так как данные есть только за 3 олимпиады, у чемпионов 2004 года нет предшественников, поэтому в соответствующих полях стоит null.

LEAD

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

– Табличное выражение ищет теннисных чемпионов и выбирает нужные столбцы
WITH Tennis_Gold AS (
SELECT
Athlete,
Gender,
Year,
Country
FROM
Summer_Medals
WHERE
Year >= 2004 AND
Sport = 'Tennis' AND
event = 'Singles' AND
Medal = 'Gold')
– Оконная функция разделяет по полу и берёт чемпиона из следующей строки
SELECT
Athlete as Champion,
Gender,
Year,
LEAD(Athlete) OVER (PARTITION BY gender
ORDER BY Year ASC) AS Future_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;

RANK

Оператор RANK похож на ROW_NUMBER, но присваивает одинаковые номера строкам с одинаковыми значениями, а «лишние» номера пропускает. Есть также DENSE_RANK, который не пропускает номеров. Звучит запутанно, так что проще показать на примере. Вот ранжирование стран по числу олимпиад, в которых они участвовали, разными операторами:

  • Row_number — ничего интересного, строки просто пронумерованы по возрастанию.
  • Rank_number — строки ранжированы по возрастанию, но нет номера 3. Вместо этого, 2 строки делят номер 2, а за ними сразу идёт номер 4.
  • Dense_rank — то же самое, что и rank_number, но номер 3 не пропущен. Номера идут подряд, но зато никто не оказался пятым из пяти.

Вот код:

-- Табличное выражение выбирает страны и считает годы
WITH countries AS (
SELECT
Country,
COUNT(DISTINCT year) AS participated
FROM
Summer_Medals
WHERE
Country in ('GBR', 'DEN', 'FRA', 'ITA','AUT')
GROUP BY
Country)

-- Разные оконные функции ранжируют страны
SELECT
Country,
participated,
ROW_NUMBER()
OVER(ORDER BY participated DESC) AS Row_Number,
RANK()
OVER(ORDER BY participated DESC) AS Rank_Number,
DENSE_RANK()
OVER(ORDER BY participated DESC) AS Dense_Rank
FROM countries
ORDER BY participated DESC;

Напоследок

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

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

Хинт для программистов: если зарегистрируетесь на соревнования Huawei Cup, то бесплатно получите доступ к онлайн-школе для участников. Можно прокачаться по разным навыкам и выиграть призы в самом соревновании.

Перейти к регистрации

Адаптированный перевод статьи «Intro to Window Functions in SQL»

13

«Group By» в SQL и Python: сравнение

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

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

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

Сегодня мы сосредоточимся на операциях GroupBy , которые являются еще одним отличным примером задачи, которая может быть выполнена как SQL, так и Python, и решение которой зависит от ваших целей.

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

 

SQL

Функции агрегации

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

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

select count(*) as num_trips
from modeanalytics.sf_bike_share_trip

 

Интересно, что агрегатная функция count() имеет другую, часто упускаемую из вида функциональность. Если вы укажете столбец внутри функции count(), он вернет количество строк с ненулевыми экземплярами этого столбца вместо абсолютного общего количества строк. Например, если мы хотим рассчитать количество поездок, когда поле zip_code не равно нулю, мы могли бы использовать следующий запрос:

select count(zip_code) as num_trips_with_zip_code
from modeanalytics.sf_bike_share_trip

 

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

 

Group By

На высоком уровне предложение group by в SQL позволяет независимо применять функции агрегирования к отдельным группам данных в наборе данных.

Возвращаясь к нашему предыдущему запросу, давайте посчитаем количество поездок на велосипеде ( count(*)) по станции, с которой они начали ( start_station_name). Мы делаем это, добавляя start_station_name к списку выбранных столбцов и добавляя предложение group by, явно указывающее SQL для группировки count(*)агрегации по столбцу start_station_name:

select
  bst.start_station_name,
  count(*) as num_trips
from modeanalytics.sf_bike_share_trip bst
group by bst.start_station_name

 

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

select
  bst.start_station_name,
  count(*) as num_trips,
  avg(bst.duration) as avg_duration_seconds,
  min(bst.duration) as min_duration_seconds,
  max(bst.duration) as max_duration_seconds
from modeanalytics.sf_bike_share_trip bst
group by bst.start_station_name

 

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

select
  bst.start_station_name,
  bst.end_station_name,
  count(*) as num_trips,
  avg(bst.duration) as avg_duration_seconds,
  min(bst.duration) as min_duration_seconds,
  max(bst.duration) as max_duration_seconds
from modeanalytics.sf_bike_share_trip bst
group by bst.start_station_name,bst.end_station_name

 

Как видите, мы добавили столбец end_station_name как в наш список select, так и в наше предложение group by. Большинство платформ SQL предлагают некоторый синтаксический сахар для этой операции, что позволяет вам использовать порядковый номер столбца в selectвыражении вместо явной ссылки на имена столбцов. На практике это будет выглядеть так:

select
  bst.start_station_name,
  bst.end_station_name,
  count(*) as num_trips,
  avg(duration) as avg_duration_seconds,
  min(duration) as min_duration_seconds,
  max(duration) as max_duration_seconds
from modeanalytics.sf_bike_share_trip bst
group by 1,2

 

Примечание: не все платформы SQL позволяют ссылаться на столбцы по их порядковому положению в операторе select. Например, в базах данных Oracle и SQL Server вам нужно будет явно ссылаться на столбцы по имени в group byпредложении.

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

 

having

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

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

select
  bst.start_station_name,
  bst.end_station_name,
  count(1) as num_trips,
  avg(duration) as avg_duration_seconds,
  min(duration) as min_duration_seconds,
  max(duration) as max_duration_seconds
from modeanalytics.sf_bike_share_trip bst
group by 1,2
having count(1) > 1000

 

На данный момент мы изучили многое из того, что SQL может делать с функциональностью group by. Давайте посмотрим, как эта же реализация будет выглядеть в Python, используя библиотеку pandas.

 

Python

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

В pandas, «groups» данные создаются с помощью python метода называется groupby(). Метод groupby() может быть вызван непосредственно на объект Dataframe в pandas.

В качестве примера мы собираемся использовать выходные данные SQL-запроса, названные Python в качестве входных данных для нашего Dataframe( df) в нашей записной книжке Python. Обратите внимание, что этот Dataframe не имеет функций агрегирования, вычисляемых с помощью SQL. Он просто использует SQL, чтобы выбрать необходимые поля для нашего анализа, а мы будем использовать панды, чтобы сделать все остальное. Дополнительным преимуществом выполнения этой операции в Python является то, что рабочая нагрузка перемещается из хранилища данных.

В нашей записной книжке Python мы собираемся использовать метод groupby(), чтобы сгруппировать наш Dataframe по столбцу start_station_name и присвоить этот результат новой переменной с именем grouped_single:

grouped_single = df.groupby('start_station_name')

 

Применение метода groupby() к нашему Dataframe объекта возвращает объект GroupBy, который затем присваивается переменной grouped_single. Важная вещь, которую следует отметить в отношении объекта GroupBy для Pandas, заключается в том, что в момент создания объекта не происходило разбиение Dataframe. Объект GroupBy просто содержит всю необходимую ему информацию о характере группировки. Агрегирование не будет происходить, пока мы явно не вызовем функцию агрегирования для объекта GroupBy.

Если мы хотим , чтобы наши группы Dataframe на как в колонках start_station_name и end_station_name, как мы это делали в нашем SQL запрос, мы можем просто добавить столбец end_station_name в наш список группирования ключей внутри метода groupby():

grouped_multiple = df.groupby(['start_station_name','end_station_name'])

 

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

Теперь, когда у нас есть объект GroupBy, созданный с соответствующими группировками, мы можем применить к нему методы агрегирования. По умолчанию pandas будет применять любой метод агрегации, который вы вызываете для объекта GroupBy, ко всем столбцам, не требующим вмешательства. Поскольку нас интересует только применение методов агрегирования к одному столбцу ( trip_duration_seconds), мы выберем только этот столбец из нашего нового объекта GroupBy. То, как вы выбираете отдельные столбцы в объекте GroupBy, аналогично тому, как вы делаете это с объектом Dataframe:

grouped_multiple_column = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds']

 

Pandas позволяет выбрать любое количество столбцов, используя эту операцию. Pandas будет возвращать сгруппированные серии, когда вы выбираете один столбец, и сгруппированные кадры данных, когда вы выбираете несколько столбцов. Теперь, когда у нас есть один столбец, выбранный из нашего объекта GroupBy, мы можем применить к нему соответствующие методы агрегирования. Есть несколько способов сделать это. Мы можем присвоить переменную объекту GroupBy и затем вызвать метод агрегирования для этой переменной:

grouped_multiple = df.groupby(['start_station_name','end_station_name'])
avg_trip_duration = grouped_multiple['trip_duration_seconds'].mean()

 

Или мы можем использовать метод цепочки:

avg_duration_seconds = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds'].mean()

 

Мы можем повторить этот процесс для каждого из агрегатов, которые мы рассчитываем:

# Вычисления агрегатов по отдельности
num_trips = df.groupby(['start_station_name','end_station_name']).size()
avg_duration_seconds = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds'].mean()
min_duration_seconds = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds'].min()
max_duration_seconds = df.groupby(['start_station_name','end_station_name'])['trip_duration_seconds'].max(

 

Вы можете увидеть, как это может запутаться, если вы вычисляете большое количество агрегатов. К счастью, pandas предлагает способ вычисления нескольких агрегатов для одного объекта GroupBy. Метод agg() может принимать принимать список методов агрегирования для отдельных столбцов:

# Calculate aggregations at once
all_together = (df.groupby(['start_station_name','end_station_name']
                  .agg({'trip_duration_seconds': [np.size, np.mean, np.min, np.max]}))

 

Вы также можете связать метод rename() в pandas, чтобы переименовать новые столбцы агрегации в нужные вам имена:

# Calculate and rename aggregations
all_together = (df.groupby(['start_station_name','end_station_name'])
                  .agg({'trip_duration_seconds': [np.size, np.mean, np.min, np.max]})
                  .rename(columns={'size': 'num_trips', 'mean': 'avg_duration_seconds', 'amin': 'min_duration_seconds', 'amax': 'max_duration_seconds'}))

 

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

all_together[all_together['trip_duration_seconds']['num_trips'] > 1000]

 

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

 

Blur The Line

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

Это преимущество возможности использования нескольких языков анализа данных; Вы можете настроить гибридный подход в соответствии с вашими потребностями по мере их развития.

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

SQL Group By: Полное руководство

На чтение 5 мин Просмотров 32 Опубликовано

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

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

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

Здесь на помощь приходит предложение SQL GROUP BY. В этом руководстве мы собираемся обсудить, как использовать предложение GROUP BY.

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

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

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

Например, предположим, что вы хотите узнать, сколько сотрудников являются торговыми партнёрами, вы можете использовать функцию COUNT. Функция COUNT подсчитывает и возвращает количество строк, соответствующих определённому набору критериев. Другие агрегатные функции включают SUM, AVG, MIN и MAX.

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

SQL Group By

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

Синтаксис оператора GROUP BY:

SELECT COUNT(column1_name), column2_name
FROM table1_name
GROUP BY column2_name;

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

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

Предложение GROUP BY используется только в операторах SQL SELECT.

Давайте посмотрим на пример предложения GROUP BY в SQL.

Пример SQL Group By

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

Мы могли бы использовать следующий запрос для получения этой информации:

SELECT title, COUNT(title)
FROM employees
GROUP BY title;

Запрос возвращает несколько записей:

заглавиесчитать
Старший специалист по продажам1
Сотрудник по продажам4
Вице-президент по продажам1
Директор по маркетингу1

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

Когда следует использовать GROUP BY в SQL?

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

Если вы хотите узнать количество ваших клиентов, вам нужно всего лишь выполнить обычный запрос. Вот пример запроса, который вернёт эту информацию:

SELECT COUNT(name) FROM customers;

Наш запрос группирует результат и возвращает:

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

SELECT loyalty_plan, COUNT(loyalty_plan)
FROM customers
GROUP BY loyalty_plan;

Наш запрос собирает данные. Затем наш запрос возвращает:

loyalty_planсчитать
Золото1
Никто3
Серебро1
Бронза2

SQL Group By по нескольким столбцам

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

SELECT branch, title, COUNT(title)
FROM employees
GROUP BY branch, title;

Наш набор результатов запроса показывает:

ответвлятьсязаглавиесчитать
СтэмфордСотрудник по продажам1
ОлбаниВице-президент по продажам1
Сан-ФранцискоСотрудник по продажам1
Сан-ФранцискоСтарший специалист по продажам1
ОлбаниДиректор по маркетингу1
БостонСотрудник по продажам2

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

Вывод

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

Вы ищете вызов? Напишите заявление, в котором узнайте, сколько сотрудников работает в каждом филиале.

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

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

Обзор групповых функций | sql-oracle.ru

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

Групповые функции могут появляться в списках выбора и в предложениях ORDER BY и HAVING. Сервер Oracle применяет групповые функции к каждой группе строк и возвращает одну строку результатов для каждой группы.

Типы групповых функций. Каждая из групповых функций – AVG, SUM, MAX, MIN, COUNT, STDDEV и VARIANCE – принимает единственный аргумент. Функции AVG, SUM, STDDEV и VARIANCE работают только с числовыми значениями. Функции MAX и MIN могут работать с числовыми и символьными значениями данных, а также с датами. Функция COUNT возвращает количество непустых строк для задан-ного выражения. В примере на рисунке определяются средний оклад, среднеквадратическое отклонение, количество сотрудников, получающих комиссионные, а также максимальная дата приема на работу для сотрудников, у которых значение JOB_ID начинается с SA.

Указания по использованию групповых функций

  • Типами данных для аргументов могут быть CHAR, VARCHAR2, NUMBER или DATE.

  • Все групповые функции, кроме COUNT(*), игнорируют значения NULL. Чтобы заменить неопределенное значение, используйте функцию NVL. Функция COUNT возвращает либо натуральное число, либо ноль.

  • При использовании предложения GROUP BY сервер Oracle неявно сортирует набор результатов в порядке возрастания заданных для группирования столбцов. Чтобы переопределить этот стандартный порядок, в предложении ORDER BY можно использовать параметр DESC.

Далее: Выборка данных из таблицы EMPLOYEES

MAX, SUM, а затем AVG в одном SQL-запросе?

Вопрос:

Сложный SQL-запрос нужен здесь, но не уверен, что это возможно.

У меня есть таблица вроде этого:

------------------------
|uid |lesson_id |score |
------------------------
|1   |0         |20    |
|1   |0         |25    |
|1   |0         |15    |
|1   |0         |40    |
|1   |1         |70    |
|1   |0         |10    |
|1   |1         |20    |
|1   |1         |55    |
|1   |1         |55    |
|1   |0         |5     |
|1   |2         |65    |
------------------------

Для конкретного пользователя мне нужно получить максимальный балл для каждого урока, суммировать их, а затем получить их среднее… все в одном SQL-запросе.

Макс. Оценки:

-----------------------
|lesson_id |max_score |
-----------------------
|0         |40        |
|1         |70        |
|2         |65        |
-----------------------

Сумма:

------
|sum |
------
|175 |
------

В среднем:

-------
|avg  |
-------
|58.3 |
-------

Как это?

SELECT uid AS id, AVG(max_score) AS avg_score FROM
(
SELECT uid, lesson_id, max(score) AS max_score FROM cdu_user_progress
WHERE session_id = $session_id
GROUP BY uid, lesson_id
) AS tt
WHERE uid IN ($ids)
GROUP BY uid
ORDER BY avg_score

Лучший ответ:

Вы можете просто использовать подзапросы:

SELECT uid, SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT uid, lesson_id,max(score) as MaxScore
  FROM scores 
  GROUP BY userid, lesson_id 

) AS m
GROUP BY  uid

или только для одного пользователя:

SELECT SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT lesson_id,max(score) as MaxScore
  FROM scores 
  WHERE uid = $uid
  GROUP BY lesson_id 
) AS m

или для “группы конкретных пользователей”:

$uidlist = (comma-delimited list of user ids)

SELECT uid, SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT uid, lesson_id,max(score) AS MaxScore
  FROM scores 
  WHERE FIND_IN_SET(uid, $uidlist)
  GROUP BY uid, lesson_id 
) AS m
GROUP BY uid

Ответ №1

Вот простой пример SQLFIDDLE

select Avg(tt.maxscores) from (
select lesson_id,max(score) as maxscores
from t
group by lesson_id ) as tt

Ответ №2

SELECT AVG( s.scores ) AS avarage, SUM( s.scores ) AS total
FROM (

SELECT lesson_id, MAX( score ) AS scores
FROM  'tblScores'
GROUP BY lesson_id
) AS s

Ответ №3

Вы можете легко сделать это в одном SQL-запросе, хотя вам понадобится использовать более одного оператора.

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

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

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

SELECT MAX(Score) As MaxScore, uid, lesson_id
FROM table
GROUP BY uid, lesson_id

Затем вы обрабатываете вышеуказанный SELECT как таблицу и делаете второй выбор вокруг него, чтобы получить средний балл за uid.

SELECT AVG(MaxScore) as Final, uid
FROM (
SELECT MAX(Score) As MaxScore, uid, lesson_id
FROM table
GROUP BY uid, lesson_id
) AS subQuery
GROUP BY uid

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

Ответ №4

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

Запрос кода PSEUDO:

SELECT lesson_id, max_score WHERE uid=X
UNION
SELECT -1, sum //done via nested query
UNION
SELECT -2, average //done via nested query

mysql — SQL выбирает макс группы

На этот вопрос уже есть ответы :

Закрыт 4 года назад.

Исходная таблица такая:

  Фрукты | Item_ID | Production_line | Amount_produced | Дата производства
-------------------------------------------------- -------------
Яблоки | 652 | 1 | 24 | 2016-05-12
Груши | 455 | 4 | 54 | 2016-05-16
Груши | 455 | 2 | 26 | 2016-05-13
Яблоки | 652 | 6 | 65 | 2016-05-14
Яблоки | 652 | 3 | 24 | 2016-05-21
Груши | 455 | 7 | 54 | 2016-05-17
Груши | 455 | 5 | 26 | 2016-05-15
Яблоки | 652 | 8 | 65 | 2016-05-22
  

В результате я хотел бы видеть производственную линию самого высокого уровня (поскольку они пронумерованы от 1 до уровня, на котором они расположены), сгруппированные по Item_ID вместе со всеми другими столбцами:

  Фрукты | Item_ID | Production_line | Amount_produced | Дата производства
-------------------------------------------------- -------------
Груши | 455 | 7 | 54 | 2016-05-17
Яблоки | 652 | 8 | 65 | 2016-05-22
  

Когда я использую SELECT с MAX (Production_line) вместе с GROUP BY Item_ID в конце моего запроса, чтобы сгруппировать фрукты в соответствии с Item_ID, я не получаю правильную дату производства (не уверен, вытаскивает ли он случайный или что) ни правильное количество произведено.

У меня нет ПЕРВИЧНОГО КЛЮЧА в этой таблице.

Я работаю в phpMyAdmin с MySQL.

3.6.4 Строки, содержащие групповой максимум определенного столбца

3.6.4 Строки, содержащие групповой максимум определенного столбца

Задача: по каждому товару найти дилера или дилеров по самой дорогой цене.

Эту проблему можно решить с помощью такого подзапроса:

  ВЫБРАТЬ артикул, дилер, цена.
ИЗ магазина s1
ГДЕ цена = (ВЫБЕРИТЕ МАКС. (S2.цена)
              ИЗ магазина s2
              ГДЕ s1.article = s2.article)
ЗАКАЗАТЬ ПО артикулам;

+ --------- + -------- + ------- +
| статья | дилер | цена |
+ --------- + -------- + ------- +
| 0001 | B | 3.99 |
| 0002 | А | 10,99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+ --------- + -------- + ------- +  

В предыдущем примере используется коррелированный подзапрос, который может быть неэффективно (см. Раздел 13.2.11.7, «Коррелированные подзапросы»). Другой Возможности решения проблемы заключаются в использовании некоррелированного подзапрос в предложении FROM , LEFT JOIN , или обычное табличное выражение с окном функция.

Некоррелированный подзапрос:

  ВЫБЕРИТЕ s1.article, дилер, s1.price
ИЗ магазина s1
ПРИСОЕДИНИТЬСЯ (
  ВЫБЕРИТЕ артикул, МАКС. (Цена) КАК цена
  ИЗ магазина
  ГРУППА ПО СТАТЬЯМ) КАК s2
  НА s1.article = s2.article И s1.price = s2.price
ЗАКАЗАТЬ ПО артикулам;  

ЛЕВОЕ СОЕДИНЕНИЕ :

  ВЫБРАТЬ s1.article, s1.dealer, s1.price
ИЗ магазина s1
СЛЕВА ПРИСОЕДИНЯЙТЕСЬ к магазину s2 НА s1.article = s2.article И s1.price  

LEFT JOIN работает на том основании, что когда s1.price максимальное значение, нет s2.price с большей стоимостью и, следовательно, соответствующее значение s2.article равно НЕТ . См. Раздел 13.2.10.2, «Предложение JOIN».

Общее табличное выражение с оконной функцией:

  С s1 AS (
   ВЫБЕРИТЕ артикул, дилера, цену,
          RANK () OVER (РАЗДЕЛЕНИЕ ПО СТАТЬЯМ
                           ЗАКАЗАТЬ ПО ЦЕНЕ УДАЛЕНИЕ
                      ) AS `Rank`
     ИЗ магазина
)
ВЫБЕРИТЕ артикул, дилера, цену
  ОТ s1
  ГДЕ `Rank` = 1
ЗАКАЗАТЬ ПО артикулам;  

MIN и MAX агрегатных функций в SQL Server

Функции SQL Server

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

Табличные функции, которые обычно определяются пользователем, могут возвращать массив, но встроенные функции SQL Server обычно являются скалярными функциями. Третий класс функций в SQL Server - это функции с агрегированными значениями. Функции MIN и MAX SQL Server являются функциями с агрегированными значениями.

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

Классификация функций SQL Server может также основываться на типе данных - строковые функции, числовые функции и функции даты. Мы можем сделать вывод, что строковые функции работают со строковыми значениями, например, LENGTH ().

МИН. И МАКС.

Функции MIN и MAX - это очень простые агрегатные функции. Тем не менее, они могут ответить на широкий спектр вопросов в зависимости от набора данных, с которым мы имеем дело.

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

  ВЫБРАТЬ МИН (имя_столбца) ИЗ имя_таблицы;
ВЫБРАТЬ МАКС. (Имя_столбца) ИЗ имя_таблицы;  

Когда мы используем этот синтаксис, SQL Server возвращает единственное значение. Таким образом, мы можем рассматривать функции MIN () и MAX () Скалярные функции.

В листинге 1 показан простой оператор для получения минимального и максимального значений веса фрахта из Sales.Таблица заказов базы данных Itzik Bengan TSQLV4 :

  - Листинг 1. Основные запросы функций MIN () и MAX ()
ИСПОЛЬЗОВАТЬ TSQLV4
ИДТИ
ВЫБРАТЬ * ИЗ [Продажи]. [Заказы];

- Без псевдонимов столбцов
ВЫБЕРИТЕ МИН (фрахт) ИЗ [Продажи]. [Заказы];
ВЫБЕРИТЕ МАКСИМУМ (фрахт) ИЗ [Продажи]. [Заказы];
- Без псевдонимов столбцов
ВЫБЕРИТЕ MIN (фрахт) min_freight ИЗ [Продажи]. [Заказы];
ВЫБЕРИТЕ МАКС. (Фрахт) max_freight ИЗ [Продажи]. [Заказы];
  
Рисунок 1: Снимок полного набора данных Рисунок 2: MIN и MAX фрахта с псевдонимами столбцов и без них

GROUP BY

«Каков минимальный и максимальный вес груза для каждой страны?» Чтобы ответить на этот вопрос, нам понадобится предложение GROUP BY .Пример приведен в листинге 2 ниже.

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

  - Листинг 2: MIN и MAX фрахт по странам
- Минимум по стране
ВЫБЕРИТЕ страну доставки, MIN (фрахт) min_freight ОТ [Продажи]. [Заказы]
ГРУППА ПО СТРАНЕ Судоходства;

- Максимум по стране
ВЫБЕРИТЕ страну доставки, МАКС. (Фрахт) max_freight ИЗ [Продажи].[Заказы]
ГРУППА ПО СТРАНЕ Судоходства;

-- Проверка
ВЫБЕРИТЕ * ИЗ [Продажи]. [Заказы] ГДЕ shipcountry = 'Финляндия'
ЗАКАЗАТЬ груз;

- Листинг 2: MIN и MAX фрахт по странам
- Минимум по заказу
ВЫБЕРИТЕ custid, MIN (фрахт) min_freight ОТ [Продажи]. [Заказы]
GROUP BY custid;

- Максимум по заказчику
ВЫБЕРИТЕ custid, MAX (фрахт) max_freight ОТ [Продажи]. [Заказы]
GROUP BY custid;


-- Проверка
ВЫБРАТЬ * ИЗ [Продажи]. [Заказы] ГДЕ custid = '23 '
ЗАКАЗАТЬ груз;
  

На Рисунке 3 показаны результаты.Мы можем сделать что-то подобное с другим столбцом - столбцом custid . В этом случае мы отвечаем на вопрос «Каков минимальный и максимальный страх для каждого клиента?»

Рисунок 3: Результат листинга 2

Дополнительные вопросы

Мы можем дополнительно исследовать данные, чтобы выяснить, в каких странах вес груза меньше 1 или больше 800. Мы делаем это с помощью предложения HAVING , которое фильтрует наборы результатов, сегментированные в группы с помощью предложения GROUP BY .

  - Листинг 3: Введение в предложение HAVING
ВЫБЕРИТЕ страну доставки, MIN (фрахт) min_freight ОТ [Продажи]. [Заказы]
ГРУППА ПО СТРАНЕ судоходства
ИМЕЮЩИЙ МИН (фрахт) <1;

ВЫБЕРИТЕ страну доставки, MAX (фрахт) max_freight ИЗ [Продажи]. [Заказы]
ГРУППА ПО СТРАНЕ судоходства
МАКСИМАЛЬНАЯ (фрахт)> 800;
  
Рисунок 4: Использование предложения HAVING

Работа с датами

При применении функций MIN () и MAX () к датам мы ожидаем увидеть самые ранние и последние даты соответственно.Взгляните на примеры - даты хранятся как целые числа внутри SQL Server.

  - Листинг 4: Работа с датами
ВЫБЕРИТЕ МИН (дата заказа) самая ранняя_дата ИЗ [Продажи]. [Заказы];
ВЫБЕРИТЕ МАКС. (Дата заказа) latest_date ИЗ [Продажи]. [Заказы];
  
Рисунок 5: Результат листинга 4

Мы можем копнуть глубже, спросив, какой клиент сделал первый заказ и какой клиент сделал последний заказ. Для этого мы используем код из листинга 5. На рисунке 6 показано, что клиент custid 85 выполнил самый ранний заказ, а клиент custid 9 разместил последний заказ.

  - Листинг 5: Даты заказа клиентов
ВЫБЕРИТЕ custid, MIN (дата заказа) раньше_дата ИЗ [Продажи]. [Заказы]
ГРУППА ПО custid
ЗАКАЗАТЬ ПО ранней_дате;

ВЫБЕРИТЕ custid, MAX (дата заказа) latest_date FROM [Продажи]. [Заказы]
ГРУППА ПО custid
ЗАКАЗАТЬ ПО latest_date desc;
  
Рисунок 6: Результаты листинга 5

Заключение

В этой статье мы кратко продемонстрировали использование агрегатных функций MIN () и MAX () в SQL Server. Роль этих функций - возвращать минимальные и максимальные значения для заданного диапазона, хранящиеся в столбце таблицы.

Мы можем ответить на другие вопросы с помощью этих функций, объединив их с другими командами T-SQL, такими как предложения GROUP BY, ORDER BY и HAVING.

Множество инструментов упрощают и ускоряют выполнение таких задач, и вы можете выбрать любое решение, отвечающее вашим требованиям. Среди них SQL Complete от Devart выполняет все необходимые манипуляции и предоставляет функцию SSMS Grid Aggregate на месте, которая вычисляет MIN, MAX и AVG выбранных значений в сетке.

(Посещали 590 раз, сегодня 2 раза)

MAX (Transact-SQL) - SQL Server

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

В этой статье

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Platform System (PDW)

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

Соглашения о синтаксисе 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 определяет логический порядок, в котором выполняется операция. partition_by_clause требуется. Дополнительные сведения см. В разделе Предложение OVER (Transact-SQL).

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

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

Замечания

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

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

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

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

Примеры

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

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

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

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

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

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

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

  ВЫБРАТЬ ОТЛИЧНОЕ имя
       , МИН (Скорость) ВЫШЕ (РАЗДЕЛЕНИЕ ПО edh.DepartmentID) AS MinSalary
       , МАКС. (Ставка) ПРЕВЫШАЕТ (РАЗДЕЛЕНИЕ ПО ИДЕНТИФИКАТОРУ ОТДЕЛЕНИЯ) КАК MaxSalary
       , 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
     НА eph.BusinessEntityID = edh.BusinessEntityID
ПРИСОЕДИНЯЙТЕСЬ HumanResources. Отдел AS d
 ON d.DepartmentID = edh.DepartmentID
ГДЕ edh.EndDate IS 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)

Group By, Have и Where в SQL

Введение

В этом блоге мы обсудим, как работать с предложениями GROUP BY, WHERE и HAVING в SQL, и просто объясним эту концепцию на примере.Я надеюсь, что это будет очень полезно для новичков и среднего уровня, чтобы помочь им понять основную концепцию.

Группировать по пункту

Предложение Group by часто используется для упорядочивания идентичных повторяющихся данных в группы с помощью оператора select для группировки набора результатов по одному или нескольким столбцам. Это предложение работает с выбранным конкретным списком элементов, и мы можем использовать предложения HAVING и ORDER BY. Предложение Group by всегда работает с агрегатными функциями, такими как MAX, MIN, SUM, AVG, COUNT.

Давайте обсудим по группам на примере. У нас есть таблица «Производство автомобилей», есть несколько моделей с ценой и некоторые повторяющиеся данные. Мы хотим отнести эти данные к другой группе с соответствующей общей ценой.

Пример

  1. Создать таблицу VehicleProduction
  2. (
  3. Id int Идентификатор первичного ключа,
  4. Модель varchar (50),
  5. Цена в деньгах
  6. )
  7. Вставить в VehicleProduction значения ('L551', 850000), ('L551', 850000), ('L551', 850000), ('L551', 750000),
  8. ('L538', 650000), ('L538', 650000), ('L538', 550000), ('L530', 450000), ('L530', 350000), ('L545', 250000)
  9. Выбрать * из VehicleProduction
MAX () - функция возвращает максимальное значение числового столбца указанного критерия.

Пример
  1. Выберите max (Price) как «MaximumCostOfModel» из VehicleProduction

Выход

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

Пример
  1. Выберите минимальную (цену) как «MinimumCostOfModel» из VehicleProduction

СУММ () - функция возвращает общую сумму числового столбца по заданным критериям.

Пример
  1. Выберите SUM (Price) как SumCostOfAllModel из VehicleProduction

Выход

AVG () - функция возвращает среднее значение числового столбца указанного критерия.

Пример
  1. Выберите AVG (Price) как «AverageCostOfModel» из VehicleProduction
COUNT () - функция возвращает количество строк, соответствующих заданным критериям.

Пример
  1. Выберите Count (Price) As 'TotalVehicleModels' из VehicleProduction

Особая статья

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

Пример
  1. Select Distinct (модель), цена от автомобиля

Выход

Предложение Group by часто используется для объединения идентичных повторяющихся данных в группы с помощью оператора select.Это предложение работает с выбранным конкретным списком элементов, для этого мы можем использовать предложения HAVING и ORDER BY.

Синтаксис
  1. ВЫБРАТЬ Столбец1, Столбец2
  2. ИЗ TableName
  3. GROUP BY Column1, Column2
Пример
  1. Выбрать * из VehicleProduction
  2. Выбор модели, цена с автомобиля Производство
  3. сгруппировать по моделям, цене
Давайте посмотрим на пример GROUP BY с агрегатными функциями.

GROUP BY с агрегатными функциями

Пример
  1. Выберите модель, цену, количество (*) как QtyOfModel, Sum (Price) как TotPriceOfModel из VehicleProduction
  2. сгруппировать по моделям, цене
Выход

Где пункт

Предложение Where работает с предложением select, но не работает с условием функции group by или агрегирования.

Пример 1

  1. Выбор модели, цена с автомобиля Производство
  2. , где Модель! = 'L530'
  3. сгруппировать по моделям, цене

    Выход

    Пример 2

    Мы не можем использовать предложение where после группы по пункту

    1. Выбор модели, цена с автомобиля Производство
    2. сгруппировать по моделям, Цена
    3. , где Model! = 'L530'

    Имеющий пункт

    Предложение Have работает с предложением group by, но, в частности, работает с условием агрегированной функции.

    Пример

    1. Выбор модели, цена с автомобиля Производство
    2. Группировка по моделям, Цена
    3. Сумма (Цена)> 600000.00

    ORDER BY пункт

    Предложение

    Order By показывает записи в порядке возрастания или убывания конкретного условия.

    Пример
    1. Выбор модели, цена с автомобиля Производство
    2. Группировка по моделям, Цена
    3. Сумма (Цена)> 400000.00
    4. упорядочить по цене по убыванию

    Выход

    Сводка

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

    Как использовать GROUP BY и HAVING в SQL-запросе

    Нет сомнений в том, что SQL - это важный навык, и каждый программист, разработчик, DevOps и бизнес-аналитик должен знать SQL.Если вы хотите изучить SQL с нуля, вы попали в нужное место. Ранее я поделился множеством вопросов на собеседовании по SQL и лучшими курсами SQL для начинающих, а сегодня я собираюсь поделиться некоторыми примерами GROPU By в SQL для написания запросов агрегирования. Предложение GROUP BY в SQL - еще одна важная команда, которую должен освоить любой программист. Мы часто используем команду GROUP BY вместе с предложением select для целей отчетности, поскольку предложение GROUP BY в основном используется для группировки связанных данных вместе, это одна из самых важных команд SQL для целей отчетности.Многие запросы, требующие использования агрегатных функций, таких как sum (), avg () или count (), требуют группировки данных с помощью предложения GROUP BY. SQL-запросы, включающие предложения GROUP BY и HAVING, также немного сбивают с толку многих программистов, не имеющих практического опыта работы с SQL, и часто используются в качестве вопросов собеседования SQL для фильтрации. В этой статье мы увидим несколько примеров предложения GROUP BY в SQL, которые помогут вам понять, где использовать предложение group by и как использовать GROUP BY вместе с предложением SELECT.Вы также узнаете некоторые правила SQL, связанные с предложением GROUP BY, которое доступно в некоторых базах данных, особенно в MySQL, как группа по расширениям.

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

    1. Группировать по пункту Пример 1 - Поиск дубликата Одно из популярных применений предложения GROUP BY - поиск повторяющихся записей в базе данных.После запроса SQL будут перечислены сотрудники, которые дублируются с точки зрения зарплаты
     mysql> выберите emp_name, count (emp_name)
           из группы сотрудников по emp_name, имеющей count (emp_name)> 1;
    + ---------- + ----------------- +
    | emp_name | счетчик (emp_name) |
    + ---------- + ----------------- +
    | Джеймс | 2 |
    + ---------- + ----------------- +
    1 ряд в комплекте (0,00 сек) 

    Это был довольно простой пример поиска повторяющихся записей в базе данных. Если вам нужно решить, является ли сотрудник дубликатом или нет, основываясь на более чем одном поле, важно включить всех в предложение group by, в противном случае вы получите неправильный счет.Более подробную информацию вы можете найти в моей статье о том, как найти повторяющиеся записи в базе данных.

    2. Группировать по пункту Пример 2 - Расчет суммы Другой популярный пример предложения group by - использование агрегатных функций, таких как sum () и avg (). Если вы знаете, предложение GROUP BY в SQL позволяет выполнять такие запросы, как определение того, сколько каждый отдел платит своим сотрудникам, то есть общую заработную плату в каждом отделе. Чтобы написать SQL-запрос, чтобы найти общую зарплату по отделам, нам нужно сгруппировать по отделам и использовать сумму (зарплата) в списке выбора, как показано в следующем SQL-запросе:
     mysql> select dept_id, sum (salary) как total_salary от группы сотрудников по dept_id;
    + --------- + -------------- +
    | dept_id | total_salary |
    + --------- + -------------- +
    | 1 | 3200 |
    | 2 | 2850 |
    | 3 | 2200 |
    | 4 | 2250 |
    + --------- + -------------- +
    4 ряда в наборе (0.00 сек) 

    Затем вы можете дополнительно отфильтровать записи, используя предложения has для выполнения запросов, таких как поиск всех отделов, общие расходы на заработную плату которых превышают 3000. Вот SQL-запрос для этого:
     mysql> выберите dept_id, sum (salary) as total_salary
           из группы сотрудников по dept_id с суммой (окладом)> 3000;
    + --------- + -------------- +
    | dept_id | total_salary |
    + --------- + -------------- +
    | 1 | 3200 |
    + --------- + -------------- +
    1 ряд в комплекте (0,01 сек) 

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

    3. Как рассчитать среднее значение с помощью группы по пункту Подобно предыдущему примеру группы по предложению, вместо sum () мы также можем использовать avg () для выполнения запросов типа , чтобы узнать среднюю зарплату сотрудников по отделу .Еще раз нам нужно сгруппировать по отделам, и на этот раз вместо агрегатной функции sum () нам нужно использовать функцию avg (), как показано в следующем запросе:
     mysql> select dept_id, avg (salary) as average_salary из группы сотрудников по dept_id;
    + --------- + ---------------- +
    | dept_id | average_salary |
    + --------- + ---------------- +
    | 1 | 1066.6667 |
    | 2 | 1425.0000 |
    | 3 | 1100.0000 |
    | 4 | 750.0000 |
    + --------- + ---------------- +
    4 ряда в наборе (0.00 сек) 

    Точно так же вы можете использовать предложение Have для дальнейшей фильтрации этого набора результатов, например, для поиска отдела, средняя зарплата которого ниже 1000. Вот SQL-запрос для этого:
     mysql>  выберите  dept_id, avg (salary) as average_salary
             из  группы сотрудников по dept_id, имеющей avg (salary) <1000;
    + --------- + ---------------- +
    | dept_id | average_salary |
    + --------- + ---------------- +
    | 4 | 750.0000 |
    + --------- + ---------------- +
    1 ряд в комплекте (0.00 сек) 

    Это ваш самый низкооплачиваемый отдел. Нет смысла заходить в этот отдел и исследовать дальше.

    4. Группировать по примеру 4 - Подсчет записей Подобно агрегатным функциям sum () и avg (), очень популярны другие агрегатные запросы, такие как подсчет записей. Примером такой группы по запросу является , как найти количество сотрудников в отделе? В этом случае нам нужно сгруппировать по отделам и использовать агрегатную функцию count () для подсчета сотрудников, как показано в следующем SQL-запросе:
     mysql>  выберите  dept_id, count (emp_id) as number_of_employees
             из группы сотрудников по dept_id;
    + --------- + --------------------- +
    | dept_id | number_of_employees |
    + --------- + --------------------- +
    | 1 | 3 |
    | 2 | 2 |
    | 3 | 2 |
    | 4 | 3 |
    + --------- + --------------------- +
    4 ряда в наборе (0.00 сек) 

    При использовании функции count () в SQL стоит отметить разницу между count () и count (field), которая может давать разные подсчеты в зависимости от того, в каком столбце вы ведете подсчет, и содержит ли этот столбец NULL или нет.

    Просто помните, что count () также считает значения NULL в столбце. NULL - это особенность SQL, и вы должны хорошо знать, как использовать NULL и как их сравнивать.

    5. Как использовать предложение Group By с более чем одним столбцом Во многих практических случаях мы используем предложение group by с более чем одним столбцом.При использовании двух или трех столбцов в группе по пунктам порядок очень важен. Столбец, который идет первым в предложении group by, будет сначала сгруппирован, а затем второй столбец будет использоваться для группировки в этой группе. Например, в следующем SQL-запросе набор результатов сначала группируется по dept_id, а затем каждая группа снова группируется по emp_id.
     mysql>  выберите  dept_id, emp_id  из  группы сотрудников  по  dept_id, emp_id;
    + --------- + -------- +
    | dept_id | emp_id |
    + --------- + -------- +
    | 1 | 101 |
    | 1 | 102 |
    | 1 | 110 |
    | 2 | 103 |
    | 2 | 104 |
    | 3 | 105 |
    | 3 | 108 |
    | 4 | 106 |
    | 4 | 107 |
    | 4 | 109 |
    + --------- + -------- +
    10 рядов в наборе (0.00 сек) 

    Другой пример использования нескольких столбцов в предложении group by - поиск повторяющихся записей в таблице, где вы должны использовать все столбцы, которые должны быть одинаковыми, чтобы запись называлась дубликатами. Если вы хотите попрактиковаться в таких продвинутых концепциях SQL, я настоятельно рекомендую курс Complete SQL + Databases Bootcamp: Zero to Mastery от Андрея Негаойе в ZTM Academy. Это один из самых всеобъемлющих и современных курсов по изучению SQL.

    Важные моменты, касающиеся предложения Group By в SQL Теперь пора пересмотреть и вернуться к некоторым важным моментам предложения GROUP BY в операторах SQL.

    1. Вы не можете использовать неагрегированное имя столбца в списке выбора, если оно не используется в предложении Group By. Например, следующий SQL-запрос является недопустимым и не будет выполняться, потому что он использовал неагрегированный столбец emp_id в списке выбора, который не назван в предложении Group By, в этом SQL-запросе только dept_id используется в предложении group by.

     mysql>  выберите  emp_id, dept_id, max (зарплата)
      от  сотрудников
           группировать по dept_id;
    
    ОШИБКА 1055 (42000): 'test.employee.emp_id 'не входит в GROUP BY 

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

    2. В стандартном SQL вы не можете использовать неагрегированное имя столбца в предложении Have, которое не используется в предложении group by, база данных MySQL также позволяет использовать его аналогично предыдущей группе по расширению. Следующий SQL-запрос недействителен, потому что мы используем max_salary, имея предложение, которое не используется в предложении group by.

     mysql> выберите dept_id, max (salary) как max_salary
           от сотрудника
           группа по dept_id , имеющая  max_salary> 1000;
    
    ОШИБКА 1463 (42000): в предложении HAVING 
    используется негрупповое поле max_salary.
    3. Другое правило использования предложения group by в стандартном SQL состоит в том, что вы не можете использовать псевдоним в предложении HAVING , предыдущий запрос SQL также является примером этой группы по правилу. Это также разрешено в базе данных MySQL. Вы можете отключить все функции MySQL group by extension, используя режим ONLY_FULL_GROUP_BY SQL в MySQL.Чтобы изменить режим SQL из командной строки MySQL, вы можете использовать следующие команды MySQL:
     mysql> SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER,
               NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY ';
    Запрос в порядке, затронуты 0 строк (0,00 сек)
    
    mysql> ВЫБРАТЬ @@ GLOBAL.sql_mode;
    + ------------------------------------------------- ---------------------------------- +
    | @@ GLOBAL.sql_mode |
    + ------------------------------------------------- ---------------------------------- +
    | ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION |
    + ------------------------------------------------- ---------------------------------- +
    1 ряд в комплекте (0.00 сек) 

    Вы также можете установить режим MySQL SQL для одного клиентского сеанса, используя слово SESSION вместо GLOBAL, как показано ниже:
     mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER,
                 NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY ';
    Запрос в порядке, затронуты 0 строк (0,00 сек)
    
    mysql> ВЫБРАТЬ @@ GLOBAL.sql_mode;
    + ------------------------------------------------- ---------------------------------- +
    | @@ GLOBAL.sql_mode |
    + ------------------------------------------------- ---------------------------------- +
    | ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION |
    + ------------------------------------------------- ---------------------------------- +
    1 ряд в комплекте (0.00 сек) 

    Также простое изменение режима MySQL SQL на Global не вступит в силу в текущем сеансе, пока вы не перезапустите новый сеанс. Чтобы включить ONLY_FULL_GROUP_BY в текущем сеансе MySQL, используя вышеуказанный запрос.

    Вот сводка всех примеров GROUP BY и HAVING, которые мы видели в этой статье:

    Это все в примере предложения GROUP BY в SQL-запросах . Мы видели, где использовать команду GROUP BY и как использовать предложение GROUP BY для группировки данных в SQL.Как я уже сказал, предложения GROUP BY и HAVING являются одними из обязательных для любого программиста, поскольку они довольно распространены в мире SQL и особенно важны для целей отчетности.

    Другие статьи по SQL и базам данных вам могут понравиться

    • 5 лучших веб-сайтов для БЕСПЛАТНОГО изучения SQL (веб-сайты)
    • Мои любимые курсы для изучения SQL и баз данных (курсы)
    • 5 лучших курсов для изучения PostgreSQL для начинающих (курсы)
    • 10 запросов SQL из интервью (запросы)
    • 5 лучших курсов для изучения Microsoft SQL Server (курсы)
    • 5 бесплатных курсов по базам данных и SQL (курсы)
    • 5 книг по лучшему изучению SQL (книги)
    • Как объединить более двух таблиц в один запрос (статья)
    • 10 бесплатных курсов для изучения Oracle и SQL Server (курсы)
    • 5 лучших курсов по изучению MySQL для начинающих (курсы)
    • Разница между предложением WHERE и HAVING (ответ)
    • 5 лучших книг по SQL для продвинутых программистов (книги)
    • Разница между SQL, T-SQL и PL / SQL? (ответ)
    • 5 бесплатных курсов для изучения базы данных MySQL (курсы)
    • 5 лучших онлайн-курсов по изучению SQL и баз данных (курсы)

    Спасибо за чтение этой статьи. Если вы найдете эту SQL GROUP By examples полезной, поделитесь ими со своими друзьями и коллегами.Если у вас есть какие-либо вопросы или отзывы, напишите нам.

    P. S. - Если вы новичок в мире SQL и ищете бесплатный курс по SQL и базам данных, чтобы начать изучение основ, я предлагаю вам пройти курс Introduction to Databases and SQL Querying на Udemy. Это совершенно бесплатно, все, что вам нужно, это создать учетную запись Udemy, и вы можете получить доступ ко всему курсу.

    Как использовать функцию SQL MAX () с датами?

    Давайте сначала узнаем, что такое функция MAX ().

    Функция

    MAX () даст вам максимальные значения из всех значений в столбце.

    Узнайте больше о MAX и MIN в этой статье:

    MIN, MAX, COUNT, AVG и SUM в SQL

    Функция

    MAX работает также с типами данных «дата» и возвращает максимальную или самую последнюю дату из таблицы.

    Давайте посмотрим на базовый пример MAX ():

    ПРИМЕЧАНИЕ

    Все следующие примеры основаны на таблице store_orders, доступной по ссылке ДЕМО в конце статьи.

     
    Запрос:
    
    ВЫБЕРИТЕ МАКС. (Дата_порядка_получения) КАК Макс_Дата ИЗ store_orders;  

    Оформить заказ DEMO, чтобы увидеть результаты запроса выше.

    Приведенный выше запрос вернет последнюю дату (последний раз), когда мы получили заказ.

     
    Результат:
    
    Последний заказ получен
    03.02.2019  

    Не забудьте указать псевдоним в запросе, иначе он не сработает.

    ГДЕ с MAX (Дата)

    Мы также можем использовать условие WHERE с MAX (дата).

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

     
    Запрос:
    
    ВЫБРАТЬ * ИЗ store_orders, где date_order_received = (SELECT MAX (date_order_received) FROM store_orders WHERE customer_id = 203)  

    В этом примере мы используем подзапрос, так как мы не можем просто использовать WHERE = MAX (date), что приведет к ошибке.

    Итак, следующий подзапрос даст нам дату, когда мы получили заказ от конкретного клиента:

     
    ВЫБЕРИТЕ MAX (date_order_received) FROM store_orders, ГДЕ customer_id = 203  

    Когда у нас будет дата, весь запрос будет иметь вид:

     
    ВЫБРАТЬ * ИЗ store_orders, где date_order_received = DATE;  

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

    Группа BY с MAX (Дата)

    Мы также можем использовать функцию Group BY с MAX (Date).

    У нас в магазине куча сотрудников с employee_id. Они выполняют разные заказы. Предположим, мы хотим узнать, когда каждый из них работал в последний раз. Мы можем сделать это, используя функцию Group By с Max (date).

     
    Запрос
    
    ВЫБЕРИТЕ employee_id, MAX (date_order_received) AS Latest_Order FROM store_orders group by employee_id;  

    В приведенном выше запросе мы получаем столбец employee_id и столбец MAX (дата).

Оставить комментарий

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

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