Come rilevare le anomalie in SQL

L’anomaly detection è una delle pratiche più diffuse nel contesto della sicurezza informatica e dell’intelligenza artificiale. Si tratta di una pratica presente nell’analisi dei dati, dove il rilevamento delle anomalie è l'identificazione di osservazioni, elementi, eventi rari che differiscono in modo significativo dalla maggior parte dei dati. È un processo molto diffuso nelle grandi compagnie per trovare dati che non sono allineati con gli standard della compagnia stessa. Esistono molti modi per rilevare anomalie nei dati e, come la maggior parte delle cose nella vita, non esiste un "metodo corretto" chiaro e definito per farlo. L'approccio sarà dettato dai dati da analizzare ma anche dai requisiti del progetto finale.

Nel mondo dell’analisi dei dati e della programmazione informatica tutto ciò che esiste, può essere svolto o eseguito tramite il linguaggio SQL.

Cos è SQL?

Il significato di SQL sta per Structured Query Language ed è il linguaggio di interrogazione standard nei database relazionali. Si tratta di uno dei linguaggi di programmazione più usati nel mondo dell’informatica ed è il linguaggio di interrogazione più diffuso tra i database.

Il rilevamento delle anomalie è un problema piuttosto onnipresente che abbraccia i casi d'uso dal rilevamento delle frodi ai guasti delle macchine. Alcuni problemi richiedono l'apprendimento automatico supervisionato o non supervisionato. Ad esempio, è possibile analizzare dati di vendita in una serie temporale e contrassegnare giorni in cui il volume di vendita è anomalo.

Il punteggio Z è una tecnica statistica semplice e non supervisionata per contrassegnare i valori anomali. Basta sottrarre la media e dividere per la deviazione standard. Questo score è utilizzato nelle matrici di correlazione, nell’analisi matematica dei dati, per valutare l’andamento di due variabili.

Proviamo a creare un modello di rilevamento dei valori anomali piuttosto generico in linguaggio SQL, basato su una tabella fittizia a livello di transazione.

Organizziamo lo script SQL per astrarre i dettagli della tabella delle transazioni nel primo CTE e abbiamo incluso lo "stato" come dimensione attraverso la quale vogliamo dividere i dati. Nella maggior parte dei casi ha senso considerare le anomalie all'interno di alcuni sottoinsiemi di dati e, ovviamente, considerare lo "stato" come sostituto di qualsiasi dimensione o dimensioni in cui si desidera segnalare le anomalie.

 1. WITH transaction AS (

 2.     SELECT transaction_id,

 3.            customer_id,

 4.            state,

 5.            amount_spent_usd

 6.     FROM < FACT_TABLE_TRANSACTION >

 7. ),

 8.      customer_spend AS (

 9.          SELECT customer_id,

10.                 state,

11.                 COUNT(1)                  AS n_transactions,

12.                 AVG(amount_spent_usd)     AS avg_customer_spend,

13.                 AVG(LN(amount_spent_usd)) AS log_avg_customer_spend

14.          FROM transaction

15.          GROUP BY 1, 2

16.      ),

17.      state_spend AS (

18.          SELECT state,

19.                 COUNT(DISTINCT customer_id)  AS n_customers,

20.                 COUNT(1)                     AS n_transactions,

21.                 AVG(amount_spent_usd)        AS avg_state_spend,

22.                 AVG(LN(amount_spent_usd))    AS log_avg_state_spend,

23.                 STDDEV(amount_spent_usd)     AS std_spend,

24.                 STDDEV(LN(amount_spent_usd)) AS log_std_spend

25.          FROM transaction

26.          GROUP BY 1

27.      ),

28.      customer_z_score AS (

29.          SELECT *,

30.                 (c.avg_customer_spend - s.avg_state_spend) / NULLIFZERO(s.std_spend)             AS customer_z_score,

31.                 (c.log_avg_customer_spend - s.log_avg_state_spend) / NULLIFZERO(s.log_std_spend) AS log_customer_z_score

32.          FROM customer_spend c

33.                   INNER JOIN state_spend s USING (state)

34.      )

35. SELECT customer_id,

36.        log_customer_z_score,

37.        avg_customer_spend

38. FROM customer_spend c

39.          INNER JOIN customer_z_score USING (customer_id)

40. WHERE ABS(log_customer_z_score) > 2

41.  

Z-scoring è un modello di rilevamento delle anomalie potente e minimalista che può essere implementato in modo rapido e flessibile nel data warehouse. Fornisce un modo per ottenere un Minimum-Viable-Product leggero, fuori dalla porta e convalidato, che è forse il lavoro più difficile che un data scientist abbia.

L’anomaly detection è un task che viene prevalentemente implementato in Machine Learning con il linguaggio Python. La complessità di un codice SQL è sicuramente più alta rispetto ad uno script in un linguaggio più avanzato, che implementa l’anomaly detection. Di contro, il linguaggio SQL permette di sfruttare al massimo l’engine del database e, in molti casi, potrebbe essere sufficiente per le tue esigenze, risparmiando la fatica di utilizzare metodi più complessi.

Questa soluzione risulta efficiente per chi non è un esperto di data scientist, non è uno statistico, ma si “limita” a svolgere il compito di sviluppatore. Essendo il machine learning e il mondo dell’analisi delle anomalie, molto vasto e complesso anche a livello matematico, avere una discreta conoscenza del linguaggio SQL in questo senso, aiuta non poco.