JOIN (Azure Stream Analytics)

Tal como o T-SQL padrão, a função JOIN na linguagem de consulta do Azure Stream Analytics é utilizada para combinar registos de duas ou mais origens de entrada. A associação ao Azure Stream Analytics é de natureza temporal, o que significa que cada JOIN tem de fornecer alguns limites sobre até que ponto as linhas correspondentes podem ser separadas a tempo. Por exemplo, dizer "associar eventos TollBoothEntry com eventos TollBoothExit quando ocorrem no mesmo LicensePlate e TollId e no prazo de 5 minutos entre si" é legítimo; mas "participar em eventos TollBoothEntry com eventos TollBoothExit quando ocorrem no LicensePlate e TollId" não é – corresponderia a cada TollBoothEntry com uma coleção infinita e não vinculada de todas as TollBoothExit ao mesmo LicensePlate e TollId.

Os limites de tempo da relação são especificados dentro da cláusula ON da função JOIN, utilizando a função DATEDIFF. O tamanho máximo de DATEDIFF é de sete dias. Para obter mais informações sobre a utilização geral, veja DATEDIFF (Azure Stream Analytics). Quando DATEDIFF é utilizado dentro da condição JOIN, o segundo e terceiro parâmetros ganham tratamento especial.

Além disso, SELECT * não pode ser utilizado em instruções JOIN.

Sintaxe

[ 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 a origem de dados de entrada.

<reference_data>

Os dados de referência aos quais pretende Associar a sua input_source. Para obter mais informações, veja a secção Associação a Dados de Referência.

<join_type>

Especifica o tipo de operação de associação.

PARTICIPAR

Indica que a operação de associação especificada deve ocorrer entre as origens de entrada especificadas e /ou os dados de referência. Todas as linhas da esquerda e da direita que cumprem a condição de associação estão incluídas no conjunto de resultados.

Aviso

Se as origens JOIN estiverem particionadas, o predicado JOIN tem de incluir uma condição que corresponda às chaves de partição de ambas as origens.

[ ASSOCIAÇÃO EXTERNA À ESQUERDA ]

Especifica que todas as linhas da tabela esquerda que não cumprem a condição de associação estão incluídas no conjunto de resultados e as colunas de saída da outra tabela estão definidas como NULL, além de todas as linhas devolvidas pela associação interna.

ON <join_condition>

Especifica a condição em que a associação se baseia. A condição de associação tem de ter um limite de tempo ou uma sala de manobra temporal definida para a relação e é especificada dentro da cláusula ON da função JOIN, utilizando a sintaxe especial da Função DATEDIFF Especial para afunção JOIN.

Exemplos

No Azure Stream Analytics, todos os eventos têm um carimbo de data/hora bem definido. Assim, o utilizador tem de utilizar aliases de linha diretamente na função DATEDIFF, da seguinte forma:

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  
  

A condição de associação acima resultará numa correspondência se e apenas se o ExitTime ocorrer após o EntryTime, mas não mais de 15 minutos depois.

Nota

DATEDIFF utilizado na instrução SELECT utiliza a sintaxe geral em que uma coluna ou expressão datetime é transmitida como o segundo e terceiro parâmetros. No entanto, quando a função DATEDIFF é utilizada dentro da condição JOIN, é utilizado o nome do input_source ou o respetivo alias. Internamente, o carimbo de data/hora associado a cada evento nessa origem é escolhido.

As condições vinculadas ao tempo podem ser combinadas entre si e com outras condições dentro da cláusula ON, por exemplo:

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  
  

Ao associar três ou mais tabelas, as mesmas regras aplicam-se --- limites de tempo têm de garantir que todos os eventos correspondentes ocorrem dentro de um período de tempo finito entre si. Por exemplo, para encontrar todos os erros ocorridos entre o início da transação e o evento de fim da transação, pode-se dizer:

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  
  

Ao associar origens particionadas, o predicado JOIN tem de incluir uma condição que corresponda às chaves de partição de ambas as origens.

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 fim, o Azure Stream Analytics suporta a associação interna (a predefinição) e a associação externa LEFT. Para uma associação interna, só é apresentado um resultado quando é encontrada uma correspondência. Mas numa associação EXTERNA, se um evento externo à associação não tiver qualquer correspondência, é apresentada a linha NULO para todas as colunas da linha à direita. Por exemplo, eis um exemplo para encontrar a ausência de eventos. A seguinte consulta devolverá as linhas em que um Veículo entrou numa Portagem, mas não saiu do Booth no prazo 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  
  

Função DATEDIFF Especial para JOIN

Sintaxe

DATEDIFF ( datepart , input_source1, input_source2 )  

Argumentos

partes de data

Exemplo. "segundo", "milissegundos", "minuto", etc.)

input_source1

A primeira origem de entrada na Associação. Internamente, o carimbo de data/hora associado aos eventos deste input_source é transmitido para a função.

input_source2

A segunda origem de entrada na Associação. Internamente, o carimbo de data/hora associado aos eventos deste input_source é transmitido para a função.

Tipo de Devolução

Devolve o número de unidades em partes de data que decorrido do carimbo de data/hora de input_source1 ao carimbo de data/hora de input_source2. O valor devolvido pode ser negativo se o carimbo de data/hora do segundo input_source for superior ao primeiro.