azure Stream analytics isfirst and last query

Question

I have the payload something like below. I need to get the first distinct batch values for every 1 minute period. Please let me know how to achieve this in stream analytics using isfirst and lag or last

Output like:

BATCH=01,"2015-01-01T00:00:01.0000000Z" BATCH=02,"2015-01-01T00:00:03.0000000Z" BATCH=03,"2015-01-01T00:00:06.0000000Z" BATCH=01,"2015-01-01T00:00:14.0000000Z" BATCH=02,"2015-01-01T00:00:18.0000000Z" BATCH=03,"2015-01-01T00:00:22.0000000Z" BATCH=01,"2015-01-01T00:00:27.0000000Z" BATCH=01,"2015-01-01T00:00:31.0000000Z"

Pay Load:
    [{
            "Payload": {
                "Make": "BATCH1",
                "VAL": "01",
                "TS": "2015-01-01T00:00:01.0000000Z"
            }
    },
    {
    "Payload": {
            "Make": "BATCH1",
            "VAL": "01",
            "TS": "2015-01-01T00:00:02.0000000Z"
        }
    },
    {
        "Payload": {
            "Make": "BATCH1",
            "VAL": "02",
            "TS": "2015-01-01T00:00:03.0000000Z"
        }
    },
    {
        "Payload": {
            "Make": "BATCH1",
            "VAL": "02",
            "TS": "2015-01-01T00:00:04.0000000Z"
        }
    },
    {
        "Payload": {
            "Make": "BATCH1",
            "VAL": "02",
            "TS": "2015-01-01T00:00:05.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH1",
            "VAL": "03",
            "TS": "2015-01-01T00:00:06.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH1",
            "VAL": "03",
            "TS": "2015-01-01T00:00:07.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH1",
            "VAL": "03",
            "TS": "2015-01-01T00:00:10.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH1",
            "VAL": "03",
            "TS": "2015-01-01T00:00:11.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH1",
            "VAL": "03",
            "TS": "2015-01-01T00:00:12.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH2",
            "VAL": "01",
            "TS": "2015-01-01T00:00:13.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH2",
            "VAL": "01",
            "TS": "2015-01-01T00:00:14.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH2",
            "VAL": "01",
            "TS": "2015-01-01T00:00:15.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH2",
            "VAL": "01",
            "TS": "2015-01-01T00:00:16.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH2",
            "VAL": "01",
            "TS": "2015-01-01T00:00:17.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH2",
            "VAL": "02",
            "TS": "2015-01-01T00:00:18.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH2",
            "VAL": "02",
            "TS": "2015-01-01T00:00:20.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH2",
            "VAL": "02",
            "TS": "2015-01-01T00:00:21.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH3",
            "VAL": "02",
            "TS": "2015-01-01T00:00:22.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH3",
            "VAL": "02",
            "TS": "2015-01-01T00:00:23.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH3",
            "VAL": "02",
            "TS": "2015-01-01T00:00:24.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH3",
            "VAL": "02",
            "TS": "2015-01-01T00:00:25.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH3",
            "VAL": "02",
            "TS": "2015-01-01T00:00:26.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH4",
            "VAL": "01",
            "TS": "2015-01-01T00:00:27.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH4",
            "VAL": "01",
            "TS": "2015-01-01T00:00:28.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH4",
            "VAL": "01",
            "TS": "2015-01-01T00:00:29.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH4",
            "VAL": "01",
            "TS": "2015-01-01T00:00:30.0000000Z"
        }

    },
    {"Payload": {
            "Make": "BATCH5",
            "VAL": "01",
            "TS": "2015-01-01T00:00:31.0000000Z"
        }

    }
    ]

Hi,mbr. Actually,i can't get your concern.You want to implement something like TOP in the 1 minute window? — Jay Gong
Hi Jay, thanks for the response and sorry that I was not clear.in a minute window there can be multiple VAL changes for each batch ID.ex: In minutes i can get Make:batch1,Val:01, Make:batch1,val:01, Make:batch1,val:02, Make:batch1,val:02 ×××××××××××× Make:batch2,val:01, Make:batch2,val:01, Xxxxxxxxxx From this I need filter only val for every batch changes and no duplicates.i need Output as Make:batch1,val:01 Make:batch1,val:02 Make:batch2,val:01 Ofcourse with the original timestamps as I mentioned my first post — mbr

Jay Gong Jay Gong · Accepted Answer · 2020-02-17T10:03:34

I tried to summarize your requirement as below:

Example input,in a minute window there can be multiple VAL changes for each batch ID:

Make:batch1,Val:01, Make:batch1,val:01, Make:batch1,val:02, Make:batch1,val:02 ×××××××××××× Make:batch2,val:01, Make:batch2,val:01, Xxxxxxxxxx

Desired output,only val for every batch changes and no duplicates:

Make:batch1,val:01 Make:batch1,val:02 Make:batch2,val:01

Answer is divided into 2 parts:

1.Collect data in a static period, you could use built-in Tumbling Window function as below:

2.No built-in ASA function like distinct to filter the duplicate.I'd suggest you using GROUP BY, MAX,ASA UDF(link) to approaching your result.

SQL:

 SELECT g.Payload.Make,g.Payload.VAL,max(udf.convertdate(g.Payload.TS)) as TS
    FROM geoinput g TIMESTAMP BY g.Payload.TS
    GROUP BY g.Payload.Make,g.Payload.VAL, TumblingWindow(Duration(minute, 1))

Test output:

BTW,i just use below code inside UDF

var date = new Date(datetime);
    return date.getTime();

Another workaround,you could collect all the data during 1 minute then use Azure Function as Output. In the Azure Function, you could process data as you want. Such as use JSON object to store the data.Key-Value structure could filter duplicate rows.

azure Stream analytics isfirst and last query

1 Answers