Query Pattern of the Week: Specify logic for different cases


Last week we kicked off a new blog series to highlight a query pattern with a real world example every week.  This week we examine how to use CASE statements to specify conditional logic.  For more query patterns, check out the Common Stream Analytics Query Patterns page.

Description: Evaluate one of multiple results based on a list of if/then/else conditions.

Example: Suppose you have a data stream of vehicles crossing a toll bridge.  Provide a string description of how many cars of each make have been recorded in a 10 second interval, with a special case for 1.

Input:

Make

Time

Honda

2015-01-01T00:00:01.0000000Z

Toyota

2015-01-01T00:00:02.0000000Z

Toyota

2015-01-01T00:00:03.0000000Z

 

Test File:

Download from GitHub: CarMakes.json

Query:

SELECT

    CASE

       WHEN COUNT(*) = 1 THEN CONCAT('1 ', Make)

       ELSE CONCAT(CAST(COUNT(*) AS NVARCHAR(MAX)), ' ', Make, 's')

    END AS CarsPassed,

    System.TimeStamp AS Time

FROM

    Input TIMESTAMP BY Time

GROUP BY

    Make,

    TumblingWindow(second, 10)

 

Output:

CarsPassed

Time

1 Honda

2015-01-01T00:00:10.0000000Z

2 Toyotas

2015-01-01T00:00:10.0000000Z

 

Explanation: The CASE statement allows us to provide a different computation based on some criteria (in our case the count of cars in the aggregate window).

Comments (0)

Skip to main content