Oracle分析查询

分析查询有好几种，感觉还是挺有用滴。

一.窗口模式
将需要计算的值进行累加处理，移动处理。
比如有产品销售表:  PRODUCT_SELL  内有若个字段。

select   sum (sold_money) amount,  sum ( sum (sold_money))  over  ( order   by  product_type rows 
between  unbounded  preceding  and   current  row) accumulativeTotal 
from  product_sell  group   by  product_type 

这句是做累加值处理，就是将从第一行到当前行所获得sum(sold_money)累加起来得到的值。
获得的结果如下，可以看出accumulativeTotal在不停累计amount的值。

AMOUNT   ACCUMULATIVETOTAL
1000              1000
1001              2001
1800              3801
1855              5656
258              5914
2045              7959
12560              20519
2545              23064

还有一种是获得移动值。

select   sum (sold_money) amount,  sum ( sum (sold_money))  over  ( order   by  product_type rows
between   2   preceding  and   current  row) avgAmount 
from  product_sell  group   by  product_type 

获得结果如下，可以看出avgAmount是累计值的基础上作了个求平均值的处理，觉得叫平均值更恰当些。

AMOUNT   AVGAMOUNT
1000              1000
1001              1000.5
1800              1267
1855              1552
258              1304.33333333333
2045              1386
12560              4954.33333333333
2545              5716.66666666667

二.制表模式
类似窗口模式，计算各种合计值和比率值。

select  sale_user, product_type,  sum (sold_money), 
avg ( sum (sold_money))  over  (partition  by  sale_user), 
min ( sum (sold_money))  over  (partition  by  sale_user) 
from  product_sell  group   by  sale_user, product_type

avg计算每个人对某类产品的基于销售区域的平均销售额
min计算每个人对某类产品的基于销售区域的最低销售额

ratio_to_report函数，用来计算某个值与总计值的比率，如下。

select  sale_user, product_type,  sum (sold_money), 
avg ( sum (sold_money))  over  (partition  by  sale_user), 
min ( sum (sold_money))  over  (partition  by  sale_user),
ratio_to_report( sum (sold_money))  over  (partition  by  sale_user)  
from  product_sell  group   by  sale_user, product_type

三.检索当前行的周围行
顾名思义

select   sum (sold_money), lag( sum (sold_money),  1 )  over  ( order    by  product_type)  from  product_sell 
group   by  product_type

结果如下，可以看到newAmount向后移了一行，若要移多行可修改上面sql的 1 。

AMOUNT   NEWAMOUNT
1000     
1001          1000
1800          1001
1855          1800
258          1855
2045          258
12560          2045
2545          12560

向前移动将lag()改为lead()。

四.等级检索

select   sum (sold_money), rank()  over  ( order   by   sum (sold_money))  from  product_sell  group   by  product_type

rank()和dense_rank()函数都用作排序
rank()允许重复序数，若存在重复，则下一行会发生跳跃。比如两行序数都为2，下一行序数就是4。
dense_rank()同上，只是不会发生跳跃。
还可以指定倒序desc。

cume_dist()会返回一行数据在组中的相对位置，一般是大于0，小于等于1的数
percent_rank()同上，算法是减1，再除1。也是返回0到1之间的数。

五.线性回归函数
根据一元一次方程的原理，y=ax+b;  当知道某一对x,y的值，则可以求出a和b，然后既可根据任何一个x得到y的值。
主要的函数：
regr_avgx(y, x)             计算x的平均值
regr_avgy(y, x)             计算y的平均值
regr_count(y, x)           统计x和y对的数量
regr_intercept(y, x)     回归直线的截距
regr_r2(y, x)                 x和y的相关系数
regr_slope(y, x)          回归直线的斜率
regr_sxx(y, x)               等于reg_count(y, x) * var_pop(x)，其中var_pop(x) 为样本方差
regr_sxy(y, x)               等于reg_count(y, x) * covar_pop(y, x)，其中covar_pop(y, x) 为样本协方差
regr_syy(y, x)               等于reg_count(y, x) * var_pop(y)，其中var_pop(y) 为样本方差

select  regr_slope(product_amount,  year ), regr_intercept(product_amount,  year )  from  product_sell

将历年来所销售的产品数量和年份建立一元一次方程，得到该方程的系数a和b，则可以用来预测未来某年的销售数量。