SQLShack (Português)

Nos poucos artigos anteriores desta série, definimos as bases sobre como criar um relatório. Vamos dar um passo adiante e ver como usar o operador de tabela PIVOT do SQL Server. Começaremos com uma consulta simples e progrediremos lentamente em direção ao SQL dinâmico e PIVOT. Vamos começar.

Modelo de dados e a ideia geral

O modelo de dados que usaremos é o mesmo que usamos ao longo desta série.

Nossa tarefa hoje é criar um relatório (usando o operador PIVOT do SQL Server) onde cada cidade estará em uma linha separada , e onde contaremos o número de todos os resultados de chamadas relacionados a cada cidade. Portanto, todos os resultados (todos os valores do dicionário) serão colunas em nosso relatório.

Já que “uma imagem vale mais que mil palavras”, descreveremos nosso objetivo final com a imagem também.

Para conseguir isso, precisaremos usar dados de 4 tabelas call, call_outcome, customer e city. Portanto, seria bom que você desse uma olhada no modelo abaixo e analise como essas tabelas estão relacionadas.

Começaremos examinando os dados nas tabelas e, a cada consulta, avançamos um passo mais perto de o resultado desejado (consulta). A abordagem usada aqui é a mesma usada em Aprender SQL: Criar um relatório manualmente usando o artigo de consultas SQL.

Categorias e dados de relatório

O O primeiro conjunto de consultas com o qual começaremos é aquele que examina os dados atualmente presentes em todas as 4 tabelas que precisaremos usar para criar o relatório. Faremos isso para que possamos confirmar se o relatório final retornou o que deveria. Depois disso, vamos criar consultas que ich retornam categorias de relatório e dados de relatório, bem como consultas de tabela PIVOT do SQL Server.

Essas consultas são tão simples quanto poderiam ser, portanto, não há nada de especial a comentar sobre sua sintaxe. Em relação aos dados retornados, devemos estar cientes do seguinte:

• A tabela de cidades contém 6 cidades e, portanto, devemos ter 6 linhas no relatório final
• Existem 3 resultados possíveis na tabela call_outcome, então devemos ter 3 colunas para resultados (4 colunas no total – > uma é para o nome da cidade)
• A chamada da tabela tem 10 linhas (apenas 8 na imagem acima), portanto, na tabela final, a soma de todos os resultados deve ser 10 (cada chamada pode ter exatamente 1 resultado)

A próxima coisa a fazer é preparar categorias de relatórios. Queremos ter uma combinação de todas as cidades e todos os resultados de chamadas possíveis. Para isso, usaremos o CROSS JOIN (produto cartesiano).

Ter todas as categorias de relatórios garante que teremos uma linha no relatório, não importa se esse par possui dados ou não. E é isso que queremos – ver 0 nessa categoria de relatório e não perder essa categoria totalmente.

A próxima coisa a fazer é juntar todas as 4 tabelas que contêm os dados de que precisamos.

A consulta em si não é complexa. No resultado, devemos notar que temos 10 linhas, o mesmo número de chamadas que temos no banco de dados. Além disso, usei a função DATEDIFF para retornar a duração de cada chamada em segundos. Eu poderia simplesmente colocar call.id aqui, mas gostaria de nos lembrar da função DATEDIFF que mencionamos neste artigo. Poderíamos usar essa duração se precisarmos de uma SOMA / AVG dos segundos gastos por cada categoria de relatório (cidade & resultado da chamada).

Relatório sem SQL Server TABELA Dinâmica

Agora estamos prontos para juntar categorias e dados. Usaremos ambas as consultas mencionadas anteriormente como subconsultas e as uniremos usando LEFT JOIN (para ter todas as categorias presentes na saída final).

Temos todas as 18 combinações (6 cidades diferentes * 3 resultados de chamadas diferentes) e também todas as 10 chamadas presentes aqui (linhas com a duração da chamada < > NULL).

Vamos agora criar a consulta PIVOT do SQL Server.

TABELA PIVOT do SQL Server (estática)

Até agora, conseguimos obter os dados de que precisamos e os temos como lista. Poderíamos exportar dados para o Excel e fazer transformações lá. Na verdade, faremos isso no próximo artigo.Ainda hoje, queremos resolver isso usando o operador PIVOT do SQL Server. Nesta seção, abordaremos o PIVOT “estático” do SQL Server. Vamos dar uma olhada na consulta usada e no resultado primeiro.

Podemos notar que o resultado é exatamente o que queríamos. Temos cada cidade em uma linha e todas as 3 categorias para resultados de chamadas em colunas separadas. Se não houver dados para um determinado par cidade-resultado, essa célula deve conter o valor 0.

Vamos comentar sobre a consulta PIVOT do SQL Server agora. Estas são algumas coisas que acho importante mencionar aqui:

• Na primeira parte de a consulta – report_data, copiamos e colamos a consulta da seção anterior. A única alteração foi que não tínhamos ORDER BY e GROUP BY nesta parte da consulta porque eles não podem ser aplicados aqui (devem vir depois o resultado final da consulta é gerado)
• A parte PIVOT da consulta consiste em 2 partes. Na primeira, definiremos qual função de agregação queremos aplicar. No nosso caso, é – COUNT ( call_duration). Em FOR parte do qu ery, vamos definir colunas. Nós listamos literariamente todos os valores que queremos ter como colunas. Isso é codificação permanente. Se adicionarmos um novo valor à tabela, isso não afetará a consulta (e deve)

