Removing duplicate array structs by last item in array struct in Spark Dataframe

Viewed 1291

So my table looks something like this:

customer_1|place|customer_2|item          |count
-------------------------------------------------
    a     | NY  | b        |(2010,304,310)| 34
    a     | NY  | b        |(2024,201,310)| 21
    a     | NY  | b        |(2010,304,312)| 76
    c     | NY  | x        |(2010,304,310)| 11
    a     | NY  | b        |(453,131,235) | 10

I've tried doing, but this does not eliminate the duplicates as the former array is still there (as it should be, I need it for end results).

val df=  df_one.withColumn("vs", struct(col("item").getItem(size(col("item"))-1), col("item"), col("count")))
      .groupBy(col("customer_1"), col("place"), col("customer_2"))
      .agg(max("vs").alias("vs"))
      .select(col("customer_1"), col("place"), col("customer_2"), col("vs.item"), col("vs.count"))

I would like to group by customer_1, place and customer_2 columns and return only array structs whose last item (-1) is unique with the highest count, any ideas?

Expected output:

customer_1|place|customer_2|item          |count
-------------------------------------------------
    a     | NY  | b        |(2010,304,312)| 76
    a     | NY  | b        |(2010,304,310)| 34
    a     | NY  | b        |(453,131,235) | 10
    c     | NY  | x        |(2010,304,310)| 11
1 Answers
Related