Azure 流分析 - 您可以查询最近的非空值吗? [英] Azure stream analytics - can you query for most recent non null values?

问题描述

我有一个 Azure 流分析作业，其中包含来自 IoT 中心的输入数据，我正在将此数据发送到 Power BI.数据正在使用 pub/sub 更新，因此每条消息只更新一个值.出于这个原因，我的输入数据看起来像这样(MessageID 5 是最近的消息):

<头>

消息ID	每分钟转数	温度	压力
5	800	空	空
4	空	50	空
3	空	空	4
2	空	23	空
1	900	空	空

我想在 Power bi 仪表板和实时固定到仪表板的报告上显示最新的非空值.在这个例子中，我想要一张卡片或仪表来显示每个变量的最新非空值，所以

RPM = 800,温度 = 50，并且压力 = 4.

默认情况下，Power BI 仪表板读取最新值，因此如果该值不是刚刚更新(并且在表中为 null)，仪表板将显示 (Blank) 作为值.

是否有 SQL 查询要从我的 Azure 流分析作业中写入以获得每个变量的最新非空值?或者其他一些解决方法?

谢谢

解决方案

ASA 的一个好处是您可以在 ，语法可能看起来有点复杂，但它很简单.因为它需要 PARTITION BY，所以我必须明确说明，如果你想在未来扩展，这是一个很好的做法.

根据您的要求，您可以使用 MAX、MIN 或 AVG 使其更简单.

你可以看看此处为其他 ASA 查询模式.

I have an Azure stream analytics job with input data from an IoT Hub and I'm sending this data to Power BI. The data is being updated using pub/sub, so only one value gets updated per message. For this reason, my input data looks something like this (MessageID 5 is the most recent message):

MessageID	RPM	Temperature	Pressure
5	800	null	null
4	null	50	null
3	null	null	4
2	null	23	null
1	900	null	null

I would like to show the latest non-null value on a power bi dashboard and on a report pinned live to a dashboard. With this example, I'd like a card or gauge to display the most recent not null value for each variable, so

RPM = 800,
Temperature = 50, and
Pressure = 4.

By default, the Power BI dashboard reads the most recent value, so if the value was not just updated (and was null in the table), the dashboard displays (Blank) as the value.

Is there a SQL query to write from my Azure Stream Analytics job to get the most recent non-null value of each variable? Or some other workaround?

Thanks

解决方案

One of the benefits of ASA is that you can group events on a time window:

Here, if I understand correctly, when you look at event one at time, you only get 1 data point. The way to solve this is to look at a window of time instead, and project all the data points in a single output event.

Let's say you should receive 1 data point each every 10 seconds. You can then write a query that will GROUP BY all events on that time window, and output a result with all 4:

WITH dataPrep AS (
SELECT
    CAST(GetMetadataPropertyValue(IoTHub, 'IoTHub.EnqueuedTime') AS datetime) AS eventDateTime,
    GetMetadataPropertyValue(IoTHub, 'IoTHub.ConnectionDeviceId')  AS DeviceId,
    *
FROM IoTHub
)

SELECT 
    System.Timestamp() AS DateTime,
    DeviceId,
    LAST(RPM) OVER (PARTITION BY DeviceId LIMIT DURATION(second, 20) WHEN RPM IS NOT NULL),
    LAST(FuelRate) OVER (PARTITION BY DeviceId LIMIT DURATION(second, 20) WHEN FuelRate IS NOT NULL),
    LAST(DischargePressure) OVER (PARTITION BY DeviceId LIMIT DURATION(second, 20) WHEN DischargePressureIS NOT NULL),  
    LAST(SystemPressure) OVER (PARTITION BY DeviceId LIMIT DURATION(second, 20) WHEN SystemPressure IS NOT NULL)   

INTO
    powerbioutput
FROM
    dataPrep TIMESTAMP BY eventDateTime
GROUP BY
    DeviceId,
    TumblingWindow(second,10)
WHERE
    DeviceId = 'rpi1'

Here I used LAST, the syntax may look a bit complicated but it's straightforward. Since it requires PARTITION BY, I had to make it explicit, which is a good practice anyway if you ever want to scale in the future.

You can make it simpler by using MAX, MIN or AVG, depending on your requirements.

You can look here for other ASA query patterns.

这篇关于Azure 流分析 - 您可以查询最近的非空值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！