Migrating window functions from SQL to spark scala

Question

Here's some SQL expression that I'm trying to migrate to spark scala.

SELECT
 a.senderId,
 b.company_id,
 ROW_NUMBER() OVER(PARTITION BY a.senderId ORDER BY b.chron_rank) AS rnk
FROM df1 a
JOIN df2 b
ON a.senderId = b.member_id
WHERE a.datepartition BETWEEN concat(b.start_date,'-00') AND concat(b.end_date,'-00')

I'm a little lost with the window function, I started something like this,

val temp = df2.join(df1, $"dimPosition.member_id" === $"df1.senderId")
    .select($"df1.senderId", $"df2.company_id")
    .......

Where are you facing issues. It should be straightforward, no? — Som
– Som, Commented Jul 24, 2020 at 3:25

Som · Accepted Answer · 2020-07-24 03:31:29Z

1

Try this-

df2.as("b")
      .join(df1.as("a"), $"a.senderId" === $"b.member_id" && $"a.datepartition".between(
        concat($"b.start_date",lit("-00")), concat($"b.end_date", lit("-00")))
      )
      .selectExpr("a.senderId",
        "b.company_id",
        "ROW_NUMBER() OVER(PARTITION BY a.senderId ORDER BY b.chron_rank) AS rnk")

answered Jul 24, 2020 at 3:31

Som

6,3681 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Heil · Accepted Answer · 2020-07-24 18:19:09Z

0

Try this .. may be you will face issue for where clause ..

val temp = df2.join(df1, $"dimPosition.member_id" === $"df1.senderId")
  .select($"df1.senderId", $"df2.company_id")
  .withColumn('rnk', ROW_NUMBER() OVER Window.partitionBy("senderId",")
  .orderBy("chron_rank"))
  .where(datepartition BETWEEN concat(b.start_date,'-00') AND concat(b.end_date,'-00'))

edited Jul 24, 2020 at 18:19

Michael Heil

18.8k6 gold badges55 silver badges90 bronze badges

answered Jul 24, 2020 at 3:37

HimanshuSPaul

3151 gold badge6 silver badges20 bronze badges

Collectives™ on Stack Overflow

Migrating window functions from SQL to spark scala

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related