Output multiple records from single select with Stream Analytics

Question

Is it possible to output multiple records dynamicly from a single select in Stream Analytics?

For example, I receive events in the following format:

{
    "value": 600,
    "duration": 300
}

duration is in seconds

How can I output a record for every second with the average value for the duration? Something like below, only 300 times?

SELECT 
    value / duration AS value
    1 AS duration
FROM Input

Returning a array record will be fine as well I think, because it might be possible to convert this into separate events using CROSS APPLY GetArrayElements

Answer 1

This is not directly possible in Streaming Analytics query language today - stay tuned for the upcoming extensibility improvements.

You may be able to use HoppingWindow to produce regular events (e.g. every second) and then JOIN with your stream to generate events every second with the condition that they are no more than [duration] seconds before incoming event.

Something like this:

WITH
InputWithTimestamp AS
(
    SELECT 
        System.Timestamp as timestamp, [value], [duration]
    FROM input 
),
RegularEvents AS
(
    SELECT
        System.Timestamp as timestamp, 
        count()
    FROM InputWithTimestamp
    GROUP BY HoppingWindow(second, 1000, 1) -- assuming at least one event arrives evry 1000 seconds
)

SELECT 
    RegularEvents.timestamp,
    InputWithTimestamp.value/InputWithTimestamp.[duration] as [value]

 FROM
    RegularEvents
JOIN
    InputWithTimestamp
ON DATEDIFF(second, RegularEvents, InputWithTimestamp) BETWEEN 0 AND 1000 -- assuming 1000 is max possible duration
AND DATEDIFF(second, RegularEvents.timestamp, InputWithTimestamp.timestamp) BETWEEN 0 AND InputWithTimestamp.[duration]