Oracle SQL中如何像使用count函数一样用avg计算平均日计数

原写法问题说明 #

你写的avg(per day)属于非法语法：per day既不是表内字段，也不是合法的计算表达式。同时如果直接在mgr+入职日期的分组层级嵌套普通avg聚合，单分组内的count(*)只有1个值，计算出的avg会和当前组的count值完全相等，无法拿到跨分组的日计数平均值。另外你原语句group by子句末尾多写了分号，会导致后续order by无法正常执行，需要把分号移到整个SQL的末尾。

正确实现方式 #

根据你需要的平均值统计维度，有两种常见写法，均基于Oracle支持的分析窗口函数实现，不需要额外写多层嵌套子查询：

• 需求1：每行展示当日计数，同时展示全部分组的日计数整体平均值

select 
  mgr,
  trunc(hiredate) as hire_date,
  count(*) as daily_count,
  avg(count(*)) over () as all_avg_daily_count
from emp
group by mgr, trunc(hiredate)
order by count(*) desc;

逻辑说明：avg(count(*)) over ()会先完成mgr+日期维度的分组计数，再对所有分组返回的计数值求全局平均，把结果附加到每一行。

• 需求2：每行展示当日计数，同时展示当前mgr维度下的日计数平均值

select 
  mgr,
  trunc(hiredate) as hire_date,
  count(*) as daily_count,
  avg(count(*)) over (partition by mgr) as mgr_level_avg_daily_count
from emp
group by mgr, trunc(hiredate)
order by count(*) desc;

逻辑说明：给窗口函数增加partition by mgr分区规则，平均值的计算范围会限定在和当前行同mgr的所有分组结果内。

内容的提问来源于stack exchange，提问作者junkone