一般的 Stream Analytics 使用状況パターンのクエリ例Query examples for common Stream Analytics usage patterns

Azure Stream Analytics のクエリは SQL に類似したクエリ言語で表現されます。Queries in Azure Stream Analytics are expressed in a SQL-like query language. 言語の構造については、「Stream Analytics query language reference」(Stream Analytics クエリ言語リファレンス) ガイドで確認できます。The language constructs are documented in the Stream Analytics query language reference guide.

クエリのデザインでは、イベント データを 1 つの入力ストリームから出力データ ストアに移動する、単純なパススルー ロジックを表すことができます。また、「Stream Analytics を使って 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.

JSON および AVRO での複合データ型の操作Work with complex Data Types in JSON and AVRO

Azure Stream Analytics では、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.

クエリの例:データ型の変換Query example: Convert data types

説明:入力ストリームのプロパティの型を定義します。Description: Define the types of properties on the input stream. たとえば、自動車の重量は入力ストリームでは文字列ですが、SUM を計算するために INT に変換する必要があります。For example, the car weight is coming on the input stream as strings and needs to be converted to INT to perform SUM.

入力:Input:

MakeMake TimeTime WeightWeight
HondaHonda 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z "1000""1000"
HondaHonda 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z "2000""2000"

出力:Output:

MakeMake WeightWeight
HondaHonda 30003000

解決策:Solution:

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

説明:Weight フィールドの CAST ステートメントを使用してそのデータ型を指定します。Explanation: Use a CAST statement in the Weight field to specify its data type. サポートされるデータ型の一覧については、「Data types (Azure Stream Analytics)」(データ型 (Azure Stream Analytics)) をご覧ください。See the list of supported data types in Data types (Azure Stream Analytics).

クエリの例:LIKE/NOT LIKE を使用してパターン マッチングを行うQuery example: Use LIKE/NOT LIKE to do pattern matching

説明:イベントのフィールド値が特定のパターンと一致することを確認します。Description: Check that a field value on the event matches a certain pattern. たとえば、結果が A で始まり 9 で終わるライセンス プレートを返すかどうかを検査します。For example, check that the result returns license plates that start with A and end with 9.

入力:Input:

MakeMake LicensePlateLicensePlate TimeTime
HondaHonda ABC-123ABC-123 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
ToyotaToyota AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
NissanNissan ABC-369ABC-369 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

出力:Output:

MakeMake LicensePlateLicensePlate TimeTime
ToyotaToyota AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
NissanNissan ABC-369ABC-369 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

解決策:Solution:

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

説明:LIKE ステートメントを使用して LicensePlate フィールドの値を検査します。Explanation: Use the LIKE statement to check the LicensePlate field value. 値は文字 A で始まり、0 個以上の文字列が続き、数字 9 で終わります。It should start with the letter A, then have any string of zero or more characters, and then end with the number 9.

クエリの例:異なるケース/値に異なるロジックを指定する (CASE ステートメント)Query example: Specify logic for different cases/values (CASE statements)

説明:特定の条件に基づいて、フィールドに異なる計算を適用します。Description: Provide a different computation for a field, based on a particular criterion. たとえば、通過した自動車の台数を製造元ごとに表す文字列情報を指定します。このとき、台数が 1 のときだけ異なる計算を適用するものとします。For example, provide a string description for how many cars of the same make passed, with a special case for 1.

入力:Input:

MakeMake TimeTime
HondaHonda 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
ToyotaToyota 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
ToyotaToyota 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

出力:Output:

CarsPassedCarsPassed TimeTime
1 Honda1 Honda 2015-01-01T00:00:10.0000000Z2015-01-01T00:00:10.0000000Z
2 Toyotas2 Toyotas 2015-01-01T00:00:10.0000000Z2015-01-01T00:00:10.0000000Z

