动态添加零值记录以供后续AP使用，以实现分析功能 [英] Dynamically adding zero-valued records for subsequent APs for analytical function to work
问题描述
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语句的顶部进行聚合:
If I aggregate now on top of this select statement using this:
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')，它将起作用.我想避免添加虚拟零值记录，并在查询中动态完成此操作.如果我在数据中添加零值记录，如下所示:
which is wrong. Notice that the value of name='Z' has not been taken into account at all in 2008. Since the cumulative function works backwards it didn't have a name='Z' record in 2008 to go backwards with. If I put a zero-value record in 2008, for name = 'Z' then it will work. I want to avoid adding dummy zero-valued records and have this done dynamically in the query. If I add the zero-valued record in the data like this:
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 ))
然后第一个查询将输出:
then the first query will output:
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 |
并且如果我再次使用第二个外部选择聚合，我将得到:
and If i aggregate again using the second outer select I will get:
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 ，但我无法完成这项工作.
which is correct. However, as I mentioned, I do not want to add zero-valued records. There is discussion on just this topic here: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8912311513313 but I haven't been able to make this work.
推荐答案
一种相当简单的方法(类似于AskTom链接显示的方法)是提取所有年/月对，以及所有名称/r_group对，以及然后交叉加入那些:
A fairly simplistic approach (and similar to what that AskTom link shows) is to extract all the year/month pairs, and all the name/r_group pairs, and then cross-join those:
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
但这会产生比您进行一级聚合所需的更多行:
But that produces more rows than you wanted with your first level fo aggregation:
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的额外行:
and when aggregated with your second level (from the other query) would produce extra rows for, say, 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
您可以在汇总之前将其部分过滤掉，因为所有中间列都为零:
which you could partially filter those out before aggregating because all the intermediate columns would be zero:
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
您可以进一步对该结果进行过滤，以删除销售总额仍为零的行，尽管如果这样做的话，不再需要之前过滤器；但还是有点混乱.尚不清楚是否可以修改最外部的聚合来做到这一点.
You could further filter that result to remove rows where the aggregated sales value is still zero, though if you're doing that the filter before aggregation isn't needed any more; but it's still a bit messy. And it isn't clear if your outermost aggregation can be modified to do that.
这篇关于动态添加零值记录以供后续AP使用，以实现分析功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
