Azure 串流分析中常見的查詢模式Common query patterns in Azure Stream Analytics

Azure 串流分析的查詢會以類似 SQL 的查詢語言表達。Queries in Azure Stream Analytics are expressed in a SQL-like query language. 語言建構記載在串流分析查詢語言參考指南中。The language constructs are documented in the Stream Analytics query language reference guide.

查詢設計能傳達簡單的傳遞邏輯,將事件資料從某個輸入資料流移至輸出資料存放區,或執行多種模式比對和時態分析,以計算一段時間的彙總,,如利用串流分析來建置 IoT 解決方案指南中所述。The query design can express simple pass-through logic to move event data from one input stream into an output data store, or it can do rich pattern matching and temporal analysis to calculate aggregates over various time windows as in the Build an IoT solution by using Stream Analytics guide. 您可以聯結多個輸入的資料來合併串流事件,也可以查閱靜態參考資料,從而擴充事件值。You can join data from multiple inputs to combine streaming events, and you can do lookups against static reference data to enrich the event values. 此外,您也可以將資料寫入至多個輸出。You can also write data to multiple outputs.

本文章根據真實世界案例概述幾個常見查詢模式的解決方案。This article outlines solutions to several common query patterns based on real-world scenarios.

支援的資料格式Supported Data Formats

Azure 串流分析可處理資料格式為 CSV、JSON 和 Avro 的事件。Azure Stream Analytics supports processing events in CSV, JSON and Avro data formats.

JSON 和 Avro 可能包含巢狀物件 (記錄) 或陣列等複雜類型。Both JSON and Avro may contain complex types such as nested objects (records) or arrays. 如需使用這些複雜資料類型的詳細資訊,請參閱剖析 JSON 和 AVRO 資料一文。For more information on working with these complex data types, refer to the Parsing JSON and AVRO data article.

將資料傳送至多個輸出Send data to multiple outputs

多個 SELECT 陳述式可用來將資料輸出到不同的輸出接收端。Multiple SELECT statements can be used to output data to different output sinks. 例如,某個 SELECT 可以輸出閾值型警示,另一個則可以將事件輸出至 Blob 儲存體。For example, one SELECT can output a threshold-based alert while another one can output events to blob storage.

輸入Input:

請確定Make TimeTime
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

輸出 ArchiveOutputOutput ArchiveOutput:

請確定Make TimeTime
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

輸出 AlertOutputOutput AlertOutput:

請確定Make TimeTime CountCount
Make2Make2 2015-01-01T00:00:10.0000000Z2015-01-01T00:00:10.0000000Z 33

查詢Query:

SELECT
    *
INTO
    ArchiveOutput
FROM
    Input TIMESTAMP BY Time

SELECT
    Make,
    System.TimeStamp() AS Time,
    COUNT(*) AS [Count]
INTO
    AlertOutput
FROM
    Input TIMESTAMP BY Time
GROUP BY
    Make,
    TumblingWindow(second, 10)
HAVING
    [Count] >= 3

INTO 子句會告訴串流分析要將資料寫入至哪個輸出。The INTO clause tells Stream Analytics which of the outputs to write the data to. 第一個 SELECT 定義傳遞查詢,其會接收來自輸入的資料,並將該資料傳送至名為 Archiveoutput 的輸出。The first SELECT defines a pass-through query that receives data from the input and sends it to the output named ArchiveOutput. 第二個查詢會執行一些簡單的彙總和篩選,然後再將結果傳送至下游的警示系統輸出,稱為 AlertOutputThe second query does some simple aggregation and filtering before sending the results to a downstream alerting system output called AlertOutput.

請注意,WITH 子句可以用來定義多個子查詢區塊。Note that the WITH clause can be used to define multiple sub-query blocks. 此選項具有對輸入來源開啟更少讀取器的優點。This option has the benefit of opening fewer readers to the input source.

查詢Query:

WITH ReaderQuery AS (
    SELECT
        *
    FROM
        Input TIMESTAMP BY Time
)

SELECT * INTO ArchiveOutput FROM ReaderQuery

SELECT 
    Make,
    System.TimeStamp() AS Time,
    COUNT(*) AS [Count] 
INTO AlertOutput 
FROM ReaderQuery
GROUP BY
    Make,
    TumblingWindow(second, 10)
HAVING [Count] >= 3

如需詳細資訊,請參閱 WITH 子句For more information, refer to WITH clause.

簡單的傳遞查詢Simple pass-through query

簡單的傳遞查詢可以用來將輸入資料流資料複製到輸出。A simple pass-through query can be used to copy the input stream data into the output. 例如,如果包含即時車輛資訊的資料流必須儲存在 SQL 資料庫中以進行信件分析,則簡單的傳遞查詢將會執行此作業。For example, if a stream of data containing real-time vehicle information needs to be saved in a SQL database for letter analysis, a simple pass-through query will do the job.

輸入Input:

請確定Make TimeTime WeightWeight
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z "1000""1000"
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z "2000""2000"

輸出Output:

請確定Make TimeTime WeightWeight
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z "1000""1000"
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z "2000""2000"

查詢Query:

SELECT
    *
INTO Output
FROM Input

SELECT * 查詢會預測傳入事件的所有欄位,並將其傳送至輸出。A SELECT * query projects all the fields of an incoming event and sends them to the output. 同樣地,SELECT 也可以用來只預測輸入中的必要欄位。The same way, SELECT can also be used to only project required fields from the input. 在此範例中,如果車輛 MakeTime 是要儲存的唯一必要欄位,則可以在 SELECT 陳述式中指定這些欄位。In this example, if vehicle Make and Time are the only required fields to be saved, those fields can be specified in the SELECT statement.

