how do I do a sum over partition in spark sql?

user_15477148 0
Viewed 854

spark sql is just different enough from the engines I use that it breaks all my code

this statement

case when sum(flag = 'Y') over (partition by id) > 0
     then 'Y' else 'N' end as flag

is supposed to return Y if any flag field of a given id is Y and it doesn't work because sum function in spark can only take numeric types. is there a workaround?

apache-spark sql
2 Answers

Your code is not valid SQL -- it happens to work in MySQL but not in most databases.

The Standard SQL approach will work, using a CASE expression:

(case when sum(case when flag = 'Y' then 1 else 0 end) over (partition by id) > 0
     then 'Y' else 'N'
 end) as flag

Or, assuming that flag only takes on the values of 'Y' and 'N', you can simplify the logic to:

min(flag) over (partition by id) as flag
user_11440350

You can cast the Boolean flag = 'Y' to an integer in order to sum it up:

case when sum(int(flag = 'Y')) over (partition by id) > 0
     then 'Y' else 'N' end as flag
user_141657300
Related