SQL Server: разница между PARTITION BY и GROUP BY

Их используют в разных местах. group by изменяет весь запрос, например:

select customerId, count(*) as orderCount
from Orders
group by customerId

Но partition by просто работает с оконной функцией, например row_number:

select row_number() over (partition by customerId order by orderId)
    as OrderNumberForThisCustomer
from Orders

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

Возьмем простой пример.

Рассмотрим таблицу с именем TableA со следующими значениями:

id  firstname                   lastname                    Mark
-------------------------------------------------------------------
1   arun                        prasanth                    40
2   ann                         antony                      45
3   sruthy                      abc                         41
6   new                         abc                         47
1   arun                        prasanth                    45
1   arun                        prasanth                    49
2   ann                         antony                      49

GROUP BY

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

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

Синтаксис:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Мы можем применить GROUP BY в нашей таблице:

select SUM(Mark)marksum,firstname from TableA
group by id,firstName

Полученные результаты:

marksum  firstname
----------------
94      ann                      
134     arun                     
47      new                      
41      sruthy   

В нашей реальной таблице у нас 7 строк, и когда мы применяем GROUP BY id, сервер группирует результаты на основе id:

Простыми словами:

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

PARTITION BY

Прежде чем перейти к PARTITION BY, давайте посмотрим на предложение OVER:

Согласно определению MSDN:

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

PARTITION BY не уменьшает количество возвращаемых строк.

Мы можем применить PARTITION BY в нашем примере таблицы:

SELECT SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname FROM TableA

Результат:

marksum firstname 
-------------------
134     arun                     
134     arun                     
134     arun                     
94      ann                      
94      ann                      
41      sruthy                   
47      new  

Посмотрите на результаты - он разделит строки и вернет все строки, в отличие от GROUP BY.

partition by фактически не сводит данные. Это позволяет вам сбросить что-то для каждой группы. Например, вы можете получить порядковый столбец в группе, разделив поле группировки и используя rownum() над строками в этой группе. Это дает вам что-то, что немного похоже на столбец идентификаторов, который сбрасывается в начале каждой группы.

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

Найдено по этой ссылке: OVER Clause

Он предоставляет сводные данные без сворачивания

т.е. предположим, я хочу вернуть относительное положение региона продаж

Используя PARTITION BY, я могу вернуть сумму продаж для данного региона и максимальную сумму для всех регионов продаж в одной строке.

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

PARTITION BY является аналитическим, а GROUP BY - агрегированным. Чтобы использовать PARTITION BY, вы должны содержать его с помощью предложения OVER. .

Насколько я понимаю, Partition By почти идентичен Group By, но со следующими отличиями:

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

Рассмотрим, например, MySQL, который позволяет иметь в списке SELECT столбцы, которые не определены в предложении Group By, и в этом случае по-прежнему возвращается одна строка для каждой группы, однако, если столбец не имеет уникальных результатов, нет никаких гарантий какой будет выход!

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

Таким образом, можно подытожить, что Group By лучше всего подходит, когда требуется вывод одной строки для каждой группы, а Partition By лучше всего, когда нужны все строки, но все же требуется агрегатная функция на основе группы.

Конечно, могут быть проблемы с производительностью, см. http://social.msdn.microsoft.com/Forums/ms-MY/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba.

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

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

Предположим, у нас есть 14 записей name столбца в таблице

in group by

select name,count(*) as totalcount from person where name='Please fill out' group BY name;

он даст счет в одной строке, т.е. 14

но в partition by

select row_number() over (partition by name) as total from person where name = 'Please fill out';

это будет 14 рядов увеличения количества

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

Извините за мой английский.

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

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

Вот полезная статья, объясняющая разницу: http://alevryustemov.com/sql/sql-partition-by/