基于值将值移动到新列的sql

vkc1a9a2 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(236)

我们有一个类似以下示例的表格：

TS                          |Description        |Value
--------------------------------------------------------
2020-06-12 14:13:12.253     |Machine Status     |Event 4
2020-06-12 14:12:56.250     |Machine Status     |Event 3
2020-06-12 14:12:26.250     |Machine Status     |Event 2
2020-06-12 14:11:06.253     |Machine Status     |Event 1
2020-06-12 14:10:12.253     |Machine Status     |Event 4
2020-06-12 14:09:56.250     |Machine Status     |Event 3
2020-06-12 14:09:26.250     |Machine Status     |Event 2
2020-06-12 14:08:06.253     |Machine Status     |Event 1

我们想根据事件转换时间戳

TS_Event4                   |TS_Event3                   |TimeDiff
------------------------------------------------------------------------
2020-06-12 14:13:12.253     |2020-06-12 14:12:56.250     |Event4-Event3
2020-06-12 14:10:12.253     |2020-06-12 14:09:56.250     |Event4-Event3
....

现在棘手的是。。。我根据时间戳对表进行排序，需要找到第一个“event4”。下一个“event3”（不总是下一行）需要在它旁边的列中。

sql sql-server aggregate-functions

来源：https://stackoverflow.com/questions/62345786/sql-to-move-value-to-new-column-based-on-value

1条答案

按热度按时间

cigdeys31#

我认为您需要条件聚合：

select max(case when value = 'Event 4' then ts end),
       max(case when value = 'Event 3' then ts end),
       datediff(second,
                max(case when value = 'Event 1' then ts end),
                max(case when value = 'Event 2' then ts end)
               )
from t;

编辑：
对于修改后的问题，可以添加 group by 通过计算每个事件的序列号：

select max(case when value = 'Event 4' then ts end),
       max(case when value = 'Event 3' then ts end),
       datediff(second,
                max(case when value = 'Event 1' then ts end),
                max(case when value = 'Event 2' then ts end)
               )
from (select t.*, row_number() over (partition by value order by ts) as seqnum
      from t
     ) t
group by seqnum;

赞(0）回复(0）举报 2021-08-09

我来回答

基于值将值移动到新列的sql

1条答案

相关问题

热门标签

最新问答