Group by order by: php — Вывод из group by с order by
Содержание
в чем разница между GROUP BY и ORDER BY в sql
ORDER BY: отсортируйте данные в порядке возрастания или убывания.
Рассмотрим таблицу CUSTOMERS :
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+Ниже приведен пример сортировки результатов в порядке возрастания по ИМЕНИ:
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME;Это даст следующий результат:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+GROUP BY: объедините идентичные данные в группы.
Теперь в таблице CUSTOMERS есть следующие записи с повторяющимися именами:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Ramesh | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | kaushik | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+если вы хотите сгруппировать одинаковые имена в одно имя, запрос GROUP BY будет следующим:
SQL> SELECT * FROM CUSTOMERS
GROUP BY NAME;Это даст следующий результат: (для одинаковых имен он выбирает последнее и, наконец, сортирует столбец в порядке возрастания)
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 4 | kaushik | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 2 | Ramesh | 25 | Delhi | 1500.00 |
+----+----------+-----+-----------+----------+как вы сделали вывод, что он бесполезен без функций SQL, таких как sum, avg и т. д.
поэтому просмотрите это определение, чтобы понять правильное использование GROUP BY:
Предложение GROUP BY работает со строками, возвращаемыми запросом, суммируя идентичные строки в одну / отдельную группу и возвращает одну строку со сводкой для каждой группы, используя соответствующую функцию Aggregate в списке SELECT, например COUNT (), SUM (), MIN (), MAX (), AVG () и т. Д.
Теперь, если вы хотите узнать общую сумму зарплаты каждого клиента (имя), запрос GROUP BY будет следующим:
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;Это даст следующий результат: (сумма зарплат идентичных имен и сортировка столбца ИМЯ после удаления идентичных имен)
+---------+-------------+
| NAME | SUM(SALARY) |
+---------+-------------+
| Hardik | 8500.00 |
| kaushik | 8500.00 |
| Komal | 4500.00 |
| Muffy | 10000.00 |
| Ramesh | 3500.00 |
+---------+-------------+Разница между группами By и Order By в SQL — Технология
Содержание:
SQL позволяет систематизировать данные, полученные по запросу. У нас есть два предложения для организации данных, полученных из запроса: предложения Group By и Order By. Пункт, который отличает предложения Group By и Order By, заключается в том, что Группа по Предложение используется, когда мы хотим применить агрегатную функцию к нескольким наборам кортежей и Сортировать по Предложение используется, когда мы хотим отсортировать данные, полученные запросом. Давайте обсудим некоторые различия между предложением Group By и предложением Order By с помощью сравнительной таблицы, показанной ниже.
- Сравнительная таблица
- Определение
- Ключевые отличия
- Вывод
Сравнительная таблица
| Основа для сравнения | Группа по | Сортировать по |
|---|---|---|
| основной | Group By используется для формирования группы из набора кортежей. | Упорядочить по используется для упорядочения данных, полученных в результате запроса, в отсортированной форме. |
| атрибут | Атрибут в функции Aggregate не может быть в предложении Group By. | Атрибут в совокупности может быть в порядке по предложению. |
| земля | Сделано на основании сходства значений атрибутов. | Сделано на основании восходящего и нисходящего порядка. |
Определение группы по пункту
Агрегатные функции, такие как avg, min, max, sum, count, применяются к одному набору кортежей. В случае, если вы хотите применить агрегатные функции к группе из набора кортежей, у нас есть предложение Group by для этого. Сгруппировать по группам группирует кортежи с одинаковым значением атрибута
Есть одна вещь, чтобы помнить о предложении Group By, убедитесь, что атрибут под Группа по пункт должен появиться в ВЫБРАТЬ оговорка но не под агрегатная функция, Если предложение Group By содержит атрибут, который находится не в предложении SELECT, или если он находится в предложении SELECT, но под агрегатной функцией, запрос становится ошибочным. Следовательно, мы можем сказать, что предложение Group By всегда используется в сотрудничестве с предложением SELECT.
Давайте возьмем пример для понимания предложения Group By.
ВЫБЕРИТЕ Отдел _ID, avg (Зарплата) как avg_salary из Учительской группы По Department_ID.
Вы можете видеть, что изначально формируется промежуточный результат, который сгруппировал отделы.
Далее, агрегатная функция avg применяется к каждой группе отделов, и результат показан ниже.
Определение порядка по пунктам
Предложение Order By используется для отображения данных, полученных по запросу, в отсортированном порядке. Как и предложение Group By, предложение Order By также используется в сотрудничестве с предложением SELECT. Если вы не упомянули порядок сортировки, предложение Order By сортирует данные в порядке возрастания. Вы можете указать возрастающий порядок как по возрастанию и в порядке убывания по убыванию.
Давайте разберемся с работой пункта Order By с помощью следующего примера. У нас есть таблица учителя, и я буду применять сортировку к двум столбцам Department_Id и Salary таблицы учителя.
Выберите Department_ID, Заработная плата из заказа учителя по Department_Id asc, Зарплата desc.
Вы можете видеть, что сначала он упорядочивает _ID отдела в порядке возрастания, а затем упорядочивает оклады в том же отделе в порядке убывания.
- Предложение Group By группирует набор кортежей в отношении, которые находятся в предложении SELECT. С другой стороны, предложение Order By сортирует результат запроса в порядке возрастания или убывания.
- Атрибут в агрегатной функции не может быть в предложении Group By, тогда как атрибут в агрегатной функции может быть в предложении Order By.
- Группировка кортежей выполняется на основе сходства значений атрибутов кортежей. С другой стороны, упорядочение или сортировка выполняется на основе возрастающего или убывающего порядка.
Вывод:
Если вы хотите сформировать группу из набора кортежей, то вы должны использовать предложение Group By. Если вы хотите упорядочить данные одного столбца или нескольких столбцов в наборе кортежей в порядке возрастания или убывания, то следует использовать предложение Order By.
Разбираемся с Group by в Django с SQL
Агрегация является источником путаницы в любом типе ORM, и Django ничем не отличается. Документация содержит множество примеров и шпаргалок, которые демонстрируют, как группировать и агрегировать данные с помощью ORM, но я решил подойти к этому с другой стороны.
В этой статье я поставил QuerySets и SQL рядом и рассказать о каждом типе группировке отдельно.
Table of Contents
Как работает Group By в Django
Для демонстрации различных запросов GROUP BY я буду использовать модель User из встроенного в Django приложения django.contrib.auth.
>>> from django.contrib.auth.models import User
Django ORM создает операторы SQL с длинными псевдонимами. Для краткости я буду показывать очищенную, но эквивалентную версию того, что использует Django.
SQL LOGGING
Чтобы увидеть, какой SQL фактически выполняется в Django, вы можете включить ведение журнала SQL в настройках Django (turn on SQL logging in the Django settings).
Как считать строки
Давайте посчитаем, сколько у нас пользователей:
>>> User.objects.count() 20
SELECT
COUNT(*)
FROM
auth_user;Подсчет строк настолько распространен, что Django включает специальную функцию count для него прямо в QuerySet. В отличие от других QuerySets, которые мы увидим далее, count возвращает число.
Как использовать агрегатные функции
Django предлагает еще два способа подсчета строк в таблице.
Начнем с aggregate:
from django.db.models import Count
>>> User.objects.aggregate(Count('id'))
{'id__count': 20}
SELECT
COUNT(id) AS id__count
FROM
auth_user;
Для использования aggregate мы импортировали функцию агрегирования Count. Функция принимает выражение для подсчета. В нашем случае мы использовали имя идентификатора столбца id для подсчета всех строк в таблице.
AGGREGATE NULL
Агрегаты игнорируют значения NULL. Подробнее о том, как агрегаты обрабатывают NULL, см. В разделе 12 Common Mistakes and Missed Optimization Opportunities in SQL.
Результатом aggregate будет словарь:
>>> from django.db.models import Count
>>> User.objects.aggregate(Count('id'))
{'id__count': 20}
Имя ключа происходит от имени поля и имени агрегата. В данном случае это id__count. Рекомендуется не полагаться на это соглашение об именах, а вместо этого указывать свое имя:
SELECT
COUNT(id) as total
FROM
auth_user;
>>> from django.db.models import Count
>>> User.objects.aggregate(total=Count('id'))
{'total': 20}
Имя аргумента для aggregate — это также имя ключа в результирующем словаре.
Как использовать Group By
Используя aggregate, мы получили результат применения агрегатной функции ко всей таблице. Это полезно, но обычно мы хотим применить агрегацию к группам строк.
Давайте посчитаем пользователей по их активному статусу:
(User.objects
.values('is_active')
.annotate(total=Count('id')))
<QuerySet [{'is_active': True, 'total': 20}]>SELECT
is_active,
COUNT(id) AS total
FROM
auth_user
GROUP BY
is_active
На этот раз мы использовали функцию annotate. Для создания GROUP BY мы используем комбинацию values и annotate:
values('is_active'): что группироватьannotate(total=Count('id')): что агрегировать
Порядок важен: невозможность вызова values до annotate не приведет к агрегированным результатам.
Как и aggregate, имя аргумента для annotate является ключом в результате оцененного QuerySet. В этом случае это total.
Как использовать Filter в QuerySet с Group By
Чтобы применить агрегацию к отфильтрованному запросу, вы можете использовать filter в любом месте запроса. Например, учитывайте только штатных пользователей по их активному статусу:
(User.objects
.values('is_active')
.filter(is_staff=True)
.annotate(total=Count('id')))
<QuerySet [{'is_active': True, 'total': 1}]>SELECT
is_active,
COUNT(id) AS total
FROM
auth_user
WHERE
is_staff = True
GROUP BY
is_active
Как сортировать QuerySet с Group By
Как и filter, для сортировки набора запросов используйте order_by в любом месте запроса:
(User.objects
.values('is_active')
.annotate(total=Count('id'))
.order_by('is_staff', 'total'))
<QuerySet [{'is_active': True, 'total': 19}, {'is_active': True, 'total': 1}]>SELECT
is_active,
COUNT(id) AS total
FROM
auth_user
GROUP BY
is_active
ORDER BY
is_active,
total
Обратите внимание, что вы можете сортировать как по ключу GROUP BY, так и по полю агрегирования.
Как комбинировать несколько Aggregations
Чтобы создать несколько агрегатов одной группы, добавьте несколько аннотаций:
from django.db.models import Max
(User.objects
.values('is_active')
.annotate(
total=Count('id'),
last_joined=Max('date_joined'),
))
<QuerySet [{'is_active': True, 'total': 20, 'last_joined': datetime.datetime(2020, 2, 20, 12, 40, 28, tzinfo=<UTC>)}]>SELECT
is_active,
COUNT(id) AS total,
MAX(date_joined) AS last_joined
FROM
auth_user
GROUP BY
is_active
В запросе будет указано количество активных и неактивных пользователей и последняя дата, когда пользователь присоединился к каждой группе.
Как группировать несколько полей
Так же, как и при выполнении нескольких агрегаций, мы также можем сгруппировать по нескольким полям. Например, сгруппировать по активному статусу и статусу персонала:
(User.objects
.values('is_active', 'is_staff')
.annotate(total=Count('id')))
<QuerySet [{'is_active': True, 'is_staff': False, 'total': 19}, {'is_active': True, 'is_staff': True, 'total': 1}]>SELECT
is_active,
is_staff,
COUNT(id) AS total
FROM
auth_user
GROUP BY
is_active,
is_staff
Результат этого запроса включает is_active, is_staff и количество пользователей в каждой группе.
Как группировать с «Выражением» (Expression)
Другим распространенным вариантом использования GROUP BY является группирование по выражению. Например, подсчет количество пользователей, которые присоединились каждый год:
(User.objects
.values('date_joined__year')
.annotate(total=Count('id')))
<QuerySet [{'date_joined__year': 2020, 'total': 20}]>SELECT
EXTRACT('year' FROM date_joined),
COUNT(id) AS total
FROM
auth_user
GROUP BY
EXTRACT('year' FROM date_joined)
Обратите внимание, что для получения года от даты мы использовали специальное выражение <field>__year при первом вызове values(). Результатом запроса является dict, а имя ключа будет date_joined__year.
Иногда встроенных выражений недостаточно, и вам нужно объединиться в более сложное выражение. Например, группировка по пользователям, которые вошли в систему с момента регистрации:
from django.db.models import (
ExpressionWrapper,
Q, F, BooleanField,
)
(User.objects
.annotate(
logged_since_joined=ExpressionWrapper(
Q(last_login__gt=F('date_joined')),
output_field=BooleanField(),
)
)
.values('logged_since_joined')
.annotate(total=Count('id'))
.values('logged_since_joined', 'total'))
<QuerySet [{'logged_since_joined': 2, 'total': 19}, {'logged_since_joined': True, 'total': 1}]>SELECT last_login > date_joined AS logged_since_joined, COUNT(id) AS total FROM auth_user GROUP BY last_login > date_joined
Выражение здесь довольно сложное. Сначала мы используем annotate для построения выражения и помечаем его как ключ GROUP BY, ссылаясь на выражение через logged_since_joined в следующем вызове values(). А далее уже действуем как обычно.
Как использовать условную агрегацию
Используя условное агрегирование, вы можете агрегировать только часть группы. Условия пригодятся, когда у вас есть несколько агрегатов. Например, подсчитайте количество сотрудников и пользователей, не являющихся сотрудниками, за год, когда они зарегистрировались:
from django.db.models import F, Q
(User.objects
.values('date_joined__year')
.annotate(
staff_users=(
Count('id', filter=Q(is_staff=True))
),
non_staff_users=(
Count('id', filter=Q(is_staff=False))
),
))
<QuerySet [{'date_joined__year': 2020, 'staff_users': 1, 'non_staff_users': 19}]>SELECT
EXTRACT('year' FROM date_joined),
COUNT(id) FILTER (
WHERE is_staff = True
) AS staff_users,
COUNT(id) FILTER (
WHERE is_staff = False
) AS non_staff_users
FROM
auth_user
GROUP BY
EXTRACT('year' FROM date_joined)
Приведенный выше SQL взят из PostgreSQL, который наряду с SQLite в настоящее время является единственным бэкэндом базы данных, который поддерживает ярлык синтаксиса FILTER (формально называемый «выборочные агрегаты» «selective aggregates»). Для других серверных баз данных ORM вместо этого будет использовать CASE … WHEN.
Совет
Я ранее писал про агрегаты с фильтрами. Посмотрите мои 9 советов по Django для работы с базами данных (9 Django tips for working with databases).
Как использовать Having
Предложение HAVING используется для фильтрации результатов статистической функции. Например, найдите годы, в которые вступили более 100 пользователей:
(User.objects
.annotate(year_joined=F('date_joined__year'))
.values('is_active')
.annotate(total=Count('id'))
.filter(total__gt=100))
SELECT
is_active,
COUNT(id) AS total
FROM
auth_user
GROUP BY
is_active
HAVING
COUNT(id) > 100
Фильтр по аннотированному итоговому полю total добавил предложение HAVING в сгенерированный SQL.
Как группировать по Distinct
Для некоторых агрегатных функций, таких как COUNT, иногда желательно учитывать только определенные случаи. Например, сколько разных фамилий существует для каждого активного статуса пользователя:
(User.objects
.values('is_active')
.annotate(
total=Count('id'),
unique_names=Count('last_name', distinct=True),
))
<QuerySet [{'is_active': True, 'total': 20, 'unique_names': 7}]>SELECT
is_active,
COUNT(id) AS total,
COUNT(DISTINCT last_name) AS unique_names
FROM
auth_user
GROUP BY
is_active
Обратите внимание на использование distinct=True в вызове Count.
Как создавать выражения с использованием агрегированных полей
Агрегатные поля часто являются лишь первым шагом к большему вопросу. Например, каков процент уникальных фамилий по активному статусу пользователя:
from django.db.models import FloatField
from django.db.models.functions import Cast
(User.objects
.values('is_active')
.annotate(
total=Count('id'),
unique_names=Count('last_name', distinct=True),
)
.annotate(pct_unique_names=(
Cast('unique_names', FloatField())
/ Cast('total', FloatField())
)))
<QuerySet [{'is_active': True, 'total': 20, 'unique_names': 7, 'pct_unique_names': 0.35}]>SELECT
is_active,
COUNT(id) AS total,
COUNT(DISTINCT last_name) AS unique_names,
(COUNT(DISTINCT last_name)::float
/ COUNT(id)::float) AS pct_unique_names
FROM
auth_user
GROUP BY
is_active
Первый annotate() определяет совокупные поля. Второй annotate() использует статистическую функцию для создания выражения.
Как группировать по различным отношениям
До сих пор мы использовали только данные в одной модели, но агрегаты часто используются в отношениях. Более простой сценарий — отношение «один к одному» или отношение внешнего ключа. Например, скажем, у нас есть UserProfile с отношением «один к одному» с User, и мы хотим подсчитывать пользователей по типу профиля:
(User.objects
.values('user_profile__type')
.annotate(total=Count('id')))
SELECT
p.type,
COUNT(u.id) AS total
FROM
auth_user u
JOIN user_profile p ON u.id = p.user_id
GROUP BY
p.type
Как и выражения GROUP BY, использование отношений в values будет группировать по этому полю. Обратите внимание, что имя типа профиля пользователя в результате будет «user_profile__type».
Как группировать отношения «многие ко многим»
Более сложный тип отношений — это отношение «многие ко многим». Например, подсчитайте, во сколько групп входит каждый пользователь:
(User.objects
.annotate(memberships=Count('groups'))
.values('id', 'memberships'))
<QuerySet [{'id': 14, 'memberships': 0}, {'id': 17, 'memberships': 0}, {'id': 12, 'memberships': 0}, {'id': 8, 'memberships': 0}, {'id': 15, 'memberships': 0}, {'id': 10, 'memberships': 0}, {'id': 11, 'memberships': 0}, {'id': 18, 'memberships': 0}, {'id': 16, 'memberships': 0}, {'id': 6, 'memberships': 0}, {'id': 19, 'memberships': 0}, {'id': 2, 'memberships': 0}, {'id': 3, 'memberships': 0}, {'id': 23, 'memberships': 0}, {'id': 13, 'memberships': 0}, {'id': 5, 'memberships': 0}, {'id': 22, 'memberships': 0}, {'id': 9, 'memberships': 0}, {'id': 24, 'memberships': 0}, {'id': 7, 'memberships': 0}]>SELECT
u.id,
COUNT(ug.group_id) AS memberships
FROM
auth_user
LEFT OUTER JOIN auth_user_groups ug ON (
u.id = ug.user_id
)
GROUP BY
u.id
Пользователь может быть членом более чем одной группы. Для подсчета количества групп, членом которых является пользователь, мы использовали связанное имя «groups» в модели User. Если связанное имя не задано явно (и явно не отключено), Django автоматически сгенерирует имя в формате {related model model}_set. Например, group_set.
Заключение
Для более глубокого изучения ORM и GROUP BY, просмотрите следующие ссылки:
Оригинальная статья: Understand Group by in Django with SQL
Была ли вам полезна эта статья?
[4 / 4]
2.12. Группировка – Group By
Очень интересных эффектов можно добиться, если использовать математику вместе с GROP_BY. Что если нужно посчитать, количество различных имен в таблице tbPeoples. Как же это можно сделать?
Если использовать только ту информацию, которую мы уже знаем, то проблема решается достаточно сложно. Для начала мы должны определить уникальные имена, которые существуют в таблице:
SELECT DISTINCT vcName FROM tbPeoples
После этого нужно определить количество каждого имени в таблице. Например, количество Андреев можно узнать следующим образом:
SELECT count(*) FROM tbPeoples WHERE vcName='Андрей'
Но это сложно и требует ручного вмешательства. Конечно же, можно было бы использовать подзапросы для определения количества без вмешательства, но это будет сложно, и подзапросы мы еще не рассматривали. Самое простое решение кроется как раз в операторе GROUP BY. Рассмотрим эту возможность на примере:
SELECT vcName, count(*) FROM tbPeoples GROUP BY vcName
Оператор GROUP BY группирует записи по указанным после оператора через запятую именам колонок. После оператора SELECT нужно перечислить те же имена колонок и математическую функцию, которую вы хотите использовать. В данном случае используется функция COUNT для подсчета количества строк в группе. Итак, в нашей таблице несколько Андреев, в запросе они объединяются в группу и в результате выводиться на экран количество строк в этой группе.
Результат выполнения запроса:
АНДРЕЙ 5 БОЛИК 1 ВЛАД 1 ИВАН 3 ЛЕЛИК 1 СЕРГЕЙ 2 СЛАВИК 1 ...
В первой колонке показано имя работника, а во второй колонке количество записей с таким именем.
Посмотрим еще пример, давайте посчитаем, сколько раз встречаются в таблице одинаковые записи в поля имени и фамилии
SELECT vcFamil, vcName, count(*) FROM tbPeoples GROUP BY vcFamil, vcName
Результат – количество повторений из сочетания полей фамилия и имя. В моей тестовой таблице содержимое этих двух полей образуют уникальное значение, поэтому в колонке количества будет всегда единица.
Прежде чем рассматривать еще примеры, давайте узнаем, как можно сортировать строки по колонке количества записей:
SELECT vcName, count(*) FROM tbPeoples GROUP BY vcName ORDER BY count(*) DESC
В операторе ORDER BY без проблем можно писать функции. Чтобы сценарий был более красивым, лучше будет задать псевдоним для поля количества записей:
SELECT vcName, count(*) AS ct FROM tbPeoples GROUP BY vcName ORDER BY ct DESC
Чтобы лучше понять работу этого оператора, необходимо рассмотреть еще несколько примеров. Я сам не сразу же понял, как ей пользоваться, поэтому постараюсь вам показать максимум разных запросов, чтобы вы на практике увидели смысл их работы. Следующий запрос определяет количество повторений фамилий:
SELECT vcSurName, count(*) FROM tbPeoples GROUP BY vcSurName
Обратите внимание, что поля, которые указываются в группировке, обязательно присутствуют в операторе SELECT. Другие поля там не могут присутствовать. Например, следующий запрос будет неверен:
SELECT vcFamil, vcSurName, count(*) FROM tbPeoples GROUP BY vcSurName
Чтобы лучше понять почему, давайте разберем его работу. Допустим, что у нас есть таблица из двух колонок – фамилии и отчества:
ИВАНОВ ИВАНЫЧ ПЕТРОВ ПАЛЫЧ СИДОРОВ ПАЛЫЧ
Во время группировки по отчеству, вторая и третья строка должны восприниматься как одно целое, но какую из двух фамилий вывести в результате: Петров или Сидоров? Вот из-за этого в разделе SELECT должны быть только те поля, по которым происходит группировка.
Давайте посмотрим пример связанных таблиц. Допустим, что нам нужно определить, количество номеров телефонов для каждого пользователя. В этом случае, должно быть подсчитано, сколько записей в таблице tbPhoneNumbers соответствует каждой записи в таблице tbPeoples. Лучше будет сгруппировать по первичному ключу таблицы tbPeoples, потому что он обеспечивает уникальность строк, для которых нужно определить количество записей в другой таблице. Так как у нас группировка происходит по одной таблице, а количество считается по другой таблице, в группировку можно добавлять любые поля помимо ключевого. Например:
SELECT pl.idPeoples, vcFamil, vcSurName, COUNT(vcPhoneNumber) FROM tbPeoples pl, tbPhoneNumbers pn WHERE pl.idPeoples *= pn.idPeoples GROUP BY pl.idPeoples, vcFamil, vcSurName ORDER BY COUNT(vcPhoneNumber) DESC
Рассмотрим этот запрос. Я решил вывести на экран помимо первичного ключа еще и фамилию и имя. Все эти поля перечислены в разделе SELECT и GROUP BY. Можно взять и другие поля из таблицы tbPeoples, но только из этой таблицы. В разделе WHERE наводиться связь между таблицами, а в разделе ORDER BY мы сортируем количество найденных телефонов.
С помощью GROUP BY можно не только определять количество записей с помощью оператора COUNT, но и суммы. Вспомним, что в нашей базе данных есть еще таблица товаров из следующих полей: Дата покупки, Название товара, Цена, Количество. Давайте сгруппируем таблицу по названию и определим количество каждого товара:
SELECT [Название товара], SUM(Количество) FROM Товары GROUP BY [Название товара]
В этом примере с помощью группировки мы определили сумму по колонке с помощью оператора SUM.
С помощью секции HAVING очень удобно ограничивать вывод. Например, вам нужно вывести сумму количества товаров, но при этом должны отражаться только те записи, в которых количество более 1. Просто GROUP BY тут уже не поможет. Нужно добавить секцию HAVING, с нужным условием:
SELECT [Название товара], SUM(Количество) FROM Товары GROUP BY [Название товара] HAVING SUM(Количество)>1
В секции HAVING мы написали, что сумма товара (SUM(Количество)) должна быть более 1.
Теперь посмотрим, как с помощью HAVING можно решить классическую задачу поиска двойных записей. Допустим, что нужно вывести на экран фамилии, которые повторяются в таблице более одного раза. Просто для подсчета фамилий достаточно использовать секцию GROUP BY, но если добавить еще и HAVING, то можно будет отобразить только двойные записи:
SELECT vcFamil, count(*) FROM tbPeoples GROUP BY vcFamil HAVING count(*)>1
Где бы я не работал, и как бы хорошо не строилась база данных, приходится регулярно выявлять и избавляться от двойных записей, потому что они портят отчетность. В таких случаях группировка оказывается незаменимой. Да, можно вводить ограничения уникальности по тем полям, которые не должны двоиться, но не всегда это может оказаться эффективным решением.
Разница между предложением упорядочить по и группировать по в SQL
Разница между предложением упорядочить по и группировать по в SQL
1. Упорядочить по:
Упорядочить по ключевому слову отсортировать набор результатов в возрастающем или убывающем порядке. Это предложение по умолчанию сортирует набор результатов в возрастающем порядке. Для сортировки набора результатов в порядке убывания используется ключевое слово DESC .
Упорядочить по синтаксису —
ВЫБРАТЬ столбец_1, столбец_2, столбец_3........... ИЗ Имя_таблицы ORDER BY column_1, column_2, column_3 ....... ASC | DESC; Table_Name: Имя таблицы. ASC: ключевое слово для возрастания DESC: ключевое слово для убывания
2. Group By:
Оператор Group by используется для группировки строк с одинаковым значением. Он часто используется с агрегатными функциями, например: AVG (), MAX (), COUNT (), MIN () и т. Д. Одна вещь, которую следует помнить о предложении group by, заключается в том, что кортежи группируются на основе сходства между значениями атрибутов. кортежей.
Группировать по синтаксису —
ВЫБРАТЬ имя_функции (столбец_1), столбец_2 ИЗ Имя_таблицы ГДЕ условие ГРУППА ПО столбцу_1, столбцу_2 ЗАКАЗАТЬ столбец_1, столбец_2;
имя_функции: Имя агрегатной функции, например:
SUM (), AVG (), COUNT () и т. Д. Table_Name: Имя таблицы.
Давайте посмотрим на разницу между пунктом Order by и group by: —
| S.NO | GROUP BY | ORDER BY |
|---|---|---|
| 1. | Оператор Group by используется для группировки строк с одинаковым значением. | Принимая во внимание, что оператор «Упорядочить по» сортирует набор результатов в возрастающем или убывающем порядке. |
| 2. | Это может быть разрешено в операторе CREATE VIEW. | Пока он не используется в операторе CREATE VIEW. |
| 3. | В операторе select он всегда используется перед заказом по ключевому слову. | В операторе select он всегда используется после группы по ключевому слову. |
| 4. | Атрибут не может входить в группу по оператору агрегатной функции. | В то время как в порядке следования по утверждениям атрибут может относиться к агрегатной функции. |
| 5. | В предложении group by кортежи группируются на основе сходства между значениями атрибутов кортежей. | Тогда как в предложении order by набор результатов сортируется в порядке возрастания или убывания. |
| 6. | Group by управляет представлением кортежей (строк). | Предложение while order by управляет представлением столбцов. |
Разница между GROUP BY и ORDER BY простыми словами
Для тех, кто изучает SQL, одно из самых распространенных мест, где можно застрять, — это изучение команды GROUP BY . GROUP BY и ORDER BY — два важных ключевых слова в SQL, которые мы используем для организации данных. Разница между GROUP BY и ORDER BY заключается в том, что ORDER BY проще, чем GROUP BY , и обычно вводится в начале курса SQL.
Иногда люди сильно путаются в этих двух концепциях (SQL ORDER BY против GROUP BY ), и причина этой путаницы — непонимание обеих концепций. Хотя оба они используются для упорядочивания данных на основе их значений, их варианты использования сильно отличаются друг от друга. Если мы правильно усвоим обе эти концепции, путаница, естественно, исчезнет.
В этой статье я использовал интересную базу данных, опубликованную здесь. Набор данных содержит более 2000 сортов крафтового пива и 500 пивоварен, используемых в Соединенных Штатах.Данные включены в файлы CSV, которые вы можете легко импортировать в любую из своих баз данных с помощью инструмента с графическим интерфейсом. Один из моих коллег показал, как это сделать, на примере данных опроса LearnSQL.
В этом наборе данных есть две таблицы: пиво и пивоварни . Давайте посмотрим на образец набора данных из таблицы beers , чтобы получить лучшее представление о нем.
ЗАКАЗАТЬ В
Наша первая тема — ЗАКАЗАТЬ НА .Прежде чем пытаться понять разницу между GROUP BY и ORDER BY , давайте посмотрим, как работает команда SELECT .
Давайте ВЫБРАТЬ имя, город и штат из каждой записи в таблице пивоварен .
ВЫБЕРИТЕ имя, Город, государственный ОТ пивоварен
Когда вы выполните указанную выше команду, вы получите что-то вроде следующего:
Вы можете видеть, что эти результаты не отсортированы по их именам.Порядок по умолчанию для оператора SELECT недетерминирован, что означает, что порядок результатов может отличаться при каждом запуске кода.
Однако недетерминированный порядок обычно не то, что нам нужно. Например, предположим, что вы хотите упорядочить результаты по возрастанию или убыванию их столбца состояния ; в этом случае вам понадобится команда ORDER BY . Все, что вам нужно сделать, это использовать предложение ORDER BY следующим образом:
ВЫБЕРИТЕ имя, Город, государственный ОТ пивоварен ЗАКАЗАТЬ по состоянию ASC
Точно так же вы можете получить результаты в порядке убывания состояния.
ВЫБЕРИТЕ имя, Город, государственный ОТ пивоварен ORDER BY state DESC
Вы можете видеть записи, упорядоченные по убыванию состояния. По умолчанию команда ORDER BY имеет порядок возрастания. Итак, если вы не использовали ASC или DESC , результаты будут в порядке возрастания.
Вы можете использовать ORDER BY в двух или более столбцах. Проверьте следующий запрос:
ВЫБЕРИТЕ имя,
Город,
государственный
ОТ пивоварен
ЗАКАЗАТЬ по состоянию,
город DESC
Этот запрос сначала упорядочивает результаты в порядке возрастания штата, а затем в порядке убывания города.
Интересно то, что тот, кто не знает, как использовать GROUP BY , может использовать ORDER BY для анализа данных. Например, предположим, что вы хотите узнать, сколько пивоварен находится в данном состоянии . Что вы можете сделать, так это получить результаты, упорядоченные по их состояниям. Это даст вам хороший сгруппированный результат, по которому вы можете вручную подсчитать количество пивоварен в заданном состоянии .
Следовательно, тот, кто плохо разбирается в GROUP BY , может подумать, что ORDER BY — отличный способ сгруппировать одни и те же данные.Эта идея не ошибочна. Если вы хотите отображать данные красиво сгруппированными, ORDER BY — хороший выбор.
GROUP BY — это не способ отображения данных в группах, это скорее способ анализа данных в группах. Чтобы лучше понять SQL ORDER BY и GROUP BY , давайте более подробно рассмотрим GROUP BY .
ГРУППА ПО
В большинстве текстов GROUP BY определяется как способ агрегирования записей по указанным столбцам, который позволяет выполнять функции агрегирования для несгруппированных столбцов (например, SUM , COUNT , AVG и т. Д.) .Другими словами, цель предложения GROUP BY — суммировать уникальные комбинации значений столбцов.
Несколько примеров пояснят:
Давайте сгруппируем пива таблица на основе столбца стиля .
ВЫБРАТЬ стиль ОТ пива ГРУППА ПО стилю
Вышеупомянутый запрос SQL выдаст следующий результат
Итак, давайте посмотрим, что здесь произошло. Этот запрос возвратил по одному результату для каждого значения стиля .Это означает, что SQL сначала создает группы из одинаковых значений стиля и возвращает одну строку, представляющую группу.
А в чем точное использование GROUP BY ? Конечно, вы можете использовать GROUP BY для поиска различных значений. Но в SQL есть ключевое слово DISTINCT специально для этого. Реальную важность GROUP BY можно увидеть, если использовать его с агрегатными функциями, такими как SUM () , COUNT () . Чтобы лучше понять это, выполните следующий SQL-запрос:
ВЫБРАТЬ стиль, COUNT (имя) ОТ пива ГРУППА ПО стилю
Это даст следующий результат:
Здесь SQL сначала группирует результаты на основе столбца стиля .Затем он проверяет, сколько имен содержится в каждой группе, и возвращает значения стиля и количество имен для каждого стиля.
Если присмотреться, можно заметить, что результаты уже отсортированы по возрастанию. Эта ситуация иногда заставляет людей поверить в то, что GROUP BY сортирует результаты. На самом деле нет никакой гарантии, что GROUP BY будет отображать результаты в порядке возрастания. Если вам нужны результаты в определенном порядке, вы должны сделать это самостоятельно, как показано ниже:
ВЫБРАТЬ стиль, COUNT (имя) ОТ пива ГРУППА ПО стилю ЗАКАЗАТЬ ПО стилю
Следовательно, GROUP BY прекрасно работает вместе с ORDER BY .
Итак, теперь вы знаете, как использовать GROUP BY для подсчета набора значений, принадлежащих определенной группе. Я дам вам еще два реальных примера, чтобы понять использование GROUP BY .
Представьте себе таблицу сотрудников, в которой вы храните информацию о сотрудниках, такую как их имя, должность и зарплата. Каждый сотрудник принадлежит к разному отделу, например, по финансам, ИТ или транспорту. Теперь предположим, что вы хотите получить сумму заработной платы, которую вы выплачивали сотрудникам, в зависимости от их отдела.Вам нужно будет выполнить команду SQL, как показано ниже:
ВЫБЕРИТЕ отдел, СУММА (зарплата) ОТ сотрудника ГРУППА ПО отделам
Предположим, у вас есть веб-сайт электронной коммерции, на котором продаются продукты нескольких типов. В вашей базе данных есть таблица для хранения информации об акциях. Если вы хотите найти количество продуктов каждого типа, вы можете использовать GROUP BY с агрегатной функцией COUNT:
ВЫБЕРИТЕ product_type, COUNT (product_id) Со склада ГРУППА ПО типу продукта
Давайте продолжим этот урок, чтобы расширить ваши знания о GROUP BY .Вы также можете сгруппировать результаты, используя два столбца. Например, давайте сгруппируем таблицу пива на основе стиля и brewery_id .
ВЫБРАТЬ стиль, brewery_id, COUNT (имя) ОТ пива ГРУППА ПО стилю, brewery_id
Этот SQL-запрос разделит группы стиля дальше, используя brewery_id . При его выполнении вы получите следующий результат:
Заключение
В этой статье я объяснил разницу между ORDER BY и GROUP BY . ORDER BY сортирует данные на основе данных столбца. Конечно, в результате сортировки данные из одних и тех же значений будут объединены в группу, что упростит вам анализ вручную позже. Но GROUP BY - это SQL-способ анализа похожих данных.
Реальное использование GROUP BY без функций агрегирования невозможно. Иногда GROUP BY может возвращать результаты в отсортированном порядке, но на это не следует полагаться. Порядок, в котором возвращаются результаты, не является детерминированным и зависит от того, как механизм db выполняет запрос.
После прочтения этой статьи вы сможете более уверенно использовать GROUP BY и ORDER BY . Если вы хотите улучшить свои знания SQL ORDER BY по сравнению с GROUP BY , проверьте этот практический набор - он содержит специальный раздел, посвященный этой теме.
ЗАКАЗАТЬ до ГРУППЫ
У меня tb_user
| id | name | info |
| 01 | Peter | text1 |
| 02 | Paul | text1 |
| 03 | Peter | text2 900 |
| 04 | Питер | text3 |
| 05 | Paul | text2 |
... и мне нужна последняя строка GROUP BY.
Простой SQL:
ВЫБРАТЬ СЧЕТЧИК (имя), имя, информацию ОТ tb_user ГРУППА ПО имени ORDER BY id;
Таб1:
| COUNT (имя) | name | info |
| 3 | Peter | text1 |
| 2 | Paul | text1 |
Но мне нужен LAST Информация!!!
Tab2:
| COUNT (имя) | имя | информация |
| 3 | Peter | text3 |
| 2 | Paul | text2 |
| SN | ГРУППА ПО | ЗАКАЗАТЬ НА |
|---|---|---|
| 1. | Используется для группировки строк с одинаковыми значениями. | Сортировка набора результатов по возрастанию или убыванию. |
| 2. | Это может быть разрешено в операторе CREATE VIEW. | Это не разрешено в операторе CREATE VIEW |
| 3. | Управляет представлением строк. | Управляет представлением столбцов. |
| 4. | Атрибут не может находиться в агрегатной функции оператора GROUP BY. | Атрибут может находиться в агрегатной функции оператора ORDER BY. |
| 5. | Он всегда используется перед предложением ORDER BY в операторе SELECT. | Он всегда используется после предложения GROUP BY в операторе SELECT. |
| 6. | В GROUP BY обязательно использовать агрегатные функции. | Не обязательно использовать агрегатные функции в ORDER BY. |
| 7. | Здесь группировка выполняется на основе сходства значений атрибутов строки. | Здесь набор результатов сортируется на основе значений атрибутов столбца в возрастающем или убывающем порядке. |
Заключение
В этой статье сравниваются предложения GROUP BY и ORDER BY. Оба предложения являются чрезвычайно полезными функциями базы данных SQL.Когда мы хотим сформировать группу строк, мы используем предложение GROUP BY. Если мы хотим организовать данные в порядке возрастания или убывания на основе определенного столбца, мы используем предложение ORDER BY. У них нет никакой связи, потому что оба используются для двух разных целей. Однако мы можем комбинировать их для определенных целей или использовать их индивидуально, в зависимости от обстоятельств. Мы можем использовать эти предложения только с оператором SELECT.
ВЫБРАТЬ (ГДЕ, ГРУППА ПО, ИМЕЮЩИЙ, ПОРЯДОК ПО) | Мой личный блог Oracle
TODO: поэкспериментируйте с другими примерами и ошибками
ВЫБРАТЬ
Выбрать все столбцы из таблицы
SELECT * FROM table_name;
РАЗЛИЧНЫЙ
Выбор уникальных значений (неповторяющихся) из таблицы.Нет двух одинаковых имен.
ВЫБРАТЬ DISTINT имя_пользователя ИЗ имя_таблицы:
НИКНЕЙМЫ
Выберите определенные столбцы из таблицы и измените их имя на псевдоним
ВЫБРАТЬ столбец1, столбец2 как newNameOfColumn FROM table;
Использование функции для столбца в операторе SELECT
SELECT MAX (имя_столбца) FROM имя_таблицы;
ГДЕ
Используйте некоторые критерии для фильтрации строк, которые должны отображаться
SELECT * FROM table_name WHERE column1 IS NOT NULL;
ВЫБРАТЬ * ИЗ имя_таблицы ГДЕ столбец1> = 10;
SELECT * FROM table_name WHERE column1 = 'некоторая строка, которая должна быть заключена в одинарные кавычки';
SELECT * FROM table_name WHERE column1 LIKE '_first character может быть любым, а затем он может заканчиваться 0 или неограниченным количеством любых символов%';
LIKE сопровождается двумя символами подстановки: символом процента (%) и символом подчеркивания (_).Символ процента используется для указания нуля или более символов подстановки, а символ подчеркивания указывает один символ подстановки.
ГРУППА BY
Группировка столбцов по определенному критерию. Обратите внимание, что вы не можете использовать псевдонимы в GROUP BY и что вы должны иметь этот столбец в SELECT.
ВЫБРАТЬ столбец1 ИЗ имя_таблицы GROUP BY column1;
У вас должны быть все столбцы из SELECT в GROUP BY, иначе это не сработает. Следующие два утверждения приводят к ОШИБКЕ.
ВЫБРАТЬ * ИЗ имя_таблицы GROUP BY column1;
ВЫБРАТЬ столбец1, столбец2 ИЗ имя_таблицы GROUP BY column1;
Если вы используете функцию для столбца 2, то она будет работать
ВЫБРАТЬ столбец1, СЧЁТ (столбец2) FROM имя_таблицы GROUP BY column1
Или, если мы поместим все столбцы из оператора SELECT в оператор GROUP BY.
ВЫБРАТЬ column1, column2 FROM table_name GROUP BY column1, column2
Если вы используете псевдоним для столбца, вы НЕ МОЖЕТЕ использовать его в операторе GROUP BY.Вы по-прежнему можете использовать его в псевдониме WHERE
SELECT column1 AS. AVG (column2) как среднееColumn FROM table_name
GROUP BY column1;
Следующий запрос приведет к ОШИБКЕ.
SELECT column1 AS псевдоним. AVG (column2) как averageColumn FROM table_name
псевдоним GROUP BY;
ИМЕЕТ
HAVING фильтрует сгруппированные поля (в отличие от WHERE, который фильтрует все поля в таблице)
SELECT column1 FROM table_name WHERE column2 IS NOT NULL
GROUP BY column1 HAVING column1 = 'some value';
Поскольку HAVING фильтрует только сгруппированные значения, столбец в HAVING также должен находиться в GROUP BY.Следующий запрос вернет ошибку.
ВЫБРАТЬ столбец1 ИЗ имя_таблицы, ГДЕ столбец2 НЕ ПУСТОЙ
ГРУППА ПО столбцу1 ИМЕЕТ столбец2 = 'какое-то значение';
Этот запрос будет работать.
ВЫБРАТЬ столбец1, столбец2 ИЗ имя_таблицы, ГДЕ столбец2 НЕ ПУСТОЙ
ГРУППА ПО столбцу1, столбец2 ИМЕЕТ столбец2 = 'некоторое значение';
Рассмотрим таблицу EMPLOYEES. Он состоит из 11 столбцов и 107 строк. Вы
можете создавать группы строк, которые имеют общее значение DEPARTMENT_ID.Затем функцию
СУММ можно использовать для создания итоговых значений заработной платы по отделам.
ВЫБРАТЬ макс (зарплата), количество (*) ИЗ сотрудников
ГРУППА ПО идентификатору отдела, имеющему count (*)> 1
ЗАКАЗАТЬ ПО Department_id;
Любой элемент в списке SELECT, который не является групповой функцией, должен быть атрибутом группировки
предложения GROUP BY.
Атрибут группировки обычно встречается в списке SELECT вместе с функциями группировки. Если элемент, который не является групповой функцией, появляется в списке SELECT и отсутствует предложение GROUP BY, возникает ошибка «ORA-00937: не групповая функция одной группы».
SELECT end_date, COUNT (*) FROM job_history; ОШИБКА: ORA-00937: функция не одногрупповой группы
Если предложение GROUP BY присутствует, но этот элемент не является атрибутом группировки, то возвращается ошибка «ORA-00979: не выражение GROUP BY».
ВЫБРАТЬ end_date, start_date, COUNT (*) FROM job_history GROUP BY end_date; ОШИБКА: ORA-00979: не выражение GROUP BY
SELECT столбец | выражение | группа_функция (столбец | выражение [псевдоним]),…}
FROM table
[WHERE condition (s)]
[GROUP BY {col (s) | expr}]
[HAVING group_condition (s)]
[ORDER BY {col (s) | expr | numeric_pos} [ASC | DESC] [NULLS FIRST | LAST]];
ЗАКАЗАТЬ В №
ORDER BY просто определяет способ отображения результатов.Он сортирует результат на основе некоторого поля или нескольких полей, которые мы вводим, и может делать это по возрастанию ASC или по убыванию DESC.
SELECT * | {[DISTINCT] столбец | выражение [псевдоним],…}
FROM table
[WHERE condition (s)]
[ORDER BY {col (s) | expr | numeric_pos} [ASC | DESC] [ ПЕРВЫЕ НУЛИ | ПОСЛЕДНИЙ ]];
ВЫБРАТЬ * ИЗ имя_таблицы ORDER BY column1 ASC;
ВЫБРАТЬ * ИЗ имя_таблицы ORDER BY column1 ASC, column2 DESC;
При сортировке символов значения чувствительны к регистру.
Как это:
Нравится Загрузка ...
Разница между GROUP BY и ORDER BY простыми словами
Для тех, кто изучает SQL, одно из самых распространенных мест, где можно застрять, - это изучение команды GROUP BY . GROUP BY и ORDER BY - два важных ключевых слова в SQL, которые мы используем для организации данных. Разница между GROUP BY и ORDER BY заключается в том, что ORDER BY проще, чем GROUP BY , и обычно вводится в начале курса SQL.
Иногда люди сильно путаются в этих двух концепциях (SQL ORDER BY против GROUP BY ), и причина этой путаницы - непонимание обеих концепций. Хотя оба они используются для упорядочивания данных на основе их значений, их варианты использования сильно отличаются друг от друга. Если мы правильно усвоим обе эти концепции, путаница, естественно, исчезнет.
В этой статье я использовал интересную базу данных, опубликованную здесь. Набор данных содержит более 2000 сортов крафтового пива и 500 пивоварен, используемых в Соединенных Штатах.Данные включены в файлы CSV, которые вы можете легко импортировать в любую из своих баз данных с помощью инструмента с графическим интерфейсом. Один из моих коллег показал, как это сделать, на примере данных опроса Vertabelo Academy.
Вам также могут понравиться:
Как связаны SQL DISTINCT и ORDER BY
В этом наборе данных есть две таблицы: пиво и пивоварни . Давайте посмотрим на образец набора данных из таблицы beers , чтобы получить более полное представление о нем.
ЗАКАЗАТЬ В
Наша первая тема - ЗАКАЗАТЬ НА . Прежде чем пытаться понять разницу между GROUP BY и ORDER BY , давайте посмотрим, как работает команда SELECT .
Давайте ВЫБРАТЬ имя, город и штат из каждой записи в таблице пивоварен .
|
Когда вы выполните указанную выше команду, вы получите что-то вроде следующего:
Вы можете видеть, что эти результаты не отсортированы по их именам.Порядок по умолчанию для оператора SELECT недетерминирован, что означает, что порядок результатов может отличаться при каждом запуске кода.
Однако недетерминированный порядок обычно не то, что нам нужно. Например, предположим, что вы хотите упорядочить результаты по возрастанию или убыванию их столбца состояния ; в этом случае вам понадобится команда ORDER BY . Все, что вам нужно сделать, это использовать предложение ORDER BY следующим образом:
штат |
Точно так же вы можете получить результаты в порядке убывания состояния.
|
Вы можете видеть записи, упорядоченные по убыванию состояния. По умолчанию команда ORDER BY имеет порядок возрастания.Итак, если вы не использовали ASC или DESC , результаты будут в порядке возрастания.
Вы можете использовать ORDER BY в двух или более столбцах. Проверьте следующий запрос:
|
Этот запрос сначала упорядочивает результаты в порядке возрастания состояния, а затем в порядке убывания города.
Интересно то, что тот, кто не знает, как использовать GROUP BY , может использовать ORDER BY для анализа данных. Например, предположим, что вы хотите узнать, сколько пивоварен находится в данном состоянии . Что вы можете сделать, так это получить результаты, упорядоченные по их состояниям. Это даст вам хороший сгруппированный результат, по которому вы можете вручную подсчитать количество пивоварен в заданном состоянии .
Следовательно, тот, кто плохо разбирается в GROUP BY , может подумать, что ORDER BY - отличный способ сгруппировать одни и те же данные.Эта идея не ошибочна. Если вы хотите отображать данные красиво сгруппированными, ORDER BY - хороший выбор.
GROUP BY - это не способ отображения данных в группах, это скорее способ анализа данных в группах. Чтобы лучше понять SQL ORDER BY и GROUP BY , давайте более подробно рассмотрим GROUP BY .
ГРУППА ПО
В большинстве текстов GROUP BY определяется как способ агрегирования записей по указанным столбцам, что позволяет выполнять функции агрегирования для несгруппированных столбцов (например, SUM , COUNT , AVG и т. Д.) .Другими словами, цель предложения GROUP BY - суммировать уникальные комбинации значений столбцов.
Несколько примеров пояснят:
Давайте сгруппируем пива таблица на основе стиля столбца.
|
Вышеупомянутый запрос SQL выдаст следующий результат
Итак, давайте посмотрим, что здесь произошло.Этот запрос возвратил по одному результату для каждого значения стиля . Это означает, что SQL сначала создает группы из одинаковых значений стиля и возвращает одну строку, представляющую группу.
А в чем точное использование GROUP BY ? Конечно, вы можете использовать GROUP BY для поиска различных значений. Но в SQL есть ключевое слово DISTINCT специально для этого. Реальную важность GROUP BY можно увидеть, если использовать его с агрегатными функциями, такими как SUM () , COUNT () .Чтобы лучше понять это, выполните следующий SQL-запрос:
|
Будет получен следующий результат:
Здесь SQL сначала группирует результаты на основе столбца стиля .Затем он проверяет, сколько имен содержится в каждой группе, и возвращает значения стиля и количество имен для каждого стиля.
Если присмотреться, можно заметить, что результаты уже отсортированы по возрастанию. Эта ситуация иногда заставляет людей поверить в то, что GROUP BY сортирует результаты. На самом деле нет никакой гарантии, что GROUP BY будет отображать результаты в порядке возрастания. Если вам нужны результаты в определенном порядке, вы должны сделать это самостоятельно, как показано ниже:
|
Таким образом, GROUP BY прекрасно работает вместе с ORDER BY .
Итак, теперь вы знаете, как использовать GROUP BY для подсчета набора значений, принадлежащих определенной группе. Я дам вам еще два реальных примера, чтобы понять использование GROUP BY .
Представьте себе таблицу сотрудников, в которой вы храните информацию о сотрудниках, такую как их имя, должность и зарплата. Каждый сотрудник принадлежит к разному отделу, например, по финансам, ИТ или транспорту. Теперь предположим, что вы хотите получить сумму заработной платы, которую вы выплачивали сотрудникам, в зависимости от их отдела.Вам нужно будет выполнить команду SQL, как показано ниже:
SELECTотдел,SUM(зарплата)ОТсотрудникGROUPBYотдел
Предположим, у вас есть веб-сайт электронной коммерции, на котором продаются продукты нескольких типов. В вашей базе данных есть таблица для хранения информации об акциях.Если вы хотите найти количество продуктов каждого типа, вы можете использовать GROUP BY с агрегатной функцией COUNT:
SELECTproduct_type,COUNT(product_id)FROMна складеGROUP_ BYproduct
Давайте продолжим этот урок, чтобы расширить ваши знания о GROUP BY .Вы также можете сгруппировать результаты, используя два столбца. Например, сгруппируем таблицу пива на основе стиля и brewery_id .
|
Этот запрос SQL разделит группы стиля дальше, используя brewery_id .При его выполнении вы получите следующий результат:
Заключение
В этой статье я объяснил разницу между ORDER BY и GROUP BY . ORDER BY сортирует данные на основе данных столбца. Конечно, в результате сортировки данные из одних и тех же значений будут объединены в группу, что упростит вам анализ вручную позже. Но GROUP BY - это SQL-способ анализа похожих данных.
Реальное использование GROUP BY без функций агрегирования невозможно. Иногда GROUP BY может возвращать результаты в отсортированном порядке, но на это не следует полагаться. Порядок, в котором возвращаются результаты, не является детерминированным и зависит от того, как механизм db выполняет запрос.
Прочитав эту статью, вы сможете более уверенно использовать GROUP BY и ORDER BY . Если вы хотите улучшить свои знания о SQL ORDER BY vs. GROUP BY , проверьте этот практический набор - он содержит специальный раздел, посвященный этой теме.
Дополнительная литература
Введение в заказ на соединение
Порядок присоединения, часть 2: «SQL»
Гай Харрисон - еще один блог о базе данных
Начиная с Oracle 10.2, вы можете заметить значительное снижение относительной производительности при объединении GROUP BY с ORDER BY в одних и тех же столбцах.
Oracle представила GROUP BY на основе хэшей в версии 10.2. Ранее операция GROUP BY включала сортировку данных по соответствующим столбцам с последующим накоплением совокупных результатов. Хеш-метод GROUP BY создает агрегаты без сортировки и почти всегда работает быстрее, чем GROUP BY на основе сортировки. К сожалению, когда вы включаете предложение ORDER BY в те же столбцы, что и столбцы в GROUP BY, Oracle возвращается к старой системе GROUP BY на основе сортировки с соответствующим падением производительности. Однако вы можете переформулировать свой SQL, чтобы воспользоваться преимуществами GROUP BY на основе хешей, при этом получая данные в желаемом порядке.
Например, рассмотрим этот простой оператор:
До 10.2 оператор выполнялся с использованием операции SORT GROUP BY:
Начиная с 10.2, мы можем ожидать появления HASH GROUP BY:
Как заметил Алекс Горбачев, новый GROUP BY может возвращать плохие результаты в ранних версиях (до 11.1.0.7 или 10.2.0.4). Вы можете отключить, установив для параметра _GBY_HASH_AGGREGATION_ENABLED значение FALSE.Ниже мы используем подсказку OPT_PARAM, чтобы установить это для отдельного SQL; вы также можете использовать ALTER SESSION или ALTER SYSTEM для изменения параметра на уровне сеанса или экземпляра:
Еще нужно помнить, что нельзя полагаться на то, что GROUP BY возвращает строки по порядку; до 10.2 GROUP BY обычно возвращала строки в порядке GROUP BY, и поэтому некоторые из нас, возможно, не удосужились добавить предложение ORDER BY. При обновлении до 10.2 вы могли быть удивлены, увидев, что запросы внезапно возвращают данные в явно случайном порядке.Том Кайт говорит об этом здесь: как правило, вы никогда не должны полагаться на побочный эффект для получения строк в определенном порядке. Если вы хотите, чтобы они были в порядке, вы должны всегда указывать предложение ORDER BY.
Вообще говоря, новый хэш GROUP BY намного более эффективен, чем старый метод сортировки. Ниже мы видим относительную производительность для двух алгоритмов GROUP BY при группировке примерной таблицы из 2,5 миллионов строк примерно в 200000 агрегированных строк:
Конечно, ваши результаты могут отличаться, но я не видел случая, чтобы SORT GROUP BY превосходила HASH GROUP BY.
К сожалению, Oracle отказывается использовать HASH GROUP BY в некоторых случаях, когда это может быть полезно. Довольно часто в одних и тех же столбцах есть GROUP BY и ORDER BY. В конце концов, вы обычно не хотите, чтобы сводный отчет был в случайном порядке.
Когда Oracle использует SORT GROUP BY, строки возвращаются в порядке группировки как побочный эффект сортировки. Итак, в приведенном ниже примере есть только один SORT - он поддерживает как GROUP BY, так и ORDER BY (обратите внимание, что я отключил HASH GROUP BY с помощью подсказки OPT_PARAM):
Вышеупомянутый план - это то, что вы ожидаете увидеть до 10.2 - поскольку HASH GROUP BY в этом выпуске недоступен.
Однако, когда мы исследуем план выполнения в 11g или 10.2, мы обнаруживаем, что Oracle по-прежнему выбирает SORT GROUP BY:
Вот важный момент:
Когда вы объединяете GROUP BY и ORDER BY в одном списке столбцов, Oracle не будет использовать параметр HASH GROUP BY.
Предположительно, оптимизатор «думает», что, поскольку SORT GROUP BY позволяет Oracle получать строки в отсортированном порядке при выполнении агрегации, лучше всего использовать SORT GROUP BY, когда SQL запрашивает ORDER BY, а также GROUP BY.Однако в этой логике есть серьезные изъяны. Входы в ORDER BY обычно будут намного меньше строк, чем входы в GROUP BY. В нашем примере выше GROUP BY обрабатывает около 2,5 миллионов строк, в то время как ORDER BY сортирует только около 200 000 строк: действительно не имеет смысла деоптимизировать дорогостоящую GROUP BY для оптимизации относительно дешевого ORDER BY.
Есть ли способ заставить Oracle использовать HASH GROUP BY, даже если у нас есть ORDER BY? Мне неизвестен параметр или подсказка оптимизатора, но я смог убедить Oracle использовать HASH GROUP BY, поместив GROUP BY в подзапрос, ORDER BY во внешний запрос и используя подсказку NO_MERGE, чтобы избежать подзапрос объединен с внешним запросом.Вот мой план запроса и выполнения, показывающий, что я получаю HASH GROUP BY вместе с SORT ORDER BY:
Вы можете подумать, что выполнение одной SORT GROUP BY лучше, чем выполнение HASH GROUP BY и SORT ORDER BY. Но помните, что SORT ORDER BY должен только отсортировать сгруппированные строки - около 200 000 в моем примере - в то время как GROUP BY должен обрабатывать все содержимое таблицы - около 2,5 миллионов в моем примере таблицы. Поэтому оптимизация GROUP BY часто более важна, чем предотвращение небольшой второй сортировки.
Вот сравнение производительности двух подходов:
В результате перезаписи затраченное время сократилось примерно на 2/3.
Заключение
Когда GROUP BY связано с ORDER BY в тех же столбцах, оптимизатор Oracle может выбрать SORT GROUP BY вместо обычно более эффективной HASH GROUP BY. Использование SORT GROUP BY позволяет избежать добавления в план SORT ORDER BY, но общий результат обычно неутешителен.
Чтобы получить лучший результат, вы можете выполнить GROUP BY во встроенном представлении и выполнить ORDER BY во внешнем запросе.Используйте подсказку NO_MERGE, чтобы предотвратить объединение двух операций.
.