sql - 如何在 Hive/SQL 中使用权重列进行加权移​​动平均？

Question

我有一列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 中做到这一点。

标签： sqldatabasehiverelational-database

Answer 1

只需使用算术：

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