Calcular la mediana en SQL-Server

Como no me apetece meterme en una discusión infructuosa con un individuo *troll ultraderechista sexualmente frustrado* voy a aprovechar la hora de comer en el trabajo para comentaros cómo podemos calcular la mediana de una serie de números en SQL-Server. Ya vimos no hace mucho el significado de este término y también cómo podemos calcularlo en Libre Office Calc.

Si vamos a trabajar con SQL-Server 2012 o superior la función PERCENTILE_DISC() nos servirá, sin tener que hacer nada más. Es una función que calcula un percentil concreto para una serie de valores ordenados de un conjunto de filas, y su sintaxis es:

PERCENTILE_DISC ( número ) WITHIN GROUP ( ORDER BY exp_ordenación [ ASC | DESC ] )
OVER ( [ partido_por ] )

Aquí os copio sus argumentos de la web de documentación de Microsoft directamente, donde también tenéis ejemplos de código para SQL-Server y para Azure:

  • número :El percentil que se va a calcular. El valor debe estar entre 0,0 y 1,0.
  • WITHIN GROUP ( ORDER BY exp_ordenación[ ASC | DESC ]):Especifica una lista de valores para ordenar y cuyo percentil se va a calcular. Solo se permite una exp_ordenación. El criterio de ordenación predeterminado es ascendente. La lista de valores puede ser de cualquiera de los tipos de datos válidos para la operación de ordenación.
  • OVER ( partido_por ):Divide el conjunto de resultados generado por la cláusula FROM en particiones a las que se aplica la función de percentil. Para más información, vea Cláusula OVER (Transact-SQL). Las cláusulas y no se pueden especificar en una función PERCENTILE_DISC.

De esta forma PERCENTILE_DISC(0.5) calculará la mediana del cojunto de filas que estamos analizando.

Pero ¿qué pasa si trabajamos con una versión anterior a SQL-Server 2012? Te parecerá algo prehistórico, pero en muchos sitios sigue funcionando SQL-Server 2008 o 2005. ¿Cómo lo hago ahí donde no dispongo de la función PERCENTILE_DISC()?

Bueno, ahí he hecho una solución un poco picapedrera, porque según el conjunto de datos que tengamos las hay bastante mejores. Esta realmente consume muchos recursos, pero por otra parte es universal. Si la tabla cuenta con una columna de Identidad la cosa puede ser más eficiente:

Básicamente he usado una tabla llamada Actividades, donde hay una columna numérica llamada Importe. Para calcular la mediana de este importe vamos a sacar los dos valores centrales dividiendo las filas en dos mitades, ordenando una de forma descendente y otra ascendente. Tras eso los sumamos y hacemos la media de la suma.

Select ((
    Select Top 1 Importe
    From   (
                    Select  Top 50 Percent Importe
                    From    Actividades
                    Where   Importe Is NOT NULL
                    Order By Importe
                    ) As A
    Order By Importe DESC) + 
    (
    Select Top 1 Importe
    From   (
                    Select  Top 50 Percent Importe
                    From    Actividades
                    Where   Importe Is NOT NULL
                    Order By Importe DESC
                    ) As A
    Order By Importe Asc)) / 2 as MedianaImportes
Anuncios

Diferencia entre muestra y población.

Población y Muestra son dos términos usados en estudios de estadística, similares y relacionados. Por dicha similitud tienden a ser confundidos, por lo que es importante que sepamos diferenciarlos para no tomar, literalmente, la parte por el todo.

La población, también llamada universo, es un conjunto de elementos sobre los que se realizan estudios y observaciones. Se trata de una variable o magnitud aleatoria con unas determinadas características comunes. Es, en resumen, el conjunto formado por todos los elementos a estudiar. Pero hay casos en los que el total de la población adquiere una magnitud demasiado extensa para ser estudiada. Por ejemplo, si queremos hacer un estudio sobre la salud cardiovascular en la Unión Europea no podemos pretender usa datos de todos los ciudadanos con historiales médicos.

