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 时间Time
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

输出 ArchiveOutput:Output ArchiveOutput:

制造商Make 时间Time
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

输出 AlertOutput:Output AlertOutput:

制造商Make 时间Time CountCount
制造商2Make2 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. 第二个查询进行了一些简单的聚合和筛选操作,然后将结果发送到名为 AlertOutput 的下游报警系统输出。The 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 时间Time 重量Weight
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z "1000""1000"
制造商1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z "2000""2000"

输出Output:

制造商Make 时间Time 重量Weight
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z "1000""1000"
制造商1Make1 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. 在此示例中,如果车辆“制造商”和“时间”是唯一要保存的必填字段,则可以在 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 时间Time 重量Weight
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z 10001000
制造商1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z 20002000
制造商2Make2 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z 15001500

输出Output:

制造商Make 时间Time
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 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

LIKE 和 NOT 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 时间Time
制造商1Make1 ABC-123ABC-123 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商2Make2 AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商3Make3 ABC-369ABC-369 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

输出Output:

制造商Make License_plateLicense_plate 时间Time
制造商2Make2 AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商3Make3 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 时间Time
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z

输出Output:

制造商Make 时间Time
制造商2Make2 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 来查看某个事件后的输入流,检索“制造商”值,并将其与当前事件的“制造商”值相比较并输出该事件 。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 时间Time
DXE 5291DXE 5291 制造商1Make1 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 制造商3Make3 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
RMV 8282RMV 8282 制造商1Make1 2015-07-27T00:05:01.0000000Z2015-07-27T00:05:01.0000000Z
YHN 6970YHN 6970 制造商2Make2 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
VFE 1616VFE 1616 制造商2Make2 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
QYF 9358QYF 9358 制造商1Make1 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 制造商4Make4 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

输出Output:

License_plateLicense_plate 制造商Make 时间Time
VFE 1616VFE 1616 制造商2Make2 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
MDR 6128MDR 6128 制造商4Make4 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 时间Time 重量Weight
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z 10001000
制造商1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z 20002000
制造商2Make2 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z 15001500

输出Output:

制造商Make CountCount
制造商1Make1 22
制造商2Make2 11

查询Query:

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

此聚合按“制造商”对汽车进行分组,并每隔 10 秒钟对其进行计数。This aggregation groups the cars by Make and counts them every 10 seconds. 输出包含通过收费站的汽车的“制造商”和“计数” 。The 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:

时间Time 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. 例如,每次同一“制造商”的连续两辆汽车在过去 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 时间Time
制造商1Make1 ABC-123ABC-123 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商1Make1 AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 DEF-987DEF-987 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z
制造商1Make1 GHI-345GHI-345 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z

输出Output:

制造商Make 时间Time Current_car_license_plateCurrent_car_license_plate First_car_license_plateFirst_car_license_plate First_car_timeFirst_car_time
制造商1Make1 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 函数可查看一个事件后的输入流,并检索“制造商”值并将该值与当前事件的“制造商”值进行比较 。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

接收到“结束”事件后,可以通过查看上一个“开始”事件来计算事件的持续时间。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:

用户User FeatureFeature 事件Event 时间Time
user@location.com RightMenuRightMenu 开始Start 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:

用户User FeatureFeature 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. 在此示例中,条件是类型为“开始”的事件,通过“分区条件”用户和功能对搜索进行分区。In this example, the condition is an event of type Start, partitioning the search by PARTITION BY user and feature. 这样,搜索“开始”事件时,将单独处理每个用户和功能。This way, every user and feature is treated independently when searching for the Start event. LIMIT DURATION 将“结束”和“开始”事件之间的向后搜索时间限制为 1 小时。LIMIT DURATION limits the search back in time to 1 hour between the End and Start events.

对唯一值进行计数Count unique values

COUNT 和 DISTINCT 可用于计算时间范围内流中显示的唯一字段值数 。COUNT and DISTINCT can be used to count the number of unique field values that appear in the stream within a time window. 可以创建一个查询,以计算在 2 秒的时间范围内,通过收费站的同一制造商的汽车数量是多少。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 时间Time
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

输出:Output:

