sql - Oracle数据密集化填补了空白

试图填补只有在表上的值发生更改时才更新的表上用于报告目的的空白。
代码：

WITH
week_list AS --List of weeks
 (
  SELECT (  (trunc(to_date('06152012','mmddyyyy'), 'IW')+7) - (LEVEL * 7) )+6 as week
      from dual
      connect by level <= 7
  order by ( (trunc(to_date('06152012','mmddyyyy'), 'IW')+7) - (LEVEL * 7) )+6
 )
SELECT
  product,
  week,
  undist_amt_eod as quantity,
  LAST_VALUE(undist_amt_eod IGNORE NULLS) OVER (PARTITION BY product ORDER BY week) repeated_quantity
FROM
 (
    SELECT
      product,
      week_list.week,
      inv_table.undist_amt_eod
    FROM
      inv_table PARTITION BY (product)
        RIGHT OUTER JOIN week_list ON (week_list.week = inv_table.history_date)
    where
      inv_table.tab = '*' --Includes all types of this product
  )
ORDER BY product, week;

周列表示例输出：
表内容：注意表每天可以有多个选项卡。*是当天所有标签的总和，所以我只对*值感兴趣。
我的代码基于oracle的例子找到了here。不确定为什么数据输出仍然不密集。

最佳答案

问题是你的where条款：

where inv_table.tab = '*' --Includes all types of this product

因为您正在执行右外部联接，所以如果没有匹配项，inv_table.tab将为空。将其更改为以下内容之一：

where inv_table.tab = '*' or inv_table.history_date is null --Includes all types of this product

或者，如果inv_table.tab永远不能为空，则：

where coalesce(inv_table.tab, '*') = '*'

关于sql - Oracle数据密集化填补了空白，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12081911/