Análise estatística SQL Parte 1: Cálculo de Freqüências e Histogramas

Os desenvolvedores de banco de dados e Business Intelligence (BI) criam diariamente um grande número de relatórios, dos quais as análises de dados são parte integrante. Se você se pergunta se pode realizar análises estatísticas em SQL, a resposta é "sim". Leia meu artigo para saber como fazer isso!

As estatísticas são muito úteis como estágio inicial de uma análise mais aprofundada, ou seja, para uma visão geral dos dados e avaliação da qualidade dos dados. Entretanto, as possibilidades de análise estatística SQL são um pouco limitadas, pois não há muitas funções estatísticas no SQL Server. . Além disso, um bom entendimento de estatísticas não é muito comum entre os praticantes de T-SQL. No SQL Server 2016, você pode usar R para calcular todos os tipos de medidas estatísticas, mas muitos desenvolvedores de SQL Server e administradores de banco de dados não programam em R. E nem todos os sites foram atualizados para o SQL Server 2016.

Esta série explicará os conceitos básicos da análise estatística SQL. O código utilizado é baseado em minhas experiências da vida real. Eu lido com projetos de BI, especialmente com mineração de dados, e muitas vezes preciso criar muitas consultas estatísticas nos estágios iniciais de um projeto. Durante esses projetos, muitas vezes o único software em que posso confiar é um RDBMS.

Otimização de consultas estatísticas SQL

A otimização de consultas estatísticas é diferente da otimização de consultas transacionais. A fim de calcular as estatísticas, a consulta normalmente varre todos os dados. Se a consulta for muito lenta, você pode preparar uma amostra aleatória de seus dados e escanear isso. Entretanto, se as consultas seguirem cegamente as fórmulas, elas freqüentemente realizam múltiplas varreduras dos dados. Otimizar tais consultas significa minimizar o número de varreduras. Para conseguir isso, você deve desenvolver um algoritmo que utilize matemática adicional para converter as fórmulas em equivalentes que possam ser melhor otimizadas no SQL Server ou em qualquer outro RDBMS. Você também precisa entender SQL em profundidade. Por exemplo, você precisa compreender muito bem as funções de janela e os cálculos do SQL.

Além de explicar estatísticas e consultas estatísticas, esta série também lhe dará algumas idéias para otimizar consultas estatísticas e não-estatísticas.

Preparando seus dados para análise estatística SQL

Antes de iniciar a análise, você precisa entender o que está analisando. Nas estatísticas, você analisa os casos usando suas variáveis. Na terminologia RDBMS, você pode pensar em um caso como uma linha de tabela e uma variável como uma coluna na mesma tabela. Para a maioria das análises estatísticas, você prepara uma única tabela ou visão. Às vezes não é tão fácil definir exatamente seu caso. Por exemplo, se você estiver realizando uma análise de risco de crédito, você pode definir uma família como um caso em vez de um único cliente.

Quando você prepara dados para análise estatística SQL, você tem que transformar os dados da fonte de acordo. Para cada caso, você precisa encapsular todas as informações disponíveis nas colunas da tabela que você vai analisar.

Variáveis Contínuas e Discretas

Antes de iniciar uma visão geral séria dos dados, você deve entender como os valores dos dados são medidos em seu conjunto de dados. Talvez você precise verificar isto com um especialista no assunto e analisar o sistema comercial que é a fonte de seus dados. Há várias maneiras de medir os valores dos dados e diferentes tipos de colunas:

• Variáveis discretas pode tomar um valor apenas de um domínio limitado de valores possíveis. Os valores discretos incluem variáveis categóricas ou nominais que não têm ordem natural. Os exemplos incluem estados, códigos de status e cores.
  • Os postos também podem obter um valor apenas a partir de um conjunto discreto de valores. Eles têm uma ordem, mas não permitem qualquer aritmética. Exemplos incluem posições de opinião e valores numéricos verdadeiros (agrupados, discretizados).
  • Há também alguns tipos específicos de variáveis categóricas. Variáveis de valor único ou constantes não são muito interessantes para análise, pois não contribuem com nenhuma informação. Variáveis bicavaliadas ou dicotômicas têm dois valores que são minimamente necessários para qualquer análise. Variáveis binárias são variáveis dicotômicas específicas que assumem apenas os valores 0 e 1.
• Variáveis contínuas pode tomar qualquer um de um número ilimitado de valores possíveis; entretanto, o domínio em si pode ter um limite inferior e/ou superior.
  • Os intervalos têm um ou dois limites, têm uma ordem e permitem alguma subtração aritmética (mas podem não permitir sempre a soma). Os exemplos incluem datas, horários e temperaturas.
  • Asvariáveis numéricas verdadeiras suportam todas as aritméticas. Os exemplos incluem quantidades e valores.
  • Variáveis monotônicas são um tipo específico de variáveis contínuas que aumentam monotonicamente sem limite. Se elas forem simplesmente IDs, podem não ser interessantes. Ainda assim, elas podem ser transformadas (encartadas em categorias) se o ID sempre crescente contiver informações de ordem de tempo (IDs menores são mais antigas que IDs maiores).

