JOIN (Análisis de transmisiones de Azure)

Al igual que T-SQL estándar, JOIN en el lenguaje de consulta de Azure Stream Analytics se usa para combinar registros de dos o más orígenes de entrada. JOIN en Azure Stream Analytics son temporales, lo que significa que cada JOIN debe proporcionar algunos límites sobre la distancia en que se pueden separar las filas coincidentes en el tiempo. Por ejemplo, decir "unirse a eventos TollBoothEntry con eventos TollBoothExit cuando se producen en el mismo LicensePlate y TollId y en un plazo de 5 minutos entre sí" es legítimo; pero "unirse a eventos TollBoothEntry con eventos TollBoothExit cuando se producen en licensePlate y TollId" no es , coincidiría con cada TollBoothEntry con una colección sin enlazar y potencialmente infinita de todos los TollBoothExit a la misma LicensePlate y TollId.

Los límites de tiempo de la relación se especifican dentro de la cláusula ON de join, mediante la función DATEDIFF. El tamaño máximo de DATEDIFF es de siete días. Para más información sobre su uso general, consulte DATEDIFF (Azure Stream Analytics) . Cuando DATEDIFF se utiliza en la condición JOIN, el segundo y tercer parámetro obtienen un tratamiento especial.

Además, SELECT * no se puede usar en instrucciones JOIN.

Sintaxis

[ FROM { <input_source> } [ ,...n ] ]  
<input_source> ::=   
{  
    input_name [ [ AS ] input_alias ]   
    | <joined_table>   
}  
  
<joined_table> ::=   
{  
    <input_source> <join_type> <input_source> ON <join_condition>   
    | [ <input_source> <join_type> <reference_data> ON <join_condition> ]  
    | [ ( ] <joined_table> [ ) ]   
}  
<join_type> ::=   
    [ { INNER | LEFT [ OUTER ] } ] JOIN  
  

Argumentos

<input_source>

Especifica el origen de datos de entrada.

<reference_data>

Los datos de referencia al que desea unir mediante JOIN su origen de entrada. Para obtener más información, consulte la sección Combinación de datos de referencia.

<join_type>

Especifica el tipo de operación de combinación.

JOIN

Indica que la operación de combinación especificada debe producirse los orígenes de entrada y/o los datos de referencia especificados. Todas las filas de la izquierda y derecha que cumplan la condición de combinación se incluyen en el conjunto de resultados.

Advertencia

Si los orígenes JOIN están particionados, el predicado JOIN debe incluir una condición que coincida con las claves de partición de ambos orígenes.

[ LEFT OUTER JOIN ]

Especifica que todas las filas de la tabla izquierda que no cumplan la condición de combinación se incluyan en el conjunto de resultados, con las columnas de resultados de la otra tabla establecidas en NULL, además de todas las filas devueltas por la combinación interna.

ON <join_condition>

Especifica la condición en la que se basa la combinación. La condición de combinación debe tener un límite de tiempo o una sala de alternancia temporal definida para la relación y se especifica dentro de la cláusula ON de join, utilizando la sintaxis especial de la función Special DATEDIFF parala función JOIN.

Ejemplos

En Análisis de transmisiones de Azure, todos los eventos tienen una marca de tiempo bien definida. Por lo tanto, el usuario debe usar alias de fila directamente en la función DATEDIFF, como se indica a continuación:

SELECT I1.TollId, I1.EntryTime,I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationInMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15  
  

La condición de combinación anterior provocará una coincidencia si y solo si se produce ExitTime después EntryTime, pero no más de 15 minutos más tarde.

Nota

DATEDIFF usado en la instrucción SELECT usa la sintaxis general en la que se pasa una expresión o columna datetime como segundo y tercer parámetro. Sin embargo, cuando se usa la función DATEDIFF dentro de la condición JOIN, se usa el nombre input_source o su alias. Internamente, se selecciona la marca de tiempo asociada a cada evento en ese origen.

Las condiciones de límite temporal se pueden combinar entre sí y con otras condiciones de la cláusula ON, por ejemplo:

SELECT I1.TollId, I1.EntryTime, I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationinMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON I1.TollId=I2.TollId  
AND I1.LicensePlate=I2.LicensePlate  
AND DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15  
  

Al combinar tres o más tablas, las mismas reglas se aplican --- límites de tiempo deben asegurarse de que todos los eventos coincidentes se produzcan dentro de una cantidad finita de tiempo entre sí. Por ejemplo, para buscar todos los errores que se produjeron entre el evento de inicio y finalización de la transacción, se puede decir:

SELECT TS.Id, TS.Name, TS.Amount, E.ErrorCode, E.Description   
FROM TStart TS TIMESTAMP BY TStartTime   
JOIN TEnd TE TIMESTAMP BY TEndTime  
ON DATEDIFF(second, TS, TE) BETWEEEN 0 AND 5  
AND TS.Id = TE.Id  
JOIN Error E TIMESTAMP BY ErrorTime  
ON DATEDIFF(second, TS, E) BETWEEN 0 AND 5
AND DATEDIFF(second, TE, E) < 0
AND E.TId = TS.Id  
  

Cuando se unen orígenes que tienen particiones, el predicado de JOIN debe incluir una condición que coincida con las claves de partición de ambos orígenes.

SELECT I1.TollId, I1.EntryTime,I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationInMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime PARTITION BY PartitionId  
JOIN Input2 I2 TIMESTAMP BY ExitTime PARTITION BY PartitionId  
ON I1.PartitionId = I2.PartitionId AND DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15  

Por último, Azure Stream Analytics admite la combinación interna (el valor predeterminado) y la combinación externa LEFT. Para una combinación interna, solo se devuelve un resultado cuando se encuentra una coincidencia. Pero, en el caso de una combinación LEFT OUTER , si un evento proveniente del lado izquierdo de la combinación no tiene coincidencia, se devolverá una fila con el valor NULL en todas las columnas de la derecha. Por ejemplo, este es un ejemplo para buscar la ausencia de eventos. La siguiente consulta devolverá las filas donde un vehículo ha entrado en una cabina de peaje pero no ha salido en un período de 15 minutos.

SELECT I1.TollId, I1.EntryTime, I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationinMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
LEFT OUTER JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON I1.TollId=I2.TollId  
AND I1.LicensePlate=I2.LicensePlate  
AND DATEDIFF( minute , I1 , I2 ) BETWEEN 0 AND 15   
WHERE I2.TollId IS NULL  
  

Función DATEDIFF especial para JOIN

Sintaxis

DATEDIFF ( datepart , input_source1, input_source2 )  

Argumentos

dateparts

Ejemplo. 'second', 'milisegundos', 'minute', etc.)

input_source1

El primer origen de entrada en la combinación. Internamente, la marca de tiempo asociada con los eventos de este valor input_source se pasa a la función.

input_source2

El segundo origen de entrada en la combinación. Internamente, la marca de tiempo asociada con los eventos de este valor input_source se pasa a la función.

Tipo de valor devuelto

Devuelve el número de unidades en los partes de fecha que ha transcurrido de la marca de tiempo de input_source1 a la marca de tiempo de input_source2. El valor devuelto puede ser negativo si la marca de tiempo de la segunda input_source es mayor que la primera.