La muestra es una selección abarcable de parte de una población para su estudio. Por definición, la muestra se siempre una parte de la población. Esta debe ser representativa, por lo que el método de selección debe ser adecuado (preferiblemente aleatorio) para evitar acabar teniendo un muestra sesgada que nos de un resultado irreal (decía un catedrático en estadística que un muestreo suficientemente torturado puede demostrar cualquier cosa). Siguiendo el ejemplo anterior, si hacemos el estudio sobre salud cardiovascular eligiendo solo a gente de entre 20 y 30 años y lo repetimos después con gente de más de 70, el resultado obtenido será radicalmente distinto.

Si la selección del muestreo es adecuada el resultado del estudio sobre una muestra será más preciso que al realizarlo sobre el total de la población, dado que trabajamos sobre un conjunto de datos más pequeño y esto nos permitirá minimizar errores.

En resumen: La población es el total de individuos o elementos que queremos estudiar y la muestra es una selección aleatoria de elementos de esa población que utilizaremos para trabajar de forma más precisa.

Diferencia entre media, mediana y moda

La media, la mediana y la moda son términos estadísticos que se usan para la comprensión de tendencias centrales cuando analizamos un conjunto de valores.

La media aritmética o promedio es especialmente útil cuando tratamos con distribuciones regulares. Se calcula mediante la suma de todos los valores observados dividida entre el número de observaciones. Pierde utilidad cuando se trata de distribuciones muy irregulares, con mucha diferencia. El ejemplo clásico de esto: si yo me como un pollo y tú no comes nada la media es que nos hemos comido medio pollo por cabeza.

La mediana es el valor numérico central de un conjunto de números, y es más útil cuando tratamos con distribuciones irregulares. Para su cálculo necesitamos ordenar toda la serie de valores de menor a mayor. Tras esto, en caso de que el número total de valores sea par, la mediana se calcula sacando el promedio de los dos valores centrales. En el caso de que sea una cantidad impar de valores la mediana será directamente el valor central.

Finalmente la moda es la incidencia más repetida en un conjunto de valores. Esto implica que puede haber más de una moda. Es útil cuando lo relevante es conocer el valor más común.

Veamos un ejemplo: imaginemos que tenemos un grupo de cinco amigas y vamos a calcular la media, mediana y moda de sus salarios anuales. Una cobra 12.000 euros, otra 15.000, otra 300.000 y hay dos que cobran 18.000.

La media sería el resultado de (12.000+15.000+300.000+18.000+18.000)/5, que es 72.600 euros. Como ves, al haber un valor tan alejado del resto dispara la media muchísimo.

Para la mediana vamos a ordenar los valores: 12.000|15.000|18.000|18.000|300.000. Al ser impar ya nos quedamos con el valor central, que sería 18.000 euros.

Finalmente para la moda miramos cual es el valor más repetido, que son también 18.000 euros ya que está dos veces en la lista.

Si crees que esto no es útil para el día a día piénsalo mejor, comprender bien estos conceptos nos permite abordar de forma más crítica muchas informaciones económicas que nos dan desde la prensa o desde los gobiernos.

Adaptando los porcentajes de tiro avanzados al Basket 3×3

Pido disculpas por lo poco que he publicado este mes, pero he estado con temas de estudios liado. Volvemos a la actividad al ritmo habitual.

Como recordarás, no hace mucho hablamos sobre el porcentaje de tiro real y el porcentaje de tiro efectivo. Esta semana se disputarán los Juegos del Mediterráneo en Tarragona y el Basket 3×3 ha tomado el lugar del baloncesto clásico en esta competición. Dado que las normas son distintas, y además afectan al valor de los tiros anotados, dichas estadísticas avanzadas no son válidas para esta variedad. ¿Cómo corregimos las fórmulas? Tal que así:

El porcentaje de tiro efectivo es la estadística que pondera el mayor valor de los “triples“. Como en este caso el valor de los tiros realizados más allá de la línea es el doble que los realizados desde el interior la fórmula quedaría así: (Tiros de campo anotados+Triples Anotados)/Tiros de campo intentados

