hadoop - hive 中的平均计数

Question

我有一个 hive 表

id name ....
 1   a
 2   a
 3   a
 4   b
 5   b
 6   b
 7   a

需要求名字出现次数的平均值，

  name  avg
   a    0.57 (4/7)
   b    0.42 (3/7)

请给我一个查询建议。

Answer 1

 select      name
            ,round (count(*) / sum(count(*)) over (),2) as avg
 from        mytable
 group by    name
 order by    name
 ;

---

+------+------+
| name | avg  |
+------+------+
| a    | 0.57 |
| b    | 0.43 |
+------+------+