SQL Server: Diferencia entre PARTICIÓN POR y GRUPO POR
He estado usando GROUP BY
para todo tipo de consultas agregadas a lo largo de los años. Recientemente, he estado haciendo ingeniería inversa a algún código que usa PARTITION BY
para realizar agregaciones. Al leer toda la documentación que puedo encontrar sobre PARTITION BY
, suena mucho como GROUP BY
, tal vez con un poco de funcionalidad adicional añadida en? ¿Son dos versiones de la misma funcionalidad general, o son algo completamente diferente?
10 answers
Se usan en diferentes lugares. group by
modifica toda la consulta, como:
select customerId, count(*) as orderCount
from Orders
group by customerId
Pero partition by
solo funciona en una función de ventana , como row_number
:
select row_number() over (partition by customerId order by orderId)
as OrderNumberForThisCustomer
from Orders
A group by
normalmente reduce el número de filas devueltas enrollándolas y calculando promedios o sumas para cada fila. partition by
no afecta el número de filas devueltas, pero cambia cómo se calcula el resultado de una función de ventana.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-19 12:59:01
Podemos tomar un ejemplo simple
Tenemos una tabla llamada TableA
con los siguientes 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
Grupo Por
La cláusula SQL GROUP BY se puede usar en una instrucción SELECT para recopilar datos en varios registros y agrupar los resultados por uno o más columna.
En palabras más simples, el GRUPO POR declaración se usa junto con las funciones aggregate para agrupar el resultado establecido por uno o más columna.
Sintaxis :
SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;
Podemos aplicar GroupBy en nuestra tabla
select SUM(Mark)marksum,firstname from TableA
group by id,firstName
Resultados:
marksum firstname
----------------
94 ann
134 arun
47 new
41 sruthy
En nuestra tabla real tenemos 7 filas y cuando aplicamos group by id, el servidor agrupa los resultados basados en id
En palabras simples
Aquí group by normalmente reduce el número de filas devueltas por rolling ellos para arriba y la suma de cálculo para cada fila.
Partición por
Antes de ir a la partición por
Veamos la cláusula OVER
Según la definición de MSDN
La cláusula OVER define una ventana o un conjunto de filas especificado por el usuario conjunto de resultados de consulta. A continuación, una función de ventana calcula un valor para cada fila en la ventana. Puede utilizar la cláusula OVER con funciones para calcular valores agregados tales como medias móviles, agregados acumulativos, totales corrientes, o un top N por grupo de resultados.
partición por no reducirá el número de filas devueltas
Podemos aplicar la partición en nuestra tabla de ejemplo
select SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname from TableA
Resultado:
marksum firstname
-------------------
134 arun
134 arun
134 arun
94 ann
94 ann
41 sruthy
47 new
Mira los resultados que va a particionar las filas y los resultados de todas las filas no como grupo por.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-12 12:29:54
partition by
en realidad no enrolla los datos. Le permite restablecer algo por grupo. Por ejemplo, puede obtener una columna ordinal dentro de un grupo particionando en el campo de agrupación y usando rownum()
sobre las filas dentro de ese grupo. Esto le da algo que se comporta un poco como una columna de identidad que se restablece al principio de cada grupo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-03-08 20:41:53
PARTICIÓN POR Divide el conjunto de resultados en particiones. La función de ventana se aplica a cada partición por separado y el cálculo se reinicia para cada partición.
Se encuentra en este enlace: Sobre la Cláusula
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-03-08 20:44:44
Proporciona datos enrollados sin enrollar
Es decir, Supongamos que quiero devolver la posición relativa de la región de ventas
Usando PARTITION BY, puedo devolver la cantidad de ventas para una región dada y la cantidad MÁXIMA en todas las regiones de ventas en la misma fila.
Esto significa que tendrá datos repetidos, pero puede adaptarse al consumidor final en el sentido de que los datos se han agregado pero no se han perdido, como sería el caso con GROUP BY.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-03-09 16:02:06
PARTITION BY
es analítico, mientras que GROUP BY
es agregado. Para usar PARTITION BY
, tienes que contenerlo con una cláusula OVER.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-12-04 14:28:50
A mi entender Partition By es casi idéntica a Group By, pero con las siguientes diferencias:
Ese grupo por agrupa realmente el conjunto de resultados que devuelve una fila por grupo, lo que resulta por lo tanto en SQL Server solo permitiendo en la lista de SELECCIÓN funciones agregadas o columnas que forman parte de la cláusula group by (en cuyo caso SQL Server puede garantizar que hay resultados únicos para cada grupo).
Considere por ejemplo MySQL que permite tener en el SELECT enumere las columnas que no están definidas en la cláusula Group By, en cuyo caso todavía se devuelve una fila por grupo, sin embargo, si la columna no tiene resultados únicos, entonces no hay garantía de cuál será la salida.
Pero con Partición Por, aunque los resultados de la función son idénticos a los resultados de una función agregada con Grupo Por, todavía está obteniendo el conjunto de resultados normal, lo que significa que uno está obteniendo una fila por fila subyacente, y no una fila por grupo, y debido a esto uno puede tener columnas que no son únicas por grupo en la lista de selección.
Así como un resumen, el Grupo Por sería mejor cuando necesita una salida de una fila por grupo, y la partición Por sería mejor cuando uno necesita todas las filas pero todavía quiere la función agregada basada en un grupo.
Por supuesto, también puede haber problemas de rendimiento, consulte http://social.msdn.microsoft.com/Forums/ms-MY/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-07-19 15:21:32
Supongamos que tenemos 14 registros de la columna name
en la tabla
select name,count(*) as totalcount from person where name='Please fill out' group BY name;
Dará cuenta en una sola fila, es decir, 14
Pero en partition by
select row_number() over (partition by name) as total from person where name = 'Please fill out';
Se 14 filas de aumento en la cuenta
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-04-13 15:54:54
Pequeña observación. Mecanismo de automatización para generar dinámicamente SQL utilizando la ' partition by 'es mucho más sencillo de implementar en relación con el'group by'. En el caso de 'group by', debemos cuidar el contenido de la columna' select'.
Lo siento por mi inglés.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-06-10 07:30:23
-- 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-12-01 14:23:12