輸入Input:

請確定Make TimeTime WeightWeight
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z 10001000
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z 20002000
Make2Make2 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z 15001500

輸出Output:

請確定Make TimeTime
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z

查詢Query:

SELECT
    Make, Time
INTO Output
FROM Input

搭配 LIKE 和 NOT LIKE 的字串比對String matching with LIKE and NOT LIKE

LIKENOT LIKE 可以用來驗證欄位是否符合特定模式。LIKE and NOT LIKE can be used to verify if a field matches a certain pattern. 例如,您可以建立一個篩選條件,只傳回以字母 'A' 開頭並以數字 9 結尾的車牌。For example, a filter can be created to return only the license plates that start with the letter 'A' and end with the number 9.

輸入Input:

請確定Make License_plateLicense_plate TimeTime
Make1Make1 ABC-123ABC-123 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make2Make2 AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make3Make3 ABC-369ABC-369 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

輸出Output:

請確定Make License_plateLicense_plate TimeTime
Make2Make2 AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make3Make3 ABC-369ABC-369 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

查詢Query:

SELECT
    *
FROM
    Input TIMESTAMP BY Time
WHERE
    License_plate LIKE 'A%9'

使用 LIKE 陳述式檢查 License_plate 欄位值。Use the LIKE statement to check the License_plate field value. 其開頭應該為字母 A,接著是長度為零或更多字元的字串,並以數字 9 結尾。It should start with the letter 'A', then have any string of zero or more characters, ending with the number 9.

過去事件的計算Calculation over past events

LAG 函式可以用來查看時間範圍內的過去事件,並將其與目前的事件進行比較。The LAG function can be used to look at past events within a time window and compare them against the current event. 例如,如果目前的汽車製造商與通過收費站的最後一輛汽車不同,就可以將其輸出。For example, the current car make can be outputted if it is different from the last car that went through the toll.

輸入Input:

請確定Make TimeTime
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z

輸出Output:

請確定Make TimeTime
Make2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z

查詢Query:

SELECT
    Make,
    Time
FROM
    Input TIMESTAMP BY Time
WHERE
    LAG(Make, 1) OVER (LIMIT DURATION(minute, 1)) <> Make

使用 LAG 查看前一個事件的輸入資料流,同時擷取 Make 值,並將其與目前事件的 Make 進行比較,然後輸出事件。Use LAG to peek into the input stream one event back, retrieving the Make value and comparing it to the Make value of the current event and output the event.

如需詳細資訊,請參閱 LAGFor more information, refer to LAG.

傳回時間範圍內的最後一個事件Return the last event in a window

當系統即時使用事件時,沒有函式可判斷該事件是否會是該時間範圍內最後到達的事件。As events are consumed by the system in real-time, there is no function that can determine if an event will be the last one to arrive for that window of time. 為了達成此目的,輸入資料流程必須與另一個輸入資料流程聯結,其中事件的時間是該時間範圍內所有事件的最長時間。To achieve this, the input stream needs to be joined with another where the time of an event is the maximum time for all events at that window.

輸入Input:

License_plateLicense_plate 請確定Make TimeTime
DXE 5291DXE 5291 Make1Make1 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 Make3Make3 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
RMV 8282RMV 8282 Make1Make1 2015-07-27T00:05:01.0000000Z2015-07-27T00:05:01.0000000Z
YHN 6970YHN 6970 Make2Make2 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
VFE 1616VFE 1616 Make2Make2 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
QYF 9358QYF 9358 Make1Make1 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 Make4Make4 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

輸出Output:

License_plateLicense_plate 請確定Make TimeTime
VFE 1616VFE 1616 Make2Make2 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
MDR 6128MDR 6128 Make4Make4 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

查詢Query:

WITH LastInWindow AS
(
    SELECT 
        MAX(Time) AS LastEventTime
    FROM 
        Input TIMESTAMP BY Time
    GROUP BY 
        TumblingWindow(minute, 10)
)

SELECT 
    Input.License_plate,
    Input.Make,
    Input.Time
FROM
    Input TIMESTAMP BY Time 
    INNER JOIN LastInWindow
    ON DATEDIFF(minute, Input, LastInWindow) BETWEEN 0 AND 10
    AND Input.Time = LastInWindow.LastEventTime

查詢的第一個步驟是在 10 分鐘的時間範圍內尋找時間戳記上限,這是該時間範圍最後一個事件的時間戳記。The first step on the query finds the maximum time stamp in 10-minute windows, that is the time stamp of the last event for that window. 第二個步驟會將第一個查詢的結果與原始串流聯結在一起,在每個時間範圍內尋找符合最後一個時間戳記的事件。The second step joins the results of the first query with the original stream to find the event that match the last time stamps in each window.

DATEDIFF 是一種日期特有的函式,可比較並傳回兩個 DateTime 欄位之間的時間差。如需詳細資訊,請參閱 date 函式DATEDIFF is a date-specific function that compares and returns the time difference between two DateTime fields, for more information, refer to date functions.

如需聯結資料流的詳細資訊,請參閱 JOINFor more information on joining streams, refer to JOIN.

一段時間的資料彙總Data aggregation over time

若要計算一段時間範圍的資訊,可以將資料彙總在一起。To compute information over a time window, data can be aggregated together. 在此範例中,會針對每個特定汽車所產生的過去10秒計算一次計數。In this example, a count is computed over the last 10 seconds of time for every specific car make.

輸入Input:

請確定Make TimeTime WeightWeight
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z 10001000
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z 20002000
Make2Make2 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z 15001500

輸出Output:

請確定Make CountCount
Make1Make1 22
Make2Make2 11

查詢Query:

SELECT
    Make,
    COUNT(*) AS Count
