分析查询有好几种,感觉还是挺有用滴。
一.窗口模式
将需要计算的值进行累加处理,移动处理。
比如有产品销售表: PRODUCT_SELL 内有若个字段。
between unbounded preceding and current row) accumulativeTotal
from product_sell group by product_type
这句是做累加值处理,就是将从第一行到当前行所获得sum(sold_money)累加起来得到的值。
获得的结果如下,可以看出accumulativeTotal在不停累计amount的值。
1000 1000
1001 2001
1800 3801
1855 5656
258 5914
2045 7959
12560 20519
2545 23064
还有一种是获得移动值。
between 2 preceding and current row) avgAmount
from product_sell group by product_type
获得结果如下,可以看出avgAmount是累计值的基础上作了个求平均值的处理,觉得叫平均值更恰当些。
1000 1000
1001 1000.5
1800 1267
1855 1552
258 1304.33333333333
2045 1386
12560 4954.33333333333
2545 5716.66666666667
二.制表模式
类似窗口模式,计算各种合计值和比率值。
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函数,用来计算某个值与总计值的比率,如下。
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
三.检索当前行的周围行
顾名思义
group by product_type
结果如下,可以看到newAmount向后移了一行,若要移多行可修改上面sql的 1 。
1000
1001 1000
1800 1001
1855 1800
258 1855
2045 258
12560 2045
2545 12560
向前移动将lag()改为lead()。
四.等级检索
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) 为样本方差
将历年来所销售的产品数量和年份建立一元一次方程,得到该方程的系数a和b,则可以用来预测未来某年的销售数量。