How do I aggregate from multiple rows

user_19607848 0
Viewed 58

I have the below table:

Person_ID   | Timestamp           | Action
1           | 01-01-2020 00:01:00 | LOGGED ON
1           | 01-01-2020 00:02:00 | ON BREAK
1           | 01-01-2020 00:02:30 | OFF BREAK
1           | 01-01-2020 00:04:00 | LOGGED OFF
1           | 01-01-2020 00:04:30 | LOGGED ON
1           | 01-01-2020 00:05:30 | ON BREAK
1           | 01-01-2020 00:06:00 | OFF BREAK
1           | 01-01-2020 00:08:00 | LOGGED OFF
2           | 01-02-2020 00:04:00 | LOGGED ON
2           | 01-02-2020 00:10:00 | LOGGED OFF

My goal is to write an SQL view to get the below result:

Person_ID   | LoggedInTime_hours | OnBreakTime_hours
1           | 6.5                | 1 
2           | 6                  | 0

For each Person_ID, calculate the difference in hours between each pair of occurrences of LOGGED IN/LOGGED OUT and ON BREAK/OFF BREAK respectively.

Thanks a lot for your expertise and help.

aggregate-functions timestamp datetime mysql sql
2 Answers

1st of all LoggedInTime_hours for PERSON_ID = 1 should be 5.5 hours not 6.5 hours.

Let us assume the table name is TEST.

Database : MySQL

SELECT
PERSON_ID,
IFNULL(SUM(CASE WHEN PREV_ACTIVE_IND - ACTIVE_IND > 0 THEN TIMESTAMPDIFF(SECOND, PREV_TIMESTAMP, TIMESTAMP)/3600 END),0) LoggedInTime_hours,
IFNULL(SUM(CASE WHEN PREV_ACTIVE_IND - ACTIVE_IND = 0 THEN TIMESTAMPDIFF(SECOND, PREV_TIMESTAMP, TIMESTAMP)/3600 END),0) OnBreakTime_hours
FROM
(SELECT
PERSON_ID,
PREV_ACTION,
ACTION,
CASE WHEN PREV_ACTION = 'LOGGED OFF' THEN 1 
WHEN PREV_ACTION LIKE '%BREAK' THEN 2 
WHEN PREV_ACTION IS NULL THEN NULL 
ELSE 3 
END AS PREV_ACTIVE_IND,
CASE WHEN ACTION = 'LOGGED OFF' THEN 1
WHEN ACTION LIKE '%BREAK' THEN 2
WHEN ACTION IS NULL THEN NULL
ELSE 3 
END AS ACTIVE_IND,
PREV_TIMESTAMP,
TIMESTAMP
FROM
(SELECT PERSON_ID,
TIMESTAMP,
LAG(TIMESTAMP,1) OVER (PARTITION BY PERSON_ID) PREV_TIMESTAMP,
LAG(ACTION,1) OVER (PARTITION BY PERSON_ID) PREV_ACTION,
ACTION
FROM TEST) T) T2
GROUP BY PERSON_ID

Hope this query will help.

user_74765420

Try the following query using MySQL v8.0:

With CTE As
(
  Select Person_ID, Action_,
  TIME_TO_SEC(
    TIMEDIFF(
              Lead(Timestamp_) Over (Partition By Person_ID 
              Order By Case When Action_ In 'LOGGED ON','LOGGED OFF')
              Then 1 Else 2 End,Timestamp_), Timestamp_
             ))/3600 As DIFF
  From MyData 
)

Select Person_ID, 
Sum(Case When Action_='LOGGED ON' Then DIFF Else 0 End) As LoggedInTime_hours,
Sum(Case When Action_='ON BREAK' Then DIFF Else 0 End) As OnBreakTime_hours 
From CTE
Group By Person_ID

See a demo from db-fiddle.

The Lead() function will find the next action date related to the current action i.e. (Logged In -> Logged Off), the actions are ordered according to their types; ('LOGGED ON','LOGGED OFF') ordered with 1, and ('ON BREAK','OFF BREAK') ordered with 2, then ordered by Timestamp of the action. Doing so, will ensure that each 'LOGGED ON' is followed by it's 'LOGGED OFF' action, and each 'ON BREAK' is followed by it's 'OFF BREAK' action.

Using TIMEDIFF we can find the difference between the actions times for each pair of actions ('LOGGED ON','LOGGED OFF'), ('ON BREAK','OFF BREAK'). Then the sum of these differences will be LoggedInTime_hours for logged in actions, and OnBreakTime_hours for on break actions.

The TIME_TO_SEC()/3600 is used to convert time differences to hours.

user_127059120
Related