Scripts Python para tabelas dinâmicas no SQL Server

Scripts Python para tabelas dinâmicas no SQL Server

Python SQL Scripts example


Este artigo fala sobre scripts Python para criar tabelas dinâmicas de várias maneiras.

Introdução

Você pode usar tabelas PIVOT no Microsoft Excel para análise de dados, preparando relatórios. Isso nos ajuda a extrair informações significativas de um grande conjunto de dados. Podemos transpor linha para coluna junto com agregações nela.

Exemplo de scripts SQL do Python

Podemos gerar dados PIVOT de diferentes maneiras no SQL Server.

Podemos usar CTE recursiva, loop While, concatenação SQL, FOR XML PATH ou CLR

Python é uma linguagem de programação interativa e fácil de usar. Exploramos muitos casos de uso de scripts Python no SQLShack. Este artigo explora o uso de dados Python para Pivot.

Scripts Python e Pivot

Vamos explorar alguns métodos que você pode usar no Python para uma tabela dinâmica. Para a demonstração, crie a tabela a seguir e insira dados nela.

Abra o anexo a seguir para inserir dados nesta tabela.

Método 1: Criar um Pivot usando dados armazenados na tabela SQL

Para esta demonstração, estou usando os dados armazenados no [SalesData] tabela do banco de dados SQLShackDemo.

Dados de amostra

Queremos uma tabela PIVOT, conforme destacado na captura de tela a seguir. Aqui, exigimos Quantity, SubTotal e UnitPrice para a coluna dinâmica ProductName.

Leia Também  Você é mal pago? Vamos descobrir: a pesquisa de salários dos profissionais de dados está aberta.

Mesa PIVOT

Executamos scripts Python no SQL Server usando o procedimento armazenado sp_execute_external_script.

Pré-requisitos para executar scripts Python no SQL Server

  • Você deve estar no SQL Server 2017 ou SQL Server 2019 para este artigo

  • Instalar Serviços de máquina (no banco de dados) – Python

  • Ative a execução de scripts externos usando sp_configure comando

Para obter instruções detalhadas, consulte este artigo Como usar o Python no SQL Server 2017 para obter análises avançadas de dados.

Depois de preparar o ambiente, execute o seguinte código Python e você obterá a saída conforme nosso requisito especificado acima.

Vamos entender o script Python.

  • No primeiro bloco, usamos o procedimento armazenado sp_execute_external_script e definimos a linguagem Python para o script

    Importamos o módulo python Pandas e Numpy no script. Esses módulos fornecem funções úteis para Pivot, classificação de dados e agregações.

    Procedimento armazenado sp_execute_external_script

  • Na próxima parte, definimos um quadro de dados para o conjunto de dados de entrada. Nós temos uma tabela dinâmica Função Python para criar uma tabela dinâmica a partir de dados de entrada

    No data.pivot_table, definimos índices e sua coluna de valor. Aqui, definimos [ProductName] como coluna de índice e [UnitPrice],[Quantity], [SubTotal] como colunas de valor de dados. Quadro de dados OutputDataSet captura esses dados. Podemos exibir a saída usando uma função PRINT

    função Python pivot_table

  • Especificamos a consulta SQL para recuperar dados de origem de [SalesData] tabela no argumento @ input_data_1

    Dados de entrada

Da mesma forma, vejamos outro exemplo dos seguintes dados de origem.

Dados de origem

Queremos gerar dados dinâmicos em dois formatos.

  • Vendas totais de cada funcionário

  • Vendas totais de cada funcionário em um ano de vendas

Execute o seguinte script Python no SSMS.

Você obtém a seguinte tabela PIVOT na saída e ela atende ao nosso primeiro requisito.

Tabela PIVOT na saída

Para o segundo requisito, vamos adicionar uma nova linha para Raj e agora temos duas entradas para Raj no ano de 2019.

Adicione uma nova linha

Agora, para esse requisito, adicionamos [Year] coluna também nas colunas de índice do Pivot.

Na saída, obtivemos o Pivot para funcionários, além de detalhes anuais. Podemos observar que, para Raj, ele combinou as duas entradas do ano de 2019 e deu uma soma das vendas na produção dinâmica.

Pivô para dados de funcionários

Método 2: ler dados de um arquivo CSV e preparar dados PIVOT usando scripts Python no SQL Server

Nos exemplos anteriores, nossos dados de origem estavam em tabelas SQL. O Python pode ler arquivos CSV, Excel e também usando os módulos dos pandas.

Podemos armazenar o arquivo CSV localmente em um diretório ou ele pode ser lido diretamente de um URL da Web. Nesta demonstração, usamos um arquivo CSV armazenado em um URL da Web.

No código a seguir, usamos pd.read_csv função e insira um URL da Web como dados de origem. O Python pega diretamente os dados desse URL, mas você deve ter uma conexão ativa com a Internet.

Posteriormente, definimos colunas de índice (Equipe, Salário) e valores (faculdade) para ela. Também usamos a função agregada np.sum nisto.

Execute esse script Python e visualize os dados dinâmicos.

Ler dados de um arquivo CSV

Na saída, podemos ver alguns registros mostrando zero na coluna da faculdade. Não temos um nome de faculdade no arquivo CSV para esses registros e, no PIVOT, ele mostra zero para as células NULL ou em branco.

Suponha que não queremos esses registros na tabela dinâmica. Vamos soltar esses registros com células em branco no CSV usando o dropna() Função Python. Usamos argumento inplace = true para fazer alterações no próprio quadro de dados.

O código completo após adicionar a função dropna () está abaixo.

Agora, execute-o e observe a alteração nas tabelas dinâmicas. Eliminou registros com valores NULL na saída, como mostrado na imagem a seguir.

Eliminar valores NULL

Método 3: Tabelas PIVOT usando a função groupby e lambda em scripts Python

Também podemos usar as funções groupby e lambda nos scripts Python para tabelas dinâmicas. Neste exemplo, tenho um conjunto de dados de alguns estados da Índia e suas cidades em uma tabela SQL.

Tabelas PIVOT usando groupby

Precisamos de uma tabela dinâmica a partir desses dados. Na saída, ele deve listar todas as cidades para um estado em uma coluna; deve usar || como um separador de nomes de cidades.

Usamos a seguinte função para nossos scripts.

  • Groupby (): Usamos a função groupby () no quadro de dados do Python pandas para dividir os dados no grupo de acordo com os critérios definidos. É uma função semelhante à da função SQL GROUP BY. Na consulta, especificamos o groupby () função na coluna de estado
  • Lambda (): Podemos usar uma função lambda para construir funções anônimas no Python. Nós definimos expressões nesta função
  • Classificado (): Classifica os resultados em uma ordem crescente ou decrescente. É semelhante a uma cláusula ORDER BY no SQL Server
  • Junte-se(): A função Join () cria uma string concatenada. Exigimos que ele concatene || no conjunto de resultados

Execute o seguinte script Python para obter os dados dinâmicos desejados.

Exibir consulta Python de saída

Conclusão

Neste artigo, exploramos scripts Python para criar tabelas PIVOT semelhantes ao Microsoft Excel. Você deve explorar o Python, pois é uma linguagem de programação popular, versátil e útil.

Rajendra Gupta
Últimas mensagens de Rajendra Gupta (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br