scala - 给定Scala中的特定条件，如何组合两个数据框中的行？

Question

我有这两个数据框称为：

表格1

+---------+------------+------+
|  Animal |    Owner   |count1|
+---------+------------+------+
|    Cat  |     Bob    |   3  |
|    Fish |    Jerry   |   2  |
|    Dog  |     Bob    |   2  |
|  Turtle |     Joe    |   5  |
+---------+------------+------+

表2

+---------+------------+------+
|  Animal |    Owner   |count2|
+---------+------------+------+
|    Cat  |     Bob    |   2  |
|    Fish |     Jerry  |   1  |
|    Dog  |     Bob    |   3  |
|   Snake |     Kim    |   6  |
+---------+------------+------+

我正在尝试以某种方式组合这两个数据框，以便下面的新数据框将包含行

• 出现在“table1”或“table2”中
• 在两个表中找到的不同行包含的计数值在“table2”中大于在“table1”中

这是我想要生成的预期输出数据帧。

+---------+------------+------+------+
|  Animal |   Owner    |count1|count2|
+---------+------------+------+------+
|   Dog   |     Bob    |   2  |   3  |
|  Turtle |     Joe    |   5  | null |
|   Snake |     Kim    | null |   6  |
+---------+------------+------+------+

出现在“table1”中而不出现在“table2”中（或出现在“table2”中而不出现在“table1”中）的行的计数值可以为“null”。

标签： scalaapache-sparkapache-spark-sql

Answer 1

在 Spark 中尝试完全加入filter条件

scala> var t1 = Seq(("Cat","Bob",3),  ("Fish" ,"Jerry" ,2),  ("Dog"  ,    "Bob",2),  ("Turtle" ,"Joe",5)).toDF("Animal","Owner","count1")

scala> var t2 = Seq(("Cat", "Bob",2),("Fish","Jerry",1),("Dog" ,"Bob",3),("Snake","Kim",6)).toDF("Animal","Owner","count2")

在数据帧 t1(table1) 和 t2(table2) 中，应用full join保留表中两个计数列的空行。

scala> t2.join(t1,Seq("Animal","Owner"),"full").filter(col("count2")>col("count1") || col("count2").isNull || col("count1").isNull).show
+------+-----+------+------+
|Animal|Owner|count2|count1|
+------+-----+------+------+
|   Dog|  Bob|     3|     2|
| Snake|  Kim|     6|  null|
|Turtle|  Joe|  null|     5|
+------+-----+------+------+