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

SQL-Server: Reiniciar el valor de una columna de identidad

Imaginemos que hemos borrado todos los registros de una tabla, en SQL-Server, que tenía definida una columna de identidad como clave primaria. Ahora queremos que las nuevas inserciones no comiencen desde el último Id borrado sino desde el principio ¿Cómo lo hacemos? Es muy simple:

DBCC CHECKIDENT ('NuestraTabla', RESEED, 1);

Vale, pero ¿y si no hemos borrado todos los valores sino, por ejemplo, solo un 20% de ellos? ¿Cómo hacemos para que empiece desde el valor máximo? Pues con este método lo haríamos:

DBCC CHECKIDENT ('NuestraTabla', RESEED, 1);
DBCC CHECKIDENT ('NuestraTabla', RESEED);

De esta forma le ponemos el valor a 1 y luego el segundo comando pondrá automáticamente el valor máximo de la tabla.

¿Y si hemos metido manualmente un valor en la Id, desactivando el chequeo de identidad para la inserción, mayor que el que tocaba y queremos que siga desde ahí?

DBCC CHECKIDENT ('NuestraTabla', RESEED);

Pues de nuevo invocamos la función sin ningún valor definido.

SQL-Server: Usar Try…Catch en una transacción.

Desde SQL-Server 2008 es posible utilizar la instrucción Try…Catch. Estas instrucciones nos permiten implementar un mecanismo de control de errores: metemos un bloque de código dentro de la instrucción Try, para intentar ejecutarlo, y dentro de Catch metemos las instrucciones para controlar la respuesta en caso de error.

La sintaxis básica de un Try…Catch en SQL-Server es la siguiente:

BEGIN TRY  
     ---Bloque de código 
END TRY  
BEGIN CATCH  
     --Código en caso de error. 
END CATCH  

Cuando realizamos una transacción podemos exprimir al 100% la instrucción Try…Catch. La idea es la siguiente: Comenzamos la transacción, intentamos una acción en un bloque Try. En caso de que falle mostramos el error y ejecutamos un rollback para anular la transacción. En caso de éxito confirmamos la ejecución de la transacción.

Veamos un código de ejemplo:

BEGIN TRANSACTION;  --Comienza

BEGIN TRY  --Aquí empieza el try
    Insert into Ejemplo(Id,Nombre) values(22,'Manuel');
    Delete From Espera Where Nombre = 'Manuel' 
--Intentaremos esas dos acciones
END TRY  
BEGIN CATCH  
--El primer paso en el Catch
--Será recoger y mostrar
--Todos los errores
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
--Si hay transacción abierta
--Hacemos un rollback sobre ella
--Para anularla
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  
--Fuera del bloque vamos a
--comprobar que haya transacción abierta.
--Si la hay es que no tuvo que ir por el CATCH
--Por tanto confirmamos.
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO  

SQL-Server: Intercambiar valores 0 y 1 en un Update

Vamos con una cuestión que seguro que nos hemos encontrado más de una vez: Tenemos un campo en una base de datos donde almacenamos un valor verdadero o falso y tenemos que realizar un Update para cambiar dicho estado. ¿Cómo hacemos para modificarlo con el menor costo posible? Te doy un par de soluciones, todas válidas para SQL-Server desde la versión 2008 hasta la 2017 (y supongo que seguirán valiendo en las posteriores):

Para la primera vamos a usar la operación del OR exclusivo bit a bit:

Update TablaEjemplo SET ValorVF = Convert(Bit,ValorVF ^ 1)

Otra opción lógica es usar el operador lógico de negación para invertir el valor:

Update TablaEjemplo SET ValorVF = Convert(Bit,~ ValorVF)

¿Y qué pasa si la tabla no está bien diseñada y en lugar de almacenar un valor bit estamos almacenando un entero? Si no podemos cambiar el diseño de la tabla todavía podemos recurrir a la primera solución, el OR exclusivo bit a bit, pero sin realizar la conversión a tipo bit:

Update TablaEjemplo SET ValorVF = ValorVF ^ 1

Y además tenemos otra opción, con una simple resta y con la función ABS(), que nos devuelve el valor absoluto del parámetro que recibe:

Update TablaEjemplo SET ValorVF = ABS(ValorVF - 1)

Evitar el mensaje de error “No está permitido guardar cambios” cuando intenta modificar una tabla en SQL Server

