DISTINCT con PARTITION BY vs. GROUPBY

He encontrado algunas consultas SQL en una aplicación que estoy examinando así:

SELECT DISTINCT Company, Warehouse, Item, SUM(quantity) OVER (PARTITION BY Company, Warehouse, Item) AS stock 

Estoy bastante seguro de que esto da el mismo resultado que:

 SELECT Company, Warehouse, Item, SUM(quantity) AS stock GROUP BY Company, Warehouse, Item 

¿Hay algún beneficio (performance, legibilidad, flexibilidad adicional en la networkingacción de la consulta, mantenibilidad, etc.) de usar el primer enfoque sobre el posterior?

Actuación:

Ganador: GROUP BY

Algunas testings muy rudimentarias en una gran tabla con columnas no indexadas mostraron que, al less en mi caso, las dos consultas generaron un plan de consulta completamente diferente. El de PARTITION BY fue significativamente más lento.

El plan de consulta GROUP BY incluía solo una exploración de tabla y una operación de agregación, mientras que el plan PARTITION BY tenía dos autoenlaces nesteds en bucle. La PARTITION BY tomó aproximadamente 2800ms en la segunda ejecución, el GROUP BY tomó solo 500ms.

Legibilidad / Mantenibilidad:

Ganador: GROUP BY

Basado en las opiniones de los comentaristas aquí, el PARTITION BY es less legible para la mayoría de los desarrolladores, por lo que probablemente sea también más difícil de mantener en el futuro.

Flexibilidad

Ganador: PARTITION BY

PARTITION BY le brinda más flexibilidad al elegir las columnas de agrupación. Con GROUP BY puede tener solo un set de columnas de agrupamiento para todas las columnas agregadas. Con DISTINCT + PARTITION BY puede tener diferentes columnas en cada partición. También en algunos DBMS puede elegir entre más funciones de agregación / análisis en la cláusula OVER .

No es necesario utilizar sum() como function analítica con over partition by . No creo que haya una gran diferencia entre ellos en ningún sentido. En Oracle hay mucha más function analítica que function de agregación. Creo que ms-sql es el mismo caso. Y, por ejemplo, lag() , lead() , rank() , dense rank() , etc. son mucho más difíciles de implementar con solo group by . Por supuesto, este argumento no es realmente para defender la primera versión …

Tal vez anteriormente había más campos computados en el set de resultados que no son implementables con group by.

Aunque ambas consultas parecen calcular lo mismo al mirar las columnas, en realidad producen un set de filas completamente diferente.

El primero que use la function analítica obtendrá exactamente una fila por cada fila de input. Eso es para CADA información de stock, devolverá una fila con la cantidad total para la compañía / almacén / artículo asociado. (Por cierto, calcular el promedio tendría más sentido para mí, pero quién sabe …)

El segundo solo devolverá una sola fila para cada compañía / almacén / artículo combinaison.

Así que sí, en ese ejemplo la primera consulta parece un poco inútil … a less que desee calcular alguna estadística de nivel de existencias como la relación de existencias actual sobre la cantidad total por empresa / almacén / artículo (solo un ejemplo, no sé si ¡tiene algún significado comercial!)

La function analítica es un mecanismo muy poderoso en SQL, en cierto sentido mucho más poderoso que un grupo por. Pero úselo con cuidado … Una regla empírica simple podría ser: si puede calcularlo usando un grupo por, bueno, no use una function analítica;)