Multiple agg functions using Over Window (Concat & max )

Question

I'm beginner in Spark, Is there any way to apply multiple agg functions for two differents columns using the same Over Window ? In my case i want to apply concat and max

I have a Dataset (DS1) like this.

+-----+--------------+---------+--------------+
|Col_1|Col_2         | Col_3   + Col_4        |
+-----+--------------+---------+--------------+
|  1  |  aa          |10       + test_1_1     +
|  1  |  bb          |20       + test_1_2     +
|  2  |  cc          |30       + test_2_1     +
|  2  |  dd          |40       + test_2_2     +

I wnat to get something like this (DS2)

+-----+--------------+---------+--------------+--------
|Col_1|Col_2         | Col_3   + Col_5                |
+-----+--------------+---------+----------------------+
|  1  |  bb          |20       + test_1_2;test_1_1    +
|  2  |  dd          |40       + test_2_2;test_2_1    +
------|--------------|---------+----------------------+

I know how to apply max function Over window, but how can I add concatenation to get the dataset DS2

val partitionColumns = Seq(
    "Col_1"
  )
     df.withColumn(
              "max_Col_3",
              max(col("Col_3")) over Window
                .partitionBy(
                  partitionColumns .map(col): _*
                )
            )
    .filter(col("max_Col_3").equalTo(col("Col_3")))
    .drop("max_Col_3")

user_17021429 · Answer

You can use sql expr to get col2, col3, col5 respectively.

val DS2 = DS1.groupBy("col_1").agg(expr("max(col_2) as col_2"), expr("max(col_3) as col_3"), expr("array_join(collect_list(col_4), ';') as col_5"))
DS2.show()

Multiple agg functions using Over Window (Concat & max )

1 Answers