在运行平均值中添加其他分组依据 [英] Adding additional group by in running average

问题描述

我有当前有效的代码

select format_date('%Y%m', date) as yyyymm,
       (sum(sum(val)) over (order by min(date)) / 
        sum(count(*)) over (order by min(date))
       ) as running_avg
from t
group by yyyymm
order by yyyymm;

返回

yyyymm  Score
201712  25.57931742
201801  24.69794466
201802  24.23110781
201803  23.85651947
201804  23.66164799
201805  23.43029053
201806  23.17074628
201807  23.09766588
201808  23.08902284

我现在正尝试为部门添加附加的group by子句.查询运行，但是结果不准确，有人可以识别我在做什么吗?

I am now trying to add an additional group by clause, for department. The query runs however the results are inaccurate, can anyone recognize what i am doing incorrectly?

select format_date('%Y%m', date) as yyyymm, department
       (sum(sum(val)) over (order by min(date)) / 
        sum(count(*)) over (order by min(date))
       ) as running_avg
from t
group by yyyymm, department
order by yyyymm;

返回

yyyymm  department  Score
201712  HR  6.704365079
201712  F&B 8.550338502
201712  Marketing   8.550338502
201712  I.T.    9.857502908
201712  Security    9.551491994
201712  Contractors 9.411654456
201712  Executive Office    9.637075283
201712  Property Services   9.45905826
201712  Corporate   9.57458477
201712  Legal   9.700320268

推荐答案

您需要将department添加到partition by:

select department, format_date('%Y%m', date) as yyyymm,
       (sum(sum(val)) over (partition by department order by min(date)) / 
        sum(count(*)) over (partition by department order by min(date))
       ) as running_avg
from t
group by yyyymm, department
order by department, yyyymm;

这篇关于在运行平均值中添加其他分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！