Need a help on scala query


#1

Hi All,

Thanks in Advance!

I am working with scala code and in a need to query which will produce below output:

Source Table :

ID yearMnth Marks
1 201612 25
1 201701 85
1 201702 72
1 201703 73
1 201704 73
1 201706 76

Target Table required :

ID yearMnth Marks TotMrksinlast4year
1 201612 25 25
1 201701 85 110
1 201702 72 182
1 201703 73 255
1 201704 73 303
1 201706 76 222

Basically It should add previous 4 year marks in new column (TotMrksinlast4year) i.e if year is 201706 then it should add mark from 201703. I tried using windows function like below but it does not work for me.

sum( case when yearMnth between yearMnth -4 and yearMnth then Marks else 0 end) over (Partition by ID order by yearMnth desc) as TotMrksinlast4year

Appreciate any help. Do let me know if any other way to do it. Thank you!


#2

I might be wrong, but it seems like we’d need a lot more of the problem example to help you. Like is this Spark usage? Is there a DSL involved? Etc. At least show your imports. Show the setup for that one-liner in other words.


#3

Sorry I didnt understand your requirements. I just need a query (Thrift/Scala query) which can produce below results
image


#4

Hello,

You might want to check out the Thrift user mailing list
https://thrift.apache.org/mailing.

 Best, Oliver