oracle查询返回数量,Oracle查询按年累计数量

您需要两层分析函数：您需要解析MAX(，其值为)，因为您要返回原始表中的所有行;而在MAX内，您需要分析(滚动)平均值。分析函数不能嵌套，所以你需要一个子查询和一个外部查询。这样的事情：

with inputs (item_id, org_id, yr, qty) as (

select 100, 121, 2015, 10 from dual

union all select 100, 121, 2016, 5 from dual

union all select 100, 121, 2017, 8 from dual

union all select 101, 146, 2014, 10 from dual

union all select 101, 146, 2015, 11 from dual

union all select 101, 146, 2016, 12 from dual

union all select 101, 146, 2017, 13 from dual

)

-- End of simulated inputs (for testing only, not part of the solution).

-- SQL query begins BELOW THIS LINE. Use your actual table and column names.

select item_id, org_id, yr, qty,

max(forward_avg) over (partition by item_id, org_id ) as max_avg

from (select item_id, org_id, yr, qty,

avg(qty) over (partition by item_id, org_id

order by yr desc ) as forward_avg

from inputs i

) b

order by item_id, org_id, yr -- If needed

;

ITEM_ID ORG_ID YR QTY MAX_AVG

---------- ---------- ---------- ---------- ----------

100 121 2015 10 8

100 121 2016 5 8

100 121 2017 8 8

101 146 2014 10 13

101 146 2015 11 13

101 146 2016 12 13

101 146 2017 13 13