Есть ли разница между GROUP BY и DISTINCT?
Я узнал кое-что простое о SQL на днях:
SELECT c FROM myTbl GROUP BY C
Имеет тот же результат, что и:
SELECT DISTINCT C FROM myTbl
Что мне интересно, есть ли что-то другое в том, как механизм SQL обрабатывает команду, или это действительно одно и то же?
Я лично предпочитаю четкий синтаксис, но я уверен, что это скорее привычка, чем что-либо еще.
РЕДАКТИРОВАТЬ: Это не вопрос о агрегатах. Использование GROUP BY с агрегатными функциями это понятно.
24 ответа
Ответ MusiGenesis является функционально правильным в отношении вашего вопроса, как указано; SQL Server достаточно умен, чтобы понять, что если вы используете "Группировать по" и не используете никаких агрегатных функций, то на самом деле вы имеете в виду "Отличительный" - и, следовательно, он генерирует план выполнения, как если бы вы просто использовали "Отличительный" ".
Тем не менее, я думаю, что важно отметить реакцию Хэнка, а также - кавалерийское обращение с "Group By" и "Distinct" может привести к пагубным ошибкам в будущем, если вы не будете осторожны. Не совсем правильно говорить, что это "не вопрос об агрегатах", потому что вы спрашиваете о функциональной разнице между двумя ключевыми словами SQL-запроса, одно из которых предназначено для использования с агрегатами, а другое - нет.
Иногда молоток может вбить винт, но если у вас под рукой есть отвертка, зачем?
(для целей этой аналогии, Hammer : Screwdriver :: GroupBy : Distinct а также screw => get list of unique values in a table column)
GROUP BY позволяет использовать агрегатные функции, такие как AVG, MAX, MIN, SUM, а также COUNT, С другой стороны DISTINCT просто удаляет дубликаты.
Например, если у вас есть куча записей о покупках, и вы хотите знать, сколько было потрачено каждым отделом, вы можете сделать что-то вроде:
SELECT department, SUM(amount) FROM purchases GROUP BY department
Это даст вам одну строку для каждого отдела, содержащую название отдела и сумму всех amount значения во всех строках для этого отдела.
В чем отличие с точки зрения простого удаления дубликатов
Помимо того, что в отличие от DISTINCT, GROUP BY позволяет агрегировать данные по группам (что было упомянуто во многих других ответах), наиболее важным отличием, на мой взгляд, является тот факт, что две операции "происходят" на двух очень разных этапах в логическом порядке операций, которые выполняются в SELECT заявление.
Вот самые важные операции:
FROM(в том числеJOIN,APPLY, так далее.)WHEREGROUP BY(можно удалить дубликаты)- Скопления
HAVING- Оконные функции
SELECTDISTINCT(можно удалить дубликаты)UNION,INTERSECT,EXCEPT(можно удалить дубликаты)ORDER BYOFFSETLIMIT
Как видите, логический порядок каждой операции влияет на то, что можно сделать с ней, и как она влияет на последующие операции. В частности, тот факт, что GROUP BY операция "происходит раньше" SELECT операция (проекция) означает, что:
- Это не зависит от прогноза (что может быть преимуществом)
- Он не может использовать какие-либо значения из проекции (что может быть недостатком)
1. Это не зависит от проекции
Пример, в котором полезно не зависеть от проекции, - это если вы хотите рассчитать оконные функции по различным значениям:
SELECT rating, row_number() OVER (ORDER BY rating) AS rn
FROM film
GROUP BY rating
При запуске с базой данных Sakila это дает:
rating rn
-----------
G 1
NC-17 2
PG 3
PG-13 4
R 5
То же самое не может быть достигнуто с DISTINCT без труда:
SELECT DISTINCT rating, row_number() OVER (ORDER BY rating) AS rn
FROM film
Этот запрос "неправильный" и дает что-то вроде:
rating rn
------------
G 1
G 2
G 3
...
G 178
NC-17 179
NC-17 180
...
Это не то, что мы хотели. DISTINCT операция "происходит после" проекции, поэтому мы больше не можем удалить DISTINCT рейтинги, потому что оконная функция уже была рассчитана и спроектирована. Для того, чтобы использовать DISTINCT нам нужно было бы вложить эту часть запроса:
SELECT rating, row_number() OVER (ORDER BY rating) AS rn
FROM (
SELECT DISTINCT rating FROM film
) f
Примечание: в данном конкретном случае мы могли бы также использовать DENSE_RANK()
SELECT DISTINCT rating, dense_rank() OVER (ORDER BY rating) AS rn
FROM film
2. Он не может использовать какие-либо значения из проекции
Одним из недостатков SQL является его многословие. По той же причине, что мы видели раньше (а именно, логический порядок операций), мы не можем "легко" группировать то, что мы проецируем.
Это неверный SQL:
SELECT first_name || ' ' || last_name AS name
FROM customer
GROUP BY name
Это верно (повторяет выражение)
SELECT first_name || ' ' || last_name AS name
FROM customer
GROUP BY first_name || ' ' || last_name
Это также верно (вложенное выражение)
SELECT name
FROM (
SELECT first_name || ' ' || last_name AS name
FROM customer
) c
GROUP BY name
Разницы нет (в SQL Server, по крайней мере). Оба запроса используют один и тот же план выполнения.
http://sqlmag.com/database-performance-tuning/distinct-vs-group
Может быть, есть разница, если задействованы подзапросы:
Нет никакой разницы (в стиле Oracle):
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32961403234212
Использование DISTINCT если вы просто хотите удалить дубликаты. использование GROUPY BY если вы хотите применить агрегатные операторы (MAX, SUM, GROUP_CONCAT,... или HAVING пункт).
Я ожидаю, что есть вероятность тонких различий в их исполнении. Я проверил планы выполнения для двух функционально эквивалентных запросов по этим направлениям в Oracle 10g:
core> select sta from zip group by sta;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 | 174 | 44 (19)| 00:00:01 |
| 1 | HASH GROUP BY | | 58 | 174 | 44 (19)| 00:00:01 |
| 2 | TABLE ACCESS FULL| ZIP | 42303 | 123K| 38 (6)| 00:00:01 |
---------------------------------------------------------------------------
core> select distinct sta from zip;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 | 174 | 44 (19)| 00:00:01 |
| 1 | HASH UNIQUE | | 58 | 174 | 44 (19)| 00:00:01 |
| 2 | TABLE ACCESS FULL| ZIP | 42303 | 123K| 38 (6)| 00:00:01 |
---------------------------------------------------------------------------
Средняя операция немного отличается: "HASH GROUP BY" и "HASH UNIQUE", но предполагаемые затраты и т. Д. Идентичны. Затем я выполнил их с включенной трассировкой, и фактическое количество операций было одинаковым для обоих (за исключением того, что второму не нужно было выполнять какие-либо физические операции чтения из-за кэширования).
Но я думаю, что из-за того, что имена операций различаются, выполнение будет следовать несколько иным путям кода, и это открывает возможность более существенных различий.
Я думаю, что вы должны предпочесть синтаксис DISTINCT для этой цели. Это не просто привычка, это более четко указывает на цель запроса.
Для отправленного вами запроса они идентичны. Но для других запросов это может быть не так.
Например, это не то же самое, что:
SELECT C FROM myTbl GROUP BY C, D
Я прочитал все приведенные выше комментарии, но не увидел, чтобы кто-то указывал на основное различие между Group By и Distinct, кроме бита агрегации.
Distinct возвращает все строки, затем дедуплицирует их, тогда как Group By дедуплицирует строки, когда они читаются алгоритмом по очереди.
Это означает, что они могут давать разные результаты!
Например, приведенные ниже коды дают разные результаты:
SELECT distinct ROW_NUMBER() OVER (ORDER BY Name), Name FROM NamesTable
SELECT ROW_NUMBER() OVER (ORDER BY Name), Name FROM NamesTable
GROUP BY Name
Если в таблице 10 имен, из которых 1 является дубликатом другого, то первый запрос возвращает 10 строк, тогда как второй запрос возвращает 9 строк.
Причина в том, что я сказал выше, чтобы они могли вести себя по-другому!
Если вы используете DISTINCT с несколькими столбцами, результирующий набор не будет сгруппирован, как с GROUP BY, и вы не сможете использовать агрегатные функции с DISTINCT.
Они имеют различную семантику, даже если у них есть эквивалентные результаты для ваших конкретных данных.
GROUP BY имеет очень специфическое значение, отличное (хе) от функции DISTINCT.
GROUP BY приводит к тому, что результаты запроса группируются с использованием выбранного выражения, затем могут применяться агрегатные функции, которые будут действовать на каждую группу, а не на весь набор результатов.
Вот пример, который может помочь:
Учитывая таблицу, которая выглядит так:
name
------
barry
dave
bill
dave
dave
barry
john
Этот запрос:
SELECT name, count(*) AS count FROM table GROUP BY name;
Будет производить вывод, как это:
name count
-------------
barry 2
dave 3
bill 1
john 1
Что, очевидно, сильно отличается от использования DISTINCT. Если вы хотите сгруппировать результаты, используйте GROUP BY, если вы просто хотите уникальный список определенного столбца, используйте DISTINCT. Это даст вашей базе данных возможность оптимизировать запрос для ваших нужд.
Пожалуйста, не используйте GROUP BY, когда вы имеете в виду DISTINCT, даже если они работают одинаково. Я предполагаю, что вы пытаетесь сэкономить миллисекунды от запросов, и я должен отметить, что время разработки на порядок дороже, чем время компьютера.
Если вы используете GROUP BY без какой-либо агрегатной функции, то внутренне она будет обрабатываться как DISTINCT, поэтому в этом случае нет разницы между GROUP BY и DISTINCT.
Но когда вам предоставляется предложение DISTINCT, лучше использовать его для поиска ваших уникальных записей, поскольку целью GROUP BY является достижение агрегации.
С точки зрения набора результатов, не имеет значения, используете ли вы DISTINCT или GROUP BY в Teradata. Набор ответов будет таким же.
С точки зрения производительности, это не то же самое.
Чтобы понять, что влияет на производительность, вам необходимо знать, что происходит с Teradata при выполнении оператора с помощью DISTINCT или GROUP BY.
В случае DISTINCT строки перераспределяются немедленно, без какой-либо предварительной агрегации, в то время как в случае GROUP BY на первом этапе выполняется предварительная агрегация, и только после этого уникальные значения перераспределяются по AMP.
Не думайте, что GROUP BY всегда лучше с точки зрения производительности. Если у вас много разных значений, шаг предварительной агрегации GROUP BY не очень эффективен. Teradata должна отсортировать данные, чтобы удалить дубликаты. В этом случае может быть лучше сначала перераспределить, то есть использовать инструкцию DISTINCT. Только если имеется много повторяющихся значений, оператор GROUP BY, вероятно, является лучшим выбором, поскольку только после выполнения шага дедупликации после перераспределения.
Короче говоря, DISTINCT против GROUP BY в Teradata означает:
GROUP BY -> для многих дубликатов DISTINCT -> нет или только несколько дубликатов. Иногда, когда вы используете DISTINCT, вам не хватает места в буфере на AMP. Причина в том, что перераспределение происходит немедленно, и перекос может привести к тому, что AMP не хватит места.
Если это произойдет, у вас, вероятно, больше шансов на использование GROUP BY, поскольку дубликаты уже удалены на первом этапе и меньше данных перемещается по AMP.
group by используется в агрегатных операциях - например, когда вы хотите получить количество Bs в разбивке по столбцу C
select C, count(B) from myTbl group by C
это звучит по-разному - вы получаете уникальные строки.
В sql server 2005 похоже, что оптимизатор запросов способен оптимизировать разницу в упрощенных примерах, которые я запускал. Не знаю, если вы можете рассчитывать на это во всех ситуациях, хотя.
В этом конкретном запросе нет никакой разницы. Но, конечно, если вы добавите какие-либо статистические столбцы, вам придется использовать группирование по.
С точки зрения использования GROUP BY используется для группировки тех строк, которые вы хотите вычислить. DISTINCT не производит никаких вычислений. Он не покажет повторяющихся строк.
Я всегда использовал DISTINCT, если хочу представить данные без дубликатов.
Если я хочу произвести расчеты, например, суммировать общее количество манго, я буду использовать GROUP BY.
Я знаю, что это старый пост. Но бывает, что у меня был запрос, который использовал group by, чтобы просто возвращать различные значения при использовании этого запроса в отчетах toad и oracle, все работало нормально, я имею в виду хорошее время отклика. Когда мы перешли с Oracle 9i на 11g, время отклика в Toad было превосходным, но в отчете на завершение отчета ушло около 35 минут, а при использовании предыдущей версии - около 5 минут.
Решение состояло в том, чтобы изменить группу и использовать DISTINCT, и теперь отчет выполняется примерно за 30 секунд.
Я надеюсь, что это полезно для кого-то с такой же ситуацией.
Вы замечаете это только потому, что выбираете один столбец.
Попробуйте выбрать два поля и посмотрите, что произойдет.
Group By предназначена для использования следующим образом:
SELECT name, SUM(transaction) FROM myTbl GROUP BY name
Который будет показывать сумму всех транзакций для каждого человека.
В Hive (HQL) группирование по может выполняться намного быстрее, чем по-разному, поскольку первое не требует сравнения всех полей в таблице. См. https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct.
С точки зрения "языка SQL" эти две конструкции эквивалентны, и то, что вы выбираете, является одним из тех вариантов "образа жизни", которые мы все должны сделать. Я думаю, что есть хороший пример того, что DISTINCT является более явным (и, следовательно, более внимательным к человеку, который унаследует ваш код и т. Д.), Но это не означает, что конструкция GROUP BY является недопустимым выбором.
Я думаю, что "GROUP BY для агрегатов" - неправильный акцент. Люди должны знать, что функция set (MAX, MIN, COUNT и т. Д.) Может быть опущена, чтобы они могли понять намерения кодера, когда оно есть.
Идеальный оптимизатор распознает эквивалентные SQL-конструкции и всегда соответственно выберет идеальный план. Для выбора реального движка SQL вы должны протестировать:)
PS обратите внимание, что позиция ключевого слова DISTINCT в предложении select может давать разные результаты, например, контраст:
SELECT COUNT(DISTINCT C) FROM myTbl;
SELECT DISTINCT COUNT(C) FROM myTbl;
Иногда они могут дать вам одинаковые результаты, но предназначены для использования в другом смысле / случае. Основное отличие в синтаксисе.
Обратите внимание на пример ниже. DISTINCTиспользуется для фильтрации повторяющегося набора значений. (6, cs, 9.1) и (1, cs, 5.5) - два разных набора. ТакDISTINCT будет отображать обе строки, пока GROUP BY Branch будет отображать только один набор.
SELECT * FROM student;
+------+--------+------+
| Id | Branch | CGPA |
+------+--------+------+
| 3 | civil | 7.2 |
| 2 | mech | 6.3 |
| 6 | cs | 9.1 |
| 4 | eee | 8.2 |
| 1 | cs | 5.5 |
+------+--------+------+
5 rows in set (0.001 sec)
SELECT DISTINCT * FROM student;
+------+--------+------+
| Id | Branch | CGPA |
+------+--------+------+
| 3 | civil | 7.2 |
| 2 | mech | 6.3 |
| 6 | cs | 9.1 |
| 4 | eee | 8.2 |
| 1 | cs | 5.5 |
+------+--------+------+
5 rows in set (0.001 sec)
SELECT * FROM student GROUP BY Branch;
+------+--------+------+
| Id | Branch | CGPA |
+------+--------+------+
| 3 | civil | 7.2 |
| 6 | cs | 9.1 |
| 4 | eee | 8.2 |
| 2 | mech | 6.3 |
+------+--------+------+
4 rows in set (0.001 sec)
Иногда результаты, которых можно достичь GROUP BY оговорка не может быть достигнута DISTINCTбез использования дополнительных предложений или условий. Например, в приведенном выше случае.
Чтобы получить тот же результат, что и DISTINCT вы должны передать все имена столбцов в GROUP BYпункт, как показано ниже. Так что посмотрите на синтаксическую разницу. Вы должны знать все имена столбцов, чтобы использоватьGROUP BY пункт в этом случае.
SELECT * FROM student GROUP BY Id, Branch, CGPA;
+------+--------+------+
| Id | Branch | CGPA |
+------+--------+------+
| 1 | cs | 5.5 |
| 2 | mech | 6.3 |
| 3 | civil | 7.2 |
| 4 | eee | 8.2 |
| 6 | cs | 9.1 |
+------+--------+------+
Также я заметил GROUP BY по умолчанию отображает результаты в порядке возрастания, DISTINCTне. Но я не уверен в этом. Это может отличаться от поставщика.
Источник: https://dbjpanda.me/dbms/languages/sql/sql-syntax-with-examples
У меня был этот вопрос раньше, мне нужно добавить три столбца из моей таблицы с 4 миллионами строк (три столбца в один новый столбец новой таблицы), но только разные.
Поэтому я запустил мою хранимую процедуру, которая содержит этот запрос, с помощью метода group by, и это заняло 32 минуты. Затем я запустил его снова, но с "отличным" методом, и это заняло 25 минут.
Это тот же результат, но это было немного быстрее, 2-й метод
Обычно мы можем использовать DISTINCT для устранения дубликатов в определенном столбце в таблице.
В случае GROUP BY мы можем применить такие функции агрегирования, как
AVG,MAX,MIN,SUM, а такжеCOUNTв определенном столбце и выберите имя столбца и результат функции агрегирования в том же столбце.
Пример:
select specialColumn,sum(specialColumn) from yourTableName group by specialColumn;
Я всегда понимал, что использование различных - это то же самое, что группирование по каждому полю, выбранному вами в порядке их выбора.
то есть:
select distinct a, b, c from table;
такой же как:
select a, b, c from table group by a, b, c
Функциональная эффективность совершенно иная. Если вы хотите выбрать только "возвращаемое значение", за исключением дубликата, лучше использовать отличный, чем группировать по. Поскольку "группировать по" включают (сортировка + удаление), "отличные" включают (удаление)
Нет существенной разницы между группировкой и отдельным предложением, за исключением использования агрегатных функций. И то, и другое можно использовать для различения значений, но если с точки зрения производительности, группировать лучше. Когда используется отдельное ключевое слово, внутренне используется операция сортировки, которую можно просмотреть в плане выполнения.
Попробуйте простой пример
Объявить таблицу @tmpresult ( Id tinyint)
Вставить в @tmpresult Выбрать 5 Объединить все Выбрать 2 Объединить все Выбрать 3 Объединить все Выбрать 4
Выберите отличный идентификатор из @tmpresult