En los pocos artículos anteriores de esta serie, hemos sentado las bases sobre cómo crear un informe. Daremos un paso más allá y veremos cómo utilizar el operador de tabla PIVOT de SQL Server. Comenzaremos con una consulta simple y avanzaremos lentamente hacia SQL dinámico y PIVOT. Empecemos.
Modelo de datos y la idea general
aña El modelo de datos que usaremos es el mismo que hemos estado usando a lo largo de esta serie.
Nuestra tarea hoy es crear un informe (usando el operador PIVOT de SQL Server) donde cada ciudad estará en una fila separada y donde contaremos el número de todos los resultados de llamadas relacionados con cada ciudad. Por lo tanto, todos los resultados (todos los valores del diccionario) serán columnas en nuestro informe.
Dado que «una imagen vale más que mil palabras», también describiremos nuestro objetivo final con la imagen.
Para lograr esto, necesitaremos usar datos de 4 tablas call, call_outcome, customer y city. Por lo tanto, Sería bueno que observara más de cerca el modelo siguiente y analizara cómo se relacionan estas tablas.
Comenzaremos examinando los datos de las tablas y, con cada consulta, nos acercaremos un paso más a el resultado deseado (consulta). El enfoque que se utiliza aquí es el mismo que se utiliza en el artículo Learn SQL: Cree un informe manualmente utilizando consultas SQL.
Categorías y datos del informe
El El primer conjunto de consultas con el que comenzaremos es el que examina los datos actualmente presentes en las 4 tablas que necesitaremos usar para crear el informe. Lo haremos para poder confirmar que el informe final arrojó lo que debería. Después de eso, crearemos consultas wh ich devuelve categorías de informes y los datos del informe, así como consultas de tabla PIVOT de SQL Server.
|
1
2
3
4
5
|
– – 1 – seleccione los datos que necesitamos
SELECT * FROM call;
SELECT * FROM call_outcome;
SELECT * FROM customer;
SELECT * FROM city;
|
Estas consultas son tan simples como podrían ser, por lo que no hay nada especial que comentar con respecto a su sintaxis. Con respecto a los datos devueltos, debemos tener en cuenta lo siguiente:
- La tabla de ciudades contiene 6 ciudades y, por lo tanto, debemos tener 6 filas en el informe final
- Hay 3 resultados posibles en la tabla call_outcome, por lo que deberíamos tener 3 columnas para los resultados (4 columnas en total – > una es para el nombre de la ciudad)
- La llamada de la tabla tiene 10 filas (solo 8 en la imagen de arriba), por lo que en la tabla final, la suma de todos los resultados debe ser 10 (cada llamada puede tener exactamente 1 resultado)
Lo siguiente que debe hacer es preparar categorías de informes. Queremos tener una combinación de todas las ciudades y todos los posibles resultados de llamadas. Para lograrlo, usaremos CROSS JOIN (producto cartesiano).
Tener todas las categorías de informes garantizará que tendremos una fila en el informe, sin importar si ese par tiene datos o no. Y eso es lo que queremos: ver 0 en esa categoría de informes y no perderla por completo.
Lo siguiente que debe hacer es unir las 4 tablas que contienen los datos que necesitamos.
La consulta en sí no es compleja. En el resultado, debemos notar que tenemos 10 filas, el mismo número que el número de llamadas que tenemos en la base de datos. Además, he utilizado la función DATEDIFF para devolver la duración de cada llamada en segundos. Podría simplemente poner call.id aquí, pero quería recordarnos la función DATEDIFF que hemos mencionado en este artículo. Podríamos usar esta duración si necesitamos una SUMA / PROMEDIO de segundos gastados por cada categoría de informe (ciudad & resultado de la llamada).
Informe sin SQL Server PIVOT TABLE
Ahora estamos listos para unir categorías y datos. Usaremos las dos consultas mencionadas anteriormente como subconsultas y las uniremos usando LEFT JOIN (para tener todas las categorías presentes en el resultado final).
Tenemos las 18 combinaciones (6 ciudades diferentes * 3 resultados de llamadas diferentes), y también tenemos las 10 llamadas presentes aquí (filas con la duración de la llamada < > NULL).
Vamos a crear ahora la consulta PIVOT de SQL Server.
TABLA PIVOT de SQL Server (estática)
Hasta ahora, hemos logrado extraer los datos que necesitamos y los tenemos como lista. Podríamos exportar datos a Excel y realizar transformaciones allí. En realidad lo haremos en el próximo artículo.Aún así, hoy queremos resolver esto usando el operador PIVOT de SQL Server. En esta sección, cubriremos el PIVOT «estático» de SQL Server. Primero veamos la consulta utilizada y el resultado.
Podemos observar que el resultado es exactamente lo que queríamos. Tenemos cada ciudad en una fila y las 3 categorías para los resultados de llamadas en columnas separadas. Si no hay datos para un determinado par ciudad-resultado, esa celda debe contener el valor 0.
Comentemos ahora la consulta PIVOT de SQL Server. Estas son algunas cosas que considero importantes mencionar aquí:
- En la primera parte de consulta – report_data, hemos copiado y pegado la consulta de la sección anterior. El único cambio fue que no teníamos ORDER BY y GROUP BY en esta parte de la consulta porque no se pueden aplicar aquí (deberían ir después se genera el resultado final de la consulta)
- La parte PIVOT de la consulta consta de 2 partes. En la primera, definiremos qué función agregada queremos aplicar. En nuestro caso, esta es – COUNT ( call_duration). En FOR parte de qu ery, definiremos columnas. Literariamente enumeramos todos los valores que queremos tener como columnas. Esto es una codificación rígida. Si agregamos un nuevo valor a la tabla, eso no afectará la consulta (y debería)
Esta consulta hace su trabajo, y funcionará perfectamente si nunca cambiamos (agregar nuevo , eliminar, actualizar sus nombres) resultados de llamadas en la tabla relacionada. Ese podría ser el problema en los casos en que necesitemos hacer cambios en el diccionario. No queremos pensar si hay una consulta que no funciona como se esperaba. Para resolver esto, necesitaremos usar un nuevo concepto: SQL dinámico.
TABLA PIVOT de SQL Server dinámico
Antes de pasar al código, expliquemos brevemente qué es realmente el SQL dinámico es (daremos una revisión mucho más detallada en los próximos artículos). La explicación más simple es que en SQL, puede «construir» sus consultas como cadenas y luego pasar esa cadena como un parámetro para el procedimiento almacenado de SQL Server. Este procedimiento ejecutará las declaraciones SQL almacenadas en esa cadena (por supuesto , si la sintaxis es correcta).
Si bien esto puede sonar como algo que no se usa tan comúnmente, tiene bastantes lugares donde le hace la vida mucho más fácil. Crear una consulta de tabla PIVOT de SQL Server con un número de columnas es exactamente uno de esos casos.
La idea de este enfoque es la siguiente:
- Declararemos una variable donde almacenar todos los nombres de las columnas (@columns ), y la variable donde almacenar la consulta completa (@query)
- Usando la consulta SELECT, encontraremos todos los valores almacenados en la columna result_text. El siguiente código – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, agregará el nombre de la columna a la lista de todos los nombres de columna anteriores devueltos por la consulta. El resultado es que tenemos todos los nombres de columna almacenados en th La variable @columns
- En la variable @query, almacenaremos la consulta completa de la sección anterior, excepto la parte donde se definen las columnas. Obtendremos esta parte de la variable @columns
- Lo último que debemos hacer es pasar la consulta completa como parámetro al procedimiento del sistema de SQL Server sp_executesql
El resultado final es el mismo que en la sección anterior, pero esta vez estamos seguros de que nuestra consulta funcionará incluso si hacemos cambios en los valores de result.outcome_text. Además, puede modificar fácilmente esta consulta y calcular cualquier otro valor, por ejemplo, duración de la llamada SUM / AVG por par ciudad-resultado.
Conclusión
El operador PIVOT de SQL Server le da un vista completamente nueva de lo que puede lograr directamente en la capa de base de datos. Cuando se combina con SQL dinámico, esto va aún más lejos. Te recomiendo encarecidamente que juegues con él; no hay mejor forma de aprender que probarlo tú mismo. En el próximo artículo, mostraremos cómo podemos usar la salida de consultas SQL y crear tablas y gráficos en Excel (esto funcionará no solo en SQL Server sino en general).
Tabla de contenido
Aprenda SQL: CREATE DATABASE & Operaciones de CREATE TABLE
Aprenda SQL: INSERT INTO TABLE
Aprender SQL: clave principal
Aprender SQL: clave externa
Aprender SQL: instrucción SELECT
Aprender SQL: INNER JOIN vs LEFT JOIN
Aprenda SQL: Scripts SQL
Aprenda SQL: Tipos de relaciones
Aprenda SQL: Join múltiples tablas
Aprenda SQL: funciones agregadas
Aprenda SQL: ¿Cómo escribir una consulta SELECT compleja?
Aprenda SQL: La base de datos INFORMATION_SCHEMA
Aprenda SQL: Tipos de datos SQL
Aprenda SQL: Teoría de conjuntos
Aprenda SQL: funciones definidas por el usuario
Aprenda SQL: procedimientos almacenados definidos por el usuario
Aprenda SQL: Vistas SQL
Aprenda SQL: disparadores de SQL
Aprenda SQL: practique consultas de SQL
Aprenda SQL: ejemplos de consultas de SQL
Aprenda SQL: cree un informe manualmente mediante consultas SQL
Aprenda SQL: funciones de fecha y hora de SQL Server
Aprenda SQL: cree informes de SQL Server uso de funciones de fecha y hora
Aprenda SQL: tablas dinámicas de SQL Server
Aprenda SQL: exportación de SQL Server a Excel
Aprenda SQL: Introducción a los bucles de SQL Server
Aprenda SQL: Cursores de SQL Server
Aprenda SQL: Prácticas recomendadas de SQL para eliminar y actualizar datos
Aprenda SQL: Convenciones de nomenclatura
Aprenda SQL: Trabajos relacionados con SQL
Aprenda SQL: Uniones no Equi en SQL Server
Aprenda SQL: inyección de SQL
- Autor
- Publicaciones recientes
Sus compromisos pasados y presentes varían desde el diseño y codificación de bases de datos hasta la enseñanza, la consultoría y la redacción de bases de datos. Tampoco hay que olvidar, BI, creación de algoritmos, ajedrez, filatelia, 2 perros, 2 gatos, 1 esposa, 1 bebé …
Lo puedes encontrar en LinkedIn
Ver todas las publicaciones de Emil Drkusic
- Aprenda SQL: Inyección SQL – 2 de noviembre de 2020
- Aprenda SQL: Non-Equi Joins en SQL Server – 29 de septiembre de 2020
- Aprenda SQL: Trabajos relacionados con SQL – 1 de septiembre de 2020