Analisi statistica SQL Parte 1: Calcolo di frequenze e istogrammi

Gli sviluppatori di database e di Business Intelligence (BI) creano quotidianamente un gran numero di report, di cui le analisi dei dati sono parte integrante. Se vi chiedete se è possibile eseguire analisi statistiche in SQL, la risposta è "sì". Leggete il mio articolo per scoprire come fare!

Le statistiche sono molto utili come fase iniziale di un'analisi più approfondita, ad esempio per la panoramica dei dati e la valutazione della loro qualità. Tuttavia, le possibilità di analisi statistica in SQL sono piuttosto limitate, poiché non esistono molte funzioni statistiche in SQL Server. . Inoltre, una buona comprensione delle statistiche non è molto comune tra i professionisti del T-SQL. In SQL Server 2016 è possibile utilizzare R per calcolare tutti i tipi di misure statistiche, ma molti sviluppatori di SQL Server e amministratori di database non programmano in R. E non tutti i siti hanno aggiornato a SQL Server 2016.

Questa serie spiegherà le basi dell'analisi statistica di SQL. Il codice utilizzato è basato sulle mie esperienze reali. Mi occupo di progetti di BI, soprattutto di data mining, e spesso devo creare molte query statistiche nelle fasi iniziali di un progetto. Durante questi progetti, spesso l'unico software su cui posso contare è un RDBMS.

Ottimizzazione delle query statistiche SQL

L'ottimizzazione delle query statistiche è diversa da quella delle query transazionali. Per calcolare le statistiche, la query in genere analizza tutti i dati. Se la query è troppo lenta, si può preparare un campione casuale dei dati e scansionarlo. Tuttavia, se le query seguono ciecamente le formule, spesso eseguono più scansioni dei dati. Ottimizzare queste query significa ridurre al minimo il numero di scansioni. Per ottenere questo risultato, è necessario sviluppare un algoritmo che utilizzi una matematica aggiuntiva per convertire le formule in equivalenti che possano essere ottimizzati meglio in SQL Server o in qualsiasi altro RDBMS. È inoltre necessario conoscere a fondo l'SQL. Ad esempio, è necessaria un'ottima conoscenza delle funzioni e dei calcoli delle finestre di SQL.

Oltre a spiegare le statistiche e le query statistiche, questa serie vi darà anche alcune idee per ottimizzare le query statistiche e non statistiche.

Preparazione dei dati per l'analisi statistica SQL

Prima di iniziare l'analisi, è necessario capire cosa si sta analizzando. In statistica, si analizzano i casi utilizzando le loro variabili. Nella terminologia RDBMS, si può pensare a un caso come a una riga di una tabella e a una variabile come a una colonna della stessa tabella. Per la maggior parte delle analisi statistiche, si prepara una singola tabella o vista. A volte non è così facile definire esattamente il caso. Ad esempio, se si sta eseguendo un'analisi del rischio di credito, si potrebbe definire una famiglia come caso piuttosto che un singolo cliente.

Quando si preparano i dati per l'analisi statistica SQL, è necessario trasformare i dati di origine di conseguenza. Per ogni caso, è necessario incapsulare tutte le informazioni disponibili nelle colonne della tabella che si intende analizzare.

Variabili continue e discrete

Prima di iniziare una seria panoramica dei dati, è necessario capire come vengono misurati i valori dei dati nel set di dati. Potrebbe essere necessario verificare questo aspetto con un esperto in materia e analizzare il sistema aziendale che è la fonte dei dati. Esistono diversi modi per misurare i valori dei dati e diversi tipi di colonne:

• Variabili discrete Le variabili continue possono assumere un valore solo da un dominio limitato di valori possibili. I valori discreti includono variabili categoriche o nominali che non hanno un ordine naturale. Ne sono un esempio gli stati, i codici di stato e i colori.
  • Anche leclassifiche possono assumere un valore solo da un insieme discreto di valori. Hanno un ordine ma non consentono alcuna aritmetica. Ne sono un esempio le classifiche di opinioni e i valori numerici reali raggruppati e discretizzati.
  • Esistono anche alcuni tipi specifici di variabili categoriche. Le variabili monovalenti o costanti non sono molto interessanti per l'analisi, perché non forniscono alcuna informazione. Le variabili a due valori o dicotomiche hanno due valori che sono minimamente necessari per qualsiasi analisi. Le variabili binarie sono variabili dicotomiche specifiche che assumono soltanto i valori 0 e 1.
• Variabili continue possono assumere un numero illimitato di valori possibili; tuttavia, il dominio stesso può avere un limite inferiore e/o superiore.
  • Gliintervalli hanno uno o due confini, hanno un ordine e consentono alcune sottrazioni di tipo aritmetico (ma non sempre consentono la somma). Ne sono un esempio le date, gli orari e le temperature.
  • Levariabili numeriche vere e proprie supportano tutte le operazioni aritmetiche. Ne sono un esempio le somme e i valori.
  • Levariabili monotone sono un tipo specifico di variabili continue che aumentano monotonamente senza limiti. Se sono semplicemente ID, potrebbero non essere interessanti. Tuttavia, possono essere trasformate (suddivise in categorie) se l'ID in continua crescita contiene informazioni sull'ordine temporale (gli ID inferiori sono più vecchi di quelli superiori).

Dati utilizzati per le analisi statistiche SQL

Per questo e per tutti i prossimi articoli, utilizzerò il database demo AdventureWorksDW2014. È possibile scaricare un backup completo di questo database dal sito Microsoft SQL Server sample. Eseguo tutto il codice su SQL Server 2016 Edizione per sviluppatori.

