Sql фраза group by


Sql фраза group by

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

Результат показан на рис. 2.3,а.

а) б) в) г)

ПР
9
11 150
12 30
15 370
1 370
3 250
5 170
6 220
8 150
7 200
2
4 100
13 190
14 70
16 250
17 50
10 220

ПС ПР Цена К_во
1 9 -0- -0-
3 9 -0- -0-
5 9 -0- -0-
1 11 1.50 50
5 11 -0- -0-
6 11 -0- -0-
8 11 1.00 100
1 12 3.00 10
3 12 2.50 20
6 12 -0- -0-
1 15 2.00 170
3 15 1.50 200
2 1 3.60 300
7 1 4.20 70
2 3 -0- -0-
7 3 4.00 250
. . .
ПР
1 370
2
3 250
4 100
5 170
6 220
7 200
8 150
9
10 220
11 150
12 30
13 190
14 70
15 370
16 250
17 50

ПР
9
11 150
12 30
15 70
1 370
3 250
5 70
6 140
8 150
7 200
2
4 100
13 190
14 70
16 250
17 50
10 220

Рис. 2.3. Иллюстрации к фразе GROUP BY

Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы Поставки группируются так, что в одной группе содержатся все строки для продукта с ПР = 1, в другой – для продукта с ПР = 2 и т.д. (см. рис. 2.3.б). Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).

Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по ПР результата рассматриваемого примера (рис. 2.3,в) следует дать запрос

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

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

Т БЛ COUNT(БЛ)
1 3 18
1 6 14
1 19 17
1 21 15
.

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

Например, выдать для каждого продукта его код и общий объем возможных поставок, учитывая временную недееспособность поставщика с ПС=2:

Результат, приведенный на рис. 2.3,г, отличается от результата (рис. 2.3,а) аналогичного запроса для всех поставщиков объемом поставок продуктов с кодами 15, 5 и 6.

Sql фраза group by

Для группировки данных в T-SQL применяются операторы GROUP BY и HAVING , для использования которых применяется следующий формальный синтаксис:

GROUP BY

Оператор GROUP BY определяет, как строки будут группироваться.

Например, сгруппируем товары по производителю

Первый столбец в выражении SELECT — Manufacturer представляет название группы, а второй столбец — ModelsCount представляет результат функции Count, которая вычисляет количество строк в группе.

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

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

Другой пример, добавим группировку по количеству товаров:

Оператор GROUP BY может выполнять группировку по множеству столбцов.

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

Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE , но до выражения ORDER BY :

Фильтрация групп. HAVING


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

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

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

При этом в одной команде мы можем использовать выражения WHERE и HAVING:

То есть в данном случае сначала фильтруются строки: выбираются те товары, общая стоимость которых больше 80000. Затем выбранные товары группируются по производителям. И далее фильтруются сами группы — выбираются те группы, которые содержат больше 1 модели.

Если при этом необходимо провести сортировку, то выражение ORDER BY идет после выражения HAVING:

В данном случае группировка идет по производителям, и также выбирается количество моделей для каждого производителя (Models) и общее количество всех товаров по всем этим моделям (Units). В конце группы сортируются по количеству товаров по убыванию.

SQL — Заявление GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями (COUNT, MAX, MIN, SUM, AVG) для группировки результирующего набора одним или несколькими столбцами.

Синтаксис GROUP BY

Пример SQL GROUP BY

В следующем выражении SQL указано количество пользователей в каждой стране:

Пример:

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

Пример:

GROUP BY с JOIN

В следующем выражении SQL указано количество заказов, отправленных каждой службой доставки:

Фразы GROUP BY и HAVING

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

Запрос:Для каждого менеджера выяснить максимальную почасовую ставку среди его подчиненных.

Для того чтобы решить эту задачу, мы должны разделить работников на группы в соответствии с их менеджерами. Затем мы определим максималь­ную ставку внутри каждой группы. В SQL это делается таким образом:

