sql—配置单元查询中的前向滚动平均值

jdg4fx2g 于 2021-06-25 发布在 Hive

关注(0)|答案(1)|浏览(425)

我想计算“4天窗口”的滚动平均值。请查看下面的详细信息

Create table stock(day int, time String, cost float);

Insert into stock values(1,"8 AM",3.1);
Insert into stock values(1,"9 AM",3.2);
Insert into stock values(1,"10 AM",4.5);
Insert into stock values(1,"11 AM",5.5);
Insert into stock values(2,"8 AM",5.1);
Insert into stock values(2,"9 AM",2.2);
Insert into stock values(2,"10 AM",1.5);
Insert into stock values(2,"11 AM",6.5);
Insert into stock values(3,"8 AM",8.1);
Insert into stock values(3,"9 AM",3.2);
Insert into stock values(3,"10 AM",2.5);
Insert into stock values(3,"11 AM",4.5);
Insert into stock values(4,"8 AM",3.1);
Insert into stock values(4,"9 AM",1.2);
Insert into stock values(4,"10 AM",0.5);
Insert into stock values(4,"11 AM",1.5);

我写了下面的查询

select day, cost,sum(cost) over (order by day range between current row and 4 Following), avg(cost) over (order by day range between current row and 4 Following) 
from stock

如您所见，我每天得到4条记录，我需要计算4天窗口的滚动平均值。为此，我编写了上面的窗口查询，因为我每天只有4天的数据包含4条记录，第一天的总和将是所有16条记录的总和。基于此，第一条记录的总和为56.20，这是正确的，平均值应为56.20/4（因为有4天），但它是56.20/16，因为总共有16条记录。我该如何解决这个问题的平均部分？
谢谢raj

sql Hive bigdata hql

来源：https://stackoverflow.com/questions/59098332/forward-rolling-average-in-hive-query

1条答案

按热度按时间

oxf4rvwz1#

这就是你想要的吗？

select t.*,
       avg(cost) over (order by day range between current row and 4 following)
from t;

编辑：
你想要的是：

select t.*,
       (sum(cost) over (order by day range between current row and 3 following) /
        count(distinct day) over (order by day range between current row and 3 following)
       )
from t;

但是，hive不支持这一点。为此，可以使用子查询：

select t.*,
       (sum(cost) over (order by day range between current row and 3 following) /
        sum(case when seqnum = 1 then 1 else 0 end) over (order by day range between current row and 3 following)
       )
from (select t.*
             row_number() over (partition by day order by time) as seqnum
      from t
     )t

赞(0）回复(0）举报 2021-06-26

我来回答

sql—配置单元查询中的前向滚动平均值

1条答案

相关问题

热门标签

最新问答