Etiqueta: sql-server

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

en por Donnie Rocken Baloncesto, Informática, Matemáticas-Ciencias, SQLDeja un comentario

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

SQL-Server: Consulta para saber cuántas tablas o vistas hay en una base de datos

en por Donnie Rocken Informática, sistemas y redes, SQLDeja un comentario

¿Manejas una base de datos en SQL-Server? ¿Quieres saber cuántas tablas tienes en tu base de datos? ¿Y cuántas vistas? ¿Y ambas? Estas consultas te darán esa información:

#Contar Tablas
SELECT COUNT(*) from Information_Schema.Tables where TABLE_TYPE = 'BASE TABLE'

#Contar Vistas
SELECT COUNT(*) from Information_Schema.Tables where TABLE_TYPE = 'VIEW'

#Contar Tablas y vistas
SELECT COUNT(*) from Information_Schema.Tables

SQL-Server y capitalización compuesta: calcular el capital final.

en por Donnie Rocken Informática, Matemáticas-Ciencias, SQLDeja un comentario

Ayer veíamos cómo calcular el capital final y los intereses sobre un importe aplicando la formula de la capitalización simple en una consulta de SQL-Server. Hoy vamos a ver cómo calcular el capital final, pero con la fórmula de la capitalización compuesta:

La capitalización compuesta es una fórmula financiera que permite calcular el equivalente de un capital en un momento posterior.

La diferencia entre la capitalización simple y la compuesta radica en que en la simple sólo genera intereses el capital inicial, mientras que en la compuesta se considera que los intereses que va generando el capital inicial, ellos mismos van generando nuevos intereses.

La fórmula para obtener el capital final sería Cf = C0 * (( 1 + i) ^ t) siendo C0 el capital inicial, i el tipo de interés y t el tiempo. Ahora supongamo, como en el ejemplo de la capitalización simple, que tenemos una tabla llamada Depositos con una columna Capital donde almacenamos el dato del capital inicial, que para el ejemplo serán 2000 euros (C0=2000), una columna Interes con el tipo de interés, que para el ejmplo será del 5% anual (i=0.05) y una columna Tiempo que será de 10 años.

Entonces ¿cómo nos quedaría la consulta?

Select CONVERT(float,Importe)*POWER(CONVERT(float,(1.00+Interes)),Tiempo) from Depositos

¿Por qué metí ahí las funciones CONVERT() para asegurarme de que los valores con los que se operan son float? Bueno, en el caso del importe porque ya sabéis, como comentamos anteriormente, que SQL-Server 2008 (también os comenté que estoy estudiando las versiones posteriores) convierte a enteros los resultados de las operaciones si operamos con algún entero en las mismas. En el caso del segundo es por el funcionamiento de la función POWER(). Dicha función recibe dos parámetros y devuelve el valor del primer parámetro elevado al valor del segundo parámetro, pero el tipo de dato devuelto es siempre el mismo tipo que el enviado en el primer parámetro. Si, por ejemplo, recibe un valor Numeric(4,2) redondearía el resultado a dos decimales y nos daría un resultado bastante impreciso por ello.

SQL-Server y Capitalización simple:calcular los intereses y el capital final

en por Donnie Rocken Informática, Matemáticas-Ciencias, SQLDeja un comentario

La capitalización simple es una fórmula financiera que permite calcular el equivalente de un capital en un momento posterior. Se utiliza generalmente en depósitos o préstamos a corto plazo.

Partiendo de un capital (aquí será C0) del que se dispone inicialmente, se determinará la cuantía final que se recuperará en el futuro sabidas las condiciones en las que la operación se contrata (tiempo –t– y tipo de interés –i-).

La fórmula para calcular los intereses sería la siguiente: I = C0 * i * t. Es decir, los intereses son el Capital multiplicado por el tipo de interés y el tiempo.

Ahora supongamos que tenemos una tabla Cuentas donde guardamos el capital inicial y las condiciones. Supongamos que tenemos un capital inicial de 1000 euros (C0=1000) guardado en el campo Capital, a un interés del 10% anual (i=0.1) almacenadado en el campo Interes y por un año (t=1) almacenado en el campo Tiempo:

Select Capital*Interes*Tiempo as InteresAnual from Cuentas

El resultado de la consulta de arriba debería ser de 100 euros.

Ahora, usando los mismos datos del ejemplo de arriba ¿Cómo sabríamos cual es el capital final? Una opción es calcular el interés y sumarlo al capital inicial, otro es aplicar la fórmula CapitalFinal = C0 * (1 + (i * t)). La consulta sería así:

#Así multiplicando
Select Capital*(1.00+(Interes*Tiempo)) as CapitalFinal from Cuentas

Recuerda que SQL-Server, al menos en la versión 2008 (estoy actualizándome ahora a 2017, con mucho por leer), convierte siempre a entero si utilizas enteros en la operación, por eso que puse el uno seguido de decimales en la segunda consulta. Recuérdalo también a la hora de almacenar los datos y de operar con ellos.

Otro punto importante: el tipo de interés y el plazo deben referirse a la misma medida temporal. Es decir: si el tipo es anual el plazo debe de ir en años, pero si el tipo es mensual el plazo irá en meses. Por ejemplo, ¿con los datos de antes cómo se calcularía el interés generado en cinco meses?

Select Capital*(Interes/12.00)*5.00 as InteresMeses from Cuentas

Dividimos el interés anual entre 12 para obtener el interés mensual y multiplicamos por 5 para obtener el resultado deseado.

Instalar Microsoft ODBC 17 para SQL-Server en Ubuntu 17.10

en por Donnie Rocken Informática, sistemas y redes, Software, SQLDeja un comentario

Una entrada rápida ¿Cómo se instala el driver ODBC 17 para SQL-Server de Microsoft en un equipo con Ubuntu? No es algo muy habitual usar SQL-Server en Linux, pero por si os interes la cosa va así:

sudo su 
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#PARA OTRA VERSIÓN
#CAMBIA EL 17.10 DE LA URL
#POR LO QUE CORRESPONDA
curl https://packages.microsoft.com/config/ubuntu/17.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql
# LO SIGUIENTE ES PARA LOS COMANDOS bcp Y sqlcmd
# ES OPCIONAL PERO MUY RECOMENDABLE
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# PARA LOS ENCABEZADOS unixODBC
# ES OPCIONAL, PERO DE NUEVO RECOMENDABLE
sudo apt-get install unixodbc-dev

SQL-Server. Vistas con índices ¿Cuándo no son recomendables?

en por Donnie Rocken Informática, SQLDeja un comentario

Añadir un índice a las vistas en SQL-Server puede ser una buena idea para mejorar la velocidad de lectura en consultas sobre la misma, pero en determinados casos puede no ser recomendable ya que el mantenimiento de estos índices penaliza la velocidad en la inserción, borrado y actualización de datos. Me ha pasado que tras crear una vista con varios índices el funcionamiento de la aplicación empeoraba: ganaba dos segundos en la carga de datos pero perdía casi diez en la inserción.

¿En qué casos NO es recomendable crear índices sobre vistas?

  • Sistemas OLTP con muchas operaciones de escritura.
  • Bases de datos con múltiples actualizaciones.
  • Consultas que no requieran de joins o de funciones de agregado.
  • Consultas con agregación de datos con un alto grado de cardinalidad. (Es decir, que el conjunto de datos que devuelve la vista tenga prácticamente el mismo número de líneas que la tabla indizada)
  • Vistas que tienen conjuntos de resultados que son más grandes que los datos originales en las tablas base.

Aquí os dejo un artículo muy interesante, en inglés, sobre el tema. Ya tiene unos años pero los consejos son igualmente válidos.

SQL-Server: Anti-Join con NOT EXISTS()

en por Donnie Rocken Informática, SQLDeja un comentario

Habíamos visto dos técnicas para hacer un anti-join en SQL-Server sin recurrir al NOT IN(): la primera con un left join y comprobando qué campos vienen a nulo y la segunda con el operado EXCEPT. ¿Hay una tercera opción? Sí, la hay: NOT EXISTS(). Como recordaréis ya habíamos visto por aquí que EXISTS() es una función de SLQ-Server muy bien optimizada, y en este caso podemos usarla también.