SELECT SUPV_ID, MAX(HRLY_RATE)

GROUP BY SUPV_ID

При обработке этого запроса система сначала разбивает строки таблицы WORKER на группы по следующему правилу. Строки помещаются в одну группу тогда и только тогда, когда у них совпадает SUPV_ID. Затем фраза SELECT применяется к каждой группе. Поскольку в данной группе только одно значение SUPV_ID, то никакой неопределенности SUPV_ID в группе нет. Для каждой группы, фраза SELECT выводит SUPV_ID, a также вычисляет и выводит значение MAX(HRLY_RATE). Результат представлен выше.

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


Фраза GROUP BY. Означает, что строки должны быть разбиты на группы с общими значениями указанного столбца (столбцов).

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

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

С командой GROUP BY можно использовать команду WHERE:

Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1.

SELECT TYPE, AVG(QLTY_LEVEL)

WHERE STATUS = 1

Фраза WHERE выполняется перед командой GROUP BY. Таким образом, ни одна группа не может содержать строку, в которой статус отличен от 1. Строки статуса 1 группируются по значению TYPE, а затем к каждой группе применяется фраза SELECT.

Фраза HAVING. Накладывает условия на группы.

Мы также можем применять условия и к группам, созданным фразой GROUP BY. Это делается при помощи фразы HAVING. Предположим, на­пример, что мы решили конкретизировать один из предыдущих запросов:

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

Мы можем отразить это условие соответствующей командой HAVING:

SELECT SUPV_ID, MAX(HRLY_RATE)

FROM WORKER GROUP BY SUPV_ID

HAVING COUNT(*) > 1

Разница между фразами WHERE и HAVING состоит в том, что WHERE применяется к строкам, в то время как HAVING применяется к группам.

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

Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1. Рассматривать только те типы зданий, максимальный уровень качества которых не превышает 3.

SELECT TYPE, AVG (QLTY_JLEVEL)

WHERE STATUS = 1

Задание 5. Выполните следующие запросы к базе данных «Перевозки».

  1. Каков максимальный вес груза, доставленного в каждый город?
  2. Для каждого города с населением свыше 1 миллиона человек выяс­нить минимальный вес груза, отправленного в этот город.
  3. Для каждого города, в который отправлено не менее трёх грузов, выяснить средний вес груза, отправленного в этот город.

7. Встроенные функции и подзапросы

Встроенные функции могут использоваться только во фразе SELECT или в команде HAVING. Однако фраза SELECT, содержащая встроенную функ­цию, может быть частью подзапроса. Рассмотрим пример такого подзапроса:

Запрос: У кого из работников почасовая ставка выше среднего?


Обратите внимание, что подзапрос не коррелирует с главным запросом. Подзапрос выдает ровно одно значение — среднюю почасовую ставку. Глав­ный запрос выбирает работника только в том случае, если его ставка больше вычисленной средней.

В коррелированных запросах также могут использоваться встроенные функции:

Запрос: У кого из работников почасовая ставка выше средней почасовой ставки среди подчиненных того же менеджера?

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

SELECT A. WORKER_NAME

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

9. Операции изменения данных

В SQL есть операции изменения данных INSERT, UPDATE и DELETE, позволяющие, соответственно, добавлять строки, изменять значения в стро­ках и удалять строки определенной таблицы в базе данных. Каждую из опе­раций мы обсудим отдельно.

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

INSERT INTO ASSIGNMENT (WORKER_ID, BLDG_ID, START DATE)

VALUES (1284, 485, 13.05)

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

Предположим, что мы создали новую таблицу с именем BUILDING_2, состоящую из столбцов BLDG_ID, TYPE и QLTY_LEVEL (УРОВ_КАЧЕСТВА), и хотим заполнить эту таблицу строками из таблицы BUILDING, имеющими STATUS 2. Тогда мы воспользуемся второй формой команды INSERT:

INSERT INTO BUILDING_2

