CONTATTACI

Blog

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.

Articoli correlati

Il linguaggio HTML (Hypertext Markup Language) è uno dei più conosciuti e diffusi: lo si trova infatti praticamente in tutte le pagine web esistenti. È una scelta popolare tra i principianti che si…

Seguici su Facebook

Vuoi entrare nel mondo della programmazione?

Scopri di più sui nostri corsi!

Chiamaci al numero verde

Contattaci su WhatsApp

Contattaci senza impegno per informazioni sul corso

Pagamento rateale

Valore della rata: A PARTIRE DA 115 €/mese.

Esempio di finanziamento 

Importo finanziato: € 2440 in 24 rate da € 115 – TAN fisso 9,55% TAEG 12,57% – importo totale del credito € 2841.

Il costo totale del credito comprende: interessi calcolati al TAN indicato, oneri fiscali (imposta di bollo sul contratto 16,00 euro*) addebitati sulla prima rata, costo mensile di gestione pratica € 3,90, spesa di istruttoria € 0,00, spesa per invio rendicontazione periodica cartacea € 0,98 (o spesa per invio rendicontazione periodica cartacea € 0,00), imposta di bollo su rendicontazione periodica € 0,00. Modalità di rimborso obbligatoria: addebito diretto su c/c. La scadenza delle rate è determinata dal giorno della liquidazione del contratto; la data di scadenza delle rate è prevista il giorno 15 del mese. L’importo di ciascuna rata comprende una quota di capitale crescente e interessi decrescente secondo un piano di ammortamento “alla francese”. Offerta valida dal 01/01/2024 al 31/12/2024.

Messaggio pubblicitario con finalità promozionale. Per le informazioni precontrattuali richiedere sul punto vendita il documento “Informazioni europee di base sul credito ai consumatori” (SECCI) e copia del testo contrattuale. Salvo approvazione di Sella Personal Credit S.p.A. Aulab S.r.l. opera quale intermediario del credito NON in esclusiva.

*In fase di richiesta del finanziamento verrà proposta la facoltà di selezionare, in alternativa all’imposta di bollo sul contratto di 16,00 euro, l’imposta sostitutiva, pari allo 0,25% dell’importo finanziato.

Pagamento rateale

Valore della rata: A PARTIRE DA 210 €/mese.

Esempio di finanziamento  

Importo finanziato: € 4500 in 24 rate da € 210,03 – TAN fisso 9,68% TAEG 11,97% – importo totale del credito € 5146,55.

Il costo totale del credito comprende: interessi calcolati al TAN indicato, oneri fiscali (imposta di bollo sul contratto 16,00 euro*) addebitati sulla prima rata, costo mensile di gestione pratica € 3,90, spesa di istruttoria € 0,00, spesa per invio rendicontazione periodica cartacea € 0,98 (o spesa per invio rendicontazione periodica cartacea € 0,00), imposta di bollo su rendicontazione periodica € 0,00. Modalità di rimborso obbligatoria: addebito diretto su c/c. La scadenza delle rate è determinata dal giorno della liquidazione del contratto; la data di scadenza delle rate è prevista il giorno 15 del mese. L’importo di ciascuna rata comprende una quota di capitale crescente e interessi decrescente secondo un piano di ammortamento “alla francese”. Offerta valida dal 01/01/2024 al 31/12/2024.

Messaggio pubblicitario con finalità promozionale. Per le informazioni precontrattuali richiedere sul punto vendita il documento “Informazioni europee di base sul credito ai consumatori” (SECCI) e copia del testo contrattuale. Salvo approvazione di Sella Personal Credit S.p.A. Aulab S.r.l. opera quale intermediario del credito NON in esclusiva.

* In fase di richiesta del finanziamento verrà proposta la facoltà di selezionare, in alternativa all’imposta di bollo sul contratto di 16,00 euro, l’imposta sostitutiva, pari allo 0,25% dell’importo finanziato.

Contattaci senza impegno per informazioni sul corso

Scopriamo insieme se i nostri corsi fanno per te. Compila il form e aspetta la chiamata di uno dei nostri consulenti.