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但我无法使其工作。
一种相当简单的方法（类似于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
您可以进一步过滤结果，以删除汇总销售价值仍为零的行，尽管如果这样做的话，则不再需要 在 汇总 之前 进行过滤；但还是有点混乱。尚不清楚是否可以修改最外部的聚合来做到这一点。
