oracle窗口函数中range interval的使用

oracle窗口函数中range interval配合一般用来针对指定时间范围进行统计。其中range表示范围，between...and 表示之前的范围和之后的范围 ， CURRENT ROW表示当前行，INTERVAL '1'  day/month/year preceding/following 表示时间范围的选择。

 

--求采购订单成交记录中 同物料过去一年的采购平均单价

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值。

转载于:https://www.cnblogs.com/fjhh/p/4036618.html