CODEWITHSUNDEEP
sqlapache-sparkdatabricks
Sam
Sam

Reputation: 1229

SQL Find the time difference within a given session

Forgive me I'm not massively familiar with SQL to do the below. But would love to learn the process of how to do it if possible.

I only have one table: Table name - SessionTracker

-----------------------------------------------------------------------------------------------------------------------------------------

bundleID                   | sessionId |                  deviceID |                                   eventType |          Timestamp    
-----------------------------------------------------------------------------------------------------------------------------------------

com.package.random          3871207406642403679         333333-00000-0000-00000-000000000000000       REQUEST             1595858140614
com.package.random          3871207406642403679         333333-00000-0000-00000-000000000000000       EVENT               1595857661417
com.package.random          3871207406642403679         333333-00000-0000-00000-000000000000000       RESPONSE            1595857662129
com.package.random          3245233406642403679         000000-00000-0000-00000-000000000000000       REQUEST             1595857661418
com.package.random          3245233406642403679         000000-00000-0000-00000-000000000000000       EVENT               1595857661418
com.package.random          3245233406642403679         000000-00000-0000-00000-000000000000000       RESPONSE            1595857661418
com.package.random          871207406643e243433         000000-00000-0000-00000-000000000000000       REQUEST             1595857662129
com.package.random2         3243254325454535422         111111-00000-0000-00000-000000000000000       REQUEST             1595857662129 
com.package.random3         4353453452525252465         222222-00000-0000-00000-000000000000000       REQUEST             1595857662129  
com.package.random4         3453656456353252345         111111-00000-0000-00000-000000000000000       REQUEST             1595857662129  
com.package.random5         4567568765745634563         111111-00000-0000-00000-000000000000000       REQUEST             1595857662129

I'd like to display a column showing the time difference between REQUEST, EVENT, RESPONSE within a session.

From the example above:

I want to be able to check the time difference between the evenType time within a given session. So an extra columns which shows the timestamp difference for say sessionId= 3871207406642403679 and eventType is REQUEST, EVENT, RESPONSE, show the difference between them.

I'm hoping a query in databricks would work, is this possible?

edit:

I know I can use this to get a more human readable timestamp: from_unixtime(dataframe.timestamp/1000, "HH:mm:ss")

But from this not sure how I would be able to compare the timestamp from each REQUEST, EVENT, RESPONSE

Upvotes: 1

Views: 316

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use conditional aggregation to calculate the times for each event:

select sessionid,
       min(case when event = 'REQUEST' then timestamp end) as request_ts,
       min(case when event = 'EVENT' then timestamp end) as event_ts,
       min(case when event = 'RESPONSE' then timestamp end) as response_ts
from t
group by sessionid;

You can then use arithmetic on these expressions:

select s.*,
       (event_ts - request_ts) as time_to_request,
       (response_ts - event_ts) as time_to_event
from (select sessionid,
             min(case when event = 'REQUEST' then timestamp end) as request_ts,
             min(case when event = 'EVENT' then timestamp end) as event_ts,
             min(case when event = 'RESPONSE' then timestamp end) as response_ts
      from t
      group by sessionid
     ) s

Upvotes: 1

Related Questions