Restando fechas que se encuentran en distintas filas (2)

Deja un comentario

Ya habíamos visto como conseguir restar fechas que se encuentran en distintas filas:

Restando fechas que se encuentran en distintas filas

Ahora en este artículo veremos otro método para conseguir el mismo resultado. Para ello usaremos tablas autoreferenciadas.

Una tabla autoreferenciada es una tabla que tiene un JOIN a sí misma, y es algo que puede ser muy útil a veces.

Listado 1.


CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   T2.PRU_FECFIN,
   T2.PRU_FECFIN - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1
LEFT JOIN
   PRUEBA1 T2
      ON T1.PRU_NUMREG = T2.PRU_NUMREG - 1

Como puedes ver en el Listado 1., la primera parte es igual a la que habíamos escrito en el otro artículo, lo único que cambia es el SELECT.

Usamos un LEFT JOIN para que siempre se muestre la Fecha Inicial, haya o no haya una Fecha Final correspondiente. Podrías usar solamente JOIN si únicamente te interesan las filas que tienen una Fecha Inicial y su correspondiente Fecha Final.

Nuestra tabla PRUEBA1 tiene estas filas:

FECHA02

Captura 1.

Y después de ejecutar el Listado 1. obtendremos lo que vemos en la Captura 2.

FECHA01

Captura 2.

Artículos relacionados:

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Autoreferenciar una tabla. Algunos ejemplos

Entendiendo las tablas autoreferenciadas

Usando una tabla autoreferenciada para averiguar números consecutivos faltantes

Restando fechas que se encuentran en distintas filas

El índice del blog Firebird21

El foro del blog Firebird21

Usando una tabla autoreferenciada para averiguar números consecutivos faltantes

Deja un comentario

Hay varios métodos para conocer si una serie de números está en secuencia. Es decir si están como 1, 2, 3, 4, 5, 6, 7, 8, etc. o si falta algún número.

Uno de esos métodos es mediante el uso de una tabla autoreferenciada, como en el siguiente ejemplo:

SELECT
   P1.PRD_IDENTI
FROM
   PRODUCTOS P1
LEFT JOIN
   PRODUCTOS P2
      ON P1.PRD_IDENTI = P2.PRD_IDENTI - 1
WHERE
   P2.PRD_IDENTI IS NULL

Como puedes ver, la tabla PRODUCTOS le hace un LEFT JOIN a la tabla … PRODUCTOS. Por eso se dice que la tabla se referencia a sí misma.

Entonces, para saber si los valores de la columna PRD_IDENTI son consecutivos o no, la referencia se hace al valor de PRD_IDENTI menos uno.

Si hay números faltantes entonces la condición no se cumplirá. ¿Y cómo podemos saber que la condición no se cumplió? porque en ese caso el valor de PRD_IDENTI de la tabla relacionada será NULL.

Si nuestra tabla de PRODUCTOS tiene estos datos:

AUTOREF1

Captura 1. Si haces clic en la imagen la verás más grande

Entonces el SELECT anterior nos mostrará esto:

AUTOREF2

Captura 2. Si haces clic en la imagen la verás más grande

 ¿Qué significa? Que después del 25 no está el 26; ni después del 35 está el 36. ¿Y por qué aparece el 38? Porque es el valor que tiene PRD_IDENTI en la última fila y evidentemente como está en la última fila no habrá un valor mayor que él. Entonces siempre el valor que se encuentra en la última fila aparecerá, ese es un pequeño detalle a tener en cuenta.

Desventajas de este método

Este método funcionará siempre pero tiene una gran desventaja: está usando un LEFT JOIN y eso hará que la cantidad de filas leídas sea muy grande (no olvides que un JOIN es la multiplicación de las filas de la tabla de la izquierda por las filas de la tabla de la derecha). En tablas grandes este método puede ser increíblemente lento, pero para tablas pequeñas puede ser útil.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Entendiendo las tablas autoreferenciadas

1 comentario

Como ya hemos visto en estos dos artículos:

https://firebird21.wordpress.com/2013/05/06/ejemplo-no-004-usando-un-inner-join-para-autoreferenciar-una-tabla/

https://firebird21.wordpress.com/2013/07/19/autorreferenciar-una-tabla-algunos-ejemplos/

podemos escribir una consulta en la cual una tabla se referencia a sí misma. Explayémonos un poco más sobre este tema.

Evidentemente para que referenciemos una tabla con sí misma en ella deben encontrarse los datos que necesitamos. Aquí tenemos dos posibilidades:

  1. Que la referencia se haga a dos columnas de la tabla
  2. Que la referencia se haga a una sola columna de la tabla

