mysql如何将时间行转为列,如何在MYSQL中根据时间间隔将行转置为列

I have sample data like this :

ID Val Name Dt Status

1, 145, 'Test1', '2020-01-28 02:18:00', 'open'

2, 145, 'Test2', '2020-01-28 04:10:00', 'open'

3, 145, 'Test3', '2020-01-28 05:50:00', 'open'

4, 145, 'Test3', '2020-01-28 05:56:00', 'close'

5, 145, 'Test4', '2020-01-28 07:36:00', 'open'

6, 145, 'Test4', '2020-01-28 07:42:00', 'open'

7, 145, 'Test4', '2020-01-28 07:44:00', 'open'

8, 145, 'Test4', '2020-01-28 07:47:00', 'close'

How can i get the output like this :

ID Val Name o_Dt o_gate c_Dt c_gate

1, 145, 'Test1', '2020-01-28 02:18:00', 'open' NULL NULL

2, 145, 'Test2', '2020-01-28 04:10:00', 'open' NULL NULL

3, 145, 'Test3', '2020-01-28 05:50:00', 'open' '2020-01-28 05:56:00', 'close'

4, 145, 'Test4', '2020-01-28 07:36:00', 'open' '2020-01-28 07:47:00', 'close'

I Have tried with different scenarios but not moving forward

Using

COALESCE(LAG(Status) OVER (ORDER BY dt)

ROW_NUMBER()OVER(PARTITION BY vehicle_id,status )

Not getting exact result . Can anyone suggest on this .

Previously I have asked question for same data set but now requirement got changed .

解决方案

One method uses lag():

select t.*

from (select t.*,

lag(status) over (partition by val, name order by date) as prev_status

from t

) t

where status = 'open' and

(prev_status is null or prev_status <> 'open');

This can return more than one result for a test, if the status can "return" to 'open'. You can use row_number() if you don't want this behavior:

select t.*

from (select t.*,

row_number() over (partition by val, name, status order by date) as seqnum

from t

) t

where status = 'open' and seqnum = 1;

EDIT:

(for adjusted data)

You can just use conditional aggregation:

select val, name,

min(case when status = 'open' then status end) as o_gate,

min(case when status = 'open' then dt end) as o_dt,

max(case when status = 'close' then status end) as c_gate,

max(case when status = 'close' then dt end) as c_dt,

from t

group by val, name;

Here is a db<>fiddle

If you want to reconstruct the id, you can use an expression like:

row_number() over (order by min(dt)) as id