动态添加零值记录以供后续AP使用，以实现分析功能

Question

with data as (
select 1 id, ‘A’ name, ‘fruit’ r_group, ‘2007’ year, ‘04’ month, 5 sales from dual union all
select 2 id, ‘Z’ name, ‘fruit’ r_group, ‘2007’ year, ‘04’ month, 99 sales from dual union all
select 3 id, ‘A’ name, ‘fruit’ r_group, ‘2008’ year, ‘05’ month, 10 sales from dual union all
select 4 id, ‘B’ name, ‘vegetable’ r_group, ‘2008’ year, ‘07’ month, 20 sales from dual
)

  select t.*,
         (sum(sales) over (partition by name, r_group
                           order by year, month
                           rows between unbounded preceding and current row
                          ) -sales ) as opening,
         sum(sales) over (partition by name, r_group
                          order by year, month
                          rows between unbounded preceding and current row
                         ) as closing
  from data t
 order by year , month

输出将是：

year   |   month   |  name   |  r_group   | sales   |  opening  |  closing |
2007   |     04    |   'A'   |   fruit   |  5       |    0      |    5     |
2007   |     04    |   'Z'   | fruit     |  99      |   0       |    99   |
2008   |     05    |   'A'   | fruit     |  10      |   5       |    15    |
2008   |     07    |   'B'   | vegetable |  20      |    0      |    20    |

如果我现在使用以下方法在此select语句之上进行聚合：

select year, month, r_group, sum(sales) sales, sum(opening) opening, sum(closing) closing from (
   select t.*, 
      (sum(sales) over........ 
) 
group by year, month, r_group
order by year, month

我得到以下结果：

year   |   month   |  r_group   | sales   |  opening   |  closing |
2007   |     04    |   fruit    |  104    |    0       |    104   |
2008   |     05    |   fruit    |  10     |    5       |    15    |
2008   |     07    | vegetable  |  20     |    0       |    20    |

这是 错误的 。请注意，2008年根本没有考虑过name =’Z’的值。由于累积函数是向后工作的，因此在2008年没有向后追溯的name
=’Z’记录。如果我在2008年输入零值记录，名称=’Z’，那么它将起作用。我想避免添加虚拟零值记录，并在查询中动态完成此操作。如果我在数据中添加零值记录，如下所示：

select 1 id, 'A' name, 'fruit' r_group, '2007', year '04' month, 5 sales from dual union all
select 2 id, 'Z' name, 'fruit' r_group, '2007', year '04' month, 99 sales from dual union all
select 3 id, 'A' name, 'fruit' r_group, '2008', year '05' month, 10 sales from dual union all
select 4 id, 'Z' name, 'fruit' r_group, '2008', year '05' month, 0 sales from dual union all
select 5 id, 'B' name, 'vegetable' r_group, '2008', year '07' month, 20 sales from dual ))

那么第一个查询将输出：

year   |   month   |  name   |  r_group   | sales   |  opening  |  closing |
2007   |     04    |   'A'   |   fruit   |  5       |    0      |    5     |
2007   |     04    |   'Z'   | fruit     |  99      |   0       |    99   |
2008   |     05    |   'A'   | fruit     |  10      |   5       |    15    |
2008   |     05    |   'Z'   | fruit     |  0       |   99      |    99    |
2008   |     07    |   'B'   | vegetable |  20      |    0      |    20    |

如果我再次使用第二个外部选择聚合，我将得到：

year   |   month   |  r_group   | sales   |  opening   |  closing |
2007   |     04    |   fruit    |  104    |    0       |    104   |
2008   |     05    |   fruit    |  10     |    104     |    114   |
2008   |     07    | vegetable  |  20     |    0       |    20    |

哪个是正确的。但是，正如我提到的，我不想添加零值记录。这里仅就此主题进行讨论：https
:
//asktom.oracle.com/pls/asktom/f?p=100
:11:0
:::::P11_QUESTION_ID
:
8912311513313但我无法使其工作。

Answer 1

一种相当简单的方法（类似于AskTom链接显示的方法）是提取所有年/月对，以及所有名称/ r_group对，然后将它们交叉连接：