FROM
    Input TIMESTAMP BY Time
GROUP BY
    Make,
    TumblingWindow(second, 10)

此彙總會依 Make 將汽車分組,並每隔 10 秒對其進行計數一次。This aggregation groups the cars by Make and counts them every 10 seconds. 對於通過收費站的汽車,輸出具有其 MakeCountThe output has the Make and Count of cars that went through the toll.

TumblingWindow 是用來將事件群組在一起的視窗化函式。TumblingWindow is a windowing function used to group events together. 彙總可以套用至所有分組的事件。An aggregation can be applied over all grouped events. 如需詳細資訊,請參閱視窗化函式For more information, see windowing functions.

如需彙總的詳細資訊,請參閱彙總函式For more information on aggregation, refer to aggregate functions.

定期輸出值Periodically output values

萬一發生異常或遺失的事件,可以從更疏鬆的資料輸入產生定期輸出。In case of irregular or missing events, a regular interval output can be generated from a more sparse data input. 例如,每隔 5 秒產生事件,報告最近所見的資料點。For example, generate an event every 5 seconds that reports the most recently seen data point.

輸入Input:

TimeTime Value
"2014-01-01T06:01:00""2014-01-01T06:01:00" 11
"2014-01-01T06:01:05""2014-01-01T06:01:05" 22
"2014-01-01T06:01:10""2014-01-01T06:01:10" 33
"2014-01-01T06:01:15""2014-01-01T06:01:15" 44
"2014-01-01T06:01:30""2014-01-01T06:01:30" 55
"2014-01-01T06:01:35""2014-01-01T06:01:35" 66

輸出 (前 10 個資料列)Output (first 10 rows):

Window_endWindow_end Last_event.TimeLast_event.Time Last_event.ValueLast_event.Value
2014-01-01T14:01:00.000Z2014-01-01T14:01:00.000Z 2014-01-01T14:01:00.000Z2014-01-01T14:01:00.000Z 11
2014-01-01T14:01:05.000Z2014-01-01T14:01:05.000Z 2014-01-01T14:01:05.000Z2014-01-01T14:01:05.000Z 22
2014-01-01T14:01:10.000Z2014-01-01T14:01:10.000Z 2014-01-01T14:01:10.000Z2014-01-01T14:01:10.000Z 33
2014-01-01T14:01:15.000Z2014-01-01T14:01:15.000Z 2014-01-01T14:01:15.000Z2014-01-01T14:01:15.000Z 44
2014-01-01T14:01:20.000Z2014-01-01T14:01:20.000Z 2014-01-01T14:01:15.000Z2014-01-01T14:01:15.000Z 44
2014-01-01T14:01:25.000Z2014-01-01T14:01:25.000Z 2014-01-01T14:01:15.000Z2014-01-01T14:01:15.000Z 44
2014-01-01T14:01:30.000Z2014-01-01T14:01:30.000Z 2014-01-01T14:01:30.000Z2014-01-01T14:01:30.000Z 55
2014-01-01T14:01:35.000Z2014-01-01T14:01:35.000Z 2014-01-01T14:01:35.000Z2014-01-01T14:01:35.000Z 66
2014-01-01T14:01:40.000Z2014-01-01T14:01:40.000Z 2014-01-01T14:01:35.000Z2014-01-01T14:01:35.000Z 66
2014-01-01T14:01:45.000Z2014-01-01T14:01:45.000Z 2014-01-01T14:01:35.000Z2014-01-01T14:01:35.000Z 66

查詢Query:

SELECT
    System.Timestamp() AS Window_end,
    TopOne() OVER (ORDER BY Time DESC) AS Last_event
FROM
    Input TIMESTAMP BY Time
GROUP BY
    HOPPINGWINDOW(second, 300, 5)

此查詢會每隔 5 秒產生事件,並輸出先前所收到的最後一個事件。This query generates events every 5 seconds and outputs the last event that was received previously. HOPPINGWINDOW 持續時間會決定查詢要回到多久前,才能找到最新的事件。The HOPPINGWINDOW duration determines how far back the query looks to find the latest event.

如需詳細資訊,請參閱跳動視窗For more information, refer to Hopping window.

使資料流中的事件相互關聯Correlate events in a stream

藉由使用 LAG 函式查看過去事件,即可使相同資料流中的事件相互關聯。Correlating events in the same stream can be done by looking at past events using the LAG function. 例如,每次連續兩部來自相同 Make 的汽車在過去 90 秒內通過收費站時,就會產生輸出。For example, an output can be generated every time two consecutive cars from the same Make go through the toll for the last 90 seconds.

輸入Input:

請確定Make License_plateLicense_plate TimeTime
Make1Make1 ABC-123ABC-123 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make1Make1 AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 DEF-987DEF-987 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z
Make1Make1 GHI-345GHI-345 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z

輸出Output:

請確定Make TimeTime Current_car_license_plateCurrent_car_license_plate First_car_license_plateFirst_car_license_plate First_car_timeFirst_car_time
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z AAA-999AAA-999 ABC-123ABC-123 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z

查詢Query:

SELECT
    Make,
    Time,
    License_plate AS Current_car_license_plate,
    LAG(License_plate, 1) OVER (LIMIT DURATION(second, 90)) AS First_car_license_plate,
    LAG(Time, 1) OVER (LIMIT DURATION(second, 90)) AS First_car_time
FROM
    Input TIMESTAMP BY Time
WHERE
    LAG(Make, 1) OVER (LIMIT DURATION(second, 90)) = Make

