10 nuevas habilidades SQL para ordenar, agrupar y realizar estadísticas
Organizar los datos de forma significativa puede ser un desafío. A veces lo que necesita puede ser una simple clasificación, pero a menudo necesita hacer más, necesita agrupar para realizar análisis y estadísticas. Afortunadamente, SQL proporciona una gran cantidad de declaraciones y operaciones para ordenar, agrupar y resumir. Los siguientes consejos le ayudarán a reconocer cuándo ordenar, cuándo agrupar y cuándo y cómo contar. Consulte los Libros en línea para obtener información detallada sobre cada estado de cuenta y operación.
1. Utilice la clasificación para mantener los datos en orden.
A menudo, todo lo que sus datos realmente necesitan es estar en algún orden. La declaración ORDER BY de SQL puede organizar datos en orden alfabético o numérico. Por lo tanto, los valores similares se clasifican en grupos. Sin embargo, el resultado de la clasificación durante esta agrupación no es la agrupación real. ORDER BY muestra cada registro, mientras que la agrupación puede representar muchos registros.
2. Agrupar para eliminar valores duplicados
La diferencia entre ordenar y agrupar es: los datos ordenados muestran todos los registros (dentro del rango estándar limitado), mientras que los datos agrupados no muestran Todos los registros. . La declaración GROUP BY muestra solo un registro para el mismo valor. Por ejemplo, la instrucción GROUP BY de la siguiente instrucción devuelve solo columnas codificadas en zip para datos recurrentes en la fuente de datos.
SELECT ZIP FROM Customers GROUP BY ZIP
Solo incluye aquellos registros definidos por las sentencias GROUP BY y SELECT. En otras palabras, la lista SELECT debe satisfacer la lista GROUP BY. Pero hay una excepción: la lista SELECT puede contener funciones agregadas (la declaración GROUP BY no permite el uso de funciones agregadas). Cabe señalar que la declaración GROUP BY no ordena los grupos de resultados. Para ordenar los grupos alfabética o numéricamente, debe agregar una declaración ORDER BY. Además, no se puede hacer referencia a campos con alias en las declaraciones GROUP BY. Las columnas de agrupación deben ser datos subyacentes, pero no es necesario que aparezcan en los resultados.
3. Filtrar datos antes de agrupar
Puedes agregar una declaración WHERE para filtrar los datos en la agrupación obtenida por GROUP BY. Por ejemplo, la siguiente declaración devuelve solo la columna de codificación ZIP para clientes de Kentucky.
SELECT ZIP FROM CustomersWHEREState = 'KY' GROUP BY ZIP
Cabe señalar que la declaración WHERE realiza el filtrado de datos antes de que se evalúe la declaración GROUP BY. Al igual que la declaración GROUP BY, la declaración WHERE no admite funciones agregadas.
4. Devolver todos los grupos
Cuando utilice la instrucción WHERE para filtrar datos, solo los registros que especifique se mostrarán en el grupo de resultados y los datos que cumplan con la definición del grupo. pero no cumple con las condiciones del filtro. No se incluirá en un grupo. Cuando desee incluir todos los datos en el grupo, simplemente agregue la palabra clave TODOS, entonces la condición WHERE no funcionará. Por ejemplo, agregar la palabra clave TODOS al ejemplo anterior devolvería todos los paquetes ZIP, no solo los de Kentucky.
SELECCIONAR ZIP FROM CustomersWHEREState = 'KY' GROUP BY ALL ZIP
Parece que estas dos declaraciones entran en conflicto y no puede utilizar la palabra clave ALL de esta manera. Cuando utiliza funciones agregadas para calcular una columna, puede resultar conveniente utilizar la palabra clave ALL. Por ejemplo, la siguiente declaración cuenta la cantidad de clientes en cada código postal de Kentucky y muestra los otros valores de código postal.
SELECCIONAR ZIP, Count(ZIP) AS KYCustomersByZIP FROM
CustomersWHEREState = 'KY' GROUP BY ALL ZIP
La agrupación de resultados incluye todos los valores ZIP en Sin embargo, para los datos subyacentes, la columna agregada (KYCustomersByZIP) mostrará 0 para aquellos que no sean agrupaciones ZIP de Kentucky. La consulta remota no admite GRUPO POR TODOS.
5. Filtrar datos después de agrupar
La declaración WHERE se calcula antes de la declaración GROUP BY. Cuando necesite filtrar datos después de agruparlos, puede utilizar la declaración HAVING. Normalmente, los resultados devueltos por la declaración WHERE y la declaración HAVING son los mismos, pero vale la pena señalar que las dos declaraciones no son intercambiables. Cuando esté confundido, siga estas instrucciones: Utilice la instrucción WHERE para filtrar registros y la instrucción HAVING para filtrar grupos.
Generalmente, utilizará la instrucción HAVING y una función agregada para calcular una agrupación. Por ejemplo, la siguiente declaración devuelve una columna codificada en ZIP, pero es posible que no contenga todos los ZIP en la fuente de datos subyacente.
SELECCIONE ZIP, Count(ZIP) COMO CustomersByZIP FROM
Clientes GRUPO POR ZIP QUE TIENEN Count(ZIP) = 1
Solo se muestran aquellos grupos que contienen un cliente en los resultados.
6. Obtenga más información sobre las declaraciones WHERE y HAVING
Si todavía no sabe cuándo usar WHERE y cuándo usar HAVING, siga las instrucciones a continuación:
La declaración WHERE precede a la declaración GROUP BY SQL evaluará la declaración WHERE antes de agrupar.
La declaración HAVING sigue a la declaración GROUP BY; SQL calculará la declaración HAVING después de la agrupación.
7. Utilice funciones agregadas para contar datos agrupados.
Los datos agrupados pueden ayudarnos a analizar los datos, pero a veces es posible que necesitemos más información que solo agrupar. Puede utilizar funciones agregadas para contar datos agrupados. Por ejemplo, la siguiente declaración muestra el precio total de cada lote de órdenes de compra.
SELECCIONE ID de pedido, Suma (costo * cantidad) AS OrderTotalFROM Orders GROUP BY OrderID
Para otras agrupaciones, las columnas SELECT y GROUP BY deben coincidir. La excepción a esta regla es cuando la instrucción SELECT contiene funciones agregadas.
8. Datos estadísticos agregados
Puede continuar recopilando datos estadísticos para mostrar estadísticas de categoría para cada grupo. El operador ROLLUP de SQL puede mostrar estadísticas categóricas adicionales para cada grupo. Esta estadística categórica se calcula utilizando una función agregada en todos los registros de cada grupo. La siguiente declaración calcula OrderTotal para cada grupo:
SELECT Customer, OrderNumber, Sum(Costo * Cantidad)AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber FROM ROLLUP
Para dos Para agrupaciones con Valores OderTotal de 20 y 25 respectivamente, ROLLUP muestra un valor OrderTotal de 45.
El primer registro en el resultado ROLLUP es sí, porque cuenta todos los registros agrupados y este valor es el valor total de todo el conjunto de registros.
ROLLUP no admite DISTINCT en funciones agregadas, ni admite la declaración GROUP BY ALL.
9. Cuenta cada columna.
El operador CUBE va un paso más allá que ROLLUP. Devuelve el número de valores duplicados en cada grupo. Tiene los mismos resultados que ROLLUP, pero contiene un registro adicional por columna del CUBE para cada cliente. La siguiente declaración muestra estadísticas para cada grupo y estadísticas adicionales para cada cliente.
SELECCIONE Cliente, Número de pedido, Suma (Costo * Cantidad) COMO Total del pedido DESDE Pedidos GRUPO POR Cliente,
Número de pedido CON CUBO
CUBO puede brindar las estadísticas más completas . No solo completa las funciones de agregación y ROLLUP, sino que también calcula otras columnas que definen agrupaciones. En otras palabras, CUBE cuenta todas las combinaciones de columnas posibles.
CUBE no admite la declaración GROUP BY ALL.
10: Ordenar resultados estadísticos
Cuando los resultados del CUBO son confusos (que a menudo lo son), puede agregar una función de AGRUPACIÓN, como se muestra a continuación:
SELECCIONE GRUPO (Cliente), Número de pedido, Suma (Costo * Cantidad) COMO Total del pedido DESDE GRUPO de pedidos
POR Cliente, Número de pedido CON CUBO
Cada fila del resultado contiene dos valores adicionales:
Un valor de 1 indica que el valor de la izquierda es un valor estadístico y es el operador de ROLLUP o CUBE.
Un valor de 0 indica que el valor de la izquierda es un registro detallado generado por la declaración GROUP BY original.