SELECT BLDG_ID, TYPE, QLTY_LEVEL

WHERE STATUS = 2

INSERT. Операция, добавляющая строки к таблице.

UPDATE. Операция UPDATE (изменить) всегда применяется ко всем строкам, удовлетворяющим условию выражения WHERE. Если мы хотим повысить на 5 процентов ставку каждого подчиненного начальника 1520, то нам потребуется следующее выражение:

SET HRLY_RATE = 1.05 * HRLY_RATE

Если фраза WHERE отсутствует, то операция применяется к каждой строке таблицы. Например, если бы мы хотели повысить на 5 процентов ставку каждого работника, то мы должны были бы просто опустить команду WHERE в выражении UPDATE.

UPDATE. Операция, изменяющая значения столбцов в строке.

DELETE. Операция DELETE (удалить) также применяется ко всем стро­кам, удовлетворяющим условию WHERE. Если фраза WHERE отсутствует. то удаляются все строки таблицы. Допустим, что все рабочие, чей начальник имеет индекс 1520, были уволены, и мы хотим удалить соответствующие строки из базы данных. Это сделает следующее выражение:

DELETE FROM WORKER

DELETE.Операция, удаляющая строки из таблицы.

Не нашли то, что искали? Воспользуйтесь поиском:


Лучшие изречения: Учись учиться, не учась! 10385 — | 7888 — или читать все.

188.64.174.135 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)

очень нужно

Операции группировки GROUP BY в запросах SQL к базе данных Oracle

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

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

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

Использование конструкции GROUP BY с операцией ROLLUP

Как с помощью конструкции GROUP BY получать промежуточные итоговые значения (subtotals), уже было показано. За счет использования конструкции GROUP BY с операцией ROLLUP , однако, можно получать как промежуточные итоговые, так и общие суммарные (totals) значения и, следовательно, генерировать промежуточные агрегатные значения на любом уровне. Другими словами, операция ROLLUP позволяет получать агрегатные значения для каждой группы на отдельных уровнях. Промежуточные итоговые строки и конечные суммарные строки называются суперагрегатными строками (superaggregate rows).

В листинге ниже приведен пример применения конструкции GROUP BY с операцией ROLLUP .

Использование конструкции GROUP BY с операцией CUBE

Операцию CUBE можно считать расширением операции ROLLUP , поскольку она помогает расширять стандартные возможности конструкции GROUP BY в Oracle. Она вычисляет все возможные варианты промежуточных итоговых значений в операции GROUP BY . В предыдущем примере операция ROLLUP вернула промежуточные итоговые значения по годам. За счет использования операции CUBE можно получить итоговые значения не только по годам, но и по всей стране. Ниже приведен простой пример:

Использование конструкции GROUP BY с операцией GROUPING

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

Использование конструкции GROUP BY с операцией GROUPING SETS

Операция GROUPING SETS позволяет распределять множество наборов столбцов по группам при вычислении агрегатных показателей вроде сумм. Ниже приведен пример, демонстрирующий применение этой операции для вычисления агрегатных показателей с их последующим распределением по трем таким группам: ( year , region, item ), ( year, item ) и ( region, item ). Операция GROUPING SETS устраняет необходимость в использовании неэффективных операций UNION ALL .

Использование конструкции GROUP BY с операцией HAVING

Операция HAVING позволяет ограничивать или исключать результаты операции GROUP BY , т.е., по сути, накладывать на результирующий набор GROUP BY условие WHERE . В следующем примере операция HAVING ограничивает результаты запроса только теми отделами, в которых максимальная зарплата превышает 20 000:

в чем разница между GROUP BY и ORDER BY в sql

когда вы используете что вообще? Примеры приветствуются!

Я имею в виду так MySql, но не могу представить, что концепция отличается от другой СУБД

11 ответов

ORDER BY изменяет порядок, в котором возвращаются элементы.