LAG 函式可以查看前一個事件的輸入資料流,並擷取 Make,將其與目前事件的 Make 值進行比較。The LAG function can look into the input stream one event back and retrieve the Make value, comparing that with the Make value of the current event. 一旦符合條件,就可以使用 SELECT 陳述式中的 LAG 來預測上一個事件中的資料。Once the condition is met, data from the previous event can be projected using LAG in the SELECT statement.

如需詳細資訊,請參閱 LAGFor more information, refer to LAG.

偵測事件之間的持續時間Detect the duration between events

一旦收到 End 事件,就可以查看最後一個 Start 事件來計算事件的持續時間。The duration of an event can be computed by looking at the last Start event once an End event is received. 此查詢有助於判斷使用者在頁面或功能上花費的時間。This query can be useful to determine the time a user spends on a page or a feature.

輸入Input:

UserUser 功能Feature 事件Event TimeTime
user@location.com RightMenuRightMenu StartStart 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
user@location.com RightMenuRightMenu 結束End 2015-01-01T00:00:08.0000000Z2015-01-01T00:00:08.0000000Z

輸出Output:

UserUser 功能Feature DurationDuration
user@location.com RightMenuRightMenu 77

查詢Query:

SELECT
    [user],
    feature,
    DATEDIFF(
        second,
        LAST(Time) OVER (PARTITION BY [user], feature LIMIT DURATION(hour, 1) WHEN Event = 'start'),
        Time) as duration
FROM input TIMESTAMP BY Time
WHERE
    Event = 'end'

LAST 函式可以用來擷取特定條件內的最後一個事件。The LAST function can be used to retrieve the last event within a specific condition. 在此範例中,條件是類型為 Start 的事件,藉由 PARTITION BY 使用者和功能來分割搜尋。In this example, the condition is an event of type Start, partitioning the search by PARTITION BY user and feature. 如此一來,在搜尋 Start 事件時,每個使用者和功能都會獨立處理。This way, every user and feature is treated independently when searching for the Start event. LIMIT DURATION 將向後搜尋的時間限制為 End 和 Start 事件之間的 1 小時。LIMIT DURATION limits the search back in time to 1 hour between the End and Start events.

計算唯一值Count unique values

COUNTDISTINCT 可用來計算某個時間範圍內在串流中所出現唯一欄位值的數目。COUNT and DISTINCT can be used to count the number of unique field values that appear in the stream within a time window. 例如,在 2 秒鐘時間範圍內有多少部獨特 Make 的汽車通過收費站。A query can be created to calculate how many unique Makes of cars passed through the toll booth in a 2-second window.

輸入Input:

請確定Make TimeTime
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

輸出:Output:

Count_makeCount_make TimeTime
22 2015-01-01T00:00:02.000Z2015-01-01T00:00:02.000Z
11 2015-01-01T00:00:04.000Z2015-01-01T00:00:04.000Z

查詢:Query:

SELECT
     COUNT(DISTINCT Make) AS Count_make,
     System.TIMESTAMP() AS Time
FROM Input TIMESTAMP BY TIME
GROUP BY 
     TumblingWindow(second, 2)

COUNT(DISTINCT Make) 會傳回一個時間範圍內 Make 資料行的相異值計數。COUNT(DISTINCT Make) returns the count of distinct values in the Make column within a time window. 如需詳細資訊,請參閱 COUNT 彙總函式For more information, refer to COUNT aggregate function.

擷取時間範圍內的第一個事件Retrieve the first event in a window

IsFirst 可用來取出時間範圍內的第一個事件。IsFirst can be used to retrieve the first event in a time window. 例如,每隔 10 分鐘輸出第一部汽車資訊。For example, outputting the first car information at every 10-minute interval.

輸入Input:

License_plateLicense_plate 請確定Make TimeTime
DXE 5291DXE 5291 Make1Make1 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 Make3Make3 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
RMV 8282RMV 8282 Make1Make1 2015-07-27T00:05:01.0000000Z2015-07-27T00:05:01.0000000Z
YHN 6970YHN 6970 Make2Make2 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
VFE 1616VFE 1616 Make2Make2 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
QYF 9358QYF 9358 Make1Make1 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 Make4Make4 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

輸出Output:

License_plateLicense_plate 請確定Make TimeTime
DXE 5291DXE 5291 Make1Make1 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
QYF 9358QYF 9358 Make1Make1 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z

查詢Query:

SELECT 
    License_plate,
    Make,
    Time
FROM 
    Input TIMESTAMP BY Time
WHERE 
    IsFirst(minute, 10) = 1

IsFirst 也可以分割資料,並計算每部特定汽車 Make 每隔 10 分鐘找到的第一個事件。IsFirst can also partition the data and calculate the first event to each specific car Make found at every 10-minute interval.

輸出Output:

License_plateLicense_plate 請確定Make TimeTime
DXE 5291DXE 5291 Make1Make1 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 Make3Make3 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
YHN 6970YHN 6970 Make2Make2 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
QYF 9358QYF 9358 Make1Make1 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 Make4Make4 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

查詢Query:

SELECT 
    License_plate,
    Make,
    Time
FROM 
    Input TIMESTAMP BY Time
WHERE 
    IsFirst(minute, 10) OVER (PARTITION BY Make) = 1

如需詳細資訊,請參閱 IsFirstFor more information, refer to IsFirst.

移除時間範圍內的重複事件Remove duplicate events in a window

執行如計算指定時間範圍內事件平均值的作業時,應該要將重複的事件篩選出來。When performing an operation such as calculating averages over events in a given time window, duplicate events should be filtered. 在下列範例中,第二個事件是第一個事件的重複。In the following example, the second event is a duplicate of the first.

輸入Input:

