JOIN (Azure Stream Analytics)

Как и стандартный T-SQL, join в языке запросов Azure Stream Analytics используется для объединения записей из двух или более источников входных данных. Join в Azure Stream Analytics является темпоральным по своей природе, то есть каждое JOIN должно предоставлять некоторые ограничения на то, насколько можно разделить совпадающие строки во времени. Например, выражение "присоединение событий TollBoothEntry к событиям TollBoothExit, когда они происходят на одном и том же LicensePlate и TollId и в течение 5 минут друг от друга" является допустимым; Но "присоединение событий TollBoothEntry к событиям TollBoothExit, когда они происходят на LicensePlate и TollId", не является — это не соответствует каждому TollBoothEntry с неограниченной и потенциально бесконечной коллекцией всех TollBoothExit с одними и теми же LicensePlate и TollId.

Временные границы для связи указываются в предложении ON join с помощью функции DATEDIFF. Максимальный размер DATEDIFF составляет семь дней. Дополнительные сведения об общем использовании см. в статье DATEDIFF (Azure Stream Analytics). Если функция DATEDIFF используется в условии JOIN, второй и третий параметр обрабатываются особым образом.

Кроме того, select * нельзя использовать в инструкциях JOIN.

Синтаксис

[ 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  
  

Аргументы

<input_source>

Указывает источник входных данных.

<reference_data>

Ссылочные данные, к которому нужно присоединить источник input_source. Дополнительные сведения см. в разделе Присоединение ссылочных данных.

<join_type>

Указание типа операции соединения.

JOIN

Указывает, что данная операция присоединения должна произойти между указанными источниками входных данных и (или) ссылочных данных. Все строки слева и справа, соответствующие условиям присоединения, включаются в результирующий набор.

Предупреждение

Если источники JOIN секционированы, предикат JOIN должен включать условие, соответствующее ключам секций обоих источников.

[ LEFT OUTER JOIN ]

Указывает, что все строки из левой таблицы, не соответствующие условиям соединения, включаются в результирующий набор, а выходные столбцы из оставшейся таблицы устанавливаются в значение NULL в дополнение ко всем строкам, возвращаемым внутренним соединением.

ON <join_condition>

Задает условие, на котором основывается соединение. Условие соединения должно иметь временную границу или темпоральную переменную, определенную для связи, и указывается в предложении ON join, используя специальный синтаксис функции Special DATEDIFF для функции JOIN.

Примеры

В Azure Stream Analytics все события имеют четко определенные метки времени. Таким образом, пользователь должен использовать псевдонимы строк непосредственно в функции DATEDIFF следующим образом:

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  
  

Вышеупомянутое условие присоединения приведет к совпадению только в том случае, если ExitTime возникает после EntryTime, но не более чем через 15 минут.

Примечание

DATEDIFF, используемый в инструкции SELECT, использует общий синтаксис, в котором столбец даты и времени или выражение передается в качестве второго и третьего параметра. Однако при использовании функции DATEDIFF в условии JOIN используется имя input_source или его псевдоним. Внутренне выбирается соответствующая метка времени для каждого события в указанном источнике.

Условия времени привязки могут объединяться друг с другом и с другими условиями в предложении ON, например:

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  
  

При соединении трех или более таблиц применяются одни и те же правила, --- временные границы должны гарантировать, что все сопоставленные события происходят в течение ограниченного промежутка времени друг от друга. Например, чтобы найти все ошибки, возникшие между началом и завершением транзакции, можно сказать следующее:

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  
  

При соединении источников, которые секционированы, предикат JOIN должен включать условие, соответствующее ключам секций обоих источников.

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  

Наконец, Azure Stream Analytics поддерживает как внутреннее соединение (по умолчанию), так и внешнее соединение LEFT. Для внутреннего соединения результат возвращается только при обнаружении совпадения. Но для соединения LEFT OUTER, если событие с левой стороны соединения не совпадает, возвращается строка со значением NULL для всех столбцов правой строки. Например, ниже приведен пример для поиска отсутствия событий. Следующий запрос возвращает те строки, в которых автомобиль вошел в пункт оплаты дорожного сбора, но не вышел из него в течение 15 минут.

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  
  

Специальная функция DATEDIFF для JOIN

Синтаксис

DATEDIFF ( datepart , input_source1, input_source2 )  

Аргументы

dateparts

Пример: second, millisecond, minute и т. д.)

input_source1

Первый входной источник в операции JOIN. Метка времени, связанная с событиями из этого источника input_source, внутренне передается в функцию.

input_source2

Второй входной источник в операции JOIN. Метка времени, связанная с событиями из этого источника input_source, внутренне передается в функцию.

Тип возвращаемых данных

Возвращает число единиц в частях даты, которое прошло с момента метки времени input_source1 до метки времени input_source2. Возвращаемое значение может быть отрицательным, если метка времени второго input_source больше первой.