Oracle分析函数二——函数用法

SELECT 
  manager_id, 
  first_name||' '||last_name employee_name,
  hire_date,
  salary,
  AVG(salary) OVER (PARTITION BY manager_id) avg_salary
FROM employees;

SELECT 
  a.manager_id,
  a.employee_name,
  a.hire_date,
  a.salary,
  b.avg_salary
FROM 
( 
  SELECT 
    manager_id, 
    first_name||' '||last_name employee_name,
    hire_date,
    salary
    FROM employees
) a,
(
  SELECT 
    manager_id,
    AVG(salary) avg_salary
    FROM employees
   GROUP BY manager_id
) b
WHERE a.manager_id=b.manager_id
ORDER BY a.manager_id

--Order by按相应的值(hire_date)进行排序并累计统计

SELECT 
  manager_id, 
  first_name||' '||last_name employee_name,
  hire_date,
  salary,
  AVG(salary) OVER (ORDER BY hire_date) 
FROM employees;

--Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
SELECT 
  manager_id, 
  first_name||' '||last_name employee_name,
  hire_date,
  salary,
  AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date) 
FROM employees;

--该平均值由当前员工和与之具有相同经理的前一个和后两个三者的平均数得来

--Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
--该平均值由当前员工和与之具有相同经理的前一个和后两个三者的平均数得来
SELECT 
  manager_id, 
  first_name||' '||last_name employee_name,
  hire_date,
  salary,
  AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) 
FROM employees;

--该平均值由当前员工和与之具有相同经理,并且雇用时间在该员工时间之前的50天以内和在该员工之后的150天之内员工的薪水的平均值
--range为取值范围，估计只有数字和日期能够进行取值了

--Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
--该平均值由当前员工和与之具有相同经理,并且雇用时间在该员工时间之前的50天以内和在该员工之后的150天之内员工的薪水的平均值
--range为取值范围，估计只有数字和日期能够进行取值了
SELECT 
  manager_id, 
  first_name||' '||last_name employee_name,
  hire_date,
  salary,
  AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) 
FROM employees;

--该平均值由当前员工和与之具有相同经理的平均值
--每行对应的数据窗口是从第一行到最后一行

--Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by的值(hire_date)进行累计统计
--该平均值由当前员工和与之具有相同经理的平均值
--每行对应的数据窗口是从第一行到最后一行
SELECT 
  manager_id, 
  first_name||' '||last_name employee_name,
  hire_date,
  salary,
  AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date) avg_salary_part_order,
  AVG(salary) OVER (PARTITION BY manager_id ) avg_salary_order,  
  AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) avg_salary_unbound1, --等同于仅partition时候的值
  AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) avg_salary_unbound2--等同于上面
FROM employees;