with data as (
  select 1 id, 'A' name, 'fruit' r_group, '2007' year, '04' month, 5 sales from dual union all
  select 2 id, 'Z' name, 'fruit' r_group, '2007' year, '04' month, 99 sales from dual union all
  select 3 id, 'A' name, 'fruit' r_group, '2008' year, '05' month, 10 sales from dual union all
  select 4 id, 'B' name, 'vegetable' r_group, '2008' year, '07' month, 20 sales from dual
)
select a.year, a.month, b.name, b.r_group, nvl(d.sales, 0) as sales
from (select distinct year, month from data) a
cross join (select distinct name, r_group from data) b
left join data d on d.year = a.year and d.month = a.month and d.name = b.name and d.r_group = b.r_group
order by year, month, name, r_group;

YEAR MO N R_GROUP        SALES
---- -- - --------- ----------
2007 04 A fruit              5
2007 04 B vegetable          0
2007 04 Z fruit             99
2008 05 A fruit             10
2008 05 B vegetable          0
2008 05 Z fruit              0
2008 07 A fruit              0
2008 07 B vegetable         20
2008 07 Z fruit              0

但这会产生比您进行一级聚合所需的更多行：

YEAR MO N R_GROUP        SALES    OPENING    CLOSING
---- -- - --------- ---------- ---------- ----------
2007 04 A fruit              5          0          5
2007 04 B vegetable          0          0          0
2007 04 Z fruit             99          0         99
2008 05 A fruit             10          5         15
2008 05 B vegetable          0          0          0
2008 05 Z fruit              0         99         99
2008 07 A fruit              0         15         15
2008 07 B vegetable         20          0         20
2008 07 Z fruit              0         99         99

当与您的第二个级别（来自另一个查询）进行汇总时，将为例如2007/04 / vegetable生成额外的行：

YEAR MO R_GROUP        SALES    OPENING    CLOSING
---- -- --------- ---------- ---------- ----------
2007 04 fruit            104          0        104
2007 04 vegetable          0          0          0
2008 05 fruit             10        104        114
2008 05 vegetable          0          0          0
2008 07 fruit              0        114        114
2008 07 vegetable         20          0         20

您可以在汇总之前将其部分过滤掉，因为所有中间列都为零：

with data as (
  select 1 id, 'A' name, 'fruit' r_group, '2007' year, '04' month, 5 sales from dual union all
  select 2 id, 'Z' name, 'fruit' r_group, '2007' year, '04' month, 99 sales from dual union all
  select 3 id, 'A' name, 'fruit' r_group, '2008' year, '05' month, 10 sales from dual union all
  select 4 id, 'B' name, 'vegetable' r_group, '2008' year, '07' month, 20 sales from dual
)
select year,
       month,
       r_group,
       sum(sales) sales,
       sum(opening) opening,
       sum(closing) closing
from (
  select t.*,
         (sum(sales) over (partition by name, r_group
                           order by year, month
                           rows between unbounded preceding and current row
                          ) -sales ) as opening,
         sum(sales) over (partition by name, r_group
                          order by year, month
                          rows between unbounded preceding and current row
                         ) as closing
  from (
    select a.year, a.month, b.name, b.r_group, nvl(d.sales, 0) as sales
    from (select distinct year, month from data) a
    cross join (select distinct name, r_group from data) b
    left join data d
    on d.year = a.year and d.month = a.month and d.name = b.name and d.r_group = b.r_group
  ) t
)
where sales != 0 or opening != 0 or closing != 0
group by year, month, r_group
order by year, month;

要得到：

YEAR MO R_GROUP        SALES    OPENING    CLOSING
---- -- --------- ---------- ---------- ----------
2007 04 fruit            104          0        104
2008 05 fruit             10        104        114
2008 07 fruit              0        114        114
2008 07 vegetable         20          0         20

您可以进一步过滤结果，以删除汇总销售价值仍为零的行，尽管如果这样做的话，则不再需要 在 汇总 之前
进行过滤；但还是有点混乱。尚不清楚是否可以修改最外部的聚合来做到这一点。