ORACLE分析函数笔记

oracle官网文档中，分析函数的标准语法如下：

analytic_function(arg1,arg2) over(analytic_clause)

1.analytic_function为分析函数

2.arg1,arg2为分析函数的参数

3.over用来标识函数是一个分析函数

4.analytic_clause用来确定分析函数的操作规则，包括query_partition_clause/order_by_clause/windowing_clause3个子句。

select t.employee_id,
       t.department_id,
       t.hire_date,
       t.salary,
       round(avg(t.salary) over(partition by t.department_id)) avg_salary,
       round(sum(t.salary) over(partition by t.department_id)) sum_salary,
       round(max(t.salary) over(partition by t.department_id)) max_salary,
       round(min(t.salary) over(partition by t.department_id)) min_salary,
       round(count(t.salary) over(partition by t.department_id)) conut_salary
  from employees t
 where t.department_id in (10, 30, 50, 60);

--range具体用法

 select t.employee_id,
       t.department_id,
       t.hire_date,
       t.salary,
      --sum(t.salary) over(partition by t.department_id) sum_salary_part_order
      sum(t.salary) over(partition by t.department_id order by t.hire_date range between unbounded preceding and 365/*value_expr*/ preceding) sum_1,
      sum(t.salary) over(partition by t.department_id order by t.hire_date range between unbounded preceding and 365/*value_expr*/ following) sum_2
  from employees t
 where t.department_id in (10, 30, 50, 60);

rows和range不同在于range是逻辑范围，rows是物理范围。