deviceIdDeviceId TimeTime 屬性Attribute Value
11 2018-07-27T00:00:01.0000000Z2018-07-27T00:00:01.0000000Z 溫度Temperature 5050
11 2018-07-27T00:00:01.0000000Z2018-07-27T00:00:01.0000000Z 溫度Temperature 5050
22 2018-07-27T00:00:01.0000000Z2018-07-27T00:00:01.0000000Z 溫度Temperature 4040
11 2018-07-27T00:00:05.0000000Z2018-07-27T00:00:05.0000000Z 溫度Temperature 6060
22 2018-07-27T00:00:05.0000000Z2018-07-27T00:00:05.0000000Z 溫度Temperature 5050
11 2018-07-27T00:00:10.0000000Z2018-07-27T00:00:10.0000000Z 溫度Temperature 100100

輸出Output:

AverageValueAverageValue deviceIdDeviceId
7070 11
4545 22

查詢Query:

With Temp AS (
SELECT
    COUNT(DISTINCT Time) AS CountTime,
    Value,
    DeviceId
FROM
    Input TIMESTAMP BY Time
GROUP BY
    Value,
    DeviceId,
    SYSTEM.TIMESTAMP()
)

SELECT
    AVG(Value) AS AverageValue, DeviceId
INTO Output
FROM Temp
GROUP BY DeviceId,TumblingWindow(minute, 5)

COUNT(DISTINCT Time) 會傳回時間範圍內 Time 資料行中的相異值數目。COUNT(DISTINCT Time) returns the number of distinct values in the Time column within a time window. 然後,可以使用第一個步驟的輸出,透過捨棄重複項目來計算每個裝置的平均值。The output of the first step can then be used to compute the average per device, by discarding duplicates.

如需詳細資訊,請參閱 COUNT(DISTINCT Time)For more information, refer to COUNT(DISTINCT Time).

針對不同的案例/值指定邏輯 (CASE 陳述式)Specify logic for different cases/values (CASE statements)

CASE 陳述式可以根據特定準則,為不同的欄位提供不同的計算。CASE statements can provide different computations for different fields, based on particular criterion. 例如,將車道 'A' 指派給 Make1 的汽車,並將車道 'B' 指派給任何其他製造商。For example, assign lane 'A' to cars of Make1 and lane 'B' to any other make.

輸入Input:

請確定Make TimeTime
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
Make2Make2 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

輸出Output:

請確定Make Dispatch_to_laneDispatch_to_lane TimeTime
Make1Make1 "A""A" 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
Make2Make2 "B""B" 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z

解決方案Solution:

SELECT
    Make
    CASE
        WHEN Make = "Make1" THEN "A"
        ELSE "B"
    END AS Dispatch_to_lane,
    System.TimeStamp() AS Time
FROM
    Input TIMESTAMP BY Time

CASE 運算式會比較運算式和一組簡單運算式來決定其結果。The CASE expression compares an expression to a set of simple expressions to determine its result. 在此範例中,Make1 的車輛會分派給車道 'A',而任何其他製造商的車輛則會指派給車道 'B'。In this example, vehicles of Make1 are dispatched to lane 'A' while vehicles of any other make will be assigned lane 'B'.

如需詳細資訊,請參閱 case 運算式For more information, refer to case expression.

資料轉換Data conversion

您可以使用 CAST 方法來即時轉換資料。Data can be cast in real-time using the CAST method. 例如,汽車權數可以從類型 nvarchar(max) 轉換成類型 bigint,並用於數值計算。For example, car weight can be converted from type nvarchar(max) to type bigint and be used on a numeric calculation.

輸入Input:

請確定Make TimeTime WeightWeight
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z "1000""1000"
Make1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z "2000""2000"

輸出Output:

請確定Make WeightWeight
Make1Make1 30003000

查詢Query:

SELECT
    Make,
    SUM(CAST(Weight AS BIGINT)) AS Weight
FROM
    Input TIMESTAMP BY Time
GROUP BY
    Make,
    TumblingWindow(second, 10)

使用 CAST 陳述式來指定其資料類型。Use a CAST statement to specify its data type. 請參閱資料類型 (Azure 串流分析) 上的支援資料類型清單。See the list of supported data types on Data types (Azure Stream Analytics).

如需詳細資訊,請參閱資料轉換函式For more information on data conversion functions.

偵測某個條件的持續時間Detect the duration of a condition

對於跨越多個事件的條件,LAG 函式可以用來識別該條件的持續時間。For conditions that span through multiple events the LAG function can be used to identify the duration of that condition. 例如,假設有個錯誤導致所有車輛的重量不正確 (超過 20,000 磅),而且必須計算該錯誤的持續時間。For example, suppose that a bug resulted in all cars having an incorrect weight (above 20,000 pounds), and the duration of that bug must be computed.

輸入Input:

請確定Make TimeTime WeightWeight
Make1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z 20002000
Make2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z 2500025000
Make1Make1 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z 2600026000
Make2Make2 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z 2500025000
Make1Make1 2015-01-01T00:00:05.0000000Z2015-01-01T00:00:05.0000000Z 2600026000
Make2Make2 2015-01-01T00:00:06.0000000Z2015-01-01T00:00:06.0000000Z 2500025000
Make1Make1 2015-01-01T00:00:07.0000000Z2015-01-01T00:00:07.0000000Z 2600026000
Make2Make2 2015-01-01T00:00:08.0000000Z2015-01-01T00:00:08.0000000Z 20002000

輸出Output:

Start_faultStart_fault End_faultEnd_fault
2015-01-01T00:00:02.000Z2015-01-01T00:00:02.000Z 2015-01-01T00:00:07.000Z2015-01-01T00:00:07.000Z

查詢Query:

