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

一尘不染

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

sql

我有这样的示例数据：

            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'

我如何获得这样的输出：

        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'

我曾尝试过不同的场景，但没有前进

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

ROW_NUMBER()OVER(PARTITION BY vehicle_id,status )

没有得到确切的结果。任何人都可以对此提出建议。

以前，我曾问过相同数据集的问题，但现在要求已更改。

阅读 176

2021-05-16

共1个答案

一尘不染

一种方法使用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');

如果状态可以“返回”，则可以返回多个测试结果'open'。row_number()如果您不希望出现这种情况，可以使用：

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;

编辑：

（用于调整后的数据）

您可以只使用条件聚合：

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;

这是一个db
<>小提琴

如果要重构id，则可以使用类似以下的表达式：

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

2021-05-16