时间戳

首页 > 解决方案 > Hive 中的窗口函数 avg with - over（按 colName 排序）

hive - Hive 中的窗口函数 avg with - over（按 colName 排序）

问题描述

我试图了解窗口函数 avg 的工作原理，并且不知何故它似乎没有按我的预期工作。

这是数据集：

select * from winsales;
+-------------------+------------------+--------------------+-------------------+---------------+-----------------------+--+
| winsales.salesid  | winsales.dateid  | winsales.sellerid  | winsales.buyerid  | winsales.qty  | winsales.qty_shipped  |
+-------------------+------------------+--------------------+-------------------+---------------+-----------------------+--+
| 30001             | NULL             | 3                  | b                 | 10            | 10                    |
| 10001             | NULL             | 1                  | c                 | 10            | 10                    |
| 10005             | NULL             | 1                  | a                 | 30            | NULL                  |
| 40001             | NULL             | 4                  | a                 | 40            | NULL                  |
| 20001             | NULL             | 2                  | b                 | 20            | 20                    |
| 40005             | NULL             | 4                  | a                 | 10            | 10                    |
| 20002             | NULL             | 2                  | c                 | 20            | 20                    |
| 30003             | NULL             | 3                  | b                 | 15            | NULL                  |
| 30004             | NULL             | 3                  | b                 | 20            | NULL                  |
| 30007             | NULL             | 3                  | c                 | 30            | NULL                  |
| 30001             | NULL             | 3                  | b                 | 10            | 10                    |
+-------------------+------------------+--------------------+-------------------+---------------+-----------------------+--+

当我触发以下查询时->

select salesid, sellerid, qty, avg(qty) over (order by sellerid) as avg_qty from winsales order by sellerid,salesid;

我得到以下信息->

 +----------+-----------+------+---------------------+--+
| salesid  | sellerid  | qty  |       avg_qty       |
+----------+-----------+------+---------------------+--+
| 10001    | 1         | 10   | 20.0                |
| 10005    | 1         | 30   | 20.0                |
| 20001    | 2         | 20   | 20.0                |
| 20002    | 2         | 20   | 20.0                |
| 30001    | 3         | 10   | 18.333333333333332  |
| 30001    | 3         | 10   | 18.333333333333332  |
| 30003    | 3         | 15   | 18.333333333333332  |
| 30004    | 3         | 20   | 18.333333333333332  |
| 30007    | 3         | 30   | 18.333333333333332  |
| 40001    | 4         | 40   | 19.545454545454547  |
| 40005    | 4         | 10   | 19.545454545454547  |
+----------+-----------+------+---------------------+--+

问题是 - 如何计算 avg(qty)。由于我没有使用分区依据，我希望所有行的 avg(qty) 都相同。

有任何想法吗 ？

标签： hivewindowing

解决方案

如果您想为所有行获取相同的 avg(qty)然后在 over 子句中删除 order by sellerid，那么您将获得所有行的19.545454545454547值。

查询以获取所有行的相同 avg(qty)：

hive> select salesid, sellerid, qty, avg(qty) over () as avg_qty from winsales order by sellerid,salesid;

如果我们包含order by sellerid在over 子句中，那么您将获得为每个卖家 ID 计算的累积平均值。即对于

sellerid 1 you are having 2 records total 2 records with qty as 10,30 so avg would be 
     (10+30)/2.
sellerid 2 you are having 2 records total 4 records with qty as 20,20 so avg would be 
     (10+30+20+20)/4 = 20.0
sellerid 3 you are having 5 records total 9 records with qty as  so 10,10,15,20,30 avg would be 
(10+30+20+20+10+10+15+20+30)/9 = 18.333
sellerid 4 avg is 19.545454545454547

当我们包含 over 子句时，这是 hive 的预期行为。

推荐阅读