A veces al realizar ciertas modificaciones sobre una tabla, como cambiar el tipo de datos o la precisión de una columna, el orden de la mismas o al agregar una nueva recibimos un mensaje de error de SQL-Server que nos dice que “No está permitido guardar cambios porque no se permiten cambios que obliguen a crear tablas de nuevo“. ¿Hay alguna forma de modificar esto y permitir el cambio? Sí, hay dos opciones.

La primera es no utilizar la interfaz gráfica para hacer la modificación sino usar instrucciones SQL. Si por ejemplo queremos cambiar la precisión y permitir valores nulos en una columna de la tabla podríamos hacerlo así:

alter table Clientes alter column ObservacionesCliente nvarchar(500) NULL

Otra solución es desactivar la opción de Impedir guardar cambios que requieren volver a crear tablas en el entorno gráfico. En el menú Herramientas, hacemos click en Opciones y, dentro de ese menú, vamos al submenú Diseñadores. Allí desactivamos la opción Impedir guardar cambios que requieren volver a crear tablas y, a continuación, hacemos click en Aceptar para guardar los datos. Os dejo una captura de dicho menú:

SQL-Server Opciones Diseñadores

SQL-Server: Calcular el descuento compuesto

Tras un mes de parón por temas varios volvemos con energía al blog. Vamos a dar por finalizado el capítulo dedicado a los descuentos y la aplicación de sus fórmulas en consultas de SQL-Server mirando el que nos queda, el descuento compuesto. Del descuento racional y del comercial ya hablamos en entradas pasadas.

El descuento compuesto es la operación inversa de la capitalización compuesta: si descontamos un capital utilizando el descuento compuesto, y el importe obtenido lo capitalizamos (capitalización compuesta) aplicando el mismo tipo de interés y plazo, obtenemos el importe inicial.

La fórmula para obtener el descuento sería la siguiente:

D = C0 * (1 – (1 + d) ^ -t )

Siendo C0 es el capital inicial en el momento t=0, d la tasa de descuento que se aplicará y t el tiempo que dura la inversión.

Para obtener el capital final la fórmula sería:

Cf = C0 * ( 1 + d ) ^ -t

Siendo C0 es el capital inicial en el momento t=0, d la tasa de descuento que se aplicará y t el tiempo que dura la inversión.

Entonces ¿cómo llevamos esto a una consulta SQL? Supongamos que, como en el ejemplo anterior, tenemos una tabla llamada TablaCreditos con los campos Valor (con el capital inicial en formato money), Descuento (en formato Numeric(5,2)) y Tiempo (en formato int). La consulta sería:

--Descuento
Select Valor * (1.00-POWER((1.00+Descuento),Tiempo)) as DescuentoCompuesto from TablaCreditos
--Capital final
Select Valor * POWER((1.00+Descuento),Tiempo) as CapitalFinal from TablaCreditos

SQL-Server: Calcular el descuento racional

Siguiendo con la matemática financiera el otro día veíamos cómo calcular el descuento comercial en una consulta de SQL. Ahora vamos con otro tipo de descuento, el descuento racional.

El descuento racional se trata, al igual que el comercial, de una forma de descuento simple, pero tiene principalmente una diferencia con el descuento comercial: Su cálculo se efectúa a partir de la diferencia entre el monto a pagar o valor nominal y su valor actual, por lo que no se toma el valor nominal sino el valor real.Es decir, el descuento racional será igual a la cantidad a pagar menos el valor actual del capital. Es el descuento de su respectivo interés aplicado al valor nominal de un valor, calculado a partir de la tasa de interés nominal vencida o con la tasa de interés efectiva vencida.

La fórmula para obtener el descuento sería la siguiente:

D = ( C0 * d * t ) / (1 + d * t)

Siendo C0 es el capital inicial en el momento t=0, d la tasa de descuento que se aplicará y t el tiempo que dura la inversión. Por ejemplo, para 20000 euros a un 15% anual la fórmula sería:

D=(20000*0.15*1)/(1+0.15*1)

Entonces ¿cómo llevamos esto a una consulta SQL? Supongamos que, como en el ejemplo anterior, tenemos una tabla llamada TablaCreditos con los campos Valor (con el capital inicial en formato money), Descuento (en formato Numeric(5,2)) y Tiempo (en formato int). La consulta sería:

Select (Valor*(Descuento/100.00)*CONVERT(numeric(4,2),Tiempo))/(1.00+(Descuento/100.00)*CONVERT(numeric(4,2),Tiempo)) as DescuentoRacional from TablaCreditos