Quizás habrás notado que en el primero de los dos artículos superiores se usaron dos columnas y en el segundo artículo solamente se usó una columna.

En general, cuando se trata de una tabla maestra (es decir: una tabla que no depende de otras tablas) usamos la primera opción. Y cuando se trata de una tabla de movimientos (es decir: una tabla que depende de otras tablas) usamos la segunda opción. Pero esa no es una regla inmutable, es lo más común pero no está grabado en piedra.

Ejemplo 1:

Necesitamos registrar los datos de algunos animales (por ejemplo: perros) y entre otras cosas queremos saber cual es el padre de cada perro, cual es el abuelo, etc. (lo que en castellano se llama pedigrí)

Para ello podríamos tener una tabla para los hijos, otra para los padres, otra para los abuelos, etc. Pero sería tedioso trabajar así porque tendríamos muchos datos duplicados (un mismo perro podría encontrarse en dos o en tres tablas). Una manera mucho más racional sería tener una sola tabla e identificar en ella al padre de cada perro.

AUTO6

(si haces clic en la imagen la verás más grande)

Y los datos de esa tabla podrían ser algo así:

AUTO7

(si haces clic en la imagen la verás más grande)

Entonces, para ver los nombres de cada perro y los nombres de sus respectivos padres podríamos escribir:

SELECT
   H.ANI_IDENTI AS "Id. perro",
   H.ANI_NOMBRE AS "Nombre del perro",
   H.ANI_FECNAC AS "Fecha nacimiento",
   P.ANI_IDENTI AS "Id. padre",
   P.ANI_NOMBRE AS "Nombre del padre"
FROM
   ANIMALES H
LEFT JOIN
   ANIMALES P
      ON H.ANI_IDEPAD = P.ANI_IDENTI

Como ves, la tabla ANIMALES tiene un JOIN a la tabla ANIMALES, o sea que se referencia a sí misma. Al ejecutar ese SELECT obtendríamos este resultado:

AUTO8

(si haces clic en la imagen la verás más grande)

Donde podemos ver el nombre del padre de cada perro. En algunos casos, también podríamos obtener el nombre del abuelo (INDIO es hijo de AFRO quien a su vez es hijo de SATAN).

SELECT
   H.ANI_IDENTI AS "Id. perro",
   H.ANI_NOMBRE AS "Nombre del perro",
   H.ANI_FECNAC AS "Fecha nacimiento",
   P.ANI_IDENTI AS "Id. padre",
   P.ANI_NOMBRE AS "Nombre del padre",
   A.ANI_IDENTI AS "Id. abuelo",
   A.ANI_NOMBRE AS "Nombre del abuelo"
FROM
   ANIMALES H
LEFT JOIN
   ANIMALES P
      ON H.ANI_IDEPAD = P.ANI_IDENTI
LEFT JOIN
   ANIMALES A
      ON P.ANI_IDEPAD = A.ANI_IDENTI

Si ejecutamos este SELECT esto será lo que obtendremos:

AUTO9

(si haces clic en la imagen la verás más grande)

 El cual claramente nos indica que el padre de INDIO es AFRO y que su abuelo es SATAN. De similar manera podríamos obtener los nombres de los perros que no tienen padre (bueno, en la vida real sí lo tienen pero no los tenemos registrados quizás porque no sabemos sus nombres), de los perros que no tienen abuelos, de los perros que son hermanos entre sí, de los hijos de un perro, de los nietos de un perro, etc.

En este ejemplo hemos usado dos columnas de la tabla ANIMALES (ANI_IDENTI y ANI_IDEPAD) para hacer la referencia.

Ejemplo 2:

Queremos conocer los nombres de los alumnos que se han aplazado en Matemática y también se han aplazado en Historia. Las calificaciones van del 0 al 100 y quienes obtienen menos de 40 puntos se aplazan.

La estructura de nuestra tabla de CALIFICACIONES es la siguiente:

AUTO10

(si haces clic en la imagen la verás más grande)

Y estas son algunas de sus filas:

AUTO11

(si haces clic en la imagen la verás más grande)

 Entonces, para ver los nombres de los alumnos que se aplazaron en Matemática y también en Historia podríamos escribir:

SELECT
   C.CAL_IDEALU AS "Id. alumno",
   A.ALU_NOMBRE AS "Nombre del alumno",
   C.CAL_NOTAXX AS "Matemática",
   D.CAL_NOTAXX AS "Historia"
