Oracle 12C为Trending添加行

Question

假设我有历史数据，列出每天显示缺陷的项目。项目进入或退出此列表。即-

TABLE NAME: ITEMS_WITH_DEFECTS

DAY        | ITEMID
-------------------
01-JAN-16  | A
01-JAN-16  | D
02-JAN-16  | B
02-JAN-16  | D
03-JAN-16  | A
03-JAN-16  | C
04-JAN-17  | A
04-JAN-17  | D

我想构建一个数据源，显示每天相对于前一天，有多少项是新的，有多少项已经下降，有多少项已经结转。它希望在不丢失历史数据源中的任何信息的情况下这样做，因此我想要的输出是：

TABLE NAME: ITEM_DEFECT_TRENDS

DAY       | ITEMID | DEFECT | TREND
------------------------------------
01-JAN-16 | A      | y      | New
01-JAN-16 | B      | n      | (null)
01-JAN-16 | C      | n      | (null)
01-JAN-16 | D      | y      | New
02-JAN-16 | A      | n      | Dropped
02-JAN-16 | B      | y      | New
02-JAN-16 | C      | n      | (null)
02-JAN-16 | D      | y      | Carryover
03-JAN-16 | A      | y      | New
03-JAN-16 | B      | n      | Dropped
03-JAN-16 | C      | y      | New
03-JAN-16 | D      | n      | Dropped
04-JAN-16 | A      | y      | Carryover
04-JAN-16 | B      | n      | (null)
04-JAN-16 | C      | n      | Dropped
04-JAN-16 | D      | y      | New

我知道如何生成趋势列，但不知道如何将行或缺陷列添加到输出中。

这是我可以用SQL或PL SQL做的事情吗？

我之所以要像这样对数据进行建模，是因为我的数据库包含了大约12,000个可能的项，但是每次只有大约500个项目会出现缺陷。与所有可能的项目相比，只考虑我必须考虑的项，这样的内存效率更高。

Answer 1

是的，我认为您可以使用SQL来完成这个任务：

select d.day, i.itemid, 
       (case when id.itemid is not null then 'y' else 'n' end) as defect,
       (case when id.itemid is null and
                  lag(id.itemid) over (partition by i.itemid order by d.day) is null
             then 'New'
             when id.itemid is not null and
                  lag(id.itemid) over (partition by i.itemid order by d.day) is not null
             then 'CarryOver'
             when lag(id.itemid) over (partition by i.itemid order by d.day) is not null
             then 'Dropped'
        end) as trend
from (select distinct day from items_with_defects) d cross join
     (select distinct itemid from items_with_defects) i left join
     items_with_defects id
     on id.day = d.day and id.itemid = i.itemid;

这样做的想法是使用cross join生成所有行--如果您有其他方法获取所需的天数和项目，那么就使用它们。

然后left join原始数据以检查匹配。defect列很简单。我认为trend列逻辑是正确的，但这个问题并没有完全解释它。