GROUP BY будет агрегировать записи по указанным столбцам, что позволяет выполнять агрегационные функции для некрупных столбцов (таких как SUM, COUNT, AVG и т. д.).

ORDER BY изменяет порядок, в котором возвращаются элементы.

GROUP BY будет агрегировать записи по указанным столбцам, что позволяет выполнять агрегационные функции для некрупных столбцов (таких как SUM, COUNT, AVG и т. д.).


ЗАКАЗ: сортировка данных в порядке возрастания или убывания.

рассмотрим клиенты стол:

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

это приведет к следующему результату:

ГРУППЫ: упорядочить идентичные данные в группы.

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

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

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

как вы заключили,что он бесполезен без SQL-функций, таких как sum, avg и т. д..

так пройти через это определение поймите правильное использование GROUP BY:

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

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

это приведет к следующему результату: (сумма зарплат идентичных имен и сортировка столбца имени после удаления идентичных имен)

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

Если у вас SELECT * FROM Customers ORDER BY Name затем вы получаете список результатов, отсортированный по имени клиентов.

Если у вас SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActive вы получаете количество активных и неактивных клиентов. Группа по агрегирует результаты на основе указанного поля.

Они имеют совершенно другое значение и на самом деле не связаны вообще.

ПРИКАЗОМ позволяет сортировать результирующий набор по различным критериям, таким как первая сортировка по имени из a-z, а затем сортировка по цене от самой высокой до самой низкой.

(заказ по названию, цена DESC)

ГРУППА ПО позволяет взять результирующий набор, сгруппировать его в логические группы, а затем выполнить агрегированные запросы к этим группам. Вы можете, например, выбрать все сотрудники, сгруппировать их по месту их работы и рассчитать среднюю зарплату всех сотрудников каждого места работы.

простой ORDER BY заказывает данные и GROUP BY группы, или объединяет данные.

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

Предположим, вы запускаете запрос как ORDER BY (student_roll_number) , он покажет вам результат в порядке возрастания числа студентов ролл. Вот, student_roll_number запись может произойти более одного раза.

на GROUP BY case, мы используем это с агрегатными функциями, и он группирует данные согласно агрегату функции, и мы получаем результат. Здесь, Если наш запрос имеет SUM (marks) вместе с GROUP BY (student_first_name) он покажет сумму баллов студентов, принадлежащих к каждой группе (где все члены группы будут иметь одинаковое имя).

GROUP BY используется для группировки строк в select, обычно при агрегировании строк(например, вычислении итогов, средних и т. д. для набора строк с одинаковыми значениями некоторых полей).


ORDER BY используется для упорядочения строк, полученных из инструкции select.

некоторые хорошие примеры там. Так же, как добавить свой собственный из webcheatsheet, который дает хорошие четкие примеры, а также позволяет выполнять собственный SQL.

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

  1. GROUP BY будет агрегировать записи по указанному столбцу, что позволяет выполнять агрегационные функции для негруппированных столбцов (например, SUM, COUNT, AVG и т. д.). ORDER BY изменяет порядок, в котором возвращаются элементы.
  2. Если у вас Выберите IsActive, COUNT (*) из группы клиентов по IsActive вы получаете количество активных и неактивных клиентов. Группа по агрегирует результаты на основе указанного поля. Если у вас Выберите * из заказа клиентов по имени затем вы получаете список результатов, отсортированных по имени клиента.
  3. Если вы группируете, результаты не обязательно сортируются; хотя во многих случаях они могут выходить в интуитивном порядке, это не гарантируется предложением GROUP. Если вы хотите, чтобы ваши группы были отсортированы, всегда используйте явный порядок после GROUP BY.
  4. сгруппированные данные не могут быть отфильтрованы по предложению WHERE. Данные заказа могут быть отфильтрованы по предложению WHERE.

стоит отметить GROUP BY не всегда необходимо, так как (по крайней мере, в PostgreSQL и, вероятно, в других вариантах SQL) вы можете использовать ORDER BY список и можно использовать ASC или DESC per.

