Oracle Analysze

最新推荐文章于 2021-04-28 15:23:13 发布

原创最新推荐文章于 2021-04-28 15:23:13 发布 · 299 阅读

CC 4.0 BY-SA版权

本文详细介绍了SQL中的窗口函数，包括Lag、Lead、First_value、Last_value、Nth_value、Rank、Dense_rank、Row_number、Ratio_to_report、Percent_rank、Percentile_cont、Percentile_disc、Ntile、Listagg、Stddev等函数的使用方法及应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)

1:lag(money, 1) over(partition by game_id order by logtime) last_money

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_value

select logtime, game_id, money, first_value(money) over( partition by game_id order by money desc ) from t_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

5.ratio_to_report

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_rank

select 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 基于列值的离散分布来计算百分位数；结果等于列中的一个特定值。

literal

要计算的百分位数。该值必须介于 0.0 和 1.0 之间。

WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ])

指定要排序的数值列表，并计算百分位数。只允许一个 order_by_expression。默认排序顺序为升序。

OVER ( <partition_by_clause> )

将 FROM 子句生成的结果集划分为数个应用百分位数函数的分区。无法在 PERCENTILE_DISC 函数中指定 <ORDER BY 子句> 和 <rows 或 range 子句>。

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