Question 109 of 170 from exam DP-200: Implementing an Azure Data Solution
Question
HOTSPOT -
You are processing streaming data from vehicles that pass through a toll booth.
You need to use Azure Stream Analytics to return the license plate, vehicle make, and hour the last vehicle passed during each 10-minute window.
How should you complete the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Explanations
Box 1: MAX -
The first step on the query finds the maximum time stamp in 10-minute windows, that is the time stamp of the last event for that window. The second step joins the results of the first query with the original stream to find the event that match the last time stamps in each window.
Query:
WITH LastInWindow AS -
(
SELECT -
MAX(Time) AS LastEventTime -
FROM -
Input TIMESTAMP BY Time -
GROUP BY -
TumblingWindow(minute, 10)
)
SELECT -
Input.License_plate,
Input.Make,
Input.Time -
FROM -
Input TIMESTAMP BY Time -
INNER JOIN LastInWindow -
ON DATEDIFF(minute, Input, LastInWindow) BETWEEN 0 AND 10
AND Input.Time = LastInWindow.LastEventTime
Box 2: TumblingWindow -
Tumbling windows are a series of fixed-sized, non-overlapping and contiguous time intervals.
Box 3: DATEDIFF -
DATEDIFF is a date-specific function that compares and returns the time difference between two DateTime fields, for more information, refer to date functions.
https://docs.microsoft.com/en-us/stream-analytics-query/tumbling-window-azure-stream-analytics