Spark Dataframe嵌套的when语句 [英] Spark Dataframe Nested Case When Statement

问题描述

我需要在Spark DataFrame

I need to implement the below SQL logic in Spark DataFrame

SELECT KEY,
    CASE WHEN tc in ('a','b') THEN 'Y'
         WHEN tc in ('a') AND amt > 0 THEN 'N'
         ELSE NULL END REASON,
FROM dataset1;

我的输入DataFrame如下:

val dataset1 = Seq((66, "a", "4"), (67, "a", "0"), (70, "b", "4"), (71, "d", "4")).toDF("KEY", "tc", "amt")

dataset1.show()

+---+---+---+
|KEY| tc|amt|
+---+---+---+
| 66|  a|  4|
| 67|  a|  0|
| 70|  b|  4|
| 71|  d|  4|
+---+---+---+

我已将when语句的嵌套情况实现为:

I have implement the nested case when statement as:

dataset1.withColumn("REASON", when(col("tc").isin("a", "b"), "Y")
  .otherwise(when(col("tc").equalTo("a") && col("amt").geq(0), "N")
    .otherwise(null))).show()

+---+---+---+------+
|KEY| tc|amt|REASON|
+---+---+---+------+
| 66|  a|  4|     Y|
| 67|  a|  0|     Y|
| 70|  b|  4|     Y|
| 71|  d|  4|  null|
+---+---+---+------+

如果嵌套的when语句进行得更远，则带有"otherwise"语句的上述逻辑的可读性也很少.

Readability of the above logic with "otherwise" statement is little messy if the nested when statements goes further.

在Spark DataFrames中的语句时，是否有更好的方法来实现嵌套大小写?

Is there any better way of implementing nested case when statements in Spark DataFrames?

推荐答案

这里没有嵌套，因此不需要otherwise.您只需要链接when:

There is no nesting here, therefore there is no need for otherwise. All you need is chained when:

import spark.implicits._

when($"tc" isin ("a", "b"), "Y")
  .when($"tc" === "a" && $"amt" >= 0, "N")

ELSE NULL是隐式的，因此您可以完全忽略它.

ELSE NULL is implicit so you can omit it completely.

您使用的模式在数据结构上更适用于folding:

Pattern you use, is more more applicable for folding over a data structure:

val cases = Seq(
  ($"tc" isin ("a", "b"), "Y"),
  ($"tc" === "a" && $"amt" >= 0, "N")
)

其中when-otherwise自然遵循递归模式，而null提供了基本情况.

where when - otherwise naturally follows recursion pattern and null provides the base case.

cases.foldLeft(lit(null)) {
  case (acc, (expr, value)) => when(expr, value).otherwise(acc)
}

请注意，在这种情况下，不可能达到"N"个结果.如果tc等于"a"，它将被第一个子句捕获.如果不是，它将无法同时满足两个谓词，并且默认为NULL.您应该宁愿:

Please note, that it is impossible to reach "N" outcome, with this chain of conditions. If tc is equal to "a" it will be captured by the first clause. If it is not, it will fail to satisfy both predicates and default to NULL. You should rather:

when($"tc" === "a" && $"amt" >= 0, "N")
 .when($"tc" isin ("a", "b"), "Y")

这篇关于Spark Dataframe嵌套的when语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！