oracle sql 找年度的新資料
測試資料表,紀錄唯一識別碼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
)
結果: