Este artículo ha sido consultado en 4,453 ocasiones.

Tabla de Contenidos

1. Manejo de fechas
2. Próximo día hábil
3. Eliminación de WHERE...IN...
4. Eliminación de consultas anidadas
5. Uso de HAVING en self-joins
6. Búsqueda de la mínima vacante
7. Acerca de una matriz
8. Uso de SET

Varios de los ejemplos aquí presentados usan tablas "abstractas" A y B. Las definiciones son:

create table A (a int)
create table B (a int, b int, s int, foreign key (a) references A(a) )

Manejo de fechas

A menudo queremos usar una fecha independientemente de la hora. Aprendí un truco que en principio resuelve el problema:

select convert(datetime, convert(char, @fecha, 1))

Esto tiene un error y un inconveniente.

El error es que el primer convert (el de más afuera) asume que el formato default de entrada de fecha es mdy. Este formato se puede cambiar con la opción:

set dateformat {mdy | dmy | ymd | ydm | myd | dym}

El formato de conversión de char a datetime usado en el convert tiene que ser el mismo que el formato de conversión de datetime a char; el código correcto es:

select convert(datetime, convert(char, @fecha, 1), 1)

El inconveniente es la ineficiencia y falta de flexibilidad de la solución: Al truncar la parte horaria del datetime, la fecha retornada es la medianoche (es decir, el primer segundo del día) del día correspondiente a la fecha original. Las comparaciones de fechas que requieran incluir el día de la fecha fallan, incluso usando <=. La solución es simple: basta hacer:

select dateadd(day, 1, convert(datetime, convert(char, @fecha, 1), 1))

pero esto agrega a la ineficiencia y se convierte en complicado de entender.

Por otra parte, el manejo de strings es más pesado que el manejo de fechas (que en definitiva son números); la conversión ("parseo") del string a fecha es muy lento.

Hay una solución mejor, basada en el concepto de días transcurridos: la diferencia en días entre la fecha deseada y una fecha fija, no importa si en el pasado o en el futuro.

select datediff(day, 'Jan 1 1980', @fecha)

Esta técnica tiene dos aplicaciones:

• Truncar la parte horaria de la fecha
• Comparar fechas sin tener en cuenta la hora

Para un ejemplo de la segunda aplicación, supongamos que tenemos una tabla de logs con el siguiente diseño:

create table logs ( id int, dt datetime )

Si queremos buscar y contar todos los eventos anteriores a la fecha @fecha agrupadas por día, en vez de hacer:

select   cnt = count(*),
         dat = convert(datetime, convert(char, dt, 1), 1)
from     logs
where    dt <= convert(datetime, convert(char, @fecha, 1), 1)
group by convert(datetime, convert(char, dt, 1), 1)
order by cnt desc

hacer:

select   cnt = count(*),
         dat = dateadd(day, datediff(day, 'Jan 1 1980', dt), 'Jan 1 1980')
from     logs
where    datediff(day, dt, @fecha) >= 0
group by datediff(day, 'Jan 1 1980', dt)
order by cnt desc

El ahorro es de tres converts. El query ejecuta mucho más rápido porque las comparaciones son por entero en vez de por caracter.

Otra ventaja es que jugando con las fechas y/o horas base, podemos hacer que el truncado se haga con respecto a un momento arbitrario en el tiempo. Por ejemplo, para truncar la fecha al mediodía correspondiente, hacer:

select dateadd(day, datediff(day, 'Jan 1 1980', @fecha), 'Jan 1 1980  12:00:00PM')

Para truncar a la medianoche del día de mañana, hacer:

select dateadd(day, datediff(day, 'Jan 1 1980', @fecha), 'Jan 2 1980')

Tiene que quedar claro que las fechas base son completamente arbitrarias, y que, no importa cuál elijamos, o si es una fecha en el pasado o en el futuro, el resultado es siempre consistente.

De vuelta a la tabla de contenidos.

Próximo día hábil

Hay un método trivial de encontrar el próximo día hábil a partir del día de hoy: basta sumarle a la fecha dos días si hoy es sábado, o un día si hoy es domingo. Lo que no es trivial de ver es que hay una aritmetización de este procedimiento que evita el uso de condicionales.

Sea f: D→N la función de días de la semana a números que vale 2 en sábado, 1 en domingo y 0 para el resto de la semana. Entonces,

select @fecha = dateadd(day, f(datepart(weekday, @fecha)), @fecha)

actualiza @fecha para forzarla a caer en día hábil. ¿Cómo encontramos esa función?

Los polinomios son las funciones más simples que podemos usar. Experimentando un poco con una calculadora, encontramos que y = x² / 18 es la función que necesitamos: para 0 ≤ x ≤ 4, es y < 1; para x = 5, es y < 2; y para x = 6, tenemos que y = 2, exactamente. Con unos cuantos masajes, estamos listos.

El primer paso es eliminar la parte fraccionaria de nuestra función: eso se logra fácilmente usando floor(). El segundo, y más importante paso es "correr la semana" de tal forma de que el sábado corresponda a 6, el domingo a 5, el lunes a 4 y así sucesivamente hasta el viernes que vale 0. Sumarizando en una tabla, lo que queremos es lo siguiente:

Nuestra sospecha inicial es que es una transformación módulo 7 del datepart(weekday). Nuestra segunda sospecha es que, como va hacia atrás (es decir, es decreciente con el día), involucra restar el día de la semana de algo. Para que el día 6 (viernes) corresponda a 0, tiene que ser un múltiplo de 7. Ese "algo" del que queremos restar es, entonces, 6 + 7 = 13. La transformación que buscamos es (13 - datepart(weekday, @fecha))%7.

Reemplazando en el argumento, la función completa queda:

select @fecha = dateadd(day,
	floor(power(
		(13 - datepart(weekday, @fecha))%7, 2)/18),
	@fecha)

Podemos sumarizar el funcionamiento de esta función mediante esta tabla:

Con el mismo criterio, la función que devuelve el día de mañana o el próximo día hábil es:

select @fecha = dateadd(day,
	floor(power(
		(12 - datepart(weekday, @fecha))%7, 2)/18) + 1,
	@fecha)

El resultado es el siguiente:

De vuelta a la tabla de contenidos.

Eliminación de WHERE...IN...

Supongamos que tenemos una consulta del tipo "Traer todos los B que tienen estado s igual a 1 ó 3". La idea es usar una función que nos de un valor que podamos distinguir como indicación de pertenencia al conjunto de valores requeridos. En vez de escribir:

select * from B where s in (1, 3)

escribir:

select * from B where (s - 1) * (s - 3) = 0

El truco es escribir un polinomio cuyas raíces sean los valores que queremos seleccionar. Si la columna es de otro tipo T, hay que buscar una función biyectiva de T a Z que nos codifique los valores del tipo T en números. El ejemplo típico es usar ascii() para columnas de tipo char, o datediff() para columnas de tipo datetime.

Si los valores a seleccionar son muchos, en vez de un polinomio puede ser preferible usar una función modular: por ejemplo, si quiero elegir los que tengan estado 1, 3, 5 ó 7, escribir where s % 2 = 1.

Otra opción (equivalente desde el punto de vista matemático) es fijarse si la representación en binario de los valores tiene algún patrón, y usar una expresión usando los operadores de bits.

De vuelta a la tabla de contenidos.

Eliminación de consultas anidadas

Las consultas sobre una tabla que tienen una condición sobre una columna de otra tabla relacionada tienen siempre la misma forma: por ejemplo, buscar todas las operaciones cuyos movimientos fueron devengados antes de una fecha dada. En vez de escribir:

select * from A where (select s from B where B.a = A.a and max(B.s) < N)

dar vuelta el join y escribir:

select A.a from A, B
  where A.a = B.a
  group by B.a
  having A.a = B.a and max(B.s) < N

La idea es fijarse en los grupos en B de acuerdo a la clave foránea, y seleccionar o no un grupo completo de acuerdo a si el máximo valor en s es menor al dado. Notar cómo la condición del join se repite en el having.

Esto es más rápido porque, como mínimo, no crea una tabla de trabajo para guardar el select anidado (que de todas maneras es un join).

C.J.Date tiene una explicación excelente y detallada (¡y crítica!) de esta transformación.

De vuelta a la tabla de contenidos.

Uso de HAVING en self-joins

Esto está explicado en el librito Optimizing Transact-SQL, pero vale la pena comentarlo:

Supongamos que tengo una tabla con una columna numérica, y quiero el máximo que es menor a un número K dado: la solución obvia es:

select max(foo) from bar where foo < K

pero no sirve si quiero traer otras columnas.

Para fijar ideas, supongamos que tengo la tabla Comisiones con campos Nivel y Porcentaje, y la consulta típica es: seleccionar el Porcentaje de Comisiones para el máximo Nivel (notar cómo la condicion establece un piso) menor a un K dado. La segunda solución obvia es:

select Porcentaje from Comisiones
	where Nivel = (select max(Nivel) from Comisiones where Nivel < K)

El truco para eliminar el select anidado es este: el where filtra filas, y el having filtra grupos. Pero no hace falta tener grupos; la tabla completa es, conceptualmente, un grupo. Entonces, esta consulta hace lo mismo:

select Porcentaje from Comisiones
	where Nivel < K
	having Nivel = max(Nivel)

Cuidado, porque si tengo más condiciones, tengo que pensar qué condiciones corresponden a la fila y qué condiciones corresponden al grupo. Por ejemplo, supongamos que la tabla además tiene una columna Plan, que puede ser 1, 2 ó 3. Si quiero seleccionar el Porcentaje de Comisiones para el Plan 3 con Nivel menor a K, la condición Plan < 3 va en el having, y no en el where:

select Porcentaje from Comisiones
	where Nivel < K
	having Plan = 3
	and Nivel = max(Nivel)

Alternativamente, puedo agrupar explícitamente:

select Porcentaje from Comisiones
	where Plan = 3
	  and Nivel < K
	group by Plan
	having Nivel = max(Nivel)

Buscar techos es el problema inverso: es el mínimo Nivel mayor a uno dado.

De nuevo, para un tratamiento completo de esta técnica, no hay mejor expositor (y crítico) que C.J.Date.

De vuelta a la tabla de contenidos.

Búsqueda de la mínima vacante

Problema: Una tabla tiene un campo numérico que tiene valores en secuencia, pero algunos faltan. Quiero una consulta que me de el primer "agujero" o vacante en la columna.

Un primer acercamiento es seleccionar todos los valores que no pertenecen a la tabla cuyos valores tienen su sucesor inmediato en la misma tabla. Por ejemplo, si la tabla tiene los valores 1, 2, 3, 5, y 7, entonces 1 y 2 tienen su sucesor presente en ella, y 3, 5 y 7 no. Entonces, si 3 no tiene sucesor, 3 + 1 = 4 no está. En SQL:

select a+1 from A
	where a not in (select x.a from A x, A y where y.a = x.a+1)

Ahora bien, hay una solución mejor, usando outer joins. La idea es buscar todos los pares de números en la tabla junto con su sucesor, si es que existe. Si no existe, entonces tenemos el valor que buscamos, menos uno:

select isnull(0*y.a,x.a+1) from A x, A y
	where x.a *= y.a-1

Si x.a = y.a-1, y.a es el sucesor no nulo de x.a, por lo tanto 0 * y.a = 0. Si no existe y.a sucesor de x.a, y.a es NULL, 0 * y.a = NULL y por lo tanto el resultado del isnull es x.a+1, es decir, la vacante.