El porcentaje de tiro verdadero incluye también el valor de los tiros libres. Pero en este caso un tiro libre tiene el mismo valor que un tiro desde el interior de la zona, y el valor de un tiro standar es de 1, así que la fórmula quedaría: Puntos / (Tiros de campo intentados + Tiros libres intentados)

Baloncesto y estadística: Consulta SQL para calcular el porcentaje de robos de un jugador

Seguimos con el tema de la estadística baloncestística avanzada. Ya vimos cómo se calcula el PIE,también los porcentajes de tiro avanzado y real y hubo otra entrada sobre estadísticas de asistencias. Hoy vamos con el porcentaje de robos.

¿Qué nos indica este porcentaje? Pues la cantida de balones que un jugador recupera, pero teniendo en cuenta los minutos que juega y el ritmo de juego del rival. Aquí es importante matizar ¿qué entendemos por robo? Pues cuando un jugador, por medio de una acción defensiva legal, logra que el rival pierda la posesión del balón ganándola a su vez para su equipo. Es decir, que no cuenta ni cuando se recuperan balones sueltos ni cuando se intercepta el balón cambiando su trayectoria pero sin recuperar la posesión. Ojo, ahora la NBA también recoge eso en sus estadísticas oficiales avanzadas como “Loose ball recoverd” y “Deflections“. Peor lo que ahora nos ocupa es el tema de los robos.

¿La fórmula? Es la siguiente:

100 * Robos del Jugador * Minutos totales de Partido / Minutos jugados por el jugador * Posesiones del equipo rival.

En una base de datos donde tuviéramos los campos Robos,MinutosPartido,MinutosJugador y PosesionesRival la consulta sería algo así

Select 100.00*Convert(Numeric(4,2),Robos)*Convert(Numeric(4,2),MinutosPartido)/Convert(Numeric(4,2),MinutosJugador)*Convert(Numeric(4,2),PosesionesRival) as StealPcnt

¿Limitaciones de esta estadística? Pues que recoge solo el porcentaje de éxitos, al igual que el total de robos, por lo que un jugador puede quedar sobrerrepresentado en la misma, pareciendo mejor defensor de lo que es. Gente como Monta Ellis o, en Europa, Bo McCalebb son jugadores con manos rápidas que por su estilo defensivo consiguen muchos robos pero que en el global no son grandes defensores.

Baloncesto y estadística: Consulta SQL para calcular los ratios de asistencias de un jugador

Seguimos con el tema de la estadística baloncestística avanzada. Ya vimos cómo se calcula el PIE y también los porcentajes de tiro avanzado y real, así que hoy vamos con los ratios de asistencias.

En este caso tenemos dos fórmulas distintas, ya que por un lado tenemos el Ratio de Asistencias de Hollinger (hAST%) y por otro el de Pomeroy (pAST%).

El ratio de Hollinger se calcula respecto al número de balones que terminan en manos del jugador mientras que el de Pomeroy lo calcula respecto al tiempo que el jugador está en la pista y el número de posesiones.

La fórmula de Hollinger sería la siguiente:

ASISTENCIAS*100 / (TIROS DE CAMPO INTENTADOS POR EL JUGADOR+ 0.44 * TIROS LIBRES INTENTADOS POR EL JUGADOR + ASISTENCIAS + PÉRDIDAS)

Por su parte la de Pomeroy sería esta:

ASISTENCIAS*100 / (((MINUTOS JUGADOS / (MINUTOS TOTALES DEL EQUIPO / 5)) * TIROS DE CAMPO INTENTADOS POR EL EQUIPO ) – TIROS DE CAMPO INTENTADOS POR EL JUGADOR)

Entonces suponiendo que tenemos una tabla Estadisticas con los campos: Asistencias, Tiros, TirosLibres, Perdidas con los valores correspondientes, la fórmula de Hollinger la sacaríamos tal que así:

Select Convert(Numeric(5,2),Asistencias)*100.00  / (Convert(Numeric(5,2),Tiros)+ 0.44 * Convert(Numeric(5,2),TirosLibres) + Convert(Numeric(5,2),Asistencias) + Convert(Numeric(5,2),Perdidas)) as hAST from Estadisticas

