oracle 分析函数（窗口函数 over）

分析函数基本格式

方法 over(partition by [字段] order by [字段] [row ……])
partition by：分组
order by：排序
[row]：窗口

三个例子先：

select row_number() over(partition by p_type, order by p_id) as pno
  from ...
--根据分类 p_class 分类，每个分类下根据 p_id 排序，生成序号

select max(grade) over(partition by stuname) as maxgrade
from ...
--查询每个学生成绩的最大值
--注意，区别于聚合函数，查询结果条目不会发生变化，每一条数据都有一个对应的 maxgrade

select t.*,
       t.ocount  sumocount as par
  from (select t.*,
               sum(ocount) over(partition by pclass) as sumocount
          from ... t) t
--查询每个科目下的子项数量（ocount）占科目总量（sumocount）的占比


select ratio_to_report(ocount) over(partition by pclass) as par
  from ...
--查询每个科目下的子项数量（ocount）占科目总量（sumocount）的占比

灵活运用聚合函数、分组、排序，开发多样化的查询。

常用的方法整理：

count() over()
sum() over()
avg() over()
max() over()
min() over()
row_number() over()         --排序
first_value() over()        --取出分区中第一条记录的字段值
last_value() over()         --取出分区中最后一条记录的字段值
lag() over()                --偏移函数，取出前n行数据
lead() over()               --偏移函数，取出后n行数据
ratio_to_report() over()    --占比函数
sqrt() over()               --开方

一些你可能感兴趣的数学 方法：

stddev() over()             --计算样本标准差，只有一行数据时返回0
stddev_samp() over()        --计算样本标准差，只有一行数据时返回null
stddev_pop() over()         --计算总体标准差
variance() over()           --计算样本方差，只有一行数据时返回0
var_samp() over()           --计算样本方差，只有一行数据时返回null
var_pop() over()            --计算总体方差
stddev() over()             --sqrt( variance() )
stddev_samp() over()        --sqrt( var_samp() )
stddec_pop over()           --sqrt( var_pop() )
covar_samp over()           --返回一对表达式的样本协方差
covar_pop over()            --返回一堆表达式的总体协方差
corr() over()               --返回一对表达式的相关系数

是否发现 Oracle 的分析函数极其强大，实际上，Oracle 还支持更多数学计算，包括回归计算，详细可以在 Oracle 官方文档查找，

https://docs.oracle.com/en/database/oracle/oracle-database/index.html

这里不细细列出来了，传统开发只需要指导上述常用方法即可，毕竟数据分析计算对于资源的压力很大，大多不在数据库中进行。

另外，官方文档是个非常好的东西，有空翻一下，会发现很多惊喜。

关于 row

在上述格式中还有一块 [row ……]，可以理解为限制行的 where 条件，

rows between unbounded preceding and unbounded following
--unbounded：无限
--preceding：在之前
--unbounded：在之后
--between 无限之前 and 无限之后 = 表中所有记录


rows between unbounded preceding and current row
--表中第一行到当前行


rows between current row and unbounded following
--表中当前行到最后一行


rows between 1 preceding and current row
--表中上一行到当前行


rows between current row and 1 following
--表中当前行到下一行

用途也很广泛，例如查询一个商品的当月累计销售量，按天展示，得到的结果就可以是：

使用的是：

rows between unbounded preceding and current row

实际上即便不使用 row 也能得到这样的情况，这里只是举例：

sum(ordcount) over(order by orddate)