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

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

Baloncesto y estadística: ¿Cómo se calcula el porcentaje de impacto o PIE?

Iba a hacer una entrada hablando de cómo calcular el PIE en una tabla de Excel o en una consulta SQL, pero mejor dejaré por aquí la fórmula y que cada cual le de el uso adecuado. Pero vayamos por partes ¿qué es el PIE? ¿Existe también la MANO?

PIE no es otra cosa que el acrónimo de Player Impact Estimate, una estadística que pretende medir el impacto estadístico de un jugador respecto a las estadísticas totales de un partido. Citando textualmente a la web de la NBA:

In its simplest terms, PIE shows what % of game events did that player or team achieve”.

Desde el fenómeno “Moneyball” en (casi) todos los deportes vivimos una época de obsesión con las estadísticas. Este artículo de Andrés Monje es un guía genial para echarle un ojo a todas estas nuevas métricas que hacen que cada vez muchos llamados “intangibles” sean más tangibles y contables. También puedes pegarle una oída a los podcast de Javi Mendoza, un apasionado de todos los temas relacionados con estadística y baloncesto.

Este PIE fue incluído en un primer momento en las estadísticas oficiales de la WNBA en la temporada 2013, como una alternativa a la popular estadística avanzada de eficiencia (PER) de John Hollinger. Seguidamente la gente de su sección de estadística  lo incluiría dentro de las estadísticas oficiales de la web de la NBA también.

Para calcular el PIE tenemos que sumar y restar las estadísticas positivas y negativas del jugador y dividirlo entre los totales de esas estadísticas del partido. La fórmula, tal cual está recogida en la web de la NBA, es la siguiente:

(PTS + (FGM + FTM – FGA – FTA) + DREB + (0.5 * OREB) + AST + STL + (0.5 * BLK) – PF – TO) / (GmPTS + (GmFGM + GmFTM – GmFGA – GmFTA) + GmDREB + (0.5 * GmOREB) + GmAST + GmSTL + (0.5 * GmBLK) – GmPF – GmTO)

O lo que es lo mismo: Sumamos los puntos, tiros de campo anotados, tiros libres anotados, rebotes defensivos, asistencias, robos, los rebotes ofensivos divididos entre dos y los tapones divididos entre dos, y restamos los tiros de campo intentados, los tiros libres intentados, las pérdidas de balón y las faltas cometidas por el jugador. Luego dividimos ese resultado entre el producto de la misma operación pero con los valores totales del partido: todos los puntos, todos los tiros intentados, todos los rebotes, etc.

¿La diferencia con el PER? El PER nos da un valor ajustado por minutos, ritmo y la media de la liga, mientras que el PIE nos da un valor relativo a los totales del partido disputado (o de todos los partidos, si así lo queremos).

¿Y con la Valoración del basket FIBA? En ese caso esa estadística solo realiza la suma de la aportación estadística del jugador, pero no la pone en relación con los totales.

¿Qué estadística es más útil? Pues hay tantos defensores como detractores de ambas. Se critica, por ejemplo, que el PER no valora el impacto defensivo del jugador o que no tiene en cuenta la eficiencia de los tiradores, no penalizando a los que fallan muchos tiros. También hay quien critica que el PIE no es más que una versión actualizada de la antigua estadística de EFF de la NBA, añadiendo el denominador con los totales para compensar el que no haya una relación con el ritmo del partido.

 

 

Exportar estadísticas a Excel desde Basketball Reference

La web Basketball Reference ofrece una buena cantidad de estadísticas relativas a la NBA, tanto a nivel de jugador como de equipos. Incluye estadísticas clásicas, avanzadas, de tiro o de uso. Incluso podemos ver las estadísticas clásicas de un jugador partido a partido y además el sistema dispone de un comparador de jugadores o de un buscador para estadísticas concretas seleccionando los parámetros que queremos pasarle (tipo “Jugadores que han hecho una temporada promediando más de 25 puntos, 10 rebotes y un 40% en triples“). Aquí os dejo unos ejemplos:

Olajuwon estadística

Estadísticas tiro Durant

Carter partidos 2001

Pero si sois tan freaks que no os basta con eso (yo me declaro culpable) desde esta web podéis exportar los datos para trabajar con ellos en una hoja de cálculo de Excel o de LibreOffice Calc. Tenéis dos opciones: exportar directamente como libro de Excel, que en principio es experimental pero a mi no me ha dado problema sacando a Word 2010  o a Word 2007, o convertir en CSV consiguiendo así un formato exportable a diversas aplicaciones.

Bird en CSV

Para exportar basta con que os situéis con el ratón sobre el desplegable que pone Share & more y desde ahí seleccionáis una de las dos opciones que están marcadas en la captura de abajo: Get as Excel Workbook, para que os lo saque como un excel, o Get Table as CSV (For Excel):

Exportar

Google Public Data Explorer

Si seguís este blog habitualmente no hace mucho veríais una entrada dónde presentaba datos sobre la evolución de la población en la comarca del Ortegal y ayuntamientos colindantes. Dichos datos habían sido recabados con una herramienta llamada Google Public Data Explorer.

Ya sabéis que Google son como Johnny 5 de Cortocircuito “Más datos, Stephanie, más datos“. Y no paran de darle a la cabeza en busca de formas para conseguir más. Este Public Data Explorer de Google nos dará acceso a un montón de datos de acceso público de diferentes agencias, en principio filtrados por el idioma por defecto de nuestra cuenta pero podremos acceder a más simplemente seleccionando otro idioma en la pantalla principal. De esta forma, mediante sencillos menús podremos generar diversas gráficas que compartir o insertar en nuestros documentos.

Evolución Población Ortegal
La gráfica que utilicé en el artículo sobre el Ortegal.

Pero no es esta la única funcionalidad de Public Data Explorer, ya que también nos permite subir nuestros propios archivos con nuestros datos sobre lo que sea (que a ellos también les interesan, claro está) para generar nuestra propias gráficas. Esto requiere tener dichos datos en un formato XML que sea interpretable por dicha aplicación de Google (el formato se llama DSPL). Habría que añadir que para esto Google tiene una herramienta más potente, dentro de Google Enterprise Analytics, llamada Data Studio 360 pero, de momento, en España no esta disponible.

¿Es útil esta herramienta? Bueno puedo entender que a muchos les dará reparo subir sus datos para generar la gráfica, por no fiarse del uso que Google les pueda dar. Pero más allá de esta legítima duda sí se trata de una herramienta fácil de usar e intuitiva, y además nos permite también trabajar con un buen montón de datos de acceso público de una forma muy sencilla. Seguramente más de un estudiante pueda sacar petróleo de ahí para ilustrar sus trabajos (y a más de un periodista no le vendría mal tampoco).