Tablas dinámicas e informes en SQL

Suficiente, existe la necesidad de cambiar la forma en que se muestran los datos en la base de datos para que sea más fácil para los usuarios analizarlos. Un ejemplo de dicha conversión es la transposición de datos de filas a columnas en SQL Server.

Diversos informes analíticos permiten visualizar los datos y agilizar la toma de decisiones en base a los resultados obtenidos.

Veamos estas operaciones más de cerca en el ejemplo de una base de datos para un servicio de selección de trabajo.

En primer lugar, así es como se ve el esquema de la base de datos:

Fig.1 Esquema de la base de datos

Índice

    Planteamiento del problema

    Necesitamos comprender rápidamente quién está solicitando un puesto que requiere conocimientos de C# y PostgreSQL.

    Primero definimos los criterios de selección.

    Para simplificar, digamos que necesitamos buscar dos habilidades: C # y PostgreSQL. Omitiremos otros para no complicar nuestro ejemplo.

    Nos interesan las esencias de Employee, JobHistory, Project y Skill, así como el grupo ProjectSkill.

    También debe tenerse en cuenta que los empleados potenciales han tenido que usar las habilidades necesarias (C #, PostgreSQL) durante los últimos 3 años, es decir. su experiencia es relevante.

    También es importante mostrar el uso de cada habilidad (tecnología) para un año en particular. Como tomamos datos de los últimos 3 años, necesitamos mostrar el uso de habilidades para 2017, 2018 y 2019. El año actual es 2020 (y escribí este artículo en enero, por lo que el año acaba de comenzar) y solo analizaremos los datos de los 3 años anteriores para facilitar las cosas.

    Es posible que ninguno de los empleados potenciales de la base de datos tenga experiencia relevante en PostgreSQL. En este caso, luego de confirmar esto con el empleador, podemos incluir la habilidad de Oracle en nuestra búsqueda (esta base de datos es la más cercana a PostgreSQL).

    La solución

    Dadas las condiciones iniciales que describimos anteriormente y la descripción de la salida deseada, obtenemos la siguiente consulta:

    SELECT emp.[EmployeeID]
       ,emp.[LastName]
       ,emp.[FirstName]
       ,s.[SkillName]
       ,DATEDIFF(DAY, jh.[StartDate], jh.[FinishDate]) / (DATEDIFF(YEAR, jh.[StartDate], jh.[FinishDate]) + 1) AS [PeriodDay]
       ,CASE
    WHEN ((2017 >= year(jh.[StartDate])) AND
    (2018 > year(jh.[FinishDate]))) THEN 2017
    WHEN ((2018 >= year(jh.[StartDate])) AND
    (2019 > year(jh.[FinishDate]))) THEN 2018
    WHEN ((2019 >= year(jh.[StartDate])) AND
    (2020 > COALESCE(year(jh.[FinishDate]), year(GetDate())))) THEN 2019
    END AS [Year]
    FROM [Employee] AS emp
    INNER JOIN [JobHistory] AS jh
    ON emp.[EmployeeID] = jh.[EmployeeID]
    INNER JOIN [Project] AS p
    ON p.[ProjectID] = jh.[ProjectID]
    INNER JOIN [ProjectSkill] AS ps
    ON p.[ProjectID] = ps.[ProjectID]
    INNER JOIN [Skill] AS s
    ON s.[SkillID] = ps.[SkillID]
    WHERE (jh.[FinishDate] >= DATEADD(YEAR, -3, GetDate())
    OR (jh.[FinishDate] IS NULL));

    Este es el posible resultado de esta consulta:

    Fig.2 Ejemplo de tabla resumen

    Esta consulta proporciona la siguiente información:

    1. EmployeeID es la identificación del solicitante
    2. El apellido es el segundo nombre del solicitante.
    3. FirstName es el nombre del solicitante
    4. SkillName - habilidad (tecnología)
    5. PeriodDay: la distribución de días en los que se utilizaron las habilidades necesarias para cada año. se calcula en [Year] gráfico, omitiendo los días no laborables (los días no laborables se incluyen en los períodos laborables)
    6. El año es el año asociado con PeriodDay.

    Hemos recibido los datos necesarios.

    Sin embargo, analizar los datos presentados de esta manera no es fácil.

    Transposición de datos usando el operador PIVOT

    Para una visualización más conveniente aplicaremos la transposición de datos mediante el operador PIVOT y deduciremos la distribución de las habilidades utilizadas para cada año en columnas separadas:

    SELECT
    [EmployeeID]
       ,[LastName]
       ,[FirstName]
       ,[SkillName]
       ,[2017]
       ,[2018]
       ,[2019]
    FROM (SELECT
    emp.[EmployeeID]
      ,emp.[LastName]
      ,emp.[FirstName]
      ,s.[SkillName]
      ,DATEDIFF(DAY, jh.[StartDate], jh.[FinishDate]) / (DATEDIFF(YEAR, jh.[StartDate], jh.[FinishDate]) + 1) AS [PeriodDay]
      ,CASE
    WHEN ((2017 >= year(jh.[StartDate])) AND
    (2018 > year(jh.[FinishDate]))) THEN 2017
    WHEN ((2018 >= year(jh.[StartDate])) AND
    (2019 > year(jh.[FinishDate]))) THEN 2018
    WHEN ((2019 >= year(jh.[StartDate])) AND
    (2020 > COALESCE(year(jh.[FinishDate]), year(GetDate())))) THEN 2019
    END AS [Year]
    FROM [Employee] AS emp
    INNER JOIN [JobHistory] AS jh
    ON emp.[EmployeeID] = jh.[EmployeeID]
    INNER JOIN [Project] AS p
    ON p.[ProjectID] = jh.[ProjectID]
    INNER JOIN [ProjectSkill] AS ps
    ON p.[ProjectID] = ps.[ProjectID]
    INNER JOIN [Skill] AS s
    ON s.[SkillID] = ps.[SkillID]
    WHERE (jh.[FinishDate] >= DATEADD(YEAR, -3, GetDate())
    OR (jh.[FinishDate] IS NULL))) AS SourceTable
    PIVOT
    (
    AVG([PeriodDay])
    FOR [Year] IN ([2017], [2018], [2019])
    ) AS PivotTable;

    Este es el posible resultado de esta consulta:

    Fig.3 Transposición de datos usando el operador PIVOT

    Las primeras cuatro columnas muestran la misma información que en la consulta anterior, y las últimas tres columnas muestran la distribución de habilidades utilizadas para 2017, 2018 y 2019, respectivamente.

    Puede crear esta visualización en herramientas como SSRS y dbForge Studio para SQL Server. Estas soluciones también le permiten crear diferentes informes.

    Transponer datos a dbForge Studio para SQL Server

    Echemos un vistazo más de cerca a cómo podemos resumir tablas en dbForge Studio para SQL Server.

    Para hacer esto, primero haga clic en Tabla de resumen en el menú principal o Informes del diseñador sobre Base de datos menú.

    Fig.4 Transición a la función de informe

    El primer comando le permite rotar datos de una hoja de cálculo y el segundo ayuda a crear un informe de usuario.

    Primero, aprendamos sobre la funcionalidad de la tabla dinámica.

    Hagamos clic en él y peguemos el primer script en la parte inferior de la ventana abierta:

    Fig.5 Actualizar datos usando una consulta de usuario

    Ahora tenemos que hacer clic Actualizar en la esquina superior izquierda de la ventana. El resultado debe ser similar al siguiente:

    Fig.6 Tabla dinámica en dbForge Studio para SQL Server

    Tenga en cuenta que los botones en la esquina superior izquierda hacen: restaurar datos a pedido y Editar texto SQL le permite cambiar la consulta.

    En la parte inferior puede cambiar entre datos (Datos botón) y solicitar textoTexto botón). Con la ayuda de c. Girar Mesa, puede girar la mesa. Volveremos a eso más tarde.

    También puede cambiar la salida de datos usando los siguientes botones:

    Fig.7 Gestión de presentación de los datos recibidos - 2

    y

    Fig.8 Gestión de presentación de los datos recibidos - 3

    En el último ejemplo, agrupamos los datos por las primeras cuatro columnas.

    También puede ordenar y agrupar datos usando el menú contextual:

    Fig.9 Menú contextual de la ventana del conjunto de resultados

    Como ves, en el menú contextual puedes configurar el filtrado, la visibilidad de ciertos datos y otros parámetros.

    Ahora volvamos a la rotación de nuestra mesa. Hacer clic Tabla de resumen y selecciona los campos requeridos:

    Fig.10 Configuración de la transposición de datos

    Debe seleccionar la columna en la esquina superior derecha y el comando requerido en la esquina inferior derecha:

    1. El área de una línea - a un número
    2. Área de columna - a la mesa
    3. Área de filtro - al filtro
    4. Área de datos - a datos

    Y luego haga clic añadir.

    En nuestro ejemplo, área de fila el comando fue seleccionado por los siguientes atributos:

    1. ID de empleado
    2. Apellido
    3. Nombre
    4. El nombre de la habilidad.

    El área de la columna se eligió un equipo para Año columna.

    El Área de datos se eligió un equipo para PeríodoDía atributo.

    Cree un informe de usuario en dbForge Studio para SQL Server

    Analicemos ahora brevemente la creación de informes de usuario en dbForge Studio para SQL Server.

    ir a primero Base de datos> Diseñador de informes:

    Fig. 11 Transición a la función de informe de usuario

    En la ventana que se abre, seleccione el tipo de informe deseado (en este caso es un informe estándar) y haga clic en próximo:

    Fig.12 Selección del tipo de informe

    Ahora seleccione la conexión y el tipo de datos (en este caso, una consulta personalizada) y luego haga clic en próximo:

    Fig.13 Configuración de la conexión de informes

    Tenga en cuenta que puede elegir el tipo de datos Tabla simple / Vista en lugar de Consulta personalizada si necesita crear un informe a partir de una sola tabla o vista. En este caso particular, tenemos que elegir Solicitud personalizada ya que el informe se basa en datos de varias tablas.

    También debe recordar que la conexión a una base de datos específica debe establecerse mediante la autenticación de SQL Server, de lo contrario, la consulta no funcionará.

    Inserte la consulta requerida y haga clic en próximo:

    Fig.14 Solicitud de informe del usuario

    cuando se presiona Diseño antes de hacer clic próximo, Se abrirá un diseñador de consultas. Con él puedes crear visualmente la consulta que necesitas.

    El Descargar el botón le permite cargar un script de solicitud previamente guardado.

    A continuación, seleccione todas las columnas necesarias para el informe y haga clic en próximo:

    Fig.15 Selección de campos a reportar

    A continuación, configure la agrupación y haga clic en próximo:

    Fig.16 Configuración de una agrupación para un informe

    A continuación, seleccione las funciones agregadas requeridas e ignore los valores NULL, ya que no los necesitamos para el análisis de datos:

    Figura 17. Configurar la agregación de datos para el informe

    Aquí dejamos la configuración predeterminada y cambiamos solo la orientación de la página de vertical a horizontal:

    Fig.18 Configuración de la página de informe

    Dejaremos el estilo negrita y haremos clic próximo:

    Fig.19 Configuración del estilo del informe

    Finalmente, llamemos al informe y hagamos clic en Terminar:

    Fig.20 Definición del título del informe

    Siempre puede volver a cualquiera de los pasos anteriores haciendo clic en atrás y guarde el informe como un archivo .bat usando un archivo Guarde la línea de comando o incluso deshacer la creación del informe haciendo clic en Cancelar.

    Este es el borrador final del informe:

    Fig.21 Informe de nuevo usuario

    Los resultados de los informes se pueden guardar en varios formatos de uso común, como RDP, HTML, RTF y otros.

    Los borradores de informes se guardan en formato RDB y puede editarlos tan fácilmente con dbForge Studio para SQL Server como archivos RDL en Report Designer o Visual Studio.

    Puede obtener más información sobre cómo crear informes en dbForge Studio para SQL Server aquí o aquí.

    Conclusión

    Si necesitamos cambiar la forma en que mostramos los datos en una base de datos, a menudo necesitamos transponer datos de filas a columnas, es decir, a tablas dinámicas. En este artículo, proporcionamos una guía paso a paso sobre cómo ejecutar el comando PIVOT en un script T-SQL y con dbForge Studio para SQL Server. La última herramienta le permite personalizar el filtrado, así como la visibilidad de ciertos datos y permite a los usuarios guardar informes en una variedad de formatos comunes, incluidos RDP, HTML, RTF, lo que facilita la edición de los usuarios.

    Artículos de interés

    Subir