解決策:Solution:

    SELECT
        CASE
            WHEN COUNT(*) = 1 THEN CONCAT('1 ', Make)
            ELSE CONCAT(CAST(COUNT(*) AS NVARCHAR(MAX)), ' ', Make, 's')
        END AS CarsPassed,
        System.TimeStamp() AS AsaTime
    FROM
        Input TIMESTAMP BY Time
    GROUP BY
        Make,
        TumblingWindow(second, 10)

説明:CASE 式は、式を一連の単純な式と比較して結果を決定します。Explanation: The CASE expression compares an expression to a set of simple expressions to determine the result. この例では、カウント 1 の自動車は、カウントが 1 以外の自動車とは異なる文字列の説明を返します。In this example, vehicle makes with a count of 1 returned a different string description than vehicle makes with a count other than 1.

クエリの例:複数の出力にデータを送信するQuery example: Send data to multiple outputs

説明:1 つのジョブから複数の出力ターゲットにデータを送信します。Description: Send data to multiple output targets from a single job. たとえば、しきい値に基づくアラートのデータを分析し、すべてのイベントを Blob Storage にアーカイブします。For example, analyze data for a threshold-based alert and archive all events to blob storage.

入力:Input:

MakeMake TimeTime
HondaHonda 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
HondaHonda 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
ToyotaToyota 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
ToyotaToyota 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
ToyotaToyota 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

出力 1:Output1:

MakeMake TimeTime
HondaHonda 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
HondaHonda 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
ToyotaToyota 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
ToyotaToyota 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
ToyotaToyota 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

出力 2:Output2:

MakeMake TimeTime CountCount
ToyotaToyota 2015-01-01T00:00:10.0000000Z2015-01-01T00:00:10.0000000Z 33

解決策:Solution:

    SELECT
        *
    INTO
        ArchiveOutput
    FROM
        Input TIMESTAMP BY Time

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

説明:INTO 句は、Stream Analytics に対して、このステートメントからのデータを書き込む出力を指定します。Explanation: The INTO clause tells Stream Analytics which of the outputs to write the data to from this statement. 1 番目のクエリは、受け取ったデータを ArchiveOutput という名前の出力にパススルーします。The first query is a pass-through of the data received to an output named ArchiveOutput. 2 番目のクエリは、簡単な集計とフィルター処理を行い、結果を下流のアラート システム AlertOutput に送信します。The second query does some simple aggregation and filtering, and it sends the results to a downstream alerting system, AlertOutput.

また、共通テーブル式 (CTE) (WITH ステートメントなど) の結果を複数の出力ステートメントに再利用することもできます。Note that you can also reuse the results of the common table expressions (CTEs) (such as WITH statements) in multiple output statements. このオプションには、入力ソースに対して開くリーダーが少なくて済むという追加の利点があります。This option has the added benefit of opening fewer readers to the input source.

例:For example:

    WITH AllRedCars AS (
        SELECT
            *
        FROM
            Input TIMESTAMP BY Time
        WHERE
            Color = 'red'
    )
    SELECT * INTO HondaOutput FROM AllRedCars WHERE Make = 'Honda'
    SELECT * INTO ToyotaOutput FROM AllRedCars WHERE Make = 'Toyota'

クエリの例:一意の値をカウントするQuery example: Count unique values

説明:ストリームに出現するフィールドの、値ごとの件数を一定間隔でカウントします。Description: Count the number of unique field values that appear in the stream within a time window. たとえば、料金所を通過した自動車の、メーカーごとの台数を 2 秒間隔でカウントする場合などです。For example, how many unique makes of cars passed through the toll booth in a 2-second window?

入力:Input:

MakeMake TimeTime
HondaHonda 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
HondaHonda 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
ToyotaToyota 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
ToyotaToyota 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
ToyotaToyota 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z

出力:Output:

CountMakeCountMake 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

解決策:Solution:

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

説明: COUNT(DISTINCT Make) は、特定の時間枠内での、Make 列の個別の値の数を返します。Explanation: COUNT(DISTINCT Make) returns the number of distinct values in the Make column within a time window.