Para la de Pomeroy necesitaríamos una tabla com los campos Asistencias,Minutos,MinutosEquipo,TirosEquipo y Tiros:

Select Convert(Numeric(5,2),Asistencias)*100.00  / (((Minutos / (Convert(Numeric(5,2),MinutosEquipo) / 5.00)) * Convert(Numeric(5,2),TirosEquipo)) - Convert(Numeric(5,2),Tiros)) as pAST from Estadisticas

Finalmente nos quedaría el ratio de asistencias por pérdida, que consiste simplemente en dividir las asistencias repartidas entre las pérdidas de balón sufridas. Suponiendo una tabla con los campos Asistencias y Perdidas la consulta sería:

Select Convert(Numeric(5,2),Asistencias)/Convert(Numeric(5,2),Perdidas) as ASTTO from Estadisticas

La críticaal ratio de Hollinger es que solamente refleja la tendencia de un jugador a asistir y no tanto su eficacia, mientras que la crítica al de Pomeroy es que si los compañeros fallan muchos tiros penalizan la estadística del asistente.

Baloncesto y estadística: Consulta SQL para calcular el porcentaje de tiro efectivo (eFG%) y el porcentaje de tiro verdadero (TS%)

El otro día habláblamos un poco sobre estadística avanzada en baloncesto viendo cómo se calcula el PIE, y como ha sido un artículo bien recibido vamos a ver cómo podemos calcular las dos principales estadísticas avanzadas de tiro y aplicarlas a una consulta SQL. Pero veamos primero qué son estas estadísticas.

La estadística de tiro clásica, el porcentaje de tiros anotados, ha dado resultados engañosos desde que se introdujo el tiro de tres. Pensemos por un segundo en dos jugadores que lanzan diez tiros: el primero lanza diez tiros de dos y anota seis, el segundo diez triples y anota cuatro. El porcentaje de tiro simple del primero es de un 60% mientras que el del segundo es de un 40%, pero en realidad ambos han anotado la misma cantidad de puntos: 12. El porcentaje de tiro efectivo y el porcentaje de tiro verdadero ponderan el mayor valor de los triples y el menor valor de los tiros libres para que el cálculo de la efectividad del tirador sea más realista. Dwight Howard o Clint Capela, que anotan mucho pegados al aro o machacando, tendrían mejores porcentajes simples que tiradores excelsos desde la larga distancia como Nowitzki o Curry. O en la ACB podría ser el caso de Tomic y Navarro.

El porcentaje de tiro efectivo es la estadística que pondera el mayor valor de los triples y la fórmula para su cálculo es: (Tiros de campo anotados+0.5*Triples Anotados)/Tiros de campo intentados.

El porcentaje de tiro verdadero incluye también el valor de los tiros libres y se calcula con la siguiente fórmula: Puntos / 2 * (Tiros de campo intentados + 0.44 * Tiros libres intentados)

Supongamos que tenemos una tabla llamada Tiros con las estadísticas de tiro de un jugador en una base de datos SQL-Server, con los siguientes campos: TirosIntentados, TirosAnotados, TriplesAnotados, TirosLibresIntentados y Puntos. Podríamos tener más, pero estos son los campos que necesitamos. Supongamos que todos estos campos almacenan números enteros, que sería lo lógico, y que tendremos que aplicar una conversión porque necesitamos un resultado decimal (en SQL-Server 2008 sería necesario ).

¿Cómo irían las consultas? Aquí para el eFG%:

Select ((Convert(Numeric(4,2),TirosAnotados)+0.5*Convert(Numeric(4,2),TriplesAnotados))/Convert(Numeric(4,2),TirosIntentados)) as eFG from Tiros

Aquí para el TS%:

Select (Convert(Numeric(4,2),Puntos) / 2.00 * (Convert(Numeric(4,2),TirosIntentados) + 0.44 * Convert(Numeric(4,2),TirosLibresIntentados))) as TS from Tiros