A puertas de las elecciones presidenciales es cuando muchos recién se preocupan por alistar lo que se va a necesitar para una campaña electoral con tantos candidatos y tantas propuestas que en realidad, en vez de resolver, nos marean. El candidato que use tecnología moderna, con el análisis correcto de la estadística real (y no la de las encuesta "truchas") es el que va a ganar las elecciones, o por lo menos, pasar la valla electoral. Analicemos nuestras opciones y aprendamos un poquito de lo que se viene. 

 En este artículo le mostraremos cómo importar funciones (estadísticas) más avanzadas de MySQL y cómo analizar los datos a gran escala

Una parte importante del análisis de cada conjunto de datos es el análisis de sus propiedades estadísticas. Especialmente si desea utilizarlo para el aprendizaje de máquinas, debe tener una visión adecuada de los datos para realizar el preprocesamiento y la extracción de sus características requeridas para su uso. 

Para todo análisis, necesitas la data. Si ya tienes la data ó conjunto de datos en una base de datos (como MySQL), resulta útil ejecutar estos análisis directamente en la base de datos ó programas escritos para automatizar dichos análisis estadísticos. Sin embargo, las funciones estadísticas integradas de algunas bases de datos no son muy extensas; este es el caso de las bases de datos MySQL, por lo que debes de agregarlo mediante su "addon", que pronto explicaremos, para su uso adecuado. En este artículo, basado desde este otro en Inglés y sólo para suscriptores, le mostraremos cómo importar funciones (estadísticas) más avanzadas de MySQL y cómo analizar los datos a gran escala.

Prerequisitos

Asumimos que tiene conocimientos básicos de estadística y del lenguaje de consulta SQL.

Instancia MySQL + el conjunto de datos de muestra

También asumimos que ya instaló una instancia en ejecución de una base de datos MySQL con un usuario privilegiado e importó la base de datos de muestra de empleados "estándar" desde el sitio web de MySQL como un conjunto de datos de muestra.

De lo contrario, le pediríamos que lea primero el siguiente artículo: “Primeros pasos hacia el análisis de datos en MySQL: funciones básicas”.

El esquema de base de datos existente, con el que trabajaremos en este artículo, debería verse así:

La mayoría de los campos se explican por sí solos, pero para estar seguro, enumeramos las descripciones de los campos no obvios para las tablas empleados y salarios a continuación:

• employees.emp_no: la clave principal de la tabla de empleados.
• employees.hire_date: la fecha de contratación del empleado.
• salaries.emp_no: la clave externa de la tabla de empleados.
• salaries.salary: El salario pagado en un período determinado.
• salaries.from_date: La fecha a partir de la cual se pagó el salario.
• salaries.to_date: La fecha en la que se pagó el salario.

Instalar las extensiones estadísticas

Dado que MySQL tiene funciones estadísticas limitadas, necesitamos una "extensión" para las avanzadas. En este artículo utilizamos el siguiente: Estadísticas para MySQL.

Esta extensión incluye varias funciones estadísticas, como el coeficiente de Gini, la mediana, el generador de números aleatorios Mersenne Twister, el coeficiente de correlación de Pearson, el número de fila y la asimetría, solo por nombrar algunas.

Los usuarios de Windows pueden descargar directamente (y copiar el archivo libsqlstat.dll de 32 o 64 bits al directorio lib\plugin de su instalación de MySQL) e instalar la extensión con el usuario root:

mysql -u root -p -f < examples\install.sql

Usuarios deLinux o MacOS deben de compilar sus proyectos siguiendo las instrucciones del INSTALL-file.

Generación Aleatoria de Números (Pseudo-number generation)

Antes de ver las funciones estadísticas, veamos cómo generar distribuciones o números aleatorios.

Para ser claros, hablamos aquí de aleatoriedad o pseudoaleatoriedad "estadística", que no contiene ningún patrón o regularidad reconocible; esto es útil y suficiente para muchos casos de uso de estadísticas.

Sin embargo, esto no implica necesariamente una “verdadera” aleatoriedad, es decir, una imprevisibilidad objetiva.

Números Aleatorios Simples