クエリの例:値が変化したかどうかを判定するQuery example: Determine if a value has changed

説明:前の値が現在の値と異なるかどうかを判定します。Description: Look at a previous value to determine if it is different than the current value. たとえば、前に有料道路を走っていた自動車のメーカーが現在の自動車と同じであるかどうかなどです。For example, is the previous car on the toll road the same make as the current car?

入力:Input:

MakeMake TimeTime
HondaHonda 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
ToyotaToyota 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z

出力:Output:

MakeMake TimeTime
ToyotaToyota 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z

解決策:Solution:

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

説明:LAG を使用して入力ストリームで 1 つ前のイベントを調べて、Make の値を取得します。Explanation: Use LAG to peek into the input stream one event back and get the Make value. 次に、現在のイベントの Make の値と比較し、異なる場合はイベントを出力します。Then compare it to the Make value on the current event and output the event if they are different.

クエリの例:期間内の最初のイベントを検索するQuery example: Find the first event in a window

説明:10 分間隔で最初の自動車を検索します。Description: Find the first car in every 10-minute interval.

入力:Input:

LicensePlateLicensePlate MakeMake TimeTime
DXE 5291DXE 5291 HondaHonda 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 FordFord 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
RMV 8282RMV 8282 HondaHonda 2015-07-27T00:05:01.0000000Z2015-07-27T00:05:01.0000000Z
YHN 6970YHN 6970 ToyotaToyota 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
VFE 1616VFE 1616 ToyotaToyota 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
QYF 9358QYF 9358 HondaHonda 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 BMWBMW 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

出力:Output:

LicensePlateLicensePlate MakeMake TimeTime
DXE 5291DXE 5291 HondaHonda 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
QYF 9358QYF 9358 HondaHonda 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z

解決策:Solution:

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

問題を変更して、10 分ごとに特定のメーカーの最初の自動車を検索します。Now let's change the problem and find the first car of a particular make in every 10-minute interval.

LicensePlateLicensePlate MakeMake TimeTime
DXE 5291DXE 5291 HondaHonda 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 FordFord 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
YHN 6970YHN 6970 ToyotaToyota 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
QYF 9358QYF 9358 HondaHonda 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 BMWBMW 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

解決策:Solution:

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

クエリの例:期間内の最後のイベントを検索するQuery example: Find the last event in a window

説明:10 分間隔で最後の自動車を検索します。Description: Find the last car in every 10-minute interval.

入力:Input:

LicensePlateLicensePlate MakeMake TimeTime
DXE 5291DXE 5291 HondaHonda 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z
YZK 5704YZK 5704 FordFord 2015-07-27T00:02:17.0000000Z2015-07-27T00:02:17.0000000Z
RMV 8282RMV 8282 HondaHonda 2015-07-27T00:05:01.0000000Z2015-07-27T00:05:01.0000000Z
YHN 6970YHN 6970 ToyotaToyota 2015-07-27T00:06:00.0000000Z2015-07-27T00:06:00.0000000Z
VFE 1616VFE 1616 ToyotaToyota 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
QYF 9358QYF 9358 HondaHonda 2015-07-27T00:12:02.0000000Z2015-07-27T00:12:02.0000000Z
MDR 6128MDR 6128 BMWBMW 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

出力:Output:

LicensePlateLicensePlate MakeMake TimeTime
VFE 1616VFE 1616 ToyotaToyota 2015-07-27T00:09:31.0000000Z2015-07-27T00:09:31.0000000Z
MDR 6128MDR 6128 BMWBMW 2015-07-27T00:13:45.0000000Z2015-07-27T00:13:45.0000000Z

解決策:Solution:

    WITH LastInWindow AS
    (
        SELECT 
            MAX(Time) AS LastEventTime
        FROM 
            Input TIMESTAMP BY Time
        GROUP BY 
            TumblingWindow(minute, 10)
    )
    SELECT 
        Input.LicensePlate,
        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