Esta consulta faz seu trabalho e funcionará perfeitamente se nunca mudarmos (adicionar novo , excluir, atualizar seus nomes) resultados de chamadas na tabela relacionada. Esse pode ser o problema nos casos em que precisamos fazer alterações no dicionário. Não queremos pensar se há uma consulta que não funciona como esperado. Para resolver isso, precisaremos usar um novo conceito – SQL dinâmico.

TABELA PIVOT do SQL Server dinâmico

Antes de passar para o código, vamos explicar brevemente o que o SQL dinâmico realmente é (daremos uma revisão muito mais detalhada dele nos próximos artigos). A explicação mais simples é que em SQL, você pode “construir” suas consultas como strings e, em seguida, passar essa string como um parâmetro para o procedimento armazenado do SQL Server. Este procedimento deve executar as instruções SQL armazenadas nessa string (é claro , se a sintaxe estiver OK).

Embora isso possa soar como algo não tão comumente usado, há alguns lugares onde torna sua vida muito mais fácil. Criando uma consulta de tabela PIVOT do SQL Server com um desconhecido número de colunas é exatamente um caso.

A ideia desta abordagem é a seguinte:

• Declararemos uma variável onde armazenar todos os nomes de coluna (@columns ), e a variável onde armazenar a consulta completa (@query)
• Usando a consulta SELECT, encontraremos todos os valores armazenados na coluna Result_text. O seguinte código – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, deve anexar o nome da coluna à lista de todos os nomes de coluna anteriores retornados pela consulta. O resultado é que temos todos os nomes das colunas armazenados no e variável @columns
• Na variável @query, vamos armazenar a consulta completa da seção anterior, exceto para a parte onde as colunas são definidas. Obteremos essa parte da variável @columns
• A última coisa que precisamos fazer é passar a consulta completa como o parâmetro para o procedimento de sistema do SQL Server sp_executesql

O resultado final é o mesmo da seção anterior, mas desta vez temos certeza de que nossa consulta funcionará mesmo se fizermos alterações em os valores de desfecho.outcome_text. Além disso, você pode modificar facilmente essa consulta e calcular quaisquer outros valores, por exemplo, duração da chamada SUM / AVG por par cidade-resultado.

Conclusão

O operador PIVOT do SQL Server fornece um visão completamente nova do que você pode alcançar diretamente na camada de banco de dados. Quando combinado com o SQL dinâmico, isso vai ainda mais longe. Eu o encorajo fortemente a brincar com ele – não há melhor maneira de aprender do que tentar você mesmo. No próximo artigo, mostraremos como podemos usar a saída de consulta SQL e criar tabelas e gráficos no Excel (isso deve funcionar não apenas no SQL Server, mas em geral).

Índice

Aprenda SQL: CREATE DATABASE & Operações CREATE TABLE

Aprenda SQL: INSERT INTO TABLE

Aprenda SQL: chave primária

Aprenda SQL: Chave estrangeira

Aprenda SQL: instrução SELECT

Aprenda SQL: INNER JOIN vs LEFT JOIN

Aprenda SQL: Scripts SQL

Aprenda SQL: Tipos de relações

Aprenda SQL: Join tabelas múltiplas

Aprenda SQL: Funções de agregação

Aprenda SQL: como escrever uma consulta SELECT complexa?

Aprenda SQL: o banco de dados INFORMATION_SCHEMA

Aprenda SQL: Tipos de dados SQL

Aprenda SQL: Teoria dos conjuntos

Aprenda SQL: Funções definidas pelo usuário

Aprenda SQL: Procedimentos armazenados definidos pelo usuário

Aprenda SQL: Exibições SQL

Aprenda SQL: SQL Triggers

Aprenda SQL: Pratique consultas SQL

Aprenda SQL: Exemplos de consulta SQL

Aprenda SQL: crie um relatório manualmente usando consultas SQL

Aprenda SQL: funções de data e hora do SQL Server

Aprenda SQL: crie relatórios do SQL Server usando funções de data e hora

Aprenda SQL: tabelas dinâmicas do SQL Server

Aprenda SQL: exportação do SQL Server para o Excel

Aprenda SQL: Introdução aos loops do SQL Server

Aprenda SQL: Cursores do SQL Server

Aprenda SQL: Práticas recomendadas de SQL para exclusão e atualização de dados

Aprenda SQL: Convenções de nomenclatura

Aprenda SQL: Trabalhos relacionados a SQL

Aprenda SQL: Associações não-Equi no SQL Server

Aprenda SQL: injeção de SQL

• Autor
• Postagens recentes

Emil é um profissional de banco de dados com mais de 10 anos de experiência em tudo relacionado a bancos de dados. Durante os anos, ele trabalhou na indústria de TI e finanças e agora trabalha como freelancer.
Seus compromissos passados e presentes variam de design e codificação de banco de dados a ensino, consultoria e redação sobre bancos de dados. Sem esquecer também, BI, criação de algoritmos, xadrez, filatelia, 2 cachorros, 2 gatos, 1 esposa, 1 bebê …
Você pode encontrá-lo no LinkedIn
Ver todas as postagens de Emil Drkusic

Postagens mais recentes de Emil Drkusic (ver todos)

• Aprenda SQL: SQL Injection – 2 de novembro de 2020
• Aprenda SQL: Non-Equi Joins no SQL Server – 29 de setembro de 2020
• Aprenda SQL: Trabalhos relacionados a SQL – 1 de setembro de 2020