AVG

来源：互联网发布：阿里巴巴数据图片尺寸编辑：程序博客网时间：2024/05/21 10:37

1）作为聚合函数

语法：

avg([distinct|all]expr)

作用：

返回关于expr的平均值。可以用于group by中的分组求各组的平均值。

例子：

select avg(distinct salary) result from hr.employees;

RESULT
----------
7037.5

select avg(all salary) result from hr.employees;

RESULT
----------
6463.55140

select avg(salary) result from hr.employees;

RESULT
----------
6463.55140

select job_id,avg(distinct salary) result from hr.employees group by job_id;

JOB_ID              RESULT
--------------- ----------
AC_ACCOUNT            8300
AC_MGR               12000
AD_ASST               4400
AD_PRES              24000
AD_VP                17000
FI_ACCOUNT            7920
FI_MGR               12000
HR_REP                6500
IT_PROG               6000
MK_MAN               13000
MK_REP                6000
PR_REP               10000
PU_CLERK              2780
PU_MAN               11000
SA_MAN               12200
SA_REP                8340
SH_CLERK        3330.76923
ST_CLERK        2838.46154
ST_MAN                7280

select job_id,avg(all salary) result from hr.employees group by job_id;

JOB_ID              RESULT
--------------- ----------
AC_MGR               12000
AC_ACCOUNT            8300
IT_PROG               5760
ST_MAN                7280
AD_ASST               4400
PU_MAN               11000
SH_CLERK              3225
AD_VP                17000
FI_ACCOUNT            7920
MK_MAN               13000
PR_REP               10000
FI_MGR               12000
PU_CLERK              2780
SA_MAN               12200
MK_REP                6000
AD_PRES              24000
SA_REP                8350
HR_REP                6500
ST_CLERK              2785

select job_id,avg(salary) result from hr.employees group by job_id;

2）作为分析函数

语法：

AVG([distinct|all] expr) over (analytic_clause)

作用：

按照analytic_clause中的规则求分组平均值。

例子：

求全部员工中salary比该员工相同job_id的平均salary大的所有员工名单。

select * from (
SELECT department_id,job_id, last_name, hire_date, salary,
   AVG(all salary) OVER (PARTITION BY job_id ORDER BY hire_date
   ROWS BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) AS c_mavg
   FROM employees
   ) where salary > c_mavg order by job_id

结果集略。