Preferisco utilizzare il database di esempio AdventureWorks per SQL Server 2014 invece del database di esempio WideWorldImportersDW di SQL Server 2016. Il database WideWorldImporters è molto utile per dimostrare le nuove funzionalità di SQL Server 2016, ma i suoi dati mancano delle correlazioni e delle associazioni necessarie per le analisi statistiche.

Usare la distribuzione di frequenza in SQL per comprendere le variabili discrete

In SQL, la distribuzione di frequenza (solitamente presentata sotto forma di tabella) viene utilizzata per ottenere una rapida panoramica delle variabili discrete. Può mostrare i valori effettivi e i loro valori:

• Frequenza assoluta
• Percentuale assoluta
• Frequenza cumulativa
• Percentuale cumulativa

Inoltre, la distribuzione di frequenza in SQL mostra un istogramma della percentuale assoluta dei valori.

Di seguito verranno illustrati diversi modi per calcolare la distribuzione di frequenza in SQL, a partire da uno piuttosto inefficiente.

Distribuzione di frequenza in SQL senza utilizzare Window Functions

Il calcolo della frequenza assoluta e della percentuale assoluta dei valori è un'aggregazione semplice. Tuttavia, il calcolo della frequenza cumulativa e della percentuale cumulativa implica il calcolo di totali progressivi. Prima che SQL Server 2012 aggiungesse il supporto per le funzioni aggregate a finestra, per questo compito era necessario utilizzare subquery correlate o join non equi. Nessuno dei due metodi è molto efficiente.

Eseguite il seguente codice, che utilizza sottoquery correlate, per analizzare in SQL la distribuzione di frequenza della variabile NumberCarsOwned dalla vista dbo.vTargetMailnel database demo 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;

Questo genera il seguente risultato:

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  *******

Distribuzione di frequenza in SQL con Window Functions - Soluzione 1

Quando si esegue un'analisi statistica in SQL, le funzioni aggregate a finestra si rivelano utili. Esse forniscono una soluzione migliore. Come già detto, queste funzioni sono disponibili in SQL Server 2012 e versioni successive.

Se si osserva la prima parte della query, si noterà che la query Common Table Expression che calcola i numeri assoluti è la stessa della query precedente. Tuttavia, i valori cumulativi - i totali correnti - sono calcolati con l'aiuto delle funzioni aggregate di Window.

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;

Il risultato di questa query è identico a quello della query precedente.

Distribuzione di frequenza in SQL con Window Functions - Soluzione 2

Ho trovato un'altra soluzione interessante che utilizza le funzioni analitiche a finestra di SQL. La funzione CUME_DIST calcola la distribuzione cumulativa, o posizione relativa, di un valore in un gruppo di valori. Per una riga r, assumendo un ordinamento ascendente, il CUME_DIST di r è il numero di righe con valori inferiori o uguali al valore di r, diviso per il numero di righe valutate nella partizione o nel set di risultati della query. La funzione PERCENT_RANK calcola il rango relativo di una riga all'interno di un gruppo di righe. È possibile utilizzare PERCENT_RANK per valutare la posizione relativa di un valore all'interno di un insieme di risultati di query o di una partizione.

La seguente query di analisi statistica SQL calcola il numero di righe una volta partizionato sulla colonna Numero di auto possedute e il numero di righe una volta su tutto l'insieme di input. Calcola inoltre il rango percentuale e la distribuzione cumulativa sull'intero set di input.

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;

L'output parziale, che mostra solo le righe pertinenti alla spiegazione dell'algoritmo di calcolo delle frequenze, è il seguente:

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
…               …          …                 …   …

Come si può vedere, l'ultimo numero di riga partizionato da Numero di auto possedute in una categoria rappresenta in realtà la frequenza assoluta dei valori in quella categoria. L'ultimo numero di riga non partizionato di una categoria rappresenta la frequenza cumulativa fino alla categoria corrente. Ad esempio, la frequenza assoluta per NumeroAuto possedute = "0" è 4.238 e la frequenza cumulativa è 4.238; per NumeroAuto possedute = "1", la frequenza assoluta è 4.883 e quella cumulativa è 9.121.

Consideriamo poi la funzione CUME_DIST (la colonna Cd_CumPerc nell'output). CUME_DIST nell'ultima riga di una categoria restituisce la percentuale cumulativa fino alla categoria inclusa. Se si sottrae il PERCENT_RANK (colonna Pr_AbsPerc nell'output) dell'ultima riga della categoria dal CUME_DIST dell'ultima riga della stessa categoria, si ottiene la percentuale assoluta della categoria. Ad esempio, la percentuale assoluta per la categoria Numero di auto possedute = "1" è superiore al 26% (0,493453797879247 - 0,22929178163718 = 0,264162016242067).

La query seguente calcola la distribuzione delle frequenze utilizzando le osservazioni dei risultati della query precedente.

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;

Sebbene l'idea di quest'ultima query sia molto interessante, essa non è efficiente come la seconda (che utilizza la funzione aggregata a finestra). Pertanto, la seconda soluzione è quella consigliata.

Conclusione

In questo articolo avete imparato a calcolare la distribuzione di frequenza SQL per le variabili discrete. Avete anche visto una soluzione che utilizza un po' di creatività. Nei prossimi articoli, potrete conoscere altri metodi di analisi statistica in SQL. Il prossimo sarà dedicato al calcolo delle misure statistiche di base per le variabili continue. Vedrete anche come scrivere query efficienti che richiedono conoscenze matematiche piuttosto che creatività.