在Pyspark中实现条件时

Question

我已经在pyspark中转换了我的t-sql代码，但给我错误

CASE
            WHEN time_on_site.eventaction = 'IN' AND time_on_site.next_action = 'OUT' AND time_on_site.timespent_sec < 72000 THEN 1  --  20 hours 
            WHEN time_on_site.eventaction = 'IN' AND time_on_site.next_action = 'OUT' AND time_on_site.timespent_sec >= 72000 THEN 0
            WHEN time_on_site.eventaction = 'IN' AND time_on_site.next_action = 'IN' AND time_on_site.timespent_sec <= 28800 THEN 2  -- 8 hours
            WHEN time_on_site.eventaction = 'IN' AND time_on_site.next_action = 'IN' AND time_on_site.timespent_sec > 28800 THEN 3
            WHEN time_on_site.type_flag = 'TYPE4' THEN 4
            ELSE NULL
         END AS "type"

下面是我的pyspark脚本，它正在丢弃错误

from pyspark.sql.functions import when
TOS=TOS.withColumn('type', F.when( (col('eventaction') == 'IN') & (col('next_action') == 'OUT') & ("timespent_sec < 72000") , 1).
                            when( (col('eventaction') == 'IN') & (col('next_action') == 'OUT') & ("timespent_sec >= 72000") , 0).
                            when( (col('eventaction') == 'IN') & (col('next_action') == 'IN') & ("timespent_sec <= 28800") , 2).
                            when( (col('eventaction') == 'IN') & (col('next_action') == 'IN') & ("timespent_sec > 28800") , 3).
                            when(col('type_flag')=='TYPE4', 4).otherwise('NULL')
                            )

我要在哪里出错！？

Answer 1

在 otherwise()中嵌套'''。我的牙套可能不是完全平衡的，所以请检查它们，但是这个想法是相同的。

TOS=TOS.withColumn('type', F.when( (col('eventaction') == 'IN') & (col('next_action') == 'OUT') & ("timespent_sec < 72000") , 1).
                            otherwise( when(   (col('eventaction') == 'IN') & (col('next_action') == 'OUT') & ("timespent_sec >= 72000") , 0).
                            otherwise( when(   (col('eventaction') == 'IN') & (col('next_action') == 'IN') & ("timespent_sec <= 28800") , 2).
                            otherwise( when(   (col('eventaction') == 'IN') & (col('next_action') == 'IN') & ("timespent_sec > 28800") , 3).
                            otherwise( when(   col('type_flag')=='TYPE4', 4).otherwise('NULL'))))))

Answer 2

我已经正确实现了它，如下所示

TOS=TOS.withColumn('type', F.when( (F.col("eventaction") == 'IN') & (F.col("next_action") == 'OUT') & (F.col("timespent_sec") < 72000) , 1).
  when( (F.col("eventaction") == 'IN') & (F.col("next_action") == 'OUT') & (F.col("timespent_sec") >= 72000) , 0).
  when( (F.col("eventaction") == 'IN') & (F.col("next_action") == 'IN') & (F.col("timespent_sec") <= 28800) , 2).
  when( (F.col("eventaction") == 'IN') & (F.col("next_action") == 'IN') & (F.col("timespent_sec") > 28800) , 3).
  when(F.col('type_flag')=='TYPE4', 4).otherwise('NULL'))