Dados utilizados para análises estatísticas SQL

Para este e todos os próximos artigos, estou usando o banco de dados de demonstração AdventureWorksDW2014. Você pode baixar um backup completo deste banco de dados do site de amostra do SQL Server da Microsoft. Eu estou executando todo o código em SQL Server 2016 Developer Edition.

Prefiro usar o banco de dados de amostras AdventureWorks para o SQL Server 2014 ao invés do banco de dados de amostras WideWorldImportersDW do SQL Server 2016. O banco de dados WideWorldImporters é muito útil para demonstrar as novas características do SQL Server 2016, mas seus dados carecem das correlações e associações necessárias para a análise estatística.

Usando a distribuição de freqüência em SQL para compreender variáveis discretas

Em SQL, a distribuição de freqüência (geralmente apresentada na forma de uma tabela) é usada para obter uma rápida visão geral das variáveis discretas. Ela pode mostrar os valores reais, assim como seus valores:

• Freqüência absoluta
• Porcentagem absoluta
• Freqüência acumulada
• Porcentagem acumulada

Além disso, a distribuição de freqüência SQL exibe um histograma da porcentagem absoluta dos valores.

Abaixo, vou mostrar várias maneiras de calcular a distribuição de freqüência em SQL, começando com uma que é bastante ineficiente.

Distribuição de Freqüência em SQL sem Utilizar Funções de Janela (Window Functions) em SQL

O cálculo da freqüência absoluta e da porcentagem absoluta dos valores é uma agregação simples. Entretanto, calcular a freqüência acumulada e a porcentagem acumulada significa calcular totais em execução. Antes do SQL Server 2012 adicionar suporte às funções de agregação de janelas, você tinha que usar subconsultas correlacionadas ou auto-inserções não-equi para esta tarefa. Nenhum dos dois métodos é muito eficiente.

Execute o seguinte código, que usa subconsultas correlacionadas, para analisar na distribuição de freqüência SQL da variável NumberCarsOwned a partir da visualização dbo.vTargetMailno banco de dados de demonstração AdventureWorksDW2014.

