Azure Stream Analytics Pivoting Rows To Columns

Question

I have a data like;

        [
        {"deviceid":"d1","parameter"="p1" value="1" timestamp="2018-03-22T12:33:00"},
        {"deviceid":"d1","parameter"="p2" value="2" timestamp="2018-03-22T12:34:00"},
        {"deviceid":"d1","parameter"="p2" value="3" timestamp="2018-03-22T12:35:00"},
        {"deviceid":"d2","parameter"="p1" value="4" timestamp="2018-03-22T12:36:00"},
        {"deviceid":"d2","parameter"="p2" value="5" timestamp="2018-03-22T12:37:00"},
        {"deviceid":"d2","parameter"="p2" value="6" timestamp="2018-03-22T12:38:00"},
        {"deviceid":"d2","parameter"="p1" value="7" timestamp="2018-03-22T12:43:00"},
        {"deviceid":"d2","parameter"="p1" value="8" timestamp="2018-03-22T12:44:00"},
        {"deviceid":"d2","parameter"="p2" value="9" timestamp="2018-03-22T12:45:00"},
        {"deviceid":"d1","parameter"="p1" value="10" timestamp="2018-03-22T12:46:00"},
        {"deviceid":"d1","parameter"="p1" value="11" timestamp="2018-03-22T12:47:00"},
        {"deviceid":"d1","parameter"="p2" value="12" timestamp="2018-03-22T12:49:00"}
        ]

I want to pivot that parameter values(for every 10 minutes window) and display last recieved value of each parameter for each deviceid and like this edit*(Parameters will be dynamic not just p1 and p2);

------------------------------------------------------------
| deviceid|     windowtime       |     p1    |     p2      |
------------------------------------------------------------
| d1      | 2018-03-22 12:40     |     1     |      3      |
------------------------------------------------------------
| d2      | 2018-03-22 12:40     |     4     |      6      | 
------------------------------------------------------------
| d2      | 2018-03-22 12:50     |     8     |      9      |
------------------------------------------------------------
| d1      | 2018-03-22 12:50     |     11    |     12      |
------------------------------------------------------------

Thank You.

Hello Metehan Mutlu, We are facing the same issue, Could you please share your solution here. Thanks. — Swati, Mar 23 '22 at 01:25

Joe zhang · Answer 1 · 2018-03-22T19:54:02.977

you can try like below script :

with tempone as (SELECT
    deviceid, system.Timestamp as windowtime, max(value) AS P1      
FROM
    test TIMESTAMP BY timestamp
where parameter = 'p1'
GROUP BY
    deviceid,
    TumblingWindow(minute, 10)),

temptwo AS ( SELECT
    deviceid, system.Timestamp as windowtime, max(value) AS P2      
FROM
    test TIMESTAMP BY timestamp
where parameter = 'p2'
GROUP BY
    deviceid,
    TumblingWindow(minute, 10))

select tempone.deviceid, tempone.windowtime, tempone.P1, temptwo.P2 from tempone
join temptwo on tempone.deviceid = temptwo.deviceid
and DATEDIFF(minute,tempone, temptwo) BETWEEN 0 AND 1

score 0 · Answer 2 · answered Mar 22 '18 at 19:53

Another more clear way is to use UDA :

SELECT
    deviceid, system.Timestamp as windowtime, uda.P1UDA(test) AS P1, uda.P2UDA(test) AS P2   
FROM
    test TIMESTAMP BY timestamp
GROUP BY
    deviceid,
    TumblingWindow(minute, 10)

JavaScript UDA:

function P1UDA() {
    this.init = function () 
{
        this.state = 0;
    }

    this.accumulate = function (value, timestamp) {

        if (value.parameter == 'p1') {
            if (value.value > this.state) {
                this.state = value.value;
            }
        }
    }

    this.computeResult = function () {
        return this.state;
    }
}

function P2UDA() {
    this.init = function () {
        this.state = 0;
    }

    this.accumulate = function (value, timestamp) {

        if (value.parameter == 'p2') {
            if (value.value > this.state) {
                this.state = value.value;
            }
        }
    }

    this.computeResult = function () {
        return this.state;
    }
}

First thanks. Sorry i forgot to mention. Parameters will be dynamic not just p1 and p2. And i want the latest record(max timestamp not value) for parameter and deviceid in every window — Metehan Mutlu, Mar 23 '18 at 06:56
This feature is not yet available as part of the standard ASA Query Language, but it is possible to create a User Defined Functions to do this. More information is available here: https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-javascript-user-defined-functions. — Joe zhang, Mar 23 '18 at 18:05

Azure Stream Analytics Pivoting Rows To Columns

2 Answers2

Linked