如何在oracle中獲取最近兩年的年度數據

有一個名為sales_data我的項目的表。表結構如下：

+------+------+------+--------+
| 身份證 | 姓名 | 年份 | 銷售 |
+------+------+------+--------+
| 1000 | 美國廣播公司 | 2016 | 50000 |
| 1000 | 美國廣播公司 | 2017 | 80000 |
| 1000 | 美國廣播公司 | 2015 | 90000 |
| 1000 | 美國廣播公司 | 2014 | 45000 |
| 1000 | 美國廣播公司 | 2013 | 30000 |
| 2000 | 二維碼 | 2017 | 80000 |
| 2000 | 二維碼 | 2015 | 90000 |
| 2000 | 二維碼 | 2014 | 75000 |
| 2000 | 二維碼 | 2013 | 60000 |
| 3000 | XYZ | 2015 | 123000 |
| 3000 | XYZ | 2013 | 56000 |
| 3000 | XYZ | 2012 | 45000 |
| 3000 | XYZ | 2011 | 30000 |
+------+------+------+--------+

從這個表中我想獲取最新的連續年度數據。我的結果集應該如下所示：

+------+------+------+--------+
| 身份證 | 姓名 | 年份 | 銷售 |
+------+------+------+--------+
| 1000 | 美國廣播公司 | 2017 | 80000 |
| 1000 | 美國廣播公司 | 2016 | 50000 |
| 2000 | 二維碼 | 2017 | 80000 |
| 2000 | 二維碼 | 2016 | 0 |
| 3000 | XYZ | 2015 | 123000 |
| 3000 | XYZ | 2014 | 0 |
+------+------+------+--------+

因為對於PQR公司來說最新的數據就是2017這樣80000。但由於2016該表中不存在數據，因此該值需要為0. XYZ公司也類似。

請幫助我。我正在使用 Oracle 11g。

樣本數據：

with data as
(
 select 1000 id, 'ABC' name, 2016 year, 50000 sales from dual union all
 select 1000 id, 'ABC' name, 2017 year, 80000 sales from dual union all
 select 1000 id, 'ABC' name, 2015 year, 90000 sales from dual union all
 select 1000 id, 'ABC' name, 2014 year, 45000 sales from dual union all
 select 1000 id, 'ABC' name, 2013 year, 30000 sales from dual union all
 select 2000 id, 'PQR' name, 2017 year, 80000 sales from dual union all
 select 2000 id, 'PQR' name, 2015 year, 90000 sales from dual union all
 select 2000 id, 'PQR' name, 2014 year, 75000 sales from dual union all
 select 2000 id, 'PQR' name, 2013 year, 60000 sales from dual union all
 select 3000 id, 'XYZ' name, 2015 year, 123000 sales from dual union all
 select 3000 id, 'XYZ' name, 2013 year, 56000 sales from dual union all
 select 3000 id, 'XYZ' name, 2012 year, 45000 sales from dual union all
 select 3000 id, 'XYZ' name, 2011 year, 30000 sales from dual
)

結果：

select 
 id, name,
 case when rn = 1 then year else fy - 1 end as year,
 case when (rn = 1 or fy - 1 = year) then sales else 0 end as sales
from
(
 select
   id, name, year, sales,
   rank() over (partition by name order by year desc) as rn,
   first_value(year) over (partition by name order by year desc) as fy
 from data
) 
where rn <= 2;

       ID NAM       YEAR      SALES
---------- --- ---------- ----------
     1000 ABC       2017      80000
     1000 ABC       2016      50000
     2000 PQR       2017      80000
     2000 PQR       2016          0
     3000 XYZ       2015     123000
     3000 XYZ       2014          0

試試這個，

;

WITH CTE1
AS (
   SELECT id,NAME
       ,max(years) years
   FROM #tmp t
   GROUP BY id,NAME

   UNION ALL

   SELECT id,NAME
       ,max(years) - 1 years
   FROM #tmp t
   GROUP BY id,NAME
   )
SELECT t.id, t.NAME
   ,t.years
   ,isnull(t1.Sales, 0) Sales
FROM cte1 t
LEFT JOIN #tmp t1 ON t.NAME = t1.NAME
   AND t.years = t1.Years
ORDER BY t.NAME
   ,t.years DESC

CTE1 也可以通過遞歸來實現last N years

引用自：https://dba.stackexchange.com/questions/203709