SQL-запросы. Группировка данных Group By и Having

В рамках данной статьи я расскажу вам о том, как осуществляется группировка данных, как правильно применять group by и having внутри SQL-запросов на примере нескольких запросов.

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

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

В рамках примера я буду рассматривать всего лишь одну таблицу. Причина проста, эти операторы применяются уже к полученной выборке данных (после объединения строк таблиц и их фильтрации). Так что от добавления операторов where и join суть не поменяется.

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

user_name forum_group raiting mess_count is_have_social_profile
Польз 1 admin 2 10 1
Польз 2 admin 5 15
Польз 3 admin 5 25 1
Польз 4 moder 4 20 1
Польз 5 moder 4 20 1
Польз 6 moder 1 10
Польз 7 user 4 10
Польз 8 user 3 100
Польз 9 user 3 15
Польз 10 user 2 25 1

user_name — имя пользователя

forum_group — имя группы

raiting — итоговый рейтинг пользователя

mess_count — количество сообщений

is_have_social_profile — указан ли в профиле форуме ссылка на страничку в социальной сети

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

Чистая группировка с помощью group by

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

Вначале, небольшое словесное описание, чтобы легче было понимать SQL-запрос. Итак, вам нужно найти вычисляемые значения по группам форума. Соответственно, вам нужно поделить все эти десять строк на три разные группы: admin, moder, user. Чтобы это сделать, нужно в конце запроса добавить группировку по значениям поля forum_group. А так же добавить в select вычисляемые выражения с использованием так называемых агрегатных функций.


Вот как будет выглядеть SQL-запрос

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

Так же я воспользовался двумя агрегатными функциями. AVG — вычисляет среднее значение. И SUM — вычисляет сумму.

Вот какой получился результат:

forum_group avg_raiting total_mess_count
admin 4 50
moder 3 50
user 3 150

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

1. Вначале все строки исходной таблицы были разбиты на три группы по значениям поля forum_group. Например, внутри группы admin было три пользователя. Внутри moder так же 3 строки. А внутри группы user было 4 строки (четыре пользователя).

2. Затем для каждой группы применялись агрегатные функции. Например, для группы admin средний рейтинг вычислялся так (2 + 5 + 5)/3 = 4. Количество сообщений вычислялось так (10 + 15 + 25) = 50.

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

Группировка с помощью group by и фильтрацией групп с having

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

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

Примечание: Стоит знать, что эту задачу можно было бы решить и с помощью группировки только по одному полю. Если использовать конструкцию case. Однако, в рамках данного примера показываются возможности именно группировки.

Так же хотел бы сразу уточнить один важный момент. Фильтровать с помощью having можно только при применении агрегатных функций, а не по отдельным полям. Другими словами, это не конструкция where, это фильтр именно групп строк, а не отдельных записей. Хотя условия внутри задаются аналогичным образом с помощью «or» и «and».

Вот как будет выглядеть SQL-запрос

Обратите внимание, что поля после конструкции group by указываются через запятую. Указание полей в select происходит аналогичным образом, как и в предыдущем примере. Так же я воспользовался агрегатной функцией count, которая вычисляет количество строк в группах.

Вот какой получился результат:

forum_group is_have_social_profile total
admin 1 2
moder 1 2
user 3

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

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

1. Вначале было получено 6 групп. Каждая из групп по forum_group была разбита на две подгруппы по значениям поля is_have_social_profile. Другими словами группы: [admin, 1], [admin, 0], [moder, 1], [moder, 0], [user, 1], [user, 0].

Примечание: Кстати, групп не обязательно должно было бы получится 6. Так, к примеру, если бы все администраторы заполнили профиль, то групп было бы 5, так как поле is_have_social_profile имело бы только одно значение у пользователей группы admin.

