Ранг и группировка в наборе данных Spark ⇐ JAVA

[Гость]

У меня есть приведенный ниже набор данных в Spark, я новичок в Spark

Код: Выделить всё

ID          , PState , MState  , Date     , TS                       , proc
1           , Iowa   , Kansas  , 20240212 , null                     , 2
1           , Iowa   , CA      , 20250212 , null                     , 4
2           , Maine  , Chicago , 20240212 , 2024-01-01T00:00:00.000Z , 2
2           , NJ     , NY      , 20250212 , null                     , 4
3           , CA     , MS      , 20240212 , null                     , 2
3           , NJ     , NY      , 20240212 , null                     , 4
3           , NJ     , WV      , 20240212 , null                     , 9

I have three conditions , I need to group by on ID
Condition 1 - if TS is present and is greater than any other row for that ID
the PState and MState of that Row needs to be picked up.
Condition 2 - if TS is null or same for all the rows with that ID
the PState and MState of the row with recent Date needs to be picked up.
Condition 3 - if Date is same for all the rows with that ID
the PState and MState of the row with least Proc needs to be picked up.
I tried to do this in multiple conditions and multiple windowspecs but this is not working as expected and the correct PState and MState are not picked up.
the output should look like this.

Код: Выделить всё

ID         , PState , MState  , Date     , TS                        , proc
1           , Iowa   , CA      , 20240212 , null                     , 2
1           , Iowa   , CA      , 20250212 , null                     , 4
2           , Maine  , Chicago , 20240212 , 2024-01-01T00:00:00.000Z , 2
2           , Maine  , Chicago ,20250212 , null                      , 4
3           , CA     , MS      , 20240212 , null                     , 2
3           , CA     , MS      , 20240212 , null                     , 4
3           , CA     , MS      , 20240212 , null                     , 9

This is what I have tried

Код: Выделить всё

WindowSpec dateSpec = Window.partitionBy(col("ID"))
.orderBy(col("Date").desc());
Dataset dateRankedDatset = dataset.withColumn(
"daterank",
functions.rank().over(dateSpec).cast(DataTypes.StringType));

I applied two more conditions with Ranks , but I am unable to join the datasets back to produce the result in the output.
I am trying to do this in spark java , can someone pls help me with the optimal way of achieving this.


Источник: https://stackoverflow.com/questions/781 ... rk-dataset