CODEWITHSUNDEEP
azure-data-explorerkusto-explorer
Gourav Joshi
Gourav Joshi

Reputation: 61

Ranking function in Kusto

enter image description hereI have below data in Kusto table .( Run_Date datetime and sensor string are two column in table)

I have requirement to add autoincrement column in such way that if Run Date or Sensor value gets changed column should incremented by one .

Please refer attached screenshot . I have tried with Rank and Rownumber function in kusto but no luck for me.

Upvotes: 1

Views: 1554

Answers (2)

Avnera
Avnera

Reputation: 7608

Another alternative is to use the row_rank() function

datatable(run_date:datetime, sensor:string)
[
    datetime(2021-08-05), "A",
    datetime(2021-08-05), "A",
    datetime(2021-08-05), "A",
    datetime(2021-08-05), "B",
    datetime(2021-08-05), "B",
    datetime(2021-09-05), "B",
]
| extend Day = bin(run_date, 1d)
| extend RankColumn = strcat(Day, sensor)
| order by RankColumn asc 
| extend Rownumber = row_rank(RankColumn)
| project-away RankColumn, Day

Upvotes: 0

Yoni L.
Yoni L.

Reputation: 25895

you could use the scan operator: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/scan-operator

datatable(run_date:datetime, sensor:string)
[
    datetime(2021-08-05), "A",
    datetime(2021-08-05), "A",
    datetime(2021-08-05), "A",
    datetime(2021-08-05), "B",
    datetime(2021-08-05), "B",
    datetime(2021-09-05), "B",
]
| order by run_date asc
| scan declare (_rank: long = 0) with 
(
    step s1: true => _rank = iff(run_date > s1.run_date or sensor != s1.sensor, s1._rank + 1, s1._rank);
)
run_date sensor _rank
2021-08-05 00:00:00.0000000 A 1
2021-08-05 00:00:00.0000000 A 1
2021-08-05 00:00:00.0000000 A 1
2021-08-05 00:00:00.0000000 B 2
2021-08-05 00:00:00.0000000 B 2
2021-09-05 00:00:00.0000000 B 3

Upvotes: 1

Related Questions