Servidor SQL: diferença entre partição por e grupo por

Temos uma tabela chamada TableA com os seguintes valores .

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

Agrupar Por

O grupo SQL por cláusula pode ser usado numa instrução seleccionada para recolher dados através de vários registos e agrupar os resultados por um ou mais coluna.

Em palavras mais simples, o grupo por declaração é usado em conjunto com as funções agregadas para agrupar o conjunto de resultados por uma ou mais coluna.

Sintaxe :

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

Podemos aplicar GroupBy na nossa tabela

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

Resultados:

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

Na nossa tabela real Temos 7 linhas e quando aplicamos grupo por id, o grupo servidor os resultados com base no id

Em palavras simples

Aqui o grupo reduz normalmente o número de linhas devolvidas ao rolar eles para cima e calculando a soma para cada linha.

Partição por @ info / Plain

De acordo com a definição MSDN

A cláusula OVER define uma janela ou um conjunto de linhas indicado pelo Utilizador dentro de uma conjunto de resultados da pesquisa. Uma função de janela então calcula um valor para cada linha na janela. Você pode usar a cláusula de OVER com funções para computar valores agregados, tais como médias móveis, agregados cumulativos, a executar totais, ou um n superior por resultados de grupo.

a partição por não irá reduzir o número de linhas devolvidas

Podemos aplicar a partição na nossa tabela de exemplo

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  

Olhe para os resultados que irá dividir as linhas e resultados Todas as linhas não como o grupo por.

partition by não enrola os dados. Ele permite que você reinicie algo em uma base de grupo. Por exemplo, você pode obter uma coluna ordinal dentro de um grupo se particionar no campo de agrupamento e usar rownum() sobre as linhas dentro desse grupo. Isso lhe dá algo que se comporta um pouco como uma coluna de identidade que reinicia no início de cada grupo.

PARTIÇÃO POR Divide o resultado em partições. A função da janela é aplicada a cada partição separadamente e a computação reinicia para cada partição.

Encontrado nesta ligação: sobre a cláusula

Fornece dados enrolados sem rebolar

Usando a partição por, eu posso devolver o montante de vendas para uma dada região e O montante máximo em todas as regiões de vendas na mesma linha.

PARTITION BY é analítico, enquanto GROUP BY é agregado. Para utilizar {[[0]}, deve contê-lo com uma cláusula sobre .

A partir do meu entendimento a partição Por é quase idêntica ao grupo por, mas com as seguintes diferenças:

Esse grupo agrupa efectivamente o conjunto de resultados devolvendo uma linha por grupo, o que resulta, portanto, no servidor SQL apenas permitindo na lista seleccionar funções agregadas ou colunas que fazem parte do grupo por cláusula (caso em que o servidor SQL pode garantir que existem resultados únicos para cada grupo).

Considere por exemplo o MySQL que permite ter no Seleccione as colunas da lista que não estão definidas no grupo por cláusula, caso em que uma linha ainda está a ser devolvida por grupo, no entanto, se a coluna não tiver resultados únicos, então não há nenhuma garantia de qual será o resultado!

Mas com a partição por, Embora os resultados da função sejam idênticos aos resultados de uma função agregada com o grupo por, ainda assim você está recebendo o conjunto de resultados normal, o que significa que um está recebendo uma linha por linha subjacente, e não uma linha por grupo, e por causa disso, pode-se ter colunas que não são únicas por grupo na lista selecionada.

De modo a ser um resumo, o grupo Por seria melhor quando necessita de uma saída de uma linha por grupo, e a partição Por seria melhor quando se necessita de todas as linhas, mas ainda quer a função agregada baseada num grupo.

Suponha que temos 14 registos da coluna name no quadro

Em group by

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

Contará numa única linha i. e 14

Mas em partition by

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

Será 14 linhas de aumento na contagem

Pequena observação. Mecanismo de automação para gerar dinamicamente SQL usando a "partição Por" é muito mais simples de implementar em relação ao "grupo por". No caso de 'grupo por', devemos cuidar do conteúdo da coluna 'selecionar'.

-- 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