azure - 如何从两个Windows获取最后一个事件的差异？

Question

我们从 IoT 中心获取事件作为输入，格式为:

[ {

  "events":[
     {
        "key":"Test1",
        "value":1345.2270720045908
     },
     {
        "key":"Test2",
        "value":1882.9263119959833
     }
  ],
  "EventProcessedUtcTime":"2016-06-29T08:39:53.9293808Z",
  "PartitionId":2,
  "EventEnqueuedUtcTime":"2016-06-29T08:39:18.7320000Z",     

}, {

  "events":[
     {
        "key":"Test1",
        "value":1456.6747534295491
     },
     {
        "key":"Test2",
        "value":1785.4095907491446
     }
  "EventProcessedUtcTime":"2016-06-29T08:49:53.9450060Z",
  "PartitionId":2,
  "EventEnqueuedUtcTime":"2016-06-29T08:49:19.0810000Z"    

} ……………………]

每 10 分钟，我们就会从 IoTHub 获取一批值。我们想要获得每小时的“Test1”差异。与 Test1 3:00 – 4:00 内的最后一个值为 600 一样，4:00 – 5:00 内的最后一个值为 800 ，因此差为800 – 600 = 200，这就是我们想要的。

首先，我们要使用持续时间1小时的TumblingWindow，从当前值获取最后一个值，并获取Pervious Window Last value， strong>然后使用当前的最后一个值-上一个值，但是我们没有成功获取上一个最后一个值，所以无论如何要获取上一个窗口中的LastValue吗？

其次，我们要使用HoppingWindow，持续时间为60分钟，与上一个窗口重叠10分钟，然后我们使用>TopOne() 通过 * **asc 和 desc 加上时间戳来获取 First 和 Last 差异，但是没有得到我们想要的，一小时内得到两个以上的值。

而且我发现我们不能在查询中使用更多WITH关键字，所以不太方便，我们从事件中获取值，我们需要使用CrossApply Get Elements(Input .Events)获取所有事件，使用“WITH”我们使用LAG(Value)函数来获取之前的值，但是我们不能使用LAG(events.arrayvalue.value)，它不支持，所以我们想使用另一个查询，也不支持。

那么我们有什么方法可以用来获取两个窗口差异中的最后一个值吗？因此，如果您有解决方案，非常感谢!

Answer 1

首先，以下陈述部分正确:

And I found that we cannot use more WITH keyword in the query, so it is not convenient, we get the Value from the Events, we need use the CrossApply Get Elements(Input.Events) get all events, use “WITH” we use LAG(Value) function to get the previous value, but we cannot use the LAG(events.arrayvalue.value), it does not supported, so we want to use another query, also does not supported.

虽然您只能使用单个 WITH 关键字，但您可以创建多个步骤，如下所示:

WITH 
    FirstStep AS (SELECT * FROM Input),
    SecondStep AS (SELECT * FROM FirstStep)

SELECT * INTO Output FROM SecondStep

First, we want use the TumblingWindow with 1hour duration, get the Last Value from the current value, and get the Pervious Window Last value, then use the current last value – previous value, but we did not get the Previous Last Value succeed, so is anyway to get the LastValue in Previous Window?

您首先需要展平输入，以便它可以与 LAG 和 LAST 关键字一起使用。您已经使用 CrossApply 执行此操作。我假设您想要此处值的“SUM”，但是当您需要其他内容时可以更改聚合函数。

HourlyAggregate AS SELECT 
   key,
   SUM(value) AS value
FROM CrossAppliedStep
GROUP BY key, TumblingWindow(hour, 1)

然后您应该能够回顾流以获取之前的值

LookBack AS SELECT
    key, 
    LAST(value) OVER (PARTITION BY key LIMIT DURATION(hour, 1)) AS previous_value,
    value AS current_value
FROM HourlyAggregate

那么计算增量就很容易

SELECT 
    key,
    value - previous_value AS delta
INTO Output
FROM LookBack
WHERE Key = 'Test1'

Second, we want to use the HoppingWindow with Duration is 60Min, overlap is 10min with previous window, then we use the TopOne() with time stamp by * **asc and desc to get the First and Last value , then get the difference, but did not get we wanted, get more than two values during one hour.

这是正确的输出，10分钟的HoppingWindow，将每10分钟输出一条记录，但包含最后60分钟的数据。