Count_makeCount_make 时间Time
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) 返回时间范围内的“制造商”列的非重复值数目 。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 时间Time
DXE 5291DXE 5291 制造商1Make1 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 制造商3Make3 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
RMV 8282RMV 8282 制造商1Make1 2015-07-27T00:05:01.0000000Z2015-07-27T00:05:01.0000000Z
YHN 6970YHN 6970 制造商2Make2 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
VFE 1616VFE 1616 制造商2Make2 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
QYF 9358QYF 9358 制造商1Make1 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 制造商4Make4 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

输出Output:

License_plateLicense_plate 制造商Make 时间Time
DXE 5291DXE 5291 制造商1Make1 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
QYF 9358QYF 9358 制造商1Make1 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 还可以对数据进行分区,并计算每隔 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 时间Time
DXE 5291DXE 5291 制造商1Make1 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 制造商3Make3 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
YHN 6970YHN 6970 制造商2Make2 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
QYF 9358QYF 9358 制造商1Make1 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 制造商4Make4 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 时间Time AttributeAttribute 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) 返回时间范围内的“时间”列的非重复值数目。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”分配给制造商1 的汽车,并将车道“B”分配给任何其他制造商的汽车。For example, assign lane 'A' to cars of Make1 and lane 'B' to any other make.

输入Input:

制造商Make 时间Time
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
制造商2Make2 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

输出Output:

制造商Make Dispatch_to_laneDispatch_to_lane 时间Time
制造商1Make1 “A”"A" 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
制造商2Make2 “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. 在此示例中,制造商1 的车辆被分派给车道“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 时间Time 重量Weight
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z "1000""1000"
制造商1Make1 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z "2000""2000"

输出Output:

制造商Make 重量Weight
制造商1Make1 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. 例如,假设某个 Bug 导致所有车的重量不正确(超出 20000 磅),因此必须计算该 Bug 的持续时间。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 时间Time 重量Weight
制造商1Make1 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z 20002000
制造商2Make2 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z 2500025000
制造商1Make1 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z 2600026000
制造商2Make2 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z 2500025000
制造商1Make1 2015-01-01T00:00:05.0000000Z2015-01-01T00:00:05.0000000Z 2600026000
制造商2Make2 2015-01-01T00:00:06.0000000Z2015-01-01T00:00:06.0000000Z 2500025000
制造商1Make1 2015-01-01T00:00:07.0000000Z2015-01-01T00:00:07.0000000Z 2600026000
制造商2Make2 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 是 End_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 时间Time TollIDTollID
DXE 5291DXE 5291 制造商1Make1 2015-07-27T00:00:01.0000000Z2015-07-27T00:00:01.0000000Z 11
YHN 6970YHN 6970 制造商2Make2 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z 11
QYF 9358QYF 9358 制造商1Make1 2015-07-27T00:00:01.0000000Z2015-07-27T00:00:01.0000000Z 22
GXF 9462GXF 9462 制造商3Make3 2015-07-27T00:00:04.0000000Z2015-07-27T00:00:04.0000000Z 22
VFE 1616VFE 1616 制造商2Make2 2015-07-27T00:00:10.0000000Z2015-07-27T00:00:10.0000000Z 11
RMV 8282RMV 8282 制造商1Make1 2015-07-27T00:00:03.0000000Z2015-07-27T00:00:03.0000000Z 33
MDR 6128MDR 6128 制造商3Make3 2015-07-27T00:00:11.0000000Z2015-07-27T00:00:11.0000000Z 22
YZK 5704YZK 5704 制造商4Make4 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 OVER BY 子句分别使用子流来查看每个设备时间线。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 时间Time 代码URL
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. 按用户和 SessionWindow 对数据进行分组,该窗口在 1 分钟内未发生交互的情况下关闭,且最大窗口大小为 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 中用户定义的函数的语言扩展性Language extensibility with User Defined Function in JavaScript

可使用以 JavaScript 语言编写的自定义函数扩展 Azure 流分析查询语言。Azure Stream Analytics query language can be extended with custom functions written in JavaScript 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);
}
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.

有关详细信息,请参阅 JavaScriptFor more information, refer to JavaScript.

使用 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 时间Time
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 intput TIMESTAMP BY time OVER ATM_id
MATCH_RECOGNIZE (
    PARTITON BY ATM_id
    LIMIT DURATION(minute, 1)
    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、第一个警告操作和第一次警告时间投影 MEASURES。Success 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 时间Time
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 时间Time
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