oracle：分析数据

select * from temp;ID YEAR MONTH AMOUNT TYPE-------------------------------1 2017 1 120 12 2017 2 80 23 2017 3 20 34 2017 4 30 15 2017 5 20 26 2017 6 10 17 2017 7 180 18 2017 8 4 

一：评级函数
评级函数ranking function用于计算等级、百分位数、n分片等。
1.RANK()和DENSE_RANK()函数
RANK()和DENSE_RANK()：计算数据项在分组中的排名
RANK()在出现等级相同的元素时，会将排名中的位置留出来
DENSE_RANK()在出现等级相同的元素时，不会将排名中的位置留出来
使用NULLS FIRST 和NULLS LAST子句控制空值的排名

select type, sum(amount)rank() over (order by sum(amount) desc nulls last) as rankt,dense_rank() over (order by sum(amount) desc nulls last) as denserankfrom tempwhere year = '2017'group by typeorder by type;TYPE SUM(AMOUNT) RANKT DENSERANK------------------------------------1 340 1 12 100 2 23 20 3 34 4 4

使用PARTITION BY子句
将分组划分为子分组是，可以将PARTITION BY子句和分析函数结合起来使用。

select type, month, sum(amount),rank() over (partition by month order by sum(amount) desc) as ranktfrom tempwhere year = '2017'and amount is not nullgroup by type, monthorder by type, month;

2.使用CUME_DIST()和PERCENT_RANK()函数
CUME_DIST()：计算某个特定值在一组值中的累计分布
PERCENT_RANK()：可以计算某个值相对于一组值的百分比排名（在0到1之间）
3.使用NTILE()函数
NTILE()：计算N分片的值

二.反百分位函数
PERCENTILE_DISC(x):在每一个分组中检查累积分布的数值，直到找到大于或等于x的值。
PERCENTILE_CONT(x):在每一个分组中检查百分比排名的数值，直到找到大于或等于x的值。
例如：

select
percentile_cont(0.6) within group (order by sum(amount) desc) as percentile_cont,
percentile_disc(0.6) witnin group (order by sum(amount) desc) as percentile_disc
from temp
where year = ‘2017’
group by type;

三.使用窗口函数
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定义了窗口的起点和终点
1.NTH_VALUE函数获取第n行
四.使用报表函数
1.使用RATIO_TO_REPORT()函数
用来计算某个值在一组值的总和中所占的比率
2.LISTAGG()函数：对分组内的各行排序并且将分组的值集连接起来
3.LAG()和LEAD()函数
获得位于距当前行指定距离的哪一行中的数据。
四：使用FIRST和LAST函数

select min(month) keep (dense_rank first order by sum(amount)) as highest,min(month) keep (dense_rank last order by sum(amount)) as lowestfrom tempwhere year = '2017'group by monthorder by month;HIGHEST LOWEST--------------------7 6

五：使用线性回归函数

REGR_AVGX(x,y):先去除x或y为空值的x和y数值对，然后返回x的平均值
REGR_AVGY(x,y):先去除x或y为空值的x和y数值对，然后返回y的平均值
REGR_COUNT(y,x):返回可用于拟合回归曲线的非空数值对的个数
REGR_INTERCEPT(y,x)：返回回归曲线在y轴方向的截距
REGR_R2(y,x):返回回归曲线的决定系数或相关系数
REGR_SLOPE(y,x):返回回归曲线的斜率
六：使用假想评级与分布函数

使用model子句
电子表格
示例：

select type, year, month, amountfrom tempwhere type between 1 and 2modelpartition by (type)dimension by (month, year)measures (amount amount) (amount[1, 2018] = amount[1, 2017])order by type, year, month;TYPE YEAR MONTH AMOUNT------------------------1 2017 1 1201 2017 4 301 2017 6 101 2017 7 1801 2018 1 1202 2017 2 802 2017 5 20

使用between和and返回特定范围内的数据单元

amount[1, 2018] = ROUND(AVG(amount)[month between 1 and 3, 2017],2)

使用for循环访问数据单元

amount[for month from 1 to 3 increment 1, 2018] = ROUND(amount[CURRENTV(), 2017] * 1.25, 2)

处理空值

amount[for month from 1 to 3 increment 1, 2018] = case when amount[CURRENTV(), 2017] is present thenROUND(amount[CURRENTV(), 2017] * 1.25, 2)else0end

PIVOT/UNPIVOT子句

select * from (select month,type,amount from temp where year='2017' and type in (1,2,3)pivot(sum(amount) for month in (1 as Jan, 2 as feb, 3 as mar))order by type;TYPE JAN FEB MAR -------------------1 1202 803 20