oracle窗口函数去重复,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' monthfollowing) 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值。