Las funciones de agrupacion le permiten aplicar los cálculos sobre los valores de una columna.La adición de la cláusula GROUP BY permite proporcionar totales en los subconjuntos de los datos
Funciones de agrupacion realizan cálculos en un conjunto de datos y retornan un valor escalar único.
Las funciones de agrupacion siguientes están disponibles en SQL Server 2008:
- AVG: Devuelve el promedio de todos los valores en el conjunto de datos.
- CHECKSUM_AGG: Devuelve la suma de todos los valores en el conjunto de datos.
- COUNT: Devuelve el número de valores contenidos en el conjunto de datos. COUNT (*) devuelve el número de filas en el conjunto. Cuando una columna se especifica, como COUNT (FaxNo), el valor devuelto corresponde al número de filas que contienen datos de esa columna. Los valores NULL se pasan por alto. Además, DISTINCT COUNT devuelve el número de únicos valores no NULL en el conjunto de datos.
- COUNT_BIG: Funciona igual que COUNT, pero que devuelve el tipo de datos bigint, mientras que COUNT devuelve sólo el tipo de datos int.
- GROUPING: Retorna 1 o 0 e identifica las filas como filas agrupadas o el detalle cuando la instrucción GROUP BY se utiliza. Un valor de 1 indica una agrupacion de filas, mientras que 0 indica los detalles.
- MAX: Devuelve el valor más alto en el conjunto de datos numéricos de los datos y los campos basados en caracteres.
- MIN: Devuelve el valor más bajo en el conjunto de datos numéricos, datos y campos basados en caracteres.
- SUM: Devuelve el número total de los valores en el conjunto de datos. Usted puede especificar ALL o DISTINCT para producir ya sea la suma de todos los valores o solo valores distintos en el conjunto de datos.
- STDEV: devuelve la desviación estándar de los valores en el conjunto de datos.
- STDEVP: devuelve la desviación estándar para la población de los valores en el conjunto de datos.
- VAR: Devuelve la variación estadística de los valores en el conjunto de datos.
- VARP: Devuelve la variación estadística de la población de los valores en el conjunto de datos.
El siguiente ejemplo devuelve el promedio, máximo y mínimo de los precios de lista de todos los productos de la tabla Production.Product. Los productos que son nuevos y no han sido un precio o que no se venden a los consumidores tener un precio de 0. Para proporcionar las agrupaciones más precisas, estos productos se eliminan del conjunto de resultados:
SELECT AVG(Listprice) AS 'Average' , MIN(Listprice) AS 'Minimum' , MAX(Listprice) AS 'Maximum' FROM Production.Product WHERE ListPrice <> 0;
Uso de la cláusula GROUP BY
Con frecuencia, la cláusula GROUP BY se incluye en las consultas con funciones de agrupacion. Cuando una función de agrupacion se incluye en la cláusula SELECT, todas las demás expresiones de la cláusula SELECT o bien deben ser funciones de agrupacion o bien deben ser incluidas en una cláusula GROUP BY.
La cláusula GROUP BY permite definir los subtotales de los datos agregados. Por ejemplo, el siguiente comando devuelve el promedio, mínimo y máximo de los precios de lista de productos que pertenecen a cada subcategoría de producto:
SELECT Production.Product.ProductSubcategoryID , AVG(Listprice) AS 'Average' , MIN(Listprice) AS 'Minimum' , MAX(Listprice) AS 'Maximum' FROM Production.Product WHERE ListPrice <> 0 GROUP BY Product.ProductSubcategoryID;
El conjunto de resultados de la consulta se muestra en la imagen anterior. La fila de arriba, donde aparece la ProductSubcategoryID como NULL, es la fila de resumen que ofrece los precios medios, mínimos y máxima de productos a través de todas las subcategorías.
Usando los operadores WITH ROLLUP y WITH CUBE
Para poder ver los subtotales de más de una columna, se puede agregar el operador WITH ROLLUP o WITH CUBE.
Estos operadores ofrecen una suma total, junto con subtotales sobre la base de las columnas incluidas en la instrucción GROUP BY.
El orden en que las columnas se especifiquen cambia los datos de resumen devueltos en el conjunto de resultados de la función WITH ROLLUP.
Por ejemplo, en la consulta que se muestra aquí devuelve el promedio, mínimo y máximo de los precios de lista para cada subcategoría dentro de cada categoría. Debido a que tanto las columnas de subcategoría y categoríaID se enumeran en la cláusula SELECT, ambos deben ser enumerados en la cláusula GROUP BY también.
Dado que la columna Production.ProductCategory.ProductCategoryID aparece en primer lugar en la instrucción GROUP BY, se incluye una fila que muestra las agrupaciones especificadas para todas las subcategorías de esta categoría entre sí. Si la columna Product.ProductSubcategoryID se había incluido en primer lugar, las filas de resumen devuelven los valores agrupados de todas las categorías para cada subcategoría.
SELECT Production.ProductCategory.ProductCategoryID , Production.Product.ProductSubcategoryID , AVG(Listprice) AS 'Average' , MIN(Listprice) AS 'Minimum' , MAX(Listprice) AS 'Maximum' FROM Production.Product JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE ListPrice <> 0 GROUP BY Production.ProductCategory.ProductCategoryID
, Product.ProductSubcategoryID
WITH ROLLUP
El conjunto de resultados parciales representados en la imagen anterior muestra el promedio, mínimo y máximo para los precios de productos subcategoría 25 en la tercera fila de la foto (fila 27).
El promedio, mínimo y los precios máximos para la categoría 3 se enumeran en la fila 28 y se hará referencia con el valor NULL para la columna de lista ProductSubcateogryID.
El promedio, mínimo y los precios máximos en todas las categorías y subcategorías se muestran en la fila 42 y se hará referencia con los valores NULL, tanto en el ProductCategoryID y las columnas ProductSubcategoryID.
Si el operador ROLLUP WITH no se había especificado, las filas de resumen en las filas 28 y 41 no han sido incluidos en el conjunto de resultados.
El operador WITH CUBE se puede utilizar cuando en la información resumida hay que incluir a más de una columna. Ya que cada subcategoría existe en una sola categoría, el operador WITH CUBE no tiene sentido con este ejemplo. Pero si tuviera una lista de los precios promedio de cada producto en un determinado orden y dentro de cada subcategoría, que podría utilizar el operador WITH CUBE para devolver la información basado en cada uno de identificación de la orden y en cada sub-categoría ID.
También tendre la fila total general incluida en el conjunto de resultados.
Nota Importante:
Evite el uso de el operador WITH CUBE en tablas grandes, donde más de tres columnas existentes en la cláusula GROUP BY . El operador WITH CUBE devuelve información de resumen de todas las columnas que figuran en la cláusula GROUP BY . Estos conjuntos de resultados pueden crecer muy rápidamente cuando las columnas adicionales se añaden a la cláusula GROUP BY , y puede disminuir el rendimiento drásticamente.
Utilizando la función de agrupacion GROUPING
Cuando hay valores NULL que aparecen en una columna que se devuelve por la cláusula GROUP BY, puede ser difícil de encontrar e interpretar las filas de resumen producido. La función de agrupación puede ser añadido a la cláusula SELECT para mostrar las filas que contienen información resumida y las filas que contienen información detallada que puede incluir valores NULL.
La función de agregado GROUPING devuelve un 1, lo que indica un agregado o una fila de resumen, o un 0, lo que indica una fila de detalles.
A pesar de estos conjuntos de resultados pueden llegar a ser abrumadores y difíciles de leer, si los resultados están siendo procesados por una aplicación, una aplicación puede utilizar el 1s y regresar 0s para determinar qué filas incluyen datos de resumen y luego realizar las operaciones adecuadas.
La muestra incluye las siguientes columnas de agrupamiento con la consulta ROLLUP utilizados en la muestra anterior. Tenga en cuenta que en el conjunto de resultados parciales muestran en la imagen siguiente, los datos de resumen no se incluye para todas las categorías de productos en una subcategoría. Cualquiera de las columnas en la cláusula GROUP BY tendría que ser revertida, o el operador WITH CUBE que es necesario especificar los datos de resumen para estar presente en esta columna:
SELECT Production.ProductCategory.ProductCategoryID ,GROUPING (Production.ProductCategory.ProductCategoryID) , Production.Product.ProductSubcategoryID ,GROUPING (Production.Product.ProductSubcategoryID) , AVG(Listprice) AS 'Average' , MIN(Listprice) AS 'Minimum' , MAX(Listprice) AS 'Maximum' FROM Production.Product JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE ListPrice <> 0 GROUP BY Production.ProductCategory.ProductCategoryID
, Product.ProductSubcategoryID WITH ROLLUP;
Utilizando GROUPING SETS
GROUPING SETS se añadieron en SQL Server 2008 para darle una mayor flexibilidad en la definición de las sentencias SELECT que incluye funciones de agregado. Dependiendo de cómo los GROUPING SETS están definidos, puede ser equivalente a un ROLLUP estándar o un operador CUBE, a varios GROUP BY combinadas con UNION ALL, o un subconjunto de los datos que normalmente se devuelve un operador ROLLUP o CUBE.
Ejemplos de GROUPING SETS:
El siguiente ejemplo incluye dos consultas independientes que producen conjuntos de idénticos resultados, pero la consulta GROUPING SETS mostrada es mucho más limpia y más fácil de leer. Los comentario entre las consultas se han insertado para señalarcomo el código equivalente en la segunda consulta más fácil de localizar:
Consulta 1:
SELECT Production.ProductCategory.ProductCategoryID , Production.Product.ProductSubcategoryID , AVG(Listprice) AS 'Average' FROM Production.Product JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE ListPrice <> 0 GROUP BY GROUPING SETS ((Production.ProductCategory.ProductCategoryID), (Product.ProductSubcategoryID))
Consulta 2:
SELECT NULL AS 'ProductCategoryID'
, Production.Product.ProductSubcategoryID , AVG(Listprice) AS 'Average' FROM Production.Product JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE ListPrice <> 0 GROUP BY (Production.Product.ProductSubcategoryID) UNION ALL SELECT Production.ProductCategory.ProductCategoryID, NULL , AVG(Listprice) AS 'Average' FROM Production.Product JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE ListPrice <> 0 GROUP BY (Production.ProductCategory.ProductCategoryID)
El resultado parcial de ambas consultas es el siguiente:
Utilizar la cláusula HAVING
A pesar de que puede utilizar la cláusula WHERE para limitar el conjunto de resultados basada en los valores que existen en las columnas especificadas en la cláusula FROM, la cláusula HAVING le permite filtrar en función de los resultados de los cálculos realizados por las funciones de agrupacion.
La consulta siguiente utiliza una cláusula HAVING que devuelve subcategorías único producto donde el precio mínimo es superior a $ 200:
SELECT Production.ProductCategory.ProductCategoryID , Production.Product.ProductSubcategoryID , AVG(Listprice) AS 'Average' , MIN(Listprice) AS 'Minimum' , MAX(Listprice) AS 'Maximum' FROM Production.Product JOIN Production.ProductSubcategory ON Production.ProductSubcategory.ProductSubcategoryID = Production.Product.ProductSubcategoryID JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID WHERE ListPrice <> 0 GROUP BY Production.ProductCategory.ProductCategoryID
, Product.ProductSubcategoryID WITH ROLLUP HAVING MIN(ListPrice) > 200;
Resumen de la lección
Funciones de agrupacion para realizan cálculos sobre las expresiones que se proporcionan como entrada.
- Utilice la cláusula GROUP BY cuando la agrupacion se debe aplicar sobre la base de los datos en filas específicas en lugar de toda la tabla.
- Incluir todas las columnas de una SELECT, WHERE o cláusula ORDER BY en la cláusula GROUP BY .
- Use ROLLUP y CUBE para proporcionar información resumida adicional.
- Utilice la función GROUPING para mostrar las filas que contienen datos de resumen proporcionado por el ROLLUP o CUBE operadores.
- El uso de GROUPING SETS para proporcionar mayor flexibilidad y facilidad de lectura de su grupo consultas.
No hay comentarios:
Publicar un comentario