（七）分析函数
function ()over(partition by column order by column rows .. )
rows 默认值：rows between unbounded preceding and current row
rows between
unbounded preceding / X preceding /x following /current row /
and
unbounded following / x preceding /x following /current row
1.聚合类分析函数
聚合函数：
sum()
avg()
max()
min()
count()
SELECT score.*,round(avg(score)over()) FROM score
-- over()分组范围默认整张表 再求平均
SELECT score.*,round(avg(score)over(PARTITION BY cid),2) FROM SCORE
-- over(PARTITION BY cid) 表示按照cid进行分页之后再求平均
SELECT score.*,sum(score)over( ORDER BY sid) FROM SCORE
-- over( ORDER BY sid) 表示 按照sid进行升序排序，并进行累加求分数总和
SELECT score.*,sum(score)over( PARTITION BY cid ORDER BY score) FROM SCORE
-- over( PARTITION BY cid ORDER BY score) 表示表示按照cid进行分页之后，
再按照sid进行升序排序，并进行累加求分数总和2.排序类分析函数
rank() – 跳跃排名
dense_rank() – 非跳跃排名
row_number() – 序号
SELECT score.*,rank()over(PARTITION BY cid ORDER BY score desc)
FROM score
SELECT score.*,dense_rank()over(PARTITION BY cid ORDER BY score desc)
FROM score
SELECT score.*,row_number()over(PARTITION BY cid ORDER BY score desc)
FROM score
3.偏移类分析函数
lag(string,n,df_val) 向上偏移
lead(string,n,df_val) 向下偏移
string:需要偏移的字段
n:向上或者向下偏移的行数
df_val：偏移后未找到任何记录后的默认值
SELECT score.*,lag(score,1,null)over(PARTITION BY cid ORDER BY score desc)
FROM score
SELECT score.*,lead(score,1,null)over(PARTITION BY cid ORDER BY score desc)
FROM score
4 移动窗口
Range
--求采购订单成交记录中 同物料过去一年的采购平均单价
SELECT
W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID,
W_PRODUCT_D.Prod_Name, --item name
W_PURCH_SCHEDULE_LINE_F.UNIT_PRICE,
W_DAY_D.DAY_DT ORDER_DT,
W_PURCH_SCHEDULE_LINE_F.PURCH_ORDER_NUM,
W_PURCH_SCHEDULE_LINE_F.PURCH_ORDER_ITEM,
W_PURCH_SCHEDULE_LINE_F.PURCH_SCHEDULE_NUM,
W_PURCH_SCHEDULE_LINE_F.RELEASE_NUM,
AVG(W_PURCH_SCHEDULE_LINE_F.UNIT_PRICE)OVER(PARTITION BY W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID ORDER BY TRUNC(W_DAY_D.DAY_DT) RANGE BETWEEN INTERVAL '1' year preceding and current row) AVG_PRICE_PAST
FROM W_PURCH_SCHEDULE_LINE_F,
W_PRODUCT_D,
W_DAY_D
WHERE
W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID=W_PRODUCT_D.ROW_WID
AND W_PURCH_SCHEDULE_LINE_F.ORDERED_ON_DT_WID=W_DAY_D.ROW_WID–求采购订单成交记录中 同物料未来一个月的采购平均单价
SELECT
W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID,
W_PRODUCT_D.Prod_Name, --item name
W_PURCH_SCHEDULE_LINE_F.UNIT_PRICE,
W_DAY_D.DAY_DT ORDER_DT,
W_PURCH_SCHEDULE_LINE_F.PURCH_ORDER_NUM,
W_PURCH_SCHEDULE_LINE_F.PURCH_ORDER_ITEM,
W_PURCH_SCHEDULE_LINE_F.PURCH_SCHEDULE_NUM,
W_PURCH_SCHEDULE_LINE_F.RELEASE_NUM,
AVG(W_PURCH_SCHEDULE_LINE_F.UNIT_PRICE)OVER(PARTITION BY W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID ORDER BY TRUNC(W_DAY_D.DAY_DT) RANGE BETWEEN current row and INTERVAL '1' month following) AVG_PRICE_PAST
FROM W_PURCH_SCHEDULE_LINE_F,
W_PRODUCT_D,
W_DAY_D
WHERE
W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID=W_PRODUCT_D.ROW_WID
AND W_PURCH_SCHEDULE_LINE_F.ORDERED_ON_DT_WID=W_DAY_D.ROW_WID
–求采购订单成交记录中 同物料5天内的采购平均单价
SELECT
W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID,
W_PRODUCT_D.Prod_Name, --item name
W_PURCH_SCHEDULE_LINE_F.UNIT_PRICE,
W_DAY_D.DAY_DT ORDER_DT,
W_PURCH_SCHEDULE_LINE_F.PURCH_ORDER_NUM,
W_PURCH_SCHEDULE_LINE_F.PURCH_ORDER_ITEM,
W_PURCH_SCHEDULE_LINE_F.PURCH_SCHEDULE_NUM,
W_PURCH_SCHEDULE_LINE_F.RELEASE_NUM,
AVG(W_PURCH_SCHEDULE_LINE_F.UNIT_PRICE)OVER(PARTITION BY W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID ORDER BY TRUNC(W_DAY_D.DAY_DT) RANGE BETWEEN INTERVAL '2' day preceding and INTERVAL '1' day following) AVG_PRICE_PAST
FROM W_PURCH_SCHEDULE_LINE_F,
W_PRODUCT_D,
W_DAY_D
WHERE
W_PURCH_SCHEDULE_LINE_F.PRODUCT_WID=W_PRODUCT_D.ROW_WID
AND W_PURCH_SCHEDULE_LINE_F.ORDERED_ON_DT_WID=W_DAY_D.ROW_WID备注：oracle将查找当前采购日期前2天，后2天范围内的记录，并出去单价的avg值。
Rows
1 WITH t AS
2 (SELECT (CASE
3 WHEN LEVEL IN (1, 2) THEN
4 1
5 WHEN LEVEL IN (4, 5) THEN
6 6
7 ELSE
8 LEVEL
9 END) ID
10 FROM dual
11 CONNECT BY LEVEL < 10)
12 SELECT id,
13 SUM(ID) over(ORDER BY ID) default_sum,
14 SUM(ID) over(ORDER BY ID RANGE BETWEEN unbounded preceding AND CURRENT ROW) range_unbound_sum,
15 SUM(ID) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) rows_unbound_sum,
16 SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum,
17 SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum
18* FROM t
SQL> /
ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_SUM ROWS_SUM
---------- ----------- ----------------- ---------------- ---------- ----------
1 2 2 1 5 5
1 2 2 2 5 11
3 5 5 5 3 16
6 23 23 11 33 21
6 23 23 17 33 25
6 23 23 23 33 27
7 30 30 30 42 30
8 38 38 38 24 24
9 47 47 47 17 17
说明
假设分组第一行的值为first_value，最后一行的值为last_value。当前行的值为current_value。
1)range between unbounded preceding and unbounded following
按升序排序的时候，表达式介于第一个值和最后一个值之间，或者
按降序排序的时候，表达式介于最后一个值和第一个值之间
2)range [between] unbounded preceding [and current row]
表达式介于第一个值与当前行的值之间，或者
表达式介于当前行的值和第一个值之间
3)range between unbounded preceding and value_expr preceding
表达式介于第一个值与当前行的值-value_expr之间，或者
表达式介于当前行的值-value_expr与第一个值之间
4)range between unbounded preceding and value_expr following
表达式介于第一个值与当前行的值+value_expr之间，或者
表达式介于当前行的值+value_expr与第一个值之间
5)range between current row and unbounded following
表达式介于当前行的值和最后一个值之间，或者
表达式介于最后一个值和当前行的值之间
6)range [between current row and] current row
表达式等于当前行的值
7)range between current row and value_expr following
表达式介于当前行的值和当前行的值+value_expr之间
8)range between value_expr preceding and unbounded following
表达式介于当前行的值-value_expr和最后一个值之间
9)range [between value_expr] preceding [and current row]
表达式介于当前行的值-value_expr和当前行的值之间
10)range between value_expr1 preceding and value_expr2 preceding
这里一定要满足value_expr1>=value_expr2。
然后表达式介于当前行的值-value_expr1和当前行的值-value_expr2之间
11)range between value_expr1 preceding and value_expr2 following
表达式介于当前行的值-value_expr1和当前行的值+value_expr2之间
12)range between value_expr following and unbounded following
表达式介于当前行的值+value_expr和最后一个值之间
表达式介于最后一个值和当前行的值+value_expr之间
13)range between value_expr1 following and value_expr2 following
这里一定要满足value_expr1<=value_expr2。
然后表达式介于当前行的值+value_expr1和当前行的值+value_expr2之间
14)range unbounded preceding
与2等价。
15)range current row
与6等价。
16)range value_expr preceding
与9等价。
注意事项：
1.若windowing_clause由rows指定，则：
(1)value_expr是物理偏移量，它必须是常量或值为非负数的表达式。
(2)若value_expr是起点的一部分，那么它必须在终点之前对行求值。
2.若windowing_clause由range指定，则:
(1)value_expr是逻辑偏移量。它必须是常量或值为非负的表达式或时间间隔文字常量。
(2)value_expr值为一个数字，那么order_by_clause中 expr必须为数字或date类型。
(3)value_expr为一个间隔值，那么order_by_clause中expr必须是一个date类型。
3.若完全忽略windowing_clause，那么默认的窗口范围为 range between unbounded preceding and current row。
关于ROWS、RANGE中的条件组合加起来达到32种，但实际上不过是几个关键字的排列组合而已，只要了解几个关键字的含义，在应用时加以灵活使用即可
最后编辑：严锋 更新时间：2024-07-04 20:01
