Sql developer: como detectar anomalías

La anomaly detection es una de las prácticas más extendidas en el contexto de la seguridad de la información y la inteligencia artificial. Es una práctica presente en el análisis de datos, donde la detección de anomalías es la identificación de observaciones raras, elementos, eventos que difieren significativamente del grueso de los datos. Es un proceso popular en las grandes empresas encontrar datos que no están alineados con los estándares de la empresa. Hay muchas formas de detectar anomalías en los datos y, como la mayoría de las cosas en la vida, no existe una "manera correcta" de hacerlo. El enfoque vendrá dictado por los datos a analizar, pero también por los requisitos del proyecto final.

En el mundo del análisis de datos y la programacion informatica todo lo que existe se puede hacer o ejecutar utilizando el lenguaje sql.

Qué es SQL

El significado de SQL significa Structured Query Language y es el lenguaje de consulta estándar en las bases de datos relacionales. Es uno de los lenguajes de programación más utilizados en el mundo de la tecnología de la información y es el lenguaje de consulta más extendido entre las bases de datos.

La detección de anomalías es un problema bastante omnipresente que abarca casos de uso desde la detección de fraudes hasta fallas en las máquinas. Algunos problemas requieren aprendizaje automático supervisado o no supervisado. Por ejemplo, puede analizar los datos de ventas en una serie temporal y señalar los días en los que el volumen de ventas es anómalo.

El Z-score es una técnica estadística simple y no supervisada para marcar valores atípicos. Solo resta la media y divide por la desviación estándar. Este puntaje se utiliza en matrices de correlación, en el análisis matemático de datos, para evaluar la tendencia de dos variables.

Intentemos crear un modelo de detección de valores atípicos bastante genérico en lenguaje SQL, basado en una tabla ficticia a nivel de transacción.

Organizamos el script SQL para abstraer los detalles de la tabla de transacciones en el primer CTE e incluimos el "estado" como una dimensión por la cual queremos dividir los datos. En la mayoría de los casos, tiene sentido observar las anomalías dentro de algún subconjunto de los datos y, por supuesto, considerar el "estado" como un sustituto de cualquier dimensión o dimensiones en las que desee informar anomalías.

 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 es un modelo de detección de anomalías potente y minimalista que se puede implementar de forma rápida y flexible en el almacén de datos. Proporciona una manera de obtener un Minimum-Viable-Product ligero, listo para usar y validado, que es quizás el trabajo más difícil que tiene un data scientist.

La detección de anomalías es una tarea que se implementa principalmente en Machine Learning con el lenguaje Python. La complejidad de un código SQL es ciertamente mayor que la de un script en un lenguaje más avanzado, que implementa la detección de anomalías. Por otro lado, el lenguaje SQL te permite aprovechar al máximo el motor de la base de datos y, en muchos casos, puede ser suficiente para tus necesidades, ahorrándote la molestia de utilizar métodos más complejos.

Esta solución es eficiente para cualquier persona que no sea un experto data scientist, que no sea estadístico, pero que esté "limitado" a realizar la tarea de desarrollador. Dado que el aprendizaje automático y el mundo del análisis de anomalías es muy amplio y complejo incluso a nivel matemático, tener un buen conocimiento del lenguaje SQL en este sentido ayuda mucho.