Pero nuestro problema original era encontrar la mínima vacante, el mínimo de entre los sucesores que no existen; y el problema es encontrar alguna manera de filtrar el 0. La función que devuelve H cuando z = 0, y 0 si no, es H*(1 - abs(sign(z))); si sabemos que z es no negativo, la función se simplifica a H*(1 - sign(z)). Si H es lo suficientemente grande, nos aseguramos de que min() elegirá sólo los valores que nos interesan; por ejemplo, podemos usar el máximo número positivo representable en un int. Usando H = 2³¹-1 = 2147483647, nuestra consulta es:

select min(2147483647 * (1 - sign(z)) + z)
	from A x, A y
	where x.a *= y.a-1

donde z = isnull(0*y.a,x.a+1) es la solución que buscamos.

Notar de paso que la suma no genera overflow aritmético.

De vuelta a la tabla de contenidos.

Acerca de una matriz

Supongamos que tenemos dos diferentes tablas y_coord e x_coord que codifican intervalos (por ejemplo, tablas de puntajes, de niveles de comisiones, etc.):

create table y_coord (i int, y float)
create table x_coord (j int, x float)

No interesa en lo más mínimo que y_coord.i (x_coord.j, resp.) sean consecutivos; sólo es necesario que los ordenamientos sean compatibles sobre y_coord.i e y_coord.y (x_coord.j, x_coord.x, resp.), es decir, siempre que y_coord.i0 < y_coord.i1 es y_coord.y0 < y_coord.y1.

Además, y esto es lo importante, para cada par (y_coord.i, x_coord.j) existe en la tabla matrix una fila con un valor asociado z:

create table matrix (i int, j int, z float)

La condición fundamental es que matrix = y_coord x x_coord, porque una matriz es una función que tiene como dominio el producto cartesiano del conjunto de índices de filas por el conjunto de índices de columnas.

Ahora bien, los índices sobre matrix no son índices propiamente dichos, sino intervalos: a la i-ésima fila le corresponde el intervalo [y_coord.yi, y_coord.yi+1) semiabierto a la derecha, similarmente para la j-ésima columna.

El problema: dado un punto P = (x, y), encontrar en matrix el valor de z que corresponde al intervalo [x_j, x_j+1) x [y_i, y_i+1) al que pertenece P.

Primero pensemos qué significa pertenecer a un intervalo: dada una secuencia ordenada S = {a_i}_0<=i<N, N > 1 tal que para todo 0 < i <= j < N es a_i <= a_j, podemos considerarla como una secuencia de intervalos semiabiertos S' = {[a_i, a_i+1)}_0<=i<N-1. Entonces si x pertenece a [a_i, a_i+1) para algún i, a_i es el máximo menor o igual a x, es decir Sup_0<=i<N {a_i | a_i <= x}. De ahí que la consulta obvia sea:

select z from matrix
	where i = (select max(i) from y_coord where y <= Y)
	  and j = (select max(j) from x_coord where x <= X)

Sin embargo, y usando ideas expuestas en la solución de anteriores problemas, hay una forma de hacerlo en Transact-SQL sin hacer uso de consultas anidadas: joins cualificados con having. En vez de buscar el máximo usando un agregado, usamos un having sabiendo que el mínimo grupo es la fila:

select j from x_coord where x <= X having x = max(x)

Con esa idea, y teniendo en cuenta que lo que quiero unir en el join son grupos y no filas, llegamos a la solución eventual:

select z
	from y_coord, x_coord, matrix
	where y <= Y
	  and x <= X

	having y = max(y)
	   and x = max(x)
	   and y_coord.i = matrix.i
	   and x_coord.j = matrix.j

De vuelta a la tabla de contenidos.

Uso de SET

Transact-SQL permite agrupar más de una opción en el comando set, si todas las opciones van a tomar el mismo valor. Esto es muy útil en este caso:

set showplan, noexec on
...
set showplan, noexec off

De hecho, el agrupamiento es la única razón que se me ocurre para justificar que noexec sea una opción "negativa".

De vuelta a la tabla de contenidos.

Copyright © 1997-2000 Matías Giovannini
Last Updated: 2000-05-15

Última actualización: 2007-04-29 10:57:00-05