Con la siguiente consulta puedes generar un número aleatorio mayor que ≥ 0 y menor que < 1 usando el algoritmo Mersenne Twister (rand_mt).

SELECT rand_mt();

Solo como muestra, generamos "ponderaciones" aleatorias para cada entrada de salario para nuestro conjunto de datos de muestra, es decir, agregamos una nueva columna a la tabla de salarios y generamos números aleatorios para esta columna.

ALTER TABLE salaries ADD weight double;

UPDATE salaries SET weight = rand_mt();

Variante de la distribución normal (Variate of the normal distribution)

Si se requiere una distribución normal para el análisis de datos, simplemente podemos generar una variable mediante la siguiente consulta (rand_norm([σ, [μ]])):

SELECT rand_norm(); // μ=0, σ=1
SELECT rand_norm(0.3);// μ=0.3, σ=1
SELECT rand_norm(0.3, 4);// μ=0.3, σ=0.4

El valor predeterminado para la media μ es 0 y el valor predeterminado de la desviación estándar σ es 1.

Funciones Estadísticas

Ahora, veamos las funciones estadísticas.

Media o Promedio (Mean or average)

El promedio o media aritmética (avg) es la suma de todos los números de una serie dividido por el conteo de números de la serie.

La consulta SQL correspondiente tiene este aspecto y devuelve el salario medio de los empleados:

SELECT AVG(s.salary) as 'mean' FROM salaries s; // 63,810.7448

Media Ponderada (Weighted average)

El promedio ponderado (avgw(x [,w]))  es un promedio en el que algunos puntos de datos contribuyen más que otros en una serie.

La consulta SQL correspondiente tiene este aspecto y devuelve el salario promedio ponderado de los empleados (tenga en cuenta que utilizamos nuestras ponderaciones aleatorias solo como muestra):

SELECT AVGW(s.salary, s.weight) as 'weighted mean' FROM salaries s;

Varianza y Desviación Típica (Variance and standard deviation)

MySQL proporciona funciones integradas para la varianza (variance) y la desviación estándar (std) de una muestra de datos.

SELECT 
  VARIANCE(salary) as 'variance',
  STD(salary) as 'std'
FROM salaries;

La varianza es una medida estadística de la dispersión entre números en un conjunto de datos, definida como el promedio de las diferencias al cuadrado del valor medio y calculada de la siguiente manera:

The variance function is a synonym for var_pop() — calculating the population standard variance of all the fields in a particular column. On the other hand, var_samp() calculates the sample variance.

Notably, you use the population variance (all functions with *_pop) if you need all of the data available whereas you use the sample variance (all functions with *_samp) if you only need a proportion of it. The same applies to all following functions in this section.

The standard deviation is a measure of how dispersed the data is in relation to the mean — calculated as follows:

If a weighted standard deviation or skewness (a measure of the asymmetry of a distribution) has to be calculated, this goes beyond the standard functions of MySQL, and we need the functions of the installed extension.

SELECT stddevw_pop(s.salary) as 'std1' FROM salaries s;
SELECT stddevw_pop(s.salary, s.weight) as 'std2' FROM salaries s;

SELECT stddevw_samp(s.salary) as 'std3' FROM salaries s;
SELECT stddevw_samp(s.salary, s.weight) as 'std4' FROM salaries s;

SELECT skewness_pop(s.salary) as 'skew1' FROM salaries s;
SELECT skewness_pop(s.salary, s.weight) as 'skew2' FROM salaries s;

SELECT skewness_samp(s.salary) as 'skew3' FROM salaries s;
SELECT skewness_samp(s.salary, s.weight) as 'skew4' FROM salaries s;

If the second parameter is not specified, the weight is equal to 1 (i.e., equally weighted).

As a concluding example, we now calculate the average salaries grouped by gender with all relevant statistical properties of the data sample.

SELECT 
  e.gender,
  AVG(s.salary) as 'avg salary',
  skewness_samp(s.salary) as 'skew',
  stddevw_samp(s.salary) as 'std',
  var_samp(s.salary) as 'variance'
FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no
GROUP BY e.gender
ORDER by asalary ASC;

// gender, avg salary, skew, std, variance
F, 63769.6032, 0.7757583639505996, 16844.89107702957, 283750105.9957
M, 63838.1769, 0.7793202853331737, 16944.629259215217, 287120292.4635