2. Затем для каждой группы было применено условие фильтрации в having. Поэтому были исключены следующие группы: [admin, 0], [moder, 0], [user, 1]. Так как внутри каждой такой группы присутствовала всего одна строка исходной таблицы.

3. После этого были вычислены необходимые данные и был получен результат.

Как видите, ничего сложно в использовании нет.


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

Теперь, вы знаете как применять группировку данных с group by, а так же как фильтровать группы с помощью having.

Как работает GROUP BY в MySQL?

Вопрос по sql по клаузуле GROUP BY .

Рассмотрим группировку по ОДНОМУ столбцу. Пример:

То есть, в столбце DEPARTMENT_ID ищется уникальное (похоже на DISTINCT ) значение отдела, например, 30, затем ищутся все строки, где упоминается отдел 30 в данной таблице, из этих строк берутся значения из столбца SALARY и суммируются ( SUM ). Потом ищется другой покупатель и все повторяется. В итоге я получаю сколько получил вообще денег каждый отдел.

Не понимаю момент: у меня есть 6 строк, в которых есть столбец DEPARTMENT_ID со значением 30. Какая из строк пойдет в таблицу- SELECT и почему? То есть, в таблице Employees было шесть строк с DEPARTMENT_ID 30, а в таблице- SELECT такая строка только одна. Как вообще эта группировка работает?

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

2 ответа 2

Добавлю с примером запросов и вывода GROUP BY по двух полях. Смотреть можна по таблице в которую например сохраняеться какой пользователь(user_id) вносил деньги, на какой счет(account) и сколько(balance). Например нужно узнать сколько каждый пользователь внес на каждый со своих счетов.

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

В выборку после group by не попадет ни одна из изначальных строк. На выходе агрегат — сумма данных в нужном разрезе. К колонкам, к которым вы явно не применили никаких групповых функций (таких как sum() ), будет применена функция «первое попавшееся». Причем только в MySQL и только при выключенной опции ONLY_FULL_GROUP_BY . В остальных СУБД запрос, в котором хотя бы к одной колонке, не являющейся разрезом указанным в group by, «забыли» применить групповую функцию выдадут ошибку.

Как работает group by можно прикинуть в экселе. Запишите данные на лист, отсортируйте по тем полям, которые должны быть в group by . Читая отсортированные данные подряд в любом случае когда значение в очередной строке в колонках, указанных в group by отличается от значений в предыдущей — вставьте новую строку, скопируйте значения колонок group by , а в остальные поместите формулы вроде СУММ() ячеек группы под которой подводится итог. Результат group by — это именно эти вставленные итоговые записи. СУБД работает примерно по такому же алгоритму — сначала сортирует, потом суммирует идущие подряд одинаковые записи.

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

SQL Не выражение GROUP BY

Я все еще новичок в SQL.

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

Я получаю ошибку «не выражение GROUP BY». Может кто-то помочь мне, пожалуйста?

решить проблему group by не зная ожидаемого результата

но я думаю, что он пытается это сделать

Ошибка связана с тем, что у вас есть коррелированный запрос. Предложение корреляции ( where в подзапросе) использует столбец из внешнего запроса, который не агрегирован. Кроме того, у вас есть topic столбца, которой нет в group by .

Я считаю, что запрошенный запрос более просто написан как:

Я замечаю, что у вас есть topic в select . Это тоже проблема. Возможно, вы хотите:

Или, если у вас есть studentid в lecture , возможно:

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

Sql фраза group by

Выражение GROUP BY в запросах Oracle SQL, группировка записей в результатах запросов

Задание:

Напишите запрос, который бы возвращал информацию о максимальной, минимальной и средней заработной плате для каждой должности в таблице employees в схеме hr . Информация о заработной плате находится в столбце salary , а информация о должности — в таблице job _ id . Результат выполнения запроса должен выглядеть так, как представлено на рис. 5.2-1

Решение:

Код соответствующего запроса может выглядеть так:

Илон Маск рекомендует:  Адаптивная модель вpемя выполнения
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL