AVG 函数

1、函数名

     AVG()  返回一组数字的平均值。

2、语法

            .-ALL------.                  

 

>>-AVG--(--+----------+--expression--)-------><

           '-DISTINCT-'   

3、解释

   参数必须是一组number类型，并且该number组的和不能大于指定的data type的范围（除了decimal类型），对于decimal类型，该number组的合计必须在decimal包含的31位精度，以及小数位必须在参数指定的范围内。

   使用select into语句的时候 变量的类型，必须和avg()函数返回的精度一致，否则会出现丢失数据精度问题。

4、应用举例

  

--Using the PROJECT table, set the host variable AVERAGE (decimal(5,2)) to the average staffing level (PRSTAFF) of projects in department (DEPTNO) 'D11'.
   SELECT AVG(PRSTAFF)
     INTO :AVERAGE
     FROM PROJECT
     WHERE DEPTNO = 'D11'
--Results in AVERAGE being set to 4.25 (that is 17/4) when using the sample table.
--Using the PROJECT table, set the host variable ANY_CALC (decimal(5,2)) to the average of each unique staffing level value (PRSTAFF) of projects in department (DEPTNO) 'D11'.
   SELECT AVG(DISTINCT PRSTAFF)
     INTO :ANY_CALC
     FROM PROJECT
     WHERE DEPTNO = 'D11'
--Results in ANY_CALC being set to 4.66 (that is 14/3) when using the sample table.