FROM
   CALIFICACIONES C
JOIN
   CALIFICACIONES D
      ON C.CAL_IDEALU = D.CAL_IDEALU
JOIN
   ALUMNOS A
      ON C.CAL_IDEALU = A.ALU_IDENTI
WHERE
   C.CAL_NOTAXX < 40 AND
   D.CAL_NOTAXX < 40 AND
   C.CAL_CODMAT = 'MAT' AND
   D.CAL_CODMAT = 'HIS'

Con lo cual obtendríamos:

AUTO12

(si haces clic en la imagen la verás más grande)

 Y así sabríamos que RAQUEL se ha aplazado en ambas materias.

En este ejemplo hemos usado solamente una columna de la tabla CALIFICACIONES (la columna: CAL_IDEALU) para hacer la referencia pero hemos necesitado además de un WHERE para establecer la condición.

Conclusión:

Hay muchos casos en los cuales lo más eficiente que podemos tener es una tabla que se referencia a sí misma. Siempre que podamos meter toda la información que necesitamos en una sola tabla es una posibilidad a considerar, pues evitamos la redundancia y en general las consultas son más rápidas.

Artículos relacionados:

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Autorreferenciar una tabla. Algunos ejemplos

El índice del blog Firebird21

Autorreferenciar una tabla. Algunos ejemplos.

4 comentarios

Hay ocasiones en las cuales la más eficiente forma de obtener el resultado de una consulta es a través de una tabla que se referencia a sí misma.

En el artículo:

https://firebird21.wordpress.com/2013/05/06/ejemplo-no-004-usando-un-inner-join-para-autoreferenciar-una-tabla/

ya hemos visto como lograrlo, pero ese no es el único caso.

Por ejemplo, tenemos una tabla llamada VENTASCAB (cabecera de las ventas) algunas de cuyas filas son las siguientes:

AUTO1

(si haces clic en la imagen la verás más grande)

Y lo que nos interesa ver son las ventas a los clientes a quienes les vendimos el día 21/04/2012 y también el día 22/04/2012, o sea los clientes a quienes les hemos vendido en ambos días.

Este SELECT no funcionará:

SELECT
   VTC_IDENTI,
   VTC_TIPDOC,
   VTC_NRODOC,
   VTC_FECHAX,
   VTC_IDECLI
FROM
   VENTASCAB
WHERE
   VTC_FECHAX = '21/APR/2012' AND
   VTC_FECHAX = '22/APR/2012'

Si lo ejecutamos esto será el resultado que obtendremos:

AUTO2

(si haces clic en la imagen la verás más grande)

 Correctamente nos dice que no hay datos para mostrar. ¿Por qué? porque en ninguna fila VTC_FECHAX es igual al 21 de abril y también igual al 22 de abril. En una fila VTC_FECHAX puede ser igual a una de esas fechas, pero no a ambas.

Probemos entonces de otra forma:

Este SELECT tampoco funcionará:

SELECT
   VTC_IDENTI,
   VTC_TIPDOC,
   VTC_NRODOC,
   VTC_FECHAX,
   VTC_IDECLI
FROM
   VENTASCAB
WHERE
   VTC_FECHAX = '21/APR/2012' OR
   VTC_FECHAX = '22/APR/2012'

Al ejecutarlo este es el resultado que obtenemos:

AUTO3

(si haces clic en la imagen la verás más grande)

 Que tampoco es lo que queremos porque nos muestra los clientes a quienes le hemos vendido o el 21 de abril o el 22 de abril o ambos días. Pero a nosotros no nos interesan los clientes a quienes solamente les hemos vendido el 21 de abril ni nos interesan los clientes a quienes solamente les hemos vendido el 22 de abril. Los que nos interesan son los clientes a quienes les hemos vendido en ambos días y el SELECT anterior nos muestra a más clientes de los que necesitamos.

Entonces ¿cómo resolvemos este problema?

Mediante una autorreferencia a la tabla VENTASCAB, como se ve en el siguiente listado:

SELECT
   DISTINCT
   V1.VTC_IDENTI,
   V1.VTC_TIPDOC,
   V1.VTC_NRODOC,
   V1.VTC_FECHAX,
   V1.VTC_IDECLI
FROM
   VENTASCAB V1
JOIN
   VENTASCAB V2
      ON V1.VTC_CODSUC = V2.VTC_CODSUC AND
         V1.VTC_IDECLI = V2.VTC_IDECLI
