填充表中缺少的组合

Question

输入：

item   loc   month    year   qty
 A     DEL    5        2020   12
 A     DEL    6        2020   14
 A     DEL    8        2020   16
 A     DEL    9        2020   17

输出：

item   loc   month    year   qty
 A     DEL    5        2020   12
 A     DEL    6        2020   14
 A     DEL    7        2020   26
 A     DEL    8        2020   16
 A     DEL    9        2020   17
 A     DEL    10       2020   33

描述：

我的输入没有第7个月。所以为了计算7个月，我做了前两个月数量的总和。

例如，第7个月的输出将为12(从第5个月开始)+14(从第6个月开始)=26

所以这就像当任何一个月都会丢失时，我应该用这个逻辑来填充那个月。

Answer 1

我已经写了一个脚本，这是两个步骤的过程，但它只考虑值之间的缺失月份，而不是边界值，即它不会假设10缺失，因为它是一个边界值。

第一步:插入misisng月份，其他所有列均为NULL。

 INSERT INTO TEST_MISSING(MONTH)
 select min_a - 1 + level
     from ( select min(MONTH) min_a
                 , max(MONTH) max_a
              from TEST_MISSING
          )
  connect by level <= max_a - min_a + 1
    minus
   select MONTH
     from TEST_MISSING;

第二步:使用lag填充其他列的值，这些值来自与它相关的行。

然后使用窗口函数计算量值。

SELECT NVL(ITEM, NEW_ITEM) ITEM, 
     NVL(LOC, NEW_LOC) LOC, 
     MONTH, NVL(YEAR, NEW_YEAR) YEAR,
     CASE WHEN QTY IS NULL THEN SUM(NVL(QTY, 0)) OVER(PARTITION BY NEW_ITEM ORDER BY MONTH ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) ELSE QTY END AS QTY
     FROM (
     SELECT A.*, 
     nvl(item,CASE WHEN ITEM IS NULL THEN (LAG(ITEM) OVER(ORDER BY MONTH)) END) NEW_ITEM,
     nvl(LOC,CASE WHEN LOC IS NULL THEN (LAG(LOC) OVER(ORDER BY MONTH)) END) NEW_LOC,
     nvl(YEAR,CASE WHEN YEAR IS NULL THEN (LAG(YEAR) OVER(ORDER BY MONTH)) END) NEW_YEAR
     FROM TEST_MISSING A) 
     X 
     ORDER BY MONTH;