CODEWITHSUNDEEP
azure-stream-analytics
Hardikrshah28
Hardikrshah28

Reputation: 126

Stream Analytics Query

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?

Upvotes: 1

Views: 273

Answers (1)

Peter Pan
Peter Pan

Reputation: 24128

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

Upvotes: 1

Related Questions