sql server: разница между partition by и group by

<p>я использовал <code>group by</code> для всех типов совокупных запросов на протяжении многих лет. недавно я провел обратный инжиниринг кода, который использует <code>partition by</code> для выполнения агрегации. читая всю документацию, которую я могу найти о <code>partition by</code>, она звучит очень похоже на <code>group by</code>, может быть, с добавлением немного дополнительной функциональности? это две версии одного и того же общего функционала или они что-то совершенно другое?</p>

<p>они используются в разных местах. <code>group by</code> изменяет весь запрос, например:</p><pre><code>select customerid, count(*) as ordercount from orders group by customerid </code></pre><p>но <code>partition by</code> просто работает с <a href="https://en.wikipedia.org/wiki/select\_(sql)#row\_number()\_window\_function">оконной функцией</a> , как <code>row\_number</code>:</p><pre><code>select row\_number() over (partition by customerid order by orderid) as ordernumberforthiscustomer from orders </code></pre><p><code>group by</code> обычно уменьшает количество возвращаемых строк, сворачивая их и вычисляя средние или суммы для каждой строки. <code>partition by</code> не влияет на количество возвращаемых строк, но меняет способ вычисления результата оконной функции.</p>

<p>мы можем взять простой пример </p><p>у нас есть таблица с именем <code>tablea</code> со следующими значениями.</p><pre><code>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 </code></pre><p><strong> группа по </strong> </p><blockquote> <p>предложение sql group by можно использовать в операторе select для сбора данные по нескольким записям и сгруппировать результаты по одной или нескольким колонны.</p> <p>в более простых словах оператор group by используется вместе с агрегатные функции для группировки результирующего набора по одному или нескольким колонны.</p> </blockquote><p><strong> синтаксис: </strong></p><pre><code>select expression1, expression2, ... expression\_n, aggregate\_function (aggregate\_expression) from tables where conditions group by expression1, expression2, ... expression\_n; </code></pre><p>мы можем применить groupby в нашей таблице </p><pre><code>select sum(mark)marksum,firstname from tablea group by id,firstname </code></pre><p>результаты :</p><pre><code>marksum firstname ---------------- 94 ann 134 arun 47 new 41 sruthy </code></pre><p>в нашей реальной таблице у нас есть 7 строк, и когда мы применяем group by id, сервер группирует результаты на основе id</p><p>простыми словами </p><blockquote> <p>здесь group by обычно уменьшает количество строк, возвращаемых путем прокрутки их и вычисление суммы для каждой строки.</p> </blockquote><p><strong> разделить на </strong></p><p>прежде чем перейти к разделу </p><p>давайте посмотрим на предложение over </p><p><strong> согласно определению msdn </strong> </p><blockquote> <p>предложение over определяет окно или заданный пользователем набор строк в набор результатов запроса. затем оконная функция вычисляет значение для каждой строки в окне вы можете использовать предложение over с функциями для вычисления агрегированные значения, такие как скользящие средние, совокупные агрегаты, промежуточные итоги, или n лучших результатов в группе.</p> </blockquote><p><em>partition by не уменьшит количество возвращаемых строк</em></p><p>мы можем применить раздел в нашем примере таблицы</p><pre><code>select sum(mark) over (partition by id) as marksum, firstname from tablea </code></pre><p>результат:</p><pre><code>marksum firstname ------------------- 134 arun 134 arun 134 arun 94 ann 94 ann 41 sruthy 47 new </code></pre><p>посмотрите на результаты, которые будут разделены на строки, и в результате все строки будут не похожи на group by. </p>

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

<blockquote> <p>partition by делит результирующий набор на разделы. функция окна применяется к каждому разделу отдельно, и вычисление перезапускается для каждого раздела.</p> </blockquote><p>найдено по этой ссылке: <a href="http://msdn.microsoft.com/en-us/library/ms189461.aspx">over clause</a> </p>

<p>предоставляет свернутые данные без свертывания</p><p>т.е. я хочу вернуть относительную позицию региона продаж</p><p>используя partition by, я могу вернуть сумму продаж для данного региона<strong> и </strong>максимальную сумму по всем регионам продаж в одной строке.</p><p>это означает, что у вас будут повторяющиеся данные, но это может подойти конечному потребителю в том смысле, что данные были агрегированы, но данные не были потеряны - как в случае с group by.</p>

<p><code>partition by</code> является аналитическим, а <code>group by</code> является совокупным. чтобы использовать <code>partition by</code>, вы должны содержать его с условием <a href="http://msdn.microsoft.com/en-us/library/ms189461.aspx">over</a> .</p>

<p>насколько я понимаю, раздел by почти идентичен группе by, но со следующими отличиями:</p><p>эта группа фактически группирует результирующий набор, возвращая по одной строке на группу, что приводит к тому, что sql server допускает только агрегированные функции или столбцы списка select, которые являются частью предложения group by (в этом случае sql server может гарантировать, что существуют уникальные результаты для каждой группы).</p><p>рассмотрим, например, mysql, который позволяет иметь в списке select столбцы, которые не определены в предложении group by, и в этом случае по-прежнему возвращается одна строка на группу, однако, если столбец не имеет уникальных результатов, гарантия не гарантируется. какой будет выход!</p><p>но с partition by, хотя результаты функции идентичны результатам агрегатной функции с group by, вы все равно получаете нормальный набор результатов, что означает, что один получает одну строку на базовую строку, а не одну строку на group, и из-за этого в списке select могут быть столбцы, которые не являются уникальными для каждой группы.</p><p>таким образом, в качестве резюме, group by будет лучше, когда требуется вывод по одной строке на группу, а partition by будет лучше, когда нужны все строки, но все же требуется агрегатная функция, основанная на группе. </p><p>конечно, также могут быть проблемы с производительностью, см. <a href="http://social.msdn.microsoft.com/forums/ms-my/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba">http://social.msdn.microsoft.com/forums/ms-my/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba</a> .</p>

<p>предположим, у нас есть 14 записей столбца <code>name</code> в таблице</p><p>в <code>group by</code></p><pre><code>select name,count(*) as totalcount from person where name='please fill out' group by name; </code></pre><p>это даст счет в одной строке, т.е. 14</p><p>но в <code>partition by</code></p><pre><code>select row\_number() over (partition by name) as total from person where name = 'please fill out'; </code></pre><p>это будет 14 рядов увеличения количества</p>

<p>небольшое наблюдение. механизм автоматизации для динамической генерации sql с использованием «разбиения по» гораздо проще реализовать по отношению к «группированию по». в случае «group by», мы должны позаботиться о содержимом столбца «select».</p><p>извините за мой английский.</p>