ISFIRST, LAG, AND LAST – Data Sources and Ingestion
The ISFIRST analytic function returns a 1 if the event is the first event in the stream for the defined interval; otherwise, it returns 0. The implementation of the tumbling window function is performed as default. The following is the syntax to achieve this, followed by some example output:
SELECT
READINGTYPE, READINGTIMESTAMP, ISFIRST(second, 10) as FIRST
FROM brainwaves
+————————–+——————+——-+
| READINGTYPE | READINGTIMESTAMP | FIRST |
+————————–+——————+——-|
| 2022-03-17T14:00:01.2006 | Brainjammer-POW | 1 |
| 2022-03-17T14:00:02.0209 | Brainjammer-POW | 0 |
| 2022-03-17T14:00:03.2011 | Brainjammer-POW | 0 |
| 2022-03-17T14:00:09.1212 | Brainjammer-POW | 0 |
+————————–+——————+——-+
The LAG analytic function enables you to retrieve the previous event in the data stream. LAST enables you to look up the most recent event in the stream for a given time window.
DATA TYPES
There is a rather small set of supported data types. However, given the use case of Azure Stream Analytics, all types are usually not necessary, for example, when you are using the CAST clause.
SELECT READINGTYPE, CAST(AF3THETA AS FLOAT) as AF3THETA
FROM brainwaves
Table 3.21 lists the supported data types.
TABLE 3.21 Azure Stream Analytics data types
| Data type | Description |
| array | Ordered collection of values; values must be supported data type. |
| bigint | Integers between ‐263 and 263 −1. |
| bit | Integer with a value of either 1, 0, or NULL. |
| datetime | A date that is combined with a time of day. |
| float | Values: ‐1.79E+308 to ‐2.23E‐308, 0, and 2.23E‐308 to 1.79E+308. |
| nvarchar(max) | Text values. |
| record | Set of name‐value pairs; values must be a supported data type. |
There is also a SQL clause, TRY_CAST, as shown in the following SQL snippet:
SELECT READINGTYPE, CAST(AF3THETA AS FLOAT) as AF3THETA
FROM brainwaves
WHERE TRY_CAST(READINGTIMESTAMP AS datetime) IS NOT NULL
The TRY_CAST clause returns the data value if the CAST succeeds; otherwise, it returns a NULL.
QUERY LANGUAGE ELEMENTS
The supported query language clauses and commands in Azure Stream Analytics are some of the more powerful ones. The more complicated ones are covered in Chapter 2. SQL commands and clauses like GROUP BY, HAVING, INTO, JOIN, OVER, UNION, and WITH are all supported. They should be enough to provide access to all the data being streamed to your Azure Stream Analytics job.