如何在MYSQL中根据时间间隔将行转置为列 [英] How to transpose rows to columns based on time intervals in MYSQL

问题描述

我有这样的示例数据:

            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 .

链接: 如何获取首次打开并mysql中的关闭状态

推荐答案

一种方法使用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():

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;

(用于调整后的数据)

您可以只使用条件聚合:

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;

此处是db< fiddle

Here is a db<>fiddle

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

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

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

这篇关于如何在MYSQL中根据时间间隔将行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！