I got a problem about recreating sessions in Kusto. I got an event named client_session_start, which timetamp gives me info about when a player starts a new session. The problem is that I don't have an event for the session end, so I should label everything between those 2 events per each player with a new session ID. This is how it looks like

ID_player   Timestamp   event_name
aaa         12:00:00    client_session_start
aaa         12:30:00    client_session_start

In this case, I need to label everything between 12:00 and 12:30 and also the last session (past 12:30) which doesn't have the next ''client_session_start''.

I thought I could extract all the events named ''client_session_start'', rank them by user and timestamp, then merge the new session_id and use something like the SQL function last_value() to fill the gaps (something like this SQL QUERY replace NULL value in a row with a value from the previous known value)

but it seems I cannot do that in that way in Kusto. Any suggestion?

CodePudding user response：

partition operator & row_cumsum()

datatable (ID_player:string, Timestamp:timespan, event_name:string)
[
     "aaa", "12:00:00", "client_session_start"
    ,"aaa", "12:10:00", ""
    ,"aaa", "12:20:00", ""
    ,"aaa", "12:30:00", "client_session_start"
    ,"bbb", "12:00:00", "client_session_start"
    ,"bbb", "12:10:00", ""
    ,"bbb", "12:20:00", ""
    ,"bbb", "12:30:00", "client_session_start" 
    ,"bbb", "12:40:00", ""    
    ,"bbb", "12:50:00", "client_session_start"  
    ,"bbb", "13:00:00", ""        
    ,"ccc", "12:00:00", "client_session_start" 
    ,"ccc", "12:10:00", ""
    ,"ccc", "12:20:00", ""
    ,"ccc", "12:30:00", ""
    ,"ccc", "12:40:00", ""             
]
|   partition hint.strategy=native by ID_player
    (
            order by Timestamp asc
        |   extend session_index = row_cumsum(case(event_name == "client_session_start", 1, 0))
    )

Fiddle