mysql - SQL查找组中两个值之间的时间差

Question

我有一个如下所示的 sql 表。

+---------------------+--------+-------+
|        time         | item   | state |
+---------------------+--------+-------+
| 2019-07-19 1:50:00 | a      |   st1 |
| 2019-07-19 2:51:00 | a      |   st2 |
| 2019-07-19 2:55:00 | b      |   st1 |
| 2019-07-19 2:59:00 | b      |   st2 |
| 2019-07-19 4:30:00 | a      |   st3 |
+---------------------+--------+-------+

我需要弄清楚 item 将状态从 st1 更改为 st2 需要多少时间等等..

最终的输出表应该是这样的。

+--------+------------+---------------+
| item   | st1_to_st2 |   st2_to_st3  | and so on
+--------+------------+---------------+
| a      |   x seconds| w-seconds     |
| b      |   y-seconds| z-seconds     |
+--------+------------+---------------+

你能帮我处理一下sql吗？

标签： mysqlsqldatetimesubquerydate-arithmetic

Answer 1

对于固定的状态列表，您可以使用窗口函数和聚合：

select item,
    sum(case when lag_state = 'st1' and state = 'st2' then timestampdiff(second, lag_time, time)) as st1_to_st2,
    sum(case when lag_state = 'st2' and state = 'st3' then timestampdiff(second, lag_time, time)) as st2_to_st3
from (
    select t.*,
        lag(time)  over(partition by item order by state) lag_time,
        lag(state) over(partition by item order by state) lag_state
    from mytable t
) t
group by item

如果您想要更通用的东西 - 即不会对状态进行编码 - 我建议将值放在行中而不是列中：

select item, lag_state, state, 
    sum(timestampdiff(second, lag_time, time)) as sum_diff
from (
    select t.*,
        lag(time)  over(partition by item order by state) lag_time,
        lag(state) over(partition by item order by state) lag_state
    from mytable t
) t
group by item, lag_state, state