hiveql - How to get average of all numbers (not a group) in hive?

Question

I have a table, and I wish to add a column which is the average of all lines (not by group).

For example, the table is like:

  name    num
   a      1
   b      2

I wish to get:

  name    num   avg_num
   a      1     1.5
   b      2     1.5

I tried this:

select name, num, avg(num) 
from table 
group by name, num

However, it returns

  name    num   avg_num
   a      1     1
   b      2     2

So how can I achieve my goal?

Answer 1

You can specify an empty window (which covers the whole table)

select name, num, avg(num) over() as avg_num from table;