JOIN (Azure 資料流分析)

  • 發行項

如同標準 T-SQL，Azure 串流分析查詢語言中的 JOIN 可用來合併來自兩個或多個輸入來源的記錄。 Azure 串流分析中的 JOIN 本質上是時態性，這表示每個 JOIN 都必須提供一些限制，以時間分隔比對資料列的程度。 例如，表示「將 TollBoothEntry 事件與 TollBoothExit 事件聯結在相同的 LicensePlate 和 TollId 上，並在彼此的 5 分鐘內發生」合法;但是「在 LicensePlate 和 TollId 上發生 TollBoothExit 事件時，聯結 TollBoothEntry 事件」不是 – 它會比對每個 TollBoothEntry 與未系結且可能無限的所有 TollBoothExit 集合到相同的 LicensePlate 和 TollId。

使用 DATEDIFF 函式，在 JOIN 的 ON 子句內指定關聯性的時間界限。 DATEDIFF 大小上限為七天。 如需其一般用途的詳細資訊，請參閱 DATEDIFF (Azure 串流分析) 。 在 JOIN 條件內使用 DATEDIFF 時，需要特別處理第二個和第三個參數。

此外，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>

指定聯結所根據的條件。 聯結條件必須有針對關聯性定義的時間界限或時態性波浪空間，而且是在 JOIN 的 ON 子句內指定，並使用 SPECIAL DATEDIFF Function for 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 分鐘時，上述聯結條件才會產生相符結果。

注意

SELECT 語句中使用的 DATEDIFF 會使用一般語法，其中 datetime 資料行或運算式會當做第二個和第三個參數傳入。 不過，在 JOIN 條件內使用 DATEDIFF 函式時，會使用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 串流分析支援內部聯結 (預設) 和 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

JOIN 的特殊 DATEDIFF 函數

語法

DATEDIFF ( datepart , input_source1, input_source2 )

引數

dateparts

例如： 'second'、'millisecond'、'minute'等)

input_source1

Join 中的第一個輸入來源。 在內部，與此 input_source 中的事件相關聯的時間戳記會傳遞至函數。

input_source2

Join 中的第二個輸入來源。 在內部，與此 input_source 中的事件相關聯的時間戳記會傳遞至函數。

傳回類型

傳回 dateparts 中從 input_source1 的時間戳記到 input_source2 的時間戳記之間經過的單位數。 如果第二個input_source的時間戳記大於第一個，則傳回的值可以是負數。