mysql - 如何根据MySQL中其他列的值创建新列

Question

编辑

我尝试了这个解决方案(下面的答案)来解决这个问题。

select timestamp, id, coalesce(
        max(case when val = 1 then unix_timestamp(timestamp) end) over (partition by idorder by timestamp),
        min(unix_timestamp(timestamp)) over (partition by id)
    )) as global_e
from table

这个解决方案有效。但mysql中执行-读取时间为30-5秒。

当我尝试这个解决方案时(上面相同但版本不同。还是我错了？)；

select timestamp, id, coalesce(
            max(case when val = 1 then unix_timestamp(timestamp) end) over prt,
            min(unix_timestamp(timestamp)) over prt        
       )) as global_e
from table 
window as prt (partition by id order by timestamp)

对于上述查询，执行 - 获取时间为 5 - 30 秒。为什么？？

<小时/>

我有一个像这样的表X；

id       timestamp       x_val
1          ts1            0
1          ts2            0
1          ts3            1
1          ts4            0
1          ts5            1 
2 ...
...

如您所见，x_val 列值只能是 0 或 1。但我想根据其他列创建新列。所有值均按 id 分区。

我想要这样的输出表；

id       timestamp       x_val    global_e
1          ts1            0         1_ts1
1          ts2            0         1_ts1
1          ts3            1         1_ts3
1          ts4            0         1_ts3
1          ts5            1         1_ts5
2 ...
...

在上表中，global_e是根据id和timestamp创建的。如果 x_val 为 1，则意味着 global_e 必须等于 id + 当前行时间戳。如果为 0，则 global_e 必须等于之前的值。

如何创建像上面这样的 global_e 列？

Answer 1

在 MySQL 8+ 中，您可以使用累积最大值。这基本上是:

select x.*,
       max(case when x_val = 1 then timestamp end) over (partition by id order by timestamp) as global_e
from x;

这并不完全是您想要的，因为当没有带有 1 的行时，您需要最小值。因此，使用coalesce():

select x.*,
       coalesce(max(case when x_val = 1 then timestamp end) over (partition by id order by timestamp),
                min(timestamp) over (partition by id)
               ) as global_e
from x;

在早期版本中，相关子查询可能是最简单的方法:

select x.*,
       (select coalesce(max(case when x2.x_val = 1 then timestamp end), min(timestamp)
        from x x2
        where x2.id = x.id and 
              x2.timestamp <= x.timestamp
       ) as global_e
from x;