説明:クエリには 2 つの手順があります。Explanation: There are two steps in the query. 最初の手順では、10 分間隔で最新のタイムスタンプを検索します。The first one finds the latest time stamp in 10-minute windows. 2 番目の手順では、最初のクエリの結果と元のストリームを結合し、各期間で最後のタイムスタンプに一致するイベントを検索します。The second step joins the results of the first query with the original stream to find the events that match the last time stamps in each window.

クエリの例:ストリームで相関イベントを検索するQuery example: Locate correlated events in a stream

説明:ストリームで相関イベントを検索します。Description: Find correlated events in a stream. たとえば、同じ製造元の 2 台の自動車が、最後の 90 秒で続けて有料道路に進入したことを把握するには、どうすればよいのでしょうか。For example, have 2 consecutive cars from the same make entered the toll road within the last 90 seconds?

入力:Input:

MakeMake LicensePlateLicensePlate TimeTime
HondaHonda ABC-123ABC-123 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
HondaHonda AAA-999AAA-999 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z
ToyotaToyota DEF-987DEF-987 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z
HondaHonda GHI-345GHI-345 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z

出力:Output:

MakeMake TimeTime CurrentCarLicensePlateCurrentCarLicensePlate FirstCarLicensePlateFirstCarLicensePlate FirstCarTimeFirstCarTime
HondaHonda 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

解決策:Solution:

    SELECT
        Make,
        Time,
        LicensePlate AS CurrentCarLicensePlate,
        LAG(LicensePlate, 1) OVER (LIMIT DURATION(second, 90)) AS FirstCarLicensePlate,
        LAG(Time, 1) OVER (LIMIT DURATION(second, 90)) AS FirstCarTime
    FROM
        Input TIMESTAMP BY Time
    WHERE
        LAG(Make, 1) OVER (LIMIT DURATION(second, 90)) = Make

説明:LAG を使用して入力ストリームで 1 つ前のイベントを調べて、Make の値を取得します。Explanation: Use LAG to peek into the input stream one event back and get the Make value. これを現在のイベントの Make の値と比較し、同じ場合はイベントを出力します。Compare it to the MAKE value in the current event, and then output the event if they are the same. また、LAG を使用して前の自動車のデータを取得することもできます。You can also use LAG to get data about the previous car.

クエリの例:イベントの間隔を検出するQuery example: Detect the duration between events

説明:特定のイベントの間隔を検出します。Description: Find the duration of a given event. たとえば、Web クリック ストリームから、ある機能に費やされた時間を調べます。For example, given a web clickstream, determine the time spent on a feature.

入力:Input:

UserUser FeatureFeature EventEvent TimeTime
user@location.com RightMenuRightMenu 開始Start 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z
user@location.com RightMenuRightMenu EndEnd 2015-01-01T00:00:08.0000000Z2015-01-01T00:00:08.0000000Z

出力:Output:

UserUser FeatureFeature 時間Duration
user@location.com RightMenuRightMenu 77