WITH SelectPreviousEvent AS
(
SELECT
    *,
    LAG([time]) OVER (LIMIT DURATION(hour, 24)) as previous_time,
    LAG([weight]) OVER (LIMIT DURATION(hour, 24)) as previous_weight
FROM input TIMESTAMP BY [time]
)

SELECT 
    LAG(time) OVER (LIMIT DURATION(hour, 24) WHEN previous_weight < 20000 ) [Start_fault],
    previous_time [End_fault]
FROM SelectPreviousEvent
WHERE
    [weight] < 20000
    AND previous_weight > 20000

第一個 SELECT 陳述式會使目前的權數測量與先前的測量相互關聯,並將其與目前的測量一起預測。The first SELECT statement correlates the current weight measurement with the previous measurement, projecting it together with the current measurement. 第二個 SELECT 往回查看 previous_weight 小於 20000 的最後一個事件,其中目前權數小於 20000,而目前事件的 previous_weight 則大於 20000。The second SELECT looks back to the last event where the previous_weight is less than 20000, where the current weight is smaller than 20000 and the previous_weight of the current event was bigger than 20000.

End_fault 是上一個事件發生錯誤的目前非錯誤事件,而 Start_fault 是在這之前的最後一個非錯誤事件。The End_fault is the current non-faulty event where the previous event was faulty, and the Start_fault is the last non-faulty event before that.

以獨立時間處理事件 (子資料流)Process events with independent time (Substreams)

事件會因事件產生器之間的時鐘誤差、分割之間的時鐘誤差或網路延遲,而導致延遲發生或順序錯誤。Events can arrive late or out of order due to clock skews between event producers, clock skews between partitions, or network latency. 例如,TollID 2 的裝置時鐘比 TollID 1 晚 5 秒,而且 TollID 3 的裝置時鐘比 TollID 1 晚 10 秒。For example, the device clock for TollID 2 is five seconds behind TollID 1, and the device clock for TollID 3 is ten seconds behind TollID 1. 計算可能會因每個收費而獨立發生,只將其自己的時鐘資料視為時間戳記。A computation can happen independently for each toll, considering only its own clock data as a timestamp.

輸入Input:

LicensePlateLicensePlate 請確定Make TimeTime TollIDTollID
DXE 5291DXE 5291 Make1Make1 2015-07-27T00:00:01.0000000Z2015-07-27T00:00:01.0000000Z 11
YHN 6970YHN 6970 Make2Make2 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z 11
QYF 9358QYF 9358 Make1Make1 2015-07-27T00:00:01.0000000Z2015-07-27T00:00:01.0000000Z 22
GXF 9462GXF 9462 Make3Make3 2015-07-27T00:00:04.0000000Z2015-07-27T00:00:04.0000000Z 22
VFE 1616VFE 1616 Make2Make2 2015-07-27T00:00:10.0000000Z2015-07-27T00:00:10.0000000Z 11
RMV 8282RMV 8282 Make1Make1 2015-07-27T00:00:03.0000000Z2015-07-27T00:00:03.0000000Z 33
MDR 6128MDR 6128 Make3Make3 2015-07-27T00:00:11.0000000Z2015-07-27T00:00:11.0000000Z 22
YZK 5704YZK 5704 Make4Make4 2015-07-27T00:00:07.0000000Z2015-07-27T00:00:07.0000000Z 33

輸出Output:

TollIDTollID CountCount
11 22
22 22
11 11
33 11
22 11
33 11

查詢Query:

SELECT
      TollId,
      COUNT(*) AS Count
FROM input
      TIMESTAMP BY Time OVER TollId
GROUP BY TUMBLINGWINDOW(second, 5), TollId

TIMESTAMP BY OVER 子句會使用子資料流個別查看每個裝置時間軸。The TIMESTAMP OVER BY clause looks at each device timeline independently using substreams. 計算時會產生每個 TollID 的輸出事件,這表示事件的順序均與每個 TollID 有關,而不會重新排列順序,就像所有裝置都依據同一個時鐘一般。The output event for each TollID is generated as they are computed, meaning that the events are in order with respect to each TollID instead of being reordered as if all devices were on the same clock.

如需詳細資訊,請參閱 TIMESTAMP BY OVERFor more information, refer to TIMESTAMP BY OVER.

工作階段時間範圍Session Windows

工作階段時間範圍是一種時間範圍,可在事件發生時持續展開,並在一段特定時間之後未收到任何事件,或時間範圍到達其最大持續時間時,關閉計算。A Session Window is a window that keeps expanding as events occur and closes for computation if no event is received after a specific amount of time or if the window reaches its maximum duration. 此時間範圍在計算使用者互動資料時特別有用。This window is particularly useful when computing user interaction data. 當使用者開始與系統互動,時間範圍就會開始,而在沒有觀察到更多事件時,也就是說,使用者已停止互動時,就會關閉時間範圍。A window starts when a user starts interacting with the system and closes when no more events are observed, meaning, the user has stopped interacting. 例如,使用者正在與記錄點擊次數的網頁進行互動,這時工作階段時間範圍可以用來找出使用者與網站互動的時間長度。For example, a user is interacting with a web page where the number of clicks is logged, a Session Window can be used to find out how long the user interacted with the site.

輸入Input:

User_idUser_id TimeTime URLURL
00 2017-01-26T00:00:00.0000000Z2017-01-26T00:00:00.0000000Z "www.example.com/a.html""www.example.com/a.html"
00 2017-01-26T00:00:20.0000000Z2017-01-26T00:00:20.0000000Z "www.example.com/b.html""www.example.com/b.html"
11 2017-01-26T00:00:55.0000000Z2017-01-26T00:00:55.0000000Z "www.example.com/c.html""www.example.com/c.html"
00 2017-01-26T00:01:10.0000000Z2017-01-26T00:01:10.0000000Z "www.example.com/d.html""www.example.com/d.html"
11 2017-01-26T00:01:15.0000000Z2017-01-26T00:01:15.0000000Z "www.example.com/e.html""www.example.com/e.html"

