SQL Server: разница между разделом ПО и группой по

мы можем взять простой пример

у нас есть таблица с именем 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

Группы По

предложение 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;

мы можем применить GroupBy в нашей таблице

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 обычно уменьшает количество строк, возвращаемых при прокатке их поднимают и вычисляют сумму для каждого ряд.

раздел

прежде чем перейти к разделу по

давайте посмотрим на предложение OVER

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

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

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() над строками внутри этой группы. Это дает вам что-то, что ведет себя немного как столбец идентификаторов, который сбрасывается в начале каждой группы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

маленькое замечание. Механизм автоматизации для динамической генерации SQL с использованием "partition by" гораздо проще реализовать по отношению к "group by". В случае 'group by 'мы должны позаботиться о содержании столбца' select'.

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

-- BELOW IS A SAMPLE WHICH OUTLINES THE SIMPLE DIFFERENCES
-- READ IT AND THEN EXECUTE IT
-- THERE ARE THREE ROWS OF EACH COLOR INSERTED INTO THE TABLE
-- CREATE A database called testDB


-- use testDB
USE [TestDB]
GO


-- create Paints table
CREATE TABLE [dbo].[Paints](
    [Color] [varchar](50) NULL,
    [glossLevel] [varchar](50) NULL
) ON [PRIMARY]

GO


-- Populate Table
insert into paints (color, glossLevel)
select 'red', 'eggshell'
union
select 'red', 'glossy'
union
select 'red', 'flat'
union
select 'blue', 'eggshell'
union
select 'blue', 'glossy'
union
select 'blue', 'flat'
union
select 'orange', 'glossy'
union
select 'orange', 'flat'
union
select 'orange', 'eggshell'
union
select 'green', 'eggshell'
union
select 'green', 'glossy'
union
select 'green', 'flat'
union
select 'black', 'eggshell'
union
select 'black', 'glossy'
union
select 'black', 'flat'
union
select 'purple', 'eggshell'
union
select 'purple', 'glossy'
union
select 'purple', 'flat'
union
select 'salmon', 'eggshell'
union
select 'salmon', 'glossy'
union
select 'salmon', 'flat'


/*   COMPARE 'GROUP BY' color to 'OVER (PARTITION BY Color)'  */

-- GROUP BY Color 
-- row quantity defined by group by
-- aggregate (count(*)) defined by group by
select count(*) from paints
group by color

-- OVER (PARTITION BY... Color 
-- row quantity defined by main query
-- aggregate defined by OVER-PARTITION BY
select color
, glossLevel
, count(*) OVER (Partition by color)
from paints

/* COMPARE 'GROUP BY' color, glossLevel to 'OVER (PARTITION BY Color, GlossLevel)'  */

-- GROUP BY Color, GlossLevel
-- row quantity defined by GROUP BY
-- aggregate (count(*)) defined by GROUP BY
select count(*) from paints
group by color, glossLevel



-- Partition by Color, GlossLevel
-- row quantity defined by main query
-- aggregate (count(*)) defined by OVER-PARTITION BY
select color
, glossLevel
, count(*) OVER (Partition by color, glossLevel)
from paints