如何在hive/sql中对权重列进行加权移动平均?

mzaanser  于 2021-06-24  发布在  Hive
关注(0)|答案(1)|浏览(947)

我有一个专栏 weights 还有一列 recall ,我想计算 recall 过去7天基于 weights ,我已经有了计算移动平均值的查询 OVER 然而,对于计算加权移动平均数,我认为我将不得不用 AVG 将函数聚合到其他一些函数,这些函数包含两列,如 WEIGHTED_AVG(recall, weights) ,有什么办法吗?这是我目前的问题。

SELECT 
weights,
recall,
avg(recall) OVER(ROWS 6 PRECEDING) AS recall_ma_week FROM TableA;

例子 TableA 可能是:

recall weight
 0.5      3
 0.2      2
 0.3      4
 0.4      2
 0.3      6
 0.2      8

例如,对于最后一行,我要计算的数字应该是 0.2*8 + 0.3*6 + ... / (8 + 6 + ...) ,在公式中, sum(weight * recall) / sum(weight) ,什么时候 sum(weight) == 0 ,将该值返回为1.0。如果我能够创建一个定制的聚合函数,那就太棒了,但我不知道如何在纯sql中实现它。

gopyfrb3

gopyfrb31#

只需使用算术:

SELECT (sum(recall * weight) OVER (ORDER BY report_created_at ASC ROWS 6 PRECEDING) /
        (case when sum(weight) OVER (ORDER BY report_created_at ASC ROWS 6 PRECEDING), 0) <> 0
              then sum(weight) OVER (ORDER BY report_created_at ASC ROWS 6 PRECEDING), 0)
         end)
       ) AS recall_ma_week

相关问题