Creating a column flag where a session ends

[slacayo]

I have a table:

LOAD * INLINE [
    mid, sid, pid ts  
    A, A1, page1 t      
    A, A1, page2 t      
    A, A1, page3 t 
    A, A2, page2 t
    A, A2, page4 t 
    A, A3, page6 t  
    A, A3, page9 t    
    B, B1, page2 t
    B, B1, page4 t       
    B, B2, page3 .     
    B, B3, page9 . 
    B, B3, page6 . 
    C, C1, page2 .
    C, C2, page1 .
    C, C2, page2 . 
    D, D1, page1 .
    . . . .
];

For some context, I am working with data from people who traverse through a website. `mid` is their main id, `sid` is their session. A user, lets say `B`,  can have multiple distinct sessions `B1`,`B2`, etc.  When the session is repeated, it means that they're simply doing an action within a session. The page ids are ids for different pages visited. Lastly, every action has a distinct time `t` in UNIX timestamp format. 

So, I want to know exactly where users end their session. I would like to sort the the table by the session id (sid) and timestamp (ts) so that it reflects a path people take in a session. The table above is already sorted for the purpose of the example but the original data table isnt. I would then like to create a column that flags the LAST row in that session so I know which page people decided to end their session. 

So the result should be something like this if I were to reimport an already finished table:
 

LOAD * INLINE [      
        mid, sid, pid ts exit_flag   
        A, A1, page1 t       
        A, A1, page2 t      
        A, A1, page3 t     end
        A, A2, page2 t
        A, A2, page4 t     end
        A, A3, page6 t  
        A, A3, page9 t     end    
        B, B1, page2 t
        B, B1, page4 t     end         
        B, B2, page3 .
        B, B2, page9 .     end     
        B, B3, page9 .     
        B, B3, page6 .     end
        C, C1, page2 .
        C, C1, page3 .     end
        C, C2, page1 .
        C, C2, page2 .     end
        D, D1, page1 .
        .   .    .   .
. . . ];

Just some additional information, every session has at least two pages, no more than 30, and no sequential duplicates. If anyone else has a better method for tagging these exits I would love an additional solution. 

[rubenmarin · Accepted solution]

Hi, you can load the table sorting by mid sid and ts decendant, so the first row of each combination is the last register for the session, using peek() you can add the flag, like:

FinalTableName:
LOAD
  mid,sid,pid,ts
  if(Peek(sid)<>sid, 1, 0) as isExit
Resident OriginalTableName
Order by mid, sid, ts des;

DROP Table OriginalTableName;

[slacayo]

Can this be done as a preceding load so I can keep the original table with the exit flag?

[rubenmarin]

Hi, preceding load won't accept the "order by" clause and this caluse it's needed, you can do a "LOAD *, if(Peek(sid)<>sid, 1, 0) as isExit Resident OrginalTable" to keep all the original records.

You can also do a "Rename Table OriginalTableName TempTableName" before the load to keep the orginal name in the flag table.

RENAME Table OriginalTableName tmpOriginalTableName;

OriginalTableName:
LOAD
  *
  if(Peek(sid)<>sid, 1, 0) as isExit
Resident tmpOriginalTableName
Order by mid, sid, ts des;

DROP Table tmpOriginalTableName;