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

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

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

Я хочу ниже вывода в 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…

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

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

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

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

Я очень новичок в 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,…

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

У меня есть вопрос о запросе 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»

«Group By» в 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 Опубликовано 11.03.2021

Оператор 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;

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

Наш запрос 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;

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

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

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

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

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

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

Вывод

Предложение 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
ЗАКАЗАТЬ ПО артикулам;  

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

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

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

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

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

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

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

ВЫБЕРИТЕ custid, MAX (дата заказа) latest_date FROM [Продажи]. [Заказы]
ГРУППА ПО custid
ЗАКАЗАТЬ ПО latest_date desc;
  

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

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

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

  ВЫБРАТЬ ОТЛИЧНОЕ имя
       , МИН (Скорость) ВЫШЕ (РАЗДЕЛЕНИЕ ПО 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 рядов)
  

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