Stream Analytics Query

Question

My requirement

I want to calculate the time for the car into the tunnel for that, I have JSON like (I have populated this JSON into Stream Analytics input job)

[{
    CarId: 1,
    Time: "2017-03-08T16:20:00.0000000",
    CarNum: "XYZ"
},{
    CarId: 2,
    Time: "2017-03-08T16:25:00.0000000",
    CarNum: "ABC"
},{
    CarId: 1,
    Time: "2017-03-08T16:27:00.0000000",
    CarNum: "XYZ"
},{
    CarId: 1,
    Time: "2017-03-08T16:30:00.0000000",
    CarNum: "XYZ"
},{
    CarId: 1,
    Time: "2017-03-08T16:35:00.0000000",
    CarNum: "XYZ"
},
]

Now Minimum time for the car to be into the tunnel is 1 minute and the maximum time is 10 min.

Based on input it is 2 visits for CarId 1 and 0 visit for CarId 2

Can anyone help me to write the query?

Peter Pan · Accepted Answer · 2017-03-22 06:51:16Z

1

In your sample data, the combine of CarId & CarNum seems to be repeated. Based on Stream Analytics Query language which is just a subset of T-SQL, the query you want is implemented hardly.

Per my experience, here is my sample query for similar scenario with yours.

If the json data like below,

[{
    CarId: 1,
    Time: "2017-03-08T16:20:00.0000000",
    CarNum: "XYZ"
},{
    CarId: 2,
    Time: "2017-03-08T16:25:00.0000000",
    CarNum: "ABC"
},{
    CarId: 1,
    Time: "2017-03-08T16:27:00.0000000",
    CarNum: "XYZ"
},{
    CarId: 1,
    Time: "2017-03-08T16:30:00.0000000",
    CarNum: "DEF"
},{
    CarId: 1,
    Time: "2017-03-08T16:35:00.0000000",
    CarNum: "DEF"
},
]

that the combine of CarId & CarNum is unique Within a period of time, the query for Stream Analytics as below.

SELECT
    CarId,
    CarNum,
    LAG(Time, 1) OVER (PARTITION BY CarId, CarNum LIMIT DURATION(MINUTE, 10)) AS startime,
    Time AS endtime,
    DATEDIFF(second, LAG(Time, 1) OVER (PARTITION BY CarId, CarNum LIMIT DURATION(MINUTE, 10)), Time) AS time
FROM
    [YourInputAlias]
WHERE LAG(CarId, 1) OVER (PARTITION BY CarId, CarNum limit LIMIT DURATION(MINUTE, 10)) IS NOT NULL

answered Mar 22, 2017 at 6:51

Peter Pan

24.2k4 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hardikrshah28 Over a year ago

Thanks Peter Pan, the query is not as per my requirement but it help me a lot.

Collectives™ on Stack Overflow

Stream Analytics Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related