WHERE
   ((V1.VTC_FECHAX = '21/APR/2012' AND V2.VTC_FECHAX = '22/APR/2012') OR
   (V1.VTC_FECHAX = '22/APR/2012' AND V2.VTC_FECHAX = '21/APR/2012'))

Al ejecutar este SELECT esto será lo que obtendremos:

AUTO5

(si haces clic en la imagen la verás más grande)

 Que es justamente lo que estábamos necesitando. Aquí podemos ver que al cliente con Identificador 348 le hemos hecho cuatro ventas el día 21 de abril y una venta el día 22 de abril. Y es el único cliente al cual le hemos vendido en ambos días.

¿Cuándo debemos autorreferenciar una tabla?

Cuando la respuesta está en ella y no se soluciona ni con AND ni con OR.

Artículos relacionados:

Usando un INNER JOIN para autoreferenciar una tabla

El índice del blog Firebird21

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

3 comentarios

Una tabla (o una vista) puede referenciarse a sí misma. Para que esto sea posible debe tener 2 columnas (o más de 2 columnas) que tengan una relación entre sí.

Por ejemplo, tenemos una tabla llamada CUENTAS que mantiene los datos de las cuentas contables con esta estructura:

CUENTAS1

(haciendo click en la imagen la verás más grande)

y cuyas primeras filas son estas:

CUENTAS2

(haciendo click en la imagen la verás más grande)

En este caso en la columna CUE_NIVSUP (en la imagen se la llamó “Cuenta superior”) se coloca el número de la cuenta padre de cada cuenta. La cuenta ACTIVO no tiene cuenta superior porque no depende de ninguna otra cuenta. La cuenta ACTIVO CORRIENTE depende de ACTIVO, entonces esa es su cuenta superior. La cuenta DISPONIBILIDADES depende de ACTIVO CORRIENTE, entonces esa es su cuenta superior, y así sucesivamente.

Como se puede ver, el número registrado en la columna CUE_NIVSUP es un número de cuenta, un número que fue previamente registrado en la columna CUE_NUMERO.

Eso implica que entre ambas columnas (CUE_NUMERO y CUE_NIVSUP) hay una relación: todos los números de cuenta que pueden registrarse en la columna CUE_NIVSUP deben existir en la columna CUE_NUMERO.

Supongamos ahora que deseamos ver una consulta similar a la de arriba pero no solamente con los números de las cuentas de nivel superior sino también con sus respectivos nombres. En este caso, estaríamos relacionando a la tabla de CUENTAS con sí misma, porque ambas columnas pertenecen a la misma tabla.

La tabla de CUENTAS tiene un índice según las columnas: CUE_ANOEJE, CUE_CODSUC, CUE_NUMERO, CUE_NUMSUB, entonces esta consulta nos mostrará el resultado deseado.

SELECT
   C.CUE_IDENTI AS "Identi",
   C.CUE_ANOEJE AS "Año",
   C.CUE_CODSUC AS "Suc.",
   C.CUE_NUMERO AS "Número",
   C.CUE_NUMSUB AS "NumSub",
   C.CUE_NOMBRE AS "Nombre de la cuenta",
   C.CUE_NIVSUP AS "Cuenta superior",
   D.CUE_NOMBRE AS "Nombre cuenta superior",
   C.CUE_NIVELX AS "Nivel",
   C.CUE_ASENTA AS "Asentable"
FROM
   CUENTAS C
JOIN
   CUENTAS D
      ON C.CUE_ANOEJE = D.CUE_ANOEJE AND
         C.CUE_CODSUC = D.CUE_CODSUC AND
         C.CUE_NIVSUP = D.CUE_NUMERO AND
         D.CUE_NUMSUB = ''
WHERE
   C.CUE_ANOEJE = 2008

Lo que obtendremos al ejecutar ese SELECT será esto:
CUENTAS3
(haciendo click en la imagen la verás más grande)

que es casi, casi, lo que deseamos. Las que están faltando son las cuentas de nivel 1, o sea todas las cuentas que no tienen una cuenta superior. Eso es muy fácil de solucionar (en lugar de un INNER JOIN escribiríamos un LEFT JOIN y listo).

Lo importante de este ejemplo es ver como una tabla puede relacionarse a sí misma. Para ello la tabla principal (la que se encuentra a continuación de la cláusula FROM) debe ser la misma que la tabla secundaria (la que se encuentra a continuación de la cláusula JOIN) pero para poder diferenciarlas deben tener alias (en este ejemplo se usó el alias “C” para la tabla principal y el alias “D” para la tabla secundaria).