輸出Output:

User_idUser_id StartTimeStartTime EndTimeEndTime Duration_in_secondsDuration_in_seconds
00 2017-01-26T00:00:00.0000000Z2017-01-26T00:00:00.0000000Z 2017-01-26T00:01:10.0000000Z2017-01-26T00:01:10.0000000Z 7070
11 2017-01-26T00:00:55.0000000Z2017-01-26T00:00:55.0000000Z 2017-01-26T00:01:15.0000000Z2017-01-26T00:01:15.0000000Z 2020

查詢Query:

SELECT
    user_id,
    MIN(time) as StartTime,
    MAX(time) as EndTime,
    DATEDIFF(second, MIN(time), MAX(time)) AS duration_in_seconds
FROM input TIMESTAMP BY time
GROUP BY
    user_id,
    SessionWindow(minute, 1, 60) OVER (PARTITION BY user_id)

SELECT 會預測與使用者互動相關的資料,以及互動的持續時間。The SELECT projects the data relevant to the user interaction, together with the duration of the interaction. 如果 1 分鐘內沒有發生任何互動,則會依使用者和關閉的 SessionWindow 將資料分組,時間範圍大小上限為 60 分鐘。Grouping the data by user and a SessionWindow that closes if no interaction happens within 1 minute, with a maximum window size of 60 minutes.

如需 SessionWindow 的詳細資訊,請參閱 工作階段時間範圍For more information on SessionWindow, refer to Session Window .

在 JavaScript 和 C# 中搭配使用者定義函式的語言擴充性Language extensibility with User Defined Function in JavaScript and C#

您可以使用以 JavaScript 或 C# 語言撰寫的自訂函式,來擴充 Azure 串流分析查詢語言。Azure Stream Analytics query language can be extended with custom functions written either in JavaScript or C# language. 使用者定義函式 (UDF) 是自訂/複雜的計算,無法使用 SQL 語言輕鬆地表示。User Defined Functions (UDF) are custom/complex computations that cannot be easily expressed using the SQL language. 這些 UDF 可以定義一次,並在查詢內多次使用。These UDFs can be defined once and used multiple times within a query. 例如,UDF 可以用來將十六進位 nvarchar(max) 值轉換成 bigint 值。For example, an UDF can be used to convert a hexadecimal nvarchar(max) value to an bigint value.

輸入Input:

Device_idDevice_id HexValueHexValue
11 "B4""B4"
22 "11B""11B"
33 "121""121"

輸出Output:

Device_idDevice_id DecimalDecimal
11 180180
22 283283
33 289289
function hex2Int(hexValue){
    return parseInt(hexValue, 16);
}
public static class MyUdfClass {
    public static long Hex2Int(string hexValue){
        return int.Parse(hexValue, System.Globalization.NumberStyles.HexNumber);
    }
}
SELECT
    Device_id,
    udf.Hex2Int(HexValue) AS Decimal
From
    Input

使用者定義函式會根據每個取用的事件,計算 HexValue 中的 bigint 值。The User Defined Function will compute the bigint value from the HexValue on every event consumed.

若需詳細資訊,請參閱 JavaScriptC#For more information, refer to JavaScript and C#.

搭配 MATCH_RECOGNIZE 的進階模式比對Advanced pattern matching with MATCH_RECOGNIZE

MATCH_RECOGNIZE 是一種進階模式比對機制,可以用來比對事件序列與定義完善的規則運算式模式。MATCH_RECOGNIZE is an advanced pattern matching mechanism that can be used to match a sequence of events to a well-defined regular expression pattern. 例如,如果有兩個連續的警告訊息,管理員需要收到通知,則在 ATM 作業期間,會即時監視 ATM 是否故障。For example, an ATM is being monitored at real time for failures, during the operation of the ATM if there are two consecutive warning messages the administrator needs to be notified.

輸入Input:

ATM_idATM_id Operation_idOperation_id Return_CodeReturn_Code TimeTime
11 「輸入 Pin 碼」"Entering Pin" "Success""Success" 2017-01-26T00:10:00.0000000Z2017-01-26T00:10:00.0000000Z
22 「打開出錢槽」"Opening Money Slot" "Success""Success" 2017-01-26T00:10:07.0000000Z2017-01-26T00:10:07.0000000Z
22 「關閉出錢槽」"Closing Money Slot" "Success""Success" 2017-01-26T00:10:11.0000000Z2017-01-26T00:10:11.0000000Z
11 「輸入提款金額」"Entering Withdraw Quantity" "Success""Success" 2017-01-26T00:10:08.0000000Z2017-01-26T00:10:08.0000000Z
11 「打開出錢槽」"Opening Money Slot" 「警告」"Warning" 2017-01-26T00:10:14.0000000Z2017-01-26T00:10:14.0000000Z
11 「列印存款餘額」"Printing Bank Balance" 「警告」"Warning" 2017-01-26T00:10:19.0000000Z2017-01-26T00:10:19.0000000Z

輸出Output:

ATM_idATM_id First_Warning_Operation_idFirst_Warning_Operation_id Warning_TimeWarning_Time
11 「打開出錢槽」"Opening Money Slot" 2017-01-26T00:10:14.0000000Z2017-01-26T00:10:14.0000000Z
SELECT *
FROM input TIMESTAMP BY time OVER ATM_id
MATCH_RECOGNIZE (
    LIMIT DURATION(minute, 1)
    PARTITON BY ATM_id
    MEASURES
        First(Warning.ATM_id) AS ATM_id,
        First(Warning.Operation_Id) AS First_Warning_Operation_id,
        First(Warning.Time) AS Warning_Time
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (Success* Warning{2,})
    DEFINE
        Success AS Succes.Return_Code = 'Success',
        Failure AS Warning.Return_Code <> 'Success'
) AS patternMatch

