I am trying to Pivot/Transpose my column values and trying get corresponding datetime .
table i have:
User Status LogTime
----------------------------------------
Tom Active 2019-09-06 17:36:08.233
Tom Active 2019-09-06 18:37:08.244
Tom Active 2019-09-06 20:46:08.133
Tom InActive 2019-09-06 23:46:08.133
Tom Active 2019-09-07 12:37:08.244
Tom Active 2019-09-08 10:46:08.133
Tom InActive 2019-09-08 11:46:08.133
Trying to get the data like below.
User Active InActive
------------------------------------------------------
Tom 2019-09-06 20:46:08.133 2019-09-06 23:46:08.133
Tom 2019-09-08 10:46:08.133 2019-09-08 11:46:08.133
I'm trying to transpose the Status column with last active logtime and inactive logtime after the last active
This query works in Hive with your dataset. I tried to take into account possible boundary conditions when it is no InActive or Active status in the log for the user, of course the logic should be verified and adjusted on real dataset.
Demo:
with data as (
select stack(7,
'Tom','Active', '2019-09-06 17:36:08.233',
'Tom','Active', '2019-09-06 18:37:08.244',
'Tom','Active', '2019-09-06 20:46:08.133',
'Tom','InActive', '2019-09-06 23:46:08.133',
'Tom','Active', '2019-09-07 12:37:08.244',
'Tom','Active', '2019-09-08 10:46:08.133',
'Tom','InActive', '2019-09-08 11:46:08.133'
) as(User,Status,LogTime)
) --use your_table instead of this
select User, Active, InActive
from
(
select User,MaxInActive,MaxActive,--Status,LogTime,nextStatus,
case when (prevStatus='Active' and Status='InActive') --the last Active LogTime
then prevLogTime
when (Status='Active' and nextStatus is NULL) --boundary condition, Active is the last status, take current
OR (LogTime=MaxActive and MaxInActive is NULL) --No InActive, take current
then LogTime
end as Active,
case when (prevStatus='Active' and Status='InActive') --InActive LogTime after the last Active
OR (LogTime=MaxInActive and MaxActive is NULL) --No Active exists, take current
then LogTime
end as InActive
from
(
select User,Status,LogTime,
max(case when Status='InActive' then LogTime end) over(partition by User) as MaxInActive ,
max(case when Status='Active' then LogTime end) over(partition by User) as MaxActive,
lead(Status) over(partition by User order by LogTime) nextStatus,
lag(Status) over(partition by User order by LogTime) prevStatus,
lag(LogTime) over(partition by User order by LogTime) prevLogTime
from data
)s
)s
where (Active is not NULL and InActive is not NULL)
or (MaxInActive is NULL and Active is not NULL) --only active records exist
or (MaxActive is NULL and MaxInActive is not NULL) --only inactive exists
;
Result:
OK
user active inactive
Tom 2019-09-06 20:46:08.133 2019-09-06 23:46:08.133
Tom 2019-09-08 10:46:08.133 2019-09-08 11:46:08.133
Time taken: 100.645 seconds, Fetched: 2 row(s)