title

測試資料表，紀錄唯一識別碼id與年度year，找出2017至2019年間各年度新增的資料有幾筆?

某id(簡稱x)在某年度(簡稱y)新增的定義為：資料表中x於y年首次出現，意即不存在任何id=x且year<y之資料

以下方資料為例，A於2016、2017都沒有資料，於2018首次出現，可說A在2018為新增

CREATE TABLE sql_test_a (ID VARCHAR2(5 char),  year number); 
INSERT INTO sql_test_a (ID, year) VALUES ('A', 2018); 
INSERT INTO sql_test_a (ID, year) VALUES ('A', 2019);
INSERT INTO sql_test_a (ID, year) VALUES ('B', 2018);
INSERT INTO sql_test_a (ID, year) VALUES ('B', 2017);
INSERT INTO sql_test_a (ID, year) VALUES ('C', 2019);
INSERT INTO sql_test_a (ID, year) VALUES ('D', 2018);
INSERT INTO sql_test_a (ID, year) VALUES ('D', 2016);
INSERT INTO sql_test_a (ID, year) VALUES ('E', 2018);

紀錄一種做法：
先做出id、year的筆數樞紐分析(y2016、y2017、y2018、y2019)
之後做出各id對於各年度是否算新增的欄位(isnew_2017、isnew_2018、isnew_2019)，填1表示為新增

select id, y2016, y2017, y2018, y2019,
       case
         when y2017 > 0 and greatest(y2016) = 0 then 1
         else 0
       end as isnew_2017,
       case
         when y2018 > 0 and greatest(y2017, y2016) = 0 then 1
         else 0
       end as isnew_2018,
       case
         when y2019 > 0 and greatest(y2018, y2017, y2016) = 0 then 1
         else 0
       end as isnew_2019
from (
    select * from sql_test_a
)
pivot (
    count(1) for year in (2016 as y2016, 2017 as y2017, 2018 as y2018, 2019 as y2019)
)
order by id

結果：

之後做sum

select sum(isnew_2017), sum(isnew_2018), sum(isnew_2019) from (
    select id, y2016, y2017, y2018, y2019,
           case
             when y2017 > 0 and greatest(y2016) = 0 then 1
             else 0
           end as isnew_2017,
           case
             when y2018 > 0 and greatest(y2017, y2016) = 0 then 1
             else 0
           end as isnew_2018,
           case
             when y2019 > 0 and greatest(y2018, y2017, y2016) = 0 then 1
             else 0
           end as isnew_2019
    from (
        select * from sql_test_a
    )
    pivot (
        count(1) for year in (2016 as y2016, 2017 as y2017, 2018 as y2018, 2019 as y2019)
    )
    order by id
)

結果：