かずきのBlog@hatena

日本マイクロソフトに勤めています。XAML + C#の組み合わせをメインに、たまにASP.NETやJavaなどの.NET系以外のことも書いています。掲載内容は個人の見解であり、所属する企業を代表するものではありません。

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でフラット化しています。割とあっさりできましたね。