CODEWITHSUNDEEP
hive
jamborta
jamborta

Reputation: 5220

use aggregate values as intermediate results in column calculation Hive

I would like to calculate a column average in Hive and deduct it from each row, as follows:

select *,(column1 - avg(column2)) from table

is this possible to do this a single query?

thanks,

Upvotes: 0

Views: 782

Answers (1)

R. Chevallier
R. Chevallier

Reputation: 116

It's possible using SQL analytics available in Hive 0.12, with a data window being the full table

 select *, column1 - (avg(column2) over ()) from table

Otherwise you can calculate the avg(column2) in a subquery and join to it

 select *, column1 - avg_col2
 from table t
 full join (
      select avg(column2) as avg_col2
      from table 
      ) a

Upvotes: 1

Related Questions