USE AdventureWorksDW2014;
GO
WITH freqCTE AS
(
SELECT v.NumberCarsOwned,
 COUNT(v.NumberCarsOwned) AS AbsFreq,
 CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) /
       (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc
FROM dbo.vTargetMail AS v
GROUP BY v.NumberCarsOwned
)
SELECT c1.NumberCarsOwned AS NCars,
 c1.AbsFreq,
 (SELECT SUM(c2.AbsFreq)
  FROM freqCTE AS c2
  WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumFreq,
 c1.AbsPerc,
 (SELECT SUM(c2.AbsPerc)
  FROM freqCTE AS c2
  WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumPerc,
 CAST(REPLICATE('*',c1.AbsPerc) AS varchar(100)) AS Histogram
  FROM freqCTE AS c1
ORDER BY c1.NumberCarsOwned;

Isto gera a seguinte saída:

NCars  AbsFreq  CumFreq  AbsPerc  CumPerc  Histogram
-----  -------  -------  -------  -------  -----------------------------------
0         4238     4238       23       23  ***********************
1         4883     9121       26       49  **************************
2         6457    15578       35       84  ***********************************
3         1645    17223        9       93  *********
4         1261    18484        7      100  *******

Distribuição de freqüência em SQL usando Funções de Janela (Window Functions) em SQL - Solução 1

Quando você realiza uma análise estatística SQL, as funções agregadas de janela se revelam úteis. Elas fornecem uma solução muito melhor. Como já observado, estas funções estão disponíveis nas versões 2012 e posteriores do SQL Server.

Se você olhar para a primeira parte da consulta, notará que a consulta de Expressão da Tabela Comum que calcula os números absolutos é a mesma que na consulta anterior. Entretanto, os valores acumulados - os totais em execução - são calculados com a ajuda das funções agregadas da janela.

WITH freqCTE AS
(
SELECT v.NumberCarsOwned,
 COUNT(v.NumberCarsOwned) AS AbsFreq,
 CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) /
       (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc
FROM dbo.vTargetMail AS v
GROUP BY v.NumberCarsOwned
)
SELECT NumberCarsOwned,
 AbsFreq,
 SUM(AbsFreq) 
  OVER(ORDER BY NumberCarsOwned 
       ROWS BETWEEN UNBOUNDED PRECEDING
	    AND CURRENT ROW) AS CumFreq,
 AbsPerc,
 SUM(AbsPerc)
  OVER(ORDER BY NumberCarsOwned
       ROWS BETWEEN UNBOUNDED PRECEDING
	    AND CURRENT ROW) AS CumPerc,
 CAST(REPLICATE('*',AbsPerc) AS VARCHAR(50)) AS Histogram
FROM freqCTE
ORDER BY NumberCarsOwned;

O resultado desta consulta é o mesmo que o resultado da consulta anterior.

Distribuição de freqüência em SQL usando Funções de Janela (Window Functions) em SQL - Solução 2

Encontrei outra solução interessante usando as funções analíticas de janela do SQL. A função CUME_DIST calcula a distribuição cumulativa, ou posição relativa, de um valor em um grupo de valores. Para uma linha r, assumindo ordem ascendente, o CUME_DIST de r é o número de linhas com valores inferiores ou iguais ao valor de r, dividido pelo número de linhas avaliadas na partição ou no conjunto de resultados da consulta. A função PERCENT_RANK calcula a classificação relativa de uma linha dentro de um grupo de linhas. Podemos usar PERCENT_RANK para avaliar a posição relativa de um valor dentro de um conjunto de resultados de consulta ou partição.

A seguinte consulta de análise estatística SQL calcula o número da linha uma vez particionada sobre a coluna NumberCarsOwned e o número da linha uma vez sobre todo o conjunto de entrada. Ela também calcula a classificação percentual e a distribuição cumulativa sobre todo o conjunto de entrada.

SELECT NumberCarsOwned AS NCars,
 ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq,
 ROW_NUMBER() OVER(
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq,
 PERCENT_RANK()
  OVER(ORDER BY NumberCarsOwned) AS Pr_AbsPerc, 
 CUME_DIST()
  OVER(ORDER BY NumberCarsOwned, CustomerKey) AS Cd_CumPerc
FROM dbo.vTargetMail;

A saída parcial, mostrando apenas as linhas pertinentes à explicação do algoritmo de cálculo de freqüências, é:

NCars  Rn_AbsFreq  Rn_CumFre  Pr_AbsPerc         Cd_CumPerc
-----  ----------  ---------  -----------------  --------------------
0               1          1                 0   5.4100843973166E-05
0               2          2                 0   0.000108201687946332
…               …          …                 …   …
0            4238       4238                 0   0.229279376758277
1               1       4239  0.22929178163718   0.229333477602251
…               …          …                 …   …
1            4883       9121  0.22929178163718   0.493453797879247
2               1       9122  0.493480495590543  0.49350789872322
…               …          …                 …   …

Como você pode ver, o último número de linha dividido por NumberCarsOwned em uma categoria realmente representa a freqüência absoluta dos valores nessa categoria. O último número de linha não particionado em uma categoria representa a freqüência acumulada até e incluindo a categoria atual. Por exemplo, a freqüência absoluta para NumberCarsOwned = "0 " é 4.238 e a freqüência acumulada é 4.238; para NumberCarsOwned = "1", a freqüência absoluta é 4.883 e a freqüência acumulada é 9.121.

Em seguida, considere a função CUME_DIST (a coluna Cd_CumPerc na saída). CUME_DIST na última linha em uma categoria retorna a porcentagem acumulada até e incluindo a categoria. Se você subtrair a PERCENT_RANK (a coluna Pr_AbsPerc na saída) para a última linha na categoria do CUME_DIST da última linha na mesma categoria, você obtém a porcentagem absoluta para a categoria. Por exemplo, a porcentagem absoluta para a categoria onde NumberCarsOwned = "1" é superior a 26% (0,493453797879247 - 0,229292917816373718 = 0,264162016242067).

A consulta seguinte calcula a distribuição de freqüências usando observações a partir dos resultados da consulta anterior.

WITH freqCTE AS
(
SELECT NumberCarsOwned,
 ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq,
 ROW_NUMBER() OVER(
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq,
 ROUND(100 * PERCENT_RANK()
  OVER(ORDER BY NumberCarsOwned), 0) AS Pr_AbsPerc, 
 ROUND(100 * CUME_DIST()
  OVER(ORDER BY NumberCarsOwned, CustomerKey), 0) AS Cd_CumPerc
FROM dbo.vTargetMail
)
SELECT NumberCarsOwned AS NCars,
 MAX(Rn_AbsFreq) AS AbsFreq,
 MAX(Rn_CumFreq) AS CumFreq,
 MAX(Cd_CumPerc) - MAX(Pr_Absperc) AS AbsPerc,
 MAX(Cd_CumPerc) AS CumPerc,
 CAST(REPLICATE('*',MAX(Cd_CumPerc) - MAX(Pr_Absperc)) AS varchar(100)) AS Histogram
FROM freqCTE
GROUP BY NumberCarsOwned
ORDER BY NumberCarsOwned;

Embora a idéia desta última consulta seja muito interessante, esta consulta não é tão eficiente quanto a segunda (usando a função de janela agregada). Portanto, a segunda solução é a recomendada.

Conclusão

Neste artigo, você aprendeu como computar a distribuição de freqüência SQL para variáveis discretas. Você também viu uma solução que usa alguma criatividade. Nos artigos seguintes, você terá uma compreensão de outros métodos de análise estatística SQL. O próximo será dedicado ao cálculo de medidas estatísticas básicas para variáveis contínuas. Você também verá como escrever consultas eficientes que exigem mais conhecimento matemático do que criatividade.