mysql - 在 Hive 中使用 case 语句填充前面的值

Question

我在 Hive 表中有一列 event，如下所示。

Event

Sent
Sent
Open
Open
Click
Sent
Open
Signup
Sent
Open
Click

现在我想使用 case 语句根据 event 列中的值创建新列。

我想在事件列中有 signup 的地方我希望 Previous_event 列正好是 preceding 值。

我试过如下。

select event, 
       case when event = 'Sent' then 'No_event' 
            when event = 'Open' then 'Sent' 
            when event = 'Click' then 'Open'  
            else -1 
       end as Previous_event 
from table;

结果

Sent    No_event
Sent    No_event
Open    Sent
Open    Sent
Click   Open
Sent    No_event
Open    Sent
Signup  -1
Sent    No_event
Open    Sent
Click   Open

预期结果

Sent    No_event
Sent    No_event
Open    Sent
Open    Sent
Click   Open
Sent    No_event
Open    Sent
Signup  Open
Sent    No_event
Open    Sent
Click   Open

我怎样才能实现我想要的？

Answer 1

使用相关子查询。

基于 OP's comments ，您可以尝试以下查询(for MySQL):

select t1.event, 
       case when t1.event = 'Sent' then 'No_event' 
            when t1.event = 'Open' then 'Sent' 
            when t1.event = 'Click' then 'Open' 
            when t1.event = 'Signup' then (select t2.event 
                                           from table as t2 
                                           where t2.eventdate < t1.eventdate 
                                           order by t2.eventdate desc 
                                           limit 1)         
            else -1 
       end as Previous_event 
from table as t1;