Vamos a poner un ejemplo: Tenemos la tabla Oficinas y tenemos la tabla Empleados. Las oficinas tienen un campo IdOficina que las identifica, los empleados también tiene un campo IdOficina que nos dicen en qué oficina trabajan. Queremos sacar en una consulta qué oficinas no tienen empleado. Veamos la consulta:

Select 
  o.IdOficina
from 
  Oficinas o
where 
  NOT EXISTS 
    (Select * from Empleados e where e.IdOficina = o.IdOficina)

¿Cual de los tres métodos es mejor? La recomendación es que le pegues un ojo al plan de ejecución para que veas cual tiene un menor coste.

SQL-Server: Hacer un anti-join con EXCEPT

en por Donnie Rocken Informática, SQL, UncategorizedDeja un comentario

El otro día os comentaba cómo hacer un anti-join en SQL-Server (a decir verdad con un método que valdría para cualquier SGBD), pero me dejé fuera otra forma de hacerlo en SQL-Server: con el operador EXCEPT.

La definción de EXCEPT vendría a ser la siguiente: Devuelve los valores distintos de la consulta situada a la izquierda del operando EXCEPT que no se devuelven desde la consulta derecha.

Vamos con un ejemplo: Tenemos la tabla Productos y tenemos la tabla Ventas, y el campo IdProducto existe en ambas, siendo la clave primaria de la tabla Productos y figurando como clave externa en tabla Ventas. Entonces ¿Podemos con EXCEPT sacar los Id de todos los Productos que no existen en la tabla Ventas? Podemos. Tal que así:

Select IdProducto from Productos
EXCEPT
Select IdProducto from Ventas

Al igual que ocurren con el operador UNION, el operador EXCEPT nos obliga a tener las mismas columnas, con tipos de datos compatibles, en cada una de las consultas.

SQL: cómo hacer un ANTI JOIN

en por Donnie Rocken Informática, SQL1 comentario

Te habrá pasado más de una vez: necesitas sacar todos los valores de la Tabla1 que no aparezcan en la Tabla2. Es decir, la operación opuesta a un INNER JOIN. En muchos casos vemos que hay quien usa un NOT IN() como solución:

Nota: Asumimos que las tablas están bien formadas y que la Tabla1 se relaciona con la Tabla2 por medio de una clave externa (en el ejemplo será el campo IdClaveExterna en Tabla2 que está relacionado con el campo id de Tabla1)

SELECT * FROM Tabla1 
WHERE Tabla1.id NOT IN (SELECT IdClaveExterna FROM Tabla2)

Y esto está mal. Muy mal. Porque el IN() tiene un rendimiento muy bajo, como hemos visto en el pasado, y su negación pues todavía ofrece uno peor. Entonces ¿cómo lo hacemos? No tenemos una unión que sea ANTI JOIN, pero lo podemos resolver de la siguiente forma ya que tenemos un campo Id en cada tabla para cada fila sirviendo como clave primaria:

SELECT * FROM Tabla1 
LEFT JOIN Tabla2 ON Tabla1.id = Tabla2.idClaveExterna
WHERE Tabla2.id IS NULL

De esta forma cogemos todos los valores de la Tabla1 unidos a la Tabla2 por la clave externa, pero descartando de la consulta aquellos valores que nos devuelvan nulo el valor de la clave primaria de la Tabla2.

SQL-Server: Select * vs Select 1 en una cláusula EXISTS()

en por Donnie Rocken Informática, SQLDeja un comentario

Una duda que me asaltó hoy ¿Es más eficiente usar Select * o usar Select 1 en una subconsulta de una cláusula EXISTS?

En principio podríamos pensar que Select 1, al no tener que devolver una lista de columnas,sería mucho más eficiente. Pero si analizamos el plan de ejecución de la consulta vemos que no, que el rendimiento es similar. ¿Por qué?

La propia Microsoft lo explica en esta entrada de sus manuales: SQL-Server no produce ningún dato cuando una consulta está dentro de EXISTS, simplemente evalúa el WHERE de dicha consulta para comprobar si devolvería algo y, según esto se cumpla, devuelve TRUE o FALSE. Por tanto, usar Select * en este caso no empeoraría el rendimiento de la consulta.