流分析，如果一段时间内发生一次，就会获取价值

Question

下面是一个查询示例，我正试图完成它：

WITH IoTData AS
( SELECT *,
CAST(iothub.time AS datetime) AS time,
TRY_CAST(iothub.value AS float) AS value,
ref.AssetSignalTag AS assetsignaltag
FROM iothub TIMESTAMP BY iothub.time
JOIN masterdatasql ref 
ON ref.[ConnectionString] = iothub.id
WHERE iothub.value IS NOT NULL
),
HISTORY AS ( 
SELECT assetsignaltag,
value,
COUNT(*) AS Count
CASE
WHEN value = 1 THEN 1
ELSE 0
END AS ConditionResult
FROM IoTData

GROUP BY assetsignaltag, SlidingWindow(minute, 30), value
)

SELECT
time,
value,
assetsignaltag,
HISTORY.ConditionResult,
HISTORY.Count 
INTO eventhub
FROM IoTData 
JOIN HISTORY
ON HISTORY.assetsignaltag = IoTData.assetsignaltag
AND DATEDIFF(minute, IoTData, HISTORY) = 10
WHERE HISTORY.ConditionResult = 1
AND MAX(HISTORY.Count) OVER (PARTITION BY IoTData.assetsignaltag LIMIT DURATION (minute, 10)) = 1
/* Here is where I run into issues, because I'm not sure how to grab the value of 'over last 10 minutes if there was only 1 value'.
Also tried AND HISTORY.Count = 1 which makes more sense to me since I'm already defining the time of 10 minutes in the DATEDIFF above, but I'm not sure that the datetime function does what I think it does.
*/

问题是，这将在流分析语法方面被接受，但不会起作用。我试图从本质上使一个"嘈杂"的数据点不那么嘈杂。我们真的不在乎它是否在2分钟内翻到1比0比1比0，但如果它翻到1并在那里停留10分钟，我们希望它进入事件中心，从而触发警报。

让我知道我是否可以澄清这方面的任何事情，或者我是否应该尝试任何特别的事情。

Answer 1

我可能不完全理解您想要进行的计算，但似乎可以跳过连接，只使用LAG。

HISTORY步骤创建一个流；对于IoTData中的每个事件，回顾30分钟并计数每个资产信号标签的事件数和1或0值"；。

如果你只想在最后10分钟内事件数量正好为1时得到这样的结果，你可以：

SELECT *
FROM HISTORY
WHERE Count() OVER (LIMIT DURATION(minute, 10)) = 1