ORACLE AVG()

오라클 AVG()

 

AVG returns average value of expr.

 

This function takes as an argument any numeric data type or any nonumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

 

If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.

 

Aggregate Example

SQL > SELECT AVG(salary) "Average" from employees ;

   --> 6461.83177570093

 

Analytic Example

The following example calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee: 

 

SQL > SELECT manager_id, EMPLOYEE_ID, last_name, hire_date, salary,
        AVG(salary) OVER(PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
    FROM employees
    ORDER BY manager_id, hire_date, salary ;

 

-->  100 102 De Haan 2001/01/13 17000 17000
      100 114 Raphaely 2002/12/07 11000 14000
      100 122 Kaufling 2003/05/01 7900 11966.6666666667
      100 201 Hartstein 2004/02/17 13000 12225
      100 120 Weiss 2004/07/18 8000 11380

 

처럼 나오지만 잘 모르겠다. 

 

 

SQL> SELECT manager_id, EMPLOYEE_ID, last_name, hire_date, salary,
        AVG(salary) OVER(PARTITION BY manager_id ) AS c_mavg
    FROM employees
    ORDER BY manager_id, hire_date, salary ;    
    

   --> 100 102 De Haan 2001/01/13 17000 11100
        100 114 Raphaely 2002/12/07 11000 11100
        100 122 Kaufling 2003/05/01 7900 11100
        100 201 Hartstein 2004/02/17 13000 11100

 

간단하게 manager_id별 평균도 함께 나오도록 하였다.