How to get Sum(Column) over (partition by other columns)?

Question

I am trying to convert Teradata code written like below

Select A.col1, sum(A.metric1) over (partition by A.col1, B.col1 order by   
  A.col2 asc) as Cust_col, B.col1 from A JOIN B on (A.join_key=B.join_key) 
  where A.col3='X' QUALIFY ROW_NUMBER () OVER (PARTITION BY A.col1,B.COL1
  ORDER BY A.col3 DESC) = 1

In Hive:

Select C.col1,C.cust_col,C.col1,ROW_NUMBER () OVER (PARTITION BY A.col1,C.COL1 
  ORDER BY C.col3 DESC) as Row_num from (Select A.col1, sum(A.metric1) over 
  (partition by A.col1, B.col1 order by A.col2 asc) as Cust_col,B.col1 from A 
  JOIN B on (A.join_key=B.join_key) where A.col3='X') C where C.Row_num =1

But, I am getting error like

SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: Primitve type DATE not supported in Value Boundary expression

I know it is because with Sum(A.metric1) partition is creating a problem here, but how to resolve this?

I don't know Hive but I think it there is also something like a schema. And I think it would help to see at least the types of the columns. — Kani
Wrong reference: where C.ROW_NUM=1. C.row_num does not exists. Use sub-query to filter by row_num — leftjoin
Hello leftjoin, indeed I am doing row_num operation inside the query and filtering the duplicates using C.Row_num=1... I am not sure am I clear with my requirement. — Ma28
Which column is DATE? it is not clear. is it col3 which is used in order by? — leftjoin

leftjoin leftjoin · Accepted Answer · 2019-03-21T12:58:18

select a_col1,
       sum(metric1) over (partition by a_col1, b_col1 order by a_col2 asc) as Cust_col, 
       b_col1
from
(
Select A.metric1, A.col1 a_col1, B.COL1 b_col1, A.col2 a_col2
       ROW_NUMBER () OVER (PARTITION BY A.col1,B.COL1 ORDER BY A.col3 DESC ) as rn
from A JOIN B on (A.join_key=B.join_key) 
  where A.col3='X' 

) s 
where rn=1

How to get Sum(Column) over (partition by other columns)?

1 Answers