かずきのBlog@hatena

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

Stream Analyticsで指定した時間内で最大の値を持つレコードを抜き出す

サンプルデータはこんな感じで。

[
  {
    "Id": 0,
    "Value": 10,
    "Time": "2015-08-30T09:00:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 11,
    "Time": "2015-08-30T09:01:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 12,
    "Time": "2015-08-30T09:02:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 13,
    "Time": "2015-08-30T09:03:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 14,
    "Time": "2015-08-30T09:04:00.0000000Z"
  },
  {
    "Id": 1,
    "Value": 20,
    "Time": "2015-08-30T09:04:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 15,
    "Time": "2015-08-30T09:05:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 16,
    "Time": "2015-08-30T09:05:30.0000000Z"
  },
  {
    "Id": 0,
    "Value": 17,
    "Time": "2015-08-30T09:06:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 18,
    "Time": "2015-08-30T09:07:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 19,
    "Time": "2015-08-30T09:08:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 20,
    "Time": "2015-08-30T09:09:00.0000000Z"
  }
]

この中から5分単位で各Idの最大のValueを持つレコードを抜き出してみたいと思います。

こんなクエリでいけました。

WITH Step1 AS (
    SELECT
        CollectTop(1) OVER (ORDER BY Value DESC) AS MaxRecord
    FROM
        Input TIMESTAMP BY Time
    GROUP BY TumblingWindow(minute, 5), Id
)
SELECT
    f.ArrayValue.Value.Id,
    f.ArrayValue.Value.Value,
    f.ArrayValue.Value.Time
FROM 
    Step1 CROSS APPLY GetElements(Step1.MaxRecord) AS f

Step1のクエリで5分単位にIdでグルーピングしてCollectTopでValueが最大のものを抜き取ります。CollectTopしたままだと配列の配列みたいな感じになってるので続けてCROSS APPLYでフラット化しています。割とあっさりできましたね。