此查詢符合至少兩個連續失敗事件,並在符合條件時產生警示。This query matches at least two consecutive failure events and generate an alarm when the conditions are met. PATTERN 定義要用於比對的規則運算式,在此情況下,任何數目的成功作業後面至少連續兩次失敗。PATTERN defines the regular expression to be used on the matching, in this case, any number of successful operations followed by at least two consecutive failures. 成功和失敗是使用 Return_Code 值所定義,一旦符合條件,就會利用 ATM_id、第一個警告作業和第一個警告時間來推算 MEASURESSuccess and Failure are defined using Return_Code value and once the condition is met, the MEASURES are projected with ATM_id, the first warning operation and first warning time.

如需詳細資訊,請參閱 MATCH_RECOGNIZEFor more information, refer to MATCH_RECOGNIZE.

地理柵欄和地理空間查詢Geofencing and geospatial queries

Azure 串流分析提供內建的地理空間函式,可用來實作車隊管理、共駕、連線汽車和資產追蹤等案例。Azure Stream Analytics provides built-in geospatial functions that can be used to implement scenarios such as fleet management, ride sharing, connected cars, and asset tracking. 地理空間資料可以採用 GeoJSON 或 WKT 格式內嵌成事件資料流或參考資料的一部分。Geospatial data can be ingested in either GeoJSON or WKT formats as part of event stream or reference data. 例如,專精於製造列印護照機器的公司,會將其機器租用給政府和領事館。For example, a company that is specialized in manufacturing machines for printing passports, lease their machines to governments and consulates. 這些機器的位置會受到嚴格控制,以避免放錯位置和可能用於偽造護照。The location of those machines is heavily controlled as to avoid the misplacing and possible use for counterfeiting of passports. 每部機器都配有 GPS 追蹤器,這項資訊會轉送回 Azure 串流分析作業。Each machine is fitted with a GPS tracker, that information is relayed back to an Azure Stream Analytics job. 製造商想要追蹤這些機器的位置,並在其中一部機器離開授權區域時收到警示,如此一來,他們就可以從遠端停用、警示當局和取回設備。The manufacture would like to keep track of the location of those machines and be alerted if one of them leaves an authorized area, this way they can remotely disable, alert authorities and retrieve the equipment.

輸入Input:

Equipment_idEquipment_id Equipment_current_locationEquipment_current_location TimeTime
11 "POINT(-122.13288797982818 47.64082002051315)""POINT(-122.13288797982818 47.64082002051315)" 2017-01-26T00:10:00.0000000Z2017-01-26T00:10:00.0000000Z
11 "POINT(-122.13307252987875 47.64081350934929)""POINT(-122.13307252987875 47.64081350934929)" 2017-01-26T00:11:00.0000000Z2017-01-26T00:11:00.0000000Z
11 "POINT(-122.13308862313283 47.6406508603241)""POINT(-122.13308862313283 47.6406508603241)" 2017-01-26T00:12:00.0000000Z2017-01-26T00:12:00.0000000Z
11 "POINT(-122.13341048821462 47.64043760861279)""POINT(-122.13341048821462 47.64043760861279)" 2017-01-26T00:13:00.0000000Z2017-01-26T00:13:00.0000000Z

參考資料輸入Reference Data Input:

Equipment_idEquipment_id Equipment_lease_locationEquipment_lease_location
11 "POLYGON((-122.13326028450979 47.6409833866794,-122.13261655434621 47.6409833866794,-122.13261655434621 47.64061471602751,-122.13326028450979 47.64061471602751,-122.13326028450979 47.6409833866794))""POLYGON((-122.13326028450979 47.6409833866794,-122.13261655434621 47.6409833866794,-122.13261655434621 47.64061471602751,-122.13326028450979 47.64061471602751,-122.13326028450979 47.6409833866794))"

輸出Output:

Equipment_idEquipment_id Equipment_alert_locationEquipment_alert_location TimeTime
11 "POINT(-122.13341048821462 47.64043760861279)""POINT(-122.13341048821462 47.64043760861279)" 2017-01-26T00:13:00.0000000Z2017-01-26T00:13:00.0000000Z
SELECT
    input.Equipment_id AS Equipment_id,
    input.Equipment_current_location AS Equipment_current_location,
    input.Time AS Time
FROM input TIMESTAMP BY time
JOIN
    referenceInput 
    ON input.Equipment_id = referenceInput.Equipment_id
    WHERE 
        ST_WITHIN(input.Equipment_currenct_location, referenceInput.Equipment_lease_location) = 1

此查詢可讓製造商自動監視機器位置,當機器離開允許的地理柵欄時,就會收到警示。The query enables the manufacturer to monitor the machines location automatically, getting alerts when a machine leaves the allowed geofence. 內建的地理空間功能可讓使用者使用查詢內的 GPS 資料,而不需要協力廠商程式庫。The built-in geospatial function allows users to use GPS data within the query without third-party libraries.

如需詳細資訊,請參閱搭配 Azure 串流分析的地理柵欄和地理空間彙總案例一文。For more information, refer to the Geofencing and geospatial aggregation scenarios with Azure Stream Analytics article.

取得說明Get help

如需進一步的協助,請嘗試 Azure 串流分析的 Microsoft 問與答頁面For further assistance, try our Microsoft Q&A question page for Azure Stream Analytics.

後續步驟Next steps