Java >> Tutorial de Java >  >> Tag >> SQL

APLICACIÓN CRUZADA DE SQL:una guía para principiantes

SQL

Introducción

En este artículo, veremos cómo funciona SQL CROSS APPLY y cómo podemos usarlo para hacer referencias cruzadas de filas de una subconsulta con filas en la tabla externa y crear conjuntos de resultados compuestos.

CROSS APPLY es básicamente equivalente a LATERAL JOIN, y ha sido compatible con SQL Server desde la versión 2005 y Oracle desde la versión 12c.

Tabla de base de datos

Supongamos que tenemos el siguiente blog tabla de la base de datos que almacena los blogs alojados en nuestra plataforma:

Y, actualmente tenemos dos blogs alojados:

| id | created_on | title                | url                      |
|----|------------|----------------------|--------------------------|
| 1  | 2013-09-30 | Vlad Mihalcea's Blog | https://vladmihalcea.com |
| 2  | 2017-01-22 | Hypersistence        | https://hypersistence.io |

Obtener el informe sin usar SQL CROSS APPLY

Queremos generar un informe que extraiga los siguientes datos de nuestro blog tabla:

  • la identificación del blog
  • la edad del blog en años
  • el próximo aniversario del blog
  • la cantidad de días restantes hasta el próximo aniversario.

La edad del blog debe calcularse restando la fecha de creación del blog de la fecha actual.

La fecha del próximo aniversario del blog se puede calcular incrementando la edad en años y agregándola a la fecha de creación del blog.

El número de días hasta el próximo aniversario se puede calcular extrayendo el número de días del intervalo dado por el próximo aniversario del blog y la fecha actual.

Para SQL Server, podemos usar la siguiente consulta para generar el informe requerido:

SELECT
  b.id as blog_id,
  FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457) AS age_in_years,
  DATEADD(
    year,
    ((FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457)) + 1),
    created_on
  ) AS next_anniversary,
  DATEDIFF(
    day,
    GETDATE(),
    DATEADD(
      year, 
      ((FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457)) + 1), 
      created_on
    )
  ) AS days_to_next_anniversary
FROM blog b
ORDER BY blog_id

Y obtendremos el resultado esperado:

| blog_id | age_in_years | next_anniversary | days_to_next_anniversary |
|---------|--------------|------------------|--------------------------|
| 1       | 7            | 2021-09-30       | 23                       |
| 2       | 4            | 2022-01-22       | 137                      |

Sin embargo, como puede ver, el age_in_years tiene que ser calculado tres veces. Y ahí es exactamente donde CROSS APPLY puede ayudarnos.

Obtener el informe utilizando SQL CROSS APPLY

CROSS APPLY nos permite reutilizar el age_in_years valor y simplemente páselo más al calcular el next_anniversary y days_to_next_anniversary valores.

Por ejemplo, la consulta anterior de SQL Server se puede reescribir así:

SELECT
  b.id as blog_id,
  age_in_years,
  DATEADD(
    year, 
	(age_in_years + 1), 
	created_on
  ) AS next_anniversary,
  DATEDIFF(
    day, 
	GETDATE(), 
	DATEADD(year, (age_in_years + 1), created_on)
  ) AS days_to_next_anniversary
FROM blog b
CROSS APPLY (
    SELECT
    FLOOR(DATEDIFF(week, b.created_on, GETDATE()) / 52.177457) AS age_in_years
) AS t
ORDER BY blog_id

Mucho mejor, ¿verdad?

El age_in_years se calcula para cada registro del blog mesa. CROSS APPLY funciona como una subconsulta correlacionada, pero, a diferencia de una subconsulta correlacionada, los registros de la subconsulta también se unen con la tabla principal y, por este motivo, podemos hacer referencia a las columnas producidas por la subconsulta CROSS APPLY.

Conclusión

CROSS APPLY es una característica muy útil cuando se trabaja con SQL Server. Puedes usarlo con OPENJSON para asignar una matriz JSON a una tabla de base de datos relacional que puede transformar aún más utilizando todas las funciones SQL disponibles.

A diferencia de unirse directamente con una tabla derivada, CROSS APPLY se evalúa para cada registro en la tabla principal, y no solo una vez.


Etiqueta Java