解決策:Solution:

    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 関数を使用して、イベントの種類が Start であった最後の TIME 値を取得します。Explanation: Use the LAST function to retrieve the last TIME value when the event type was Start. LAST 関数は PARTITION BY [user] が使用し、一意のユーザーごとに結果が計算されることを示します。The LAST function uses PARTITION BY [user] to indicate that the result is computed per unique user. このクエリでは、Start イベントと Stop イベントの時間差の最大しきい値を 1 時間としていますが、必要に応じて構成可能です (LIMIT DURATION(hour, 1)The query has a 1-hour maximum threshold for the time difference between Start and Stop events, but is configurable as needed (LIMIT DURATION(hour, 1).

クエリの例:条件の期間を検出するQuery example: Detect the duration of a condition

説明:条件が発生していた時間の長さを調べます。Description: Find out how long a condition occurred. たとえば、バグのためにすべての自動車の重量が正しくない (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:

MakeMake TimeTime WeightWeight
HondaHonda 2015-01-01T00:00:01.0000000Z2015-01-01T00:00:01.0000000Z 20002000
ToyotaToyota 2015-01-01T00:00:02.0000000Z2015-01-01T00:00:02.0000000Z 2500025000
HondaHonda 2015-01-01T00:00:03.0000000Z2015-01-01T00:00:03.0000000Z 2600026000
ToyotaToyota 2015-01-01T00:00:04.0000000Z2015-01-01T00:00:04.0000000Z 2500025000
HondaHonda 2015-01-01T00:00:05.0000000Z2015-01-01T00:00:05.0000000Z 2600026000
ToyotaToyota 2015-01-01T00:00:06.0000000Z2015-01-01T00:00:06.0000000Z 2500025000
HondaHonda 2015-01-01T00:00:07.0000000Z2015-01-01T00:00:07.0000000Z 2600026000
ToyotaToyota 2015-01-01T00:00:08.0000000Z2015-01-01T00:00:08.0000000Z 20002000

出力:Output:

StartFaultStartFault EndFaultEndFault
2015-01-01T00:00:02.000Z2015-01-01T00:00:02.000Z 2015-01-01T00:00:07.000Z2015-01-01T00:00:07.000Z

解決策:Solution:

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

    SELECT 
        LAG(time) OVER (LIMIT DURATION(hour, 24) WHEN previousWeight < 20000 ) [StartFault],
        previousTime [EndFault]
    FROM SelectPreviousEvent
    WHERE
        [weight] < 20000
        AND previousWeight > 20000

説明:LAG を使用して 24 時間の入力ストリームに着目し、重量の上限を 20000 として、StartFaultStopFault の範囲で該当するインスタンスを探します。Explanation: Use LAG to view the input stream for 24 hours and look for instances where StartFault and StopFault are spanned by the weight < 20000.

クエリの例:欠落値を入力するQuery example: Fill missing values

説明:欠落値があるイベントのストリームの場合、定期的な間隔でイベントのストリームを生成します。Description: For the stream of events that have missing values, produce a stream of events with regular intervals. たとえば、最近検出されたデータ ポイントを報告する 5 秒ごとのイベントを生成します。For example, generate an event every 5 seconds that reports the most recently seen data point.

入力:Input:

tt valuevalue
"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):

windowendwindowend lastevent.tlastevent.t lastevent.valuelastevent.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

解決策:Solution:

    SELECT
        System.Timestamp() AS windowEnd,
        TopOne() OVER (ORDER BY t DESC) AS lastEvent
    FROM
        input TIMESTAMP BY t
    GROUP BY HOPPINGWINDOW(second, 300, 5)

説明:このクエリは、5 秒ごとにイベントを生成し、それまでに受信した最後のイベントを出力します。Explanation: This query generates events every 5 seconds and outputs the last event that was received previously. ホッピング ウィンドウ期間は、クエリが最新のイベントを検出するためにさかのぼる期間 (この例では 300 秒) を指定します。The Hopping window duration determines how far back the query looks to find the latest event (300 seconds in this example).

クエリの例:同じストリーム内で 2 つのイベントの種類を関連付けるQuery example: Correlate two event types within the same stream

説明:場合によっては、特定の期間中に発生した複数のイベントの種類に基づいて、アラートを生成する必要があります。Description: Sometimes alerts need to be generated based on multiple event types that occurred in a certain time range. たとえば、家庭用電子レンジの IoT シナリオにおいて、ファン温度が 40 未満、直近 3 分間の最大電力が 10 未満の場合にアラートを生成する必要があるとします。For example, in an IoT scenario for home ovens, an alert must be generated when the fan temperature is less than 40 and the maximum power during the last 3 minutes is less than 10.

入力:Input:

timetime deviceIddeviceId sensorNamesensorName valuevalue
"2018-01-01T16:01:00""2018-01-01T16:01:00" "Oven1""Oven1" "temp""temp" 120120
"2018-01-01T16:01:00""2018-01-01T16:01:00" "Oven1""Oven1" "power""power" 1515
"2018-01-01T16:02:00""2018-01-01T16:02:00" "Oven1""Oven1" "temp""temp" 100100
"2018-01-01T16:02:00""2018-01-01T16:02:00" "Oven1""Oven1" "power""power" 1515
"2018-01-01T16:03:00""2018-01-01T16:03:00" "Oven1""Oven1" "temp""temp" 7070
"2018-01-01T16:03:00""2018-01-01T16:03:00" "Oven1""Oven1" "power""power" 1515
"2018-01-01T16:04:00""2018-01-01T16:04:00" "Oven1""Oven1" "temp""temp" 5050
"2018-01-01T16:04:00""2018-01-01T16:04:00" "Oven1""Oven1" "power""power" 1515
"2018-01-01T16:05:00""2018-01-01T16:05:00" "Oven1""Oven1" "temp""temp" 3030
"2018-01-01T16:05:00""2018-01-01T16:05:00" "Oven1""Oven1" "power""power" 88
"2018-01-01T16:06:00""2018-01-01T16:06:00" "Oven1""Oven1" "temp""temp" 2020
"2018-01-01T16:06:00""2018-01-01T16:06:00" "Oven1""Oven1" "power""power" 88
"2018-01-01T16:07:00""2018-01-01T16:07:00" "Oven1""Oven1" "temp""temp" 2020
"2018-01-01T16:07:00""2018-01-01T16:07:00" "Oven1""Oven1" "power""power" 88
"2018-01-01T16:08:00""2018-01-01T16:08:00" "Oven1""Oven1" "temp""temp" 2020
"2018-01-01T16:08:00""2018-01-01T16:08:00" "Oven1""Oven1" "power""power" 88

出力:Output:

eventTimeeventTime deviceIddeviceId temptemp alertMessagealertMessage maxPowerDuringLast3minsmaxPowerDuringLast3mins
"2018-01-01T16:05:00""2018-01-01T16:05:00" "Oven1""Oven1" 3030 "発熱体がショートしています""Short circuit heating elements" 1515
"2018-01-01T16:06:00""2018-01-01T16:06:00" "Oven1""Oven1" 2020 "発熱体がショートしています""Short circuit heating elements" 1515
"2018-01-01T16:07:00""2018-01-01T16:07:00" "Oven1""Oven1" 2020 "発熱体がショートしています""Short circuit heating elements" 1515

解決策:Solution:

WITH max_power_during_last_3_mins AS (
    SELECT 
        System.TimeStamp() AS windowTime,
        deviceId,
        max(value) as maxPower
    FROM
        input TIMESTAMP BY t
    WHERE 
        sensorName = 'power' 
    GROUP BY 
        deviceId, 
        SlidingWindow(minute, 3) 
)

SELECT 
    t1.t AS eventTime,
    t1.deviceId, 
    t1.value AS temp,
    'Short circuit heating elements' as alertMessage,
    t2.maxPower AS maxPowerDuringLast3mins
    
INTO resultsr

FROM input t1 TIMESTAMP BY t
JOIN max_power_during_last_3_mins t2
    ON t1.deviceId = t2.deviceId 
    AND t1.t = t2.windowTime
    AND DATEDIFF(minute,t1,t2) between 0 and 3
    
WHERE
    t1.sensorName = 'temp'
    AND t1.value <= 40
    AND t2.maxPower > 10

説明:1 つ目の max_power_during_last_3_mins クエリでは、スライディング ウィンドウを使用して、すべてのデバイスの電力センサーの最大値 (直近 3 分間) を特定しています。Explanation: The first query max_power_during_last_3_mins, uses the Sliding window to find the max value of the power sensor for every device, during the last 3 minutes. 2 つ目のクエリでは、1 つ目のクエリと結合して、現在のイベントに関連する直近のウィンドウの電力値を特定しています。The second query is joined to the first query to find the power value in the most recent window relevant for the current event. その後、条件が満たされた場合には、デバイスについてのアラートが生成されます。And then, provided the conditions are met, an alert is generated for the device.

クエリの例:デバイスのクロックのずれ (サブストリーム) と関係なくイベントを処理するQuery example: Process events independent of Device Clock Skew (substreams)

説明:イベント プロデューサー間またはパーティション間のクロックのずれや、ネットワーク待機時間が原因でイベントが遅れて、あるいは順序がずれて到着することがあります。Description: 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 秒遅れています。In the following 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.

入力:Input:

LicensePlateLicensePlate MakeMake TimeTime TollIDTollID
DXE 5291DXE 5291 HondaHonda 2015-07-27T00:00:01.0000000Z2015-07-27T00:00:01.0000000Z 11
YHN 6970YHN 6970 ToyotaToyota 2015-07-27T00:00:05.0000000Z2015-07-27T00:00:05.0000000Z 11
QYF 9358QYF 9358 HondaHonda 2015-07-27T00:00:01.0000000Z2015-07-27T00:00:01.0000000Z 22
GXF 9462GXF 9462 BMWBMW 2015-07-27T00:00:04.0000000Z2015-07-27T00:00:04.0000000Z 22
VFE 1616VFE 1616 ToyotaToyota 2015-07-27T00:00:10.0000000Z2015-07-27T00:00:10.0000000Z 11
RMV 8282RMV 8282 HondaHonda 2015-07-27T00:00:03.0000000Z2015-07-27T00:00:03.0000000Z 33
MDR 6128MDR 6128 BMWBMW 2015-07-27T00:00:11.0000000Z2015-07-27T00:00:11.0000000Z 22
YZK 5704YZK 5704 FordFord 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

解決策:Solution:

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

説明:TIMESTAMP BY OVER 句は、サブストリームを使用して各デバイスのタイムラインで個別に検索します。Explanation: The TIMESTAMP BY OVER clause looks at each device timeline separately using substreams. 各 TollID の出力イベントは計算されると同時に生成され、同じクロックをすべてのデバイスが参照しているかのように順序が変更されるのではなく、各 TollID ごとにイベントが順序付けられます。The output events for each TollID are 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.

クエリの例:期間内の重複するイベントを削除するQuery example: Remove duplicate events in a window

説明:特定の期間内のイベントに対する平均の計算などの操作を実行するときは、重複するイベントを除外する必要があります。Description: When performing an operation such as calculating averages over events in a given time window, duplicate events should be filtered. 次の例では、2 番目のイベントは最初のイベントの重複です。In the following example, the second event is a duplicate of the first.

入力:Input:

deviceIdDeviceId TimeTime 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

解決策:Solution:

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 列の個別の値の数を返します。Explanation: COUNT(DISTINCT Time) returns the number of distinct values in the Time column within a time window. その後、このステップの出力を使用し、重複を除去することによって、デバイスあたりの平均を計算できます。You can then use the output of this step to compute the average per device by discarding duplicates.

ジオフェンシングおよび地理空間クエリGeofencing and geospatial queries

Azure Stream Analytics には、フリート管理、ライド シェア、コネクテッド カー、および資産追跡などのシナリオを実装するために使用できる、組み込みの地理空間関数が用意されています。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. 詳細については、「Azure Stream Analytics を使用したジオフェンシングおよび地理空間集計のシナリオ」を参照してください。For more information, refer to the Geofencing and geospatial aggregation scenarios with Azure Stream Analytics article.

JavaScript と C# による言語の拡張性Language extensibility through JavaScript and C#

Azure Stream Ananlytics のクエリ言語は、JavaScript または C# 言語で記述されたカスタム関数を使用して拡張できます。Azure Stream Ananlytics query langugae can be extended with custom functions written in JavaScript or C# languages. 詳細については、次の記事を参照してください。For more information see the foolowing articles:

問い合わせGet help

さらにサポートが必要な場合は、 Azure Stream Analytics フォーラムを参照してください。For further assistance, try our Azure Stream Analytics forum.

次のステップNext steps