Oracle Query仅按最近3年的年份汇总数量 [英] Oracle Query to rollup QTY by Year- only last 3 years

问题描述

我需要从除以YEAR的数量总和中找出MAX VALUE(需要编写Oracle查询).

I have a requirement to find out MAX VALUE from SUM of Quantities Divided by YEAR (Need to write a Oracle Query).

例如

 ITEM_ID     ORG_ID     YEAR    QTY

  100         121        2015    10
  100         121        2016    5
  100         121        2017    8
  101         146        2014    10
  101         146        2015    11
  101         146        2016    12
  101         146        2017    13

我的输出应该是这样的:-

My Output should be like this :-

for Item_id 100,121 the max_avg should be max(10+5+8/3, 5+10/2, 10/1)... max (7.6, 7.5, 8) = 8
for Item_id 101,146 the max_avg should be (11+12+13/3, 12+13/2, 13/1)... max(11.5, 12, 12.5, 13) = 13... I should not consider 10+11+12+13/4.我只需要考虑过去3年中汇总的AVG并分配最大值即可.

for Item_id 100,121 the max_avg should be max(10+5+8/3, 5+10/2, 10/1)... max (7.6, 7.5, 8) = 8
for Item_id 101,146 the max_avg should be (11+12+13/3, 12+13/2, 13/1)... max(11.5, 12, 12.5, 13) = 13... I should not consider 10+11+12+13/4. I only need the consider the AVG rolled up by last 3 years and assign the Max Value

 ITEM_ID     ORG_ID     YEAR    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

任何帮助将不胜感激.

Any help would be greatly appreciated.

推荐答案

select item_id, org_id, yr, qty,
       greatest (
 avg(case when yr = 2017                then qty end) over (partition by item_id, org_id),
 avg(case when yr in (2016, 2017)       then qty end) over (partition by item_id, org_id),
 avg(case when yr in (2015, 2016, 2017) then qty end) over (partition by item_id, org_id)
       ) as max_avg
from   inputs_table
;

这篇关于Oracle Query仅按最近3年的年份汇总数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！