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…
Essere uno sviluppatore web vuol dire imparare più linguaggi di programmazione per poter creare diversi tipi di applicazioni.  Per ogni applicazione, la maggior parte delle volte, …
Un buon programmatore, si sa, necessita di un ampio bagaglio di conoscenze per potersi districare tra le difficoltà che questo mestiere comporta. Oggi le richieste sul mercato sono molteplic…
UI UX design: un settore dalle mille sfaccettature Il design dell’esperienza utente (UX) e dell’interfaccia utente (UI) costituisce il fondamento su cui si costruiscono le interazioni digitali coinvo…
Nel contesto dello sviluppo web, il backend riveste un ruolo cruciale nell’assicurare il corretto funzionamento di un sito. Il backend, si occupa delle operazioni non visibili agli utenti finali, gest…
Il Data Analyst è una figura professionale sempre più richiesta nel mondo del lavoro, grazie alla sua capacità di analizzare e interpretare grandi quantità di dati per supportare le decisioni azie…

Seguici su Facebook

Scopri di più sul corso Hackademy

Inizia la tua nuova carriera nel mondo digital e tech.

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.

Contattaci senza impegno per informazioni sul corso

Pagamento rateale

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

Esempio di finanziamento  

Importo finanziato: € 2440 in 24 rate da € 112 – TAN fisso 9,37% TAEG 12,57% – importo totale del credito € 2788,68.

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/07/2023 al 31/12/2023.

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 183 €/mese.

Esempio di finanziamento 

Importo finanziato: € 3990 in 24 rate da € 183 – TAN fisso 9,37% TAEG 12,57% – importo totale del credito € 4496,56.

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/07/2023 al 31/12/2023.

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.