oracle：分析数据

select * from temp;
ID YEAR MONTH AMOUNT TYPE
-------------------------------
1 2017 1 120 1
2 2017 2 80 2
3 2017 3 20 3
4 2017 4 30 1
5 2017 5 20 2
6 2017 6 10 1
7 2017 7 180 1
8 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 denserank
from temp
where year = '2017'
group by type
order by type;

TYPE SUM(AMOUNT) RANKT DENSERANK
------------------------------------
1 340 1 1
2 100 2 2
3 20 3 3
4 4 4

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

select type, month, sum(amount),
rank() over (partition by month order by sum(amount) desc) as rankt
from temp
where year = '2017'
and amount is not null
group by type, month
order 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 lowest
from temp
where year = '2017'
group by month
order 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, amount
from temp
where type between 1 and 2
model
partition 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 120
1 2017 4 30
1 2017 6 10
1 2017 7 180
1 2018 1 120
2 2017 2 80
2 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 then
ROUND(amount[CURRENTV(), 2017] * 1.25, 2)
else
0
end

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 120
2 80
3 20