Median

The median is the value exactly in the middle of a data set, separating the higher half from the lower half of the data samples.

This means 50% of the values are smaller (or equal) than the median value, and 50% are higher (or equal). Null values are ignored.

SELECT MEDIAN(s.salary) as 'median' FROM salaries s;

The above SQL query gives the median salary of all employees.

Gini coefficient

The Gini coefficient is a measure of statistical dispersion or unequal distribution or inequality. 0 indicates that all values are equal; 1 indicates that all values but one are zero.

SELECT GINI(s.salary) as 'gini coefficients' FROM salaries s; // 0.14754749176775583

SELECT gini(g.asalary)
FROM(
  SELECT 
    e.gender,
    AVG(s.salary) as 'asalary'
  FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no
  GROUP BY e.gender
  ORDER by asalary ASC
) as g; // 0.00026868933832358

The above SQL queries show us some small inequality in individual salaries (0.1475), but equality in the average gender-specific salaries (0.0003).

Statistical relations of two variables

Next, we look at the statistical relationships between two variables x and y.

Correlation

The statistics extension provides the Pearson correlation coefficient (corr(x,y [,w])), which measures the linear correlation between two variables and is calculated as follows:

The resulting value is a number between -1 and 1 that measures the strength and direction of the relationship:

• Between -1 and 0: negative correlation, i.e., when one variable changes, the other variable changes in the opposite direction.
• 0: no correlation, i.e., there is no relationship between these variables.
• Between 0 and 1: positive correlation, i.e., when one variable changes, the other variable changes in the same direction.

For our data set, we now want to find out whether there is a correlation between age and salary.

SELECT
 corr(m.start_year - m.birth_year,m.salary)
FROM
(
    SELECT
        YEAR(e.birth_date) as 'birth_year',
        YEAR(s.from_date) as 'start_year',
        s.salary as 'salary'
    FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no
) as m; // 0.20731856518839656

As we can see, there is a very slight (negligible) positive correlation (0.207) between age and salary.

Ranking

Ranking is the data transformation in which numeric or ordinal values are replaced by their rank when the data is sorted.

We can create such a ranking list with the function rownumber().

Row number

In this first example, however, the function returns the row number of the whole data set.

SELECT 
  rownumber() AS rowNo,
  e.first_name,
  e.last_name,
  e.gender,
  s.salary
FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.to_date > NOW()
ORDER by s.salary DESC
LIMIT 10;

Only in the second example, after the data set has been filtered and sorted, can we use the rownumber function to create a ranked list.

SELECT
  rownumber() AS rank,
  r.*
FROM
(
  SELECT
      e.first_name,
      e.last_name,
      e.gender,
      s.salary
    FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no
    WHERE s.to_date > NOW()
    ORDER by s.salary DESC
    LIMIT 10
) as r;


//rank,first_name,last_name,gender,salary
//1,Tokuyasu,Pesch,M,158220
//2,Honesty,Mukaidono,M,156286
//3,Xiahua,Whitcomb,M,155709
//4,Sanjai,Luders,M,155513
//5,Tsutomu,Alameldin,M,155190
//6,Willard,Baca,M,154459
//7,Lidong,Meriste,M,154376
//8,Charmane,Griswold,M,153715
//9,Weijing,Chenoweth,F,152710
//10,Weicheng,Hatcliff,F,152687

The above SQL query gives us the current top 10 highest earners (the to_date in the salaries table is MAX when it is the currently valid salary).

Conclusión

Hemos dejado la mitad de este artículo en inglés para que lo practiques. Aparte, absolutamente todos los comandos aquí explicados, la computadora los entiende solamente en inglés y no existen en español o en ningún otro lenguaje. En este artículo, hemos mostrado cómo puede realizar análisis de datos avanzados en bases de datos MySQL utilizando una extensión de estadísticas del poderoso motor MySQL.

Con estas funciones (incluidas las funciones integradas de MySQL), puede obtener de manera eficiente una visión profunda de conjuntos de datos a gran escala antes de exportarlos a herramientas estadísticas/ML dedicadas, que describiremos en un artículo siguiente.