Oracle Analysze
来源:互联网 发布:淘宝客推广具体步骤 编辑:程序博客网 时间:2024/04/18 09:06
1.Lag:
select logtime,
game_id,
money,
last_money,
money - last_money,
nvl((money - last_money) / money, 0) * 100
from (select logtime,
game_id,
money,
lag(money, 1) over(partition by game_id order by logtime) last_money
from t_dw_au_billlog_temp
order by logtime, game_id)
lag(money, 1):money所需获取的数据列 1:向前的天数可以是n
over(partition by game_id order by logtime) 以什么分区和排序。
2.Lead:
与Lag相同 lag向前取值,lead向后取值。
3.first_value/last_value/nth_valueselect logtime, game_id, money, first_value(money) over( partition by game_id order by money desc ) fromt_dw_au_billlog_temp
first_value(money) over( partition by game_id order by money desc ) :first_value(money)要取值的列 over(partition by game_id order by money desc)
取值为相对值 按照排序进行排列。
last_value/first_value/nth_value 类型相同。
4.rank/dense_rank/row_number
1.rank排序是有重复数据是会标记同一level下一个跳过,dense_rank 不会跳过,row_number按顺序分配level
例如:rank:1,2,3,3,5
denese_rank:1,2,3,3,4
row_number:1,2,3,4,5
select logtime,game_id,money,dense_rank() over(partition by logtime order by money desc) from t_dw_au_billlog_temp
select logtime,game_id,money,100*ratio_to_report(money) over(partition by logtime ) from t_dw_au_billlog_temp
计算各数值在全部数据中的比例
ratio_to_report(money) over(partition by logtime ):ratio_to_report(money) 计算百分比列 over(partition by logtime) 分区列
6.percent_rankselect logtime,game_id,money,percent_rank() over(partition by logtime order by money) from t_dw_au_billlog_temp
7.percentile_count
percentile_cont函数对于计算内插值是非常有用的。percentile_cont函数接收一个0到1之间的几率值并返回与声明了排序的percent_rank函数计算值相等的内插值百分比。
percentile_con(expr) within group(sort-clause) over(partition-clause order-by-clause)
select logtime,
game_id,
money,
percentile_cont(0.25) within group (order by money desc) over(partition by logtime)
from t_dw_au_billlog_temp
8.percentile_disc
PERCENTILE_DISC 基于列值的离散分布来计算百分位数;结果等于列中的一个特定值。
select logtime, game_id, money, percentile_disc(0.9) within group (order by money ) over(partition by logtime) from t_dw_au_billlog_temp
9.ntile
ntile将数据分组成多个单元
ntile(a) over(partition by column order by column)
select logtime, game_id, money, ntile(30) over(partition by game_id order by money desc) from t_dw_au_billlog_temp
a:需要将数据分成的组数。
10.listagg
字符串聚合就是按照分组把多行数据串联成一行
listagg(column,',') within group(order by column) [over(partition by column)]
select logtime,
listagg(game_id, ',') within group(order by game_id)
from t_dw_au_billlog_temp
group by logtime
11.stddev
StdDev返回expr的样本标准偏差
STDDEV([DISTINCT | ALL] value) [OVER (analytic_clause)]
select stddev(money) from t_dw_au_billlog_temp
0 0
- Oracle Analysze
- Oracle???
- oracle
- oracle
- oracle
- oracle
- oracle...
- oracle
- oracle
- ORACLE
- Oracle
- ORACLE
- Oracle
- Oracle
- oracle
- oracle
- oracle
- ORACLE
- Linux VNC 安装
- Oracle .bash_profile
- Oracle Limits.conf
- Oracle Login
- Oracle Sysctl.conf
- Oracle Analysze
- 单位换算
- Oracle 参数设置
- Android 5.1 修改系统默认语言
- Oracle 软件包安装
- Oracle 修改字符集
- 在linux下查看oracle错误
- oracle重启与关闭
- Oracle分区表修改分区名