かずきのBlog@hatena

すきな言語は C# + XAML の組み合わせ。Azure Functions も好き。最近は Go 言語勉強中。日本マイクロソフトで働いていますが、ここに書いていることは個人的なメモなので会社の公式見解ではありません。

Stream Analytics練習

2つのセンサーから同じ場所のちょっと違う場所のある測定値を送ってもらってる。

ちょっと違う場所はInput1, Input2という別々のイベントハブに送られてるものとする。

そんなときに、3分間隔で最大値を比べてどれくらいの差があるかどうか…。

WITH Step1 AS (
    SELECT
        LocationId,
        CollectTop(1) OVER (ORDER BY Value DESC) AS [Record]
    FROM
        [Input1] TIMESTAMP BY Time
    GROUP BY TumblingWindow(minute, 3), LocationId
),
Step2 AS (
    SELECT
        LocationId,
        CollectTop(1) OVER (ORDER BY Value DESC) AS [Record]
    FROM
        [Input2] TIMESTAMP BY Time
    GROUP BY TumblingWindow(minute, 3), LocationId
),
Step1Flat AS (
    SELECT
        s1.LocationId,
        flat.ArrayValue.Value.Value,
        flat.ArrayValue.Value.Time
    FROM Step1 AS s1 CROSS APPLY GetElements(s1.[Record]) as flat
),
Step2Flat AS (
    SELECT
        s2.LocationId,
        flat.ArrayValue.Value.Value,
        flat.ArrayValue.Value.Time
    FROM Step2 AS s2 CROSS APPLY GetElements(s2.[Record]) as flat
)
SELECT
    s1.LocationId
    , s1.Value - s2.Value AS Diff
    , s1.Time AS Time
INTO Output
FROM
    Step1Flat AS s1
    JOIN Step2Flat AS s2 ON DATEDIFF(minute, s1, s2) BETWEEN 0 AND 1 AND s1.LocationId = s2.LocationId

チョーむずいんだけど。