如何使用scala在spark sql中使用when条件编写案例 [英] how to write case with when condition in spark sql using scala

问题描述

SELECT c.PROCESS_ID, 
       CASE WHEN c.PAYMODE = 'M' 
           THEN 
               CASE WHEN CURRENCY = 'USD' 
                   THEN c.PREMIUM * c.RATE 
                   ELSE c.PREMIUM END * 12
           ELSE 
               CASE WHEN CURRENCY = 'USD' 
                   THEN c.PREMIUM * c.RATE 
                   ELSE c.PREMIUM END END VAlue
FROM CMM c

我想转换sql查询spark sql api怎么办?

i want to convert sql query spark sql api how can i do?

谢谢

推荐答案

如果您正在寻找使用 Column 对象执行此操作的方法，您可以像这样进行直译:

If you are looking for the way to do this using Column objects, you can do a literal translation like this:

val df: DataFrame = ...

df.select(
  col("PROCESS_ID"),
  when(col("PAYMODE") === lit("M"),
    (when(col("CURRENCY") === lit("USD"), col("PREMIUM") * col("RATE"))
    .otherwise(col("PREMIUM"))) * 12
  ).otherwise(
    when(col("CURRENCY") === lit("USD"), col("PREMIUM") * col("RATE"))
    .otherwise(col("PREMIUM"))
  )
)

不过，可能更简洁的方法是执行以下操作:

Probably a cleaner way to do it, however, is to do something like:

df.withColumn(
"result",
  when(col("CURRENCY") === lit("USD"), col("PREMIUM") * col("RATE"))
    .otherwise(col("PREMIUM"))
).withColumn(
  "result",
  when(col("PAYMODE") === lit("M"), col("result") * 12)
    .otherwise(col("result"))
)

至少，第二个对我来说更容易阅读.

At least, the second one is a lot easier to read to me.

这篇关于如何使用scala在spark sql中使用when条件编写案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！