Making up Data with Partition Outer Join

refer:

http://nuijten.blogspot.com/2009/07/making-up-data-with-partition-outer.html

 

Just the other day on the Oracle SQL and PL/SQL forum, someone asked on how to create non-existent rows. This post is not about generating a number of rows.
It's about handling Sparse Data, when you want to fill in some missing records in your result set.
First time I heard about this was in a blog written by Lucas Jellema.
Let's first start out with the table and some test data:

01. SQL> create table test_table1
02.   2  (status varchar2(15)
03.   3  ,manager number
04.   4  ,sales number);
05.  
06. Table created.
07.  
08. SQL> insert into test_table1 values ('in process', 14, 100);
09.  
10. 1 row created.
11.  
12. SQL> insert into test_table1 values ('in process', 15, 10);
13.  
14. 1 row created.
15.  
16. SQL> insert into test_table1 values ('in process', 15, 40);
17.  
18. 1 row created.
19.  
20. SQL> insert into test_table1 values ('done', 14, 200);
21.  
22. 1 row created.
23.  
24. SQL> insert into test_table1 values ('done', 16, 50);
25.  
26. 1 row created.
27. SQL> select *
28.   2    from test_table1
29.   3  /
30.  
31. STATUS             MANAGER      SALES
32. --------------- ---------- ----------
33. in process              14        100
34. in process              15         10
35. in process              15         40
36. done                    14        200
37. done                    16         50


As you can see in the sample data, Manager 14 has entries for the status "in process" and "done". Manager 15 only has entries for "in process". Manager 16 only has a single entry for "done".
The result that we are after is to show for each Manager a total sales value for both statuses "in process" and "done".
When we use a regular SUM and GROUP BY:

01. SQL> select manager
02.   2       , status
03.   3       , sum(sales)
04.   4    from test_table1
05.   5   group by manager
06.   6          , status
07.   7   order by manager
08.   8          , status
09.   9  /
10.  
11.    MANAGER STATUS          SUM(SALES)
12. ---------- --------------- ----------
13.         14 done                   200
14.         14 in process             100
15.         15 in process              50
16.         16 done                    50


we only see values for records that are actually in the table... Go Figure!

Nice results, but not exactly what we are after. We want an extra record for Manager 15 (with status "done" and sales value of 0) and an extra record for Manager 16 (with status "in process" and also a value of 0).

One way to tackle this problem (or challenge if you prefer) is to use a Partition Outer Join. As far as i know this is not ANSI-SQL, but Oracle specific syntax. Tahiti.Oracle.com calls it an "extension to the ANSI syntax".
To make this query work, we need a "table" (or inline view) which has all possible statuses. Something like

01. SQL> select 'done' st from dual
02.   union all
03.   select 'in process' from dual
04.   4  /
05.  
06. ST
07. ----------
08. done
09. in process


This inline view will be outer joined to our table.
What makes a Partition Outer Join work differently from a regular Outer Join?
A regular Outer Join will show an extra single record even when a matching value is not present. In our case, this will not make a difference as the values "done" and "in process" are present in our base table.
What we want is to outer join all statuses from the inline view to our base table for each manager.
And this is exactly what the Partition Clause does. It breaks up the result set per manager. Per partition (one for Manager 14, one for Manager 15 and one for Manager 16) we want to outer join to the inline view.

Putting it all together, and here is the final result:

01. SQL> select manager
02.   2       , st
03.   3       , nvl (sum (sales) , 0)
04.   4    from test_table1 t partition by (manager)
05.   5   right outer
06.   6    join (select 'done' st from dual
07.   7          union all
08.   8          select 'in process' from dual
09.   9         ) sts
10.  10      on (t.status = sts.st)
11.  11   group by manager
12.  12          , st
13.  13   order by manager
14.  14          , st
15.  15  /
16.  
17.    MANAGER ST         NVL(SUM(SALES),0)
18. ---------- ---------- -----------------
19.         14 done                     600
20.         14 in process               300
21.         15 done                       0
22.         15 in process               150
23.         16 done                     150
24.         16 in process                 0
25.  
26. 6 rows selected.


Each Manager shows an entry for both statuses "done" and "in process", even when this value is not in the base table.

If -for whatever reason- you don't like RIGHT OUTER, just flip the tables around and call it a LEFT OUTER:

01. SQL> select manager
02.   2       , st
03.   3       , nvl (sum (sales) , 0)
04.   4    from (select 'done' st from dual
05.   5          union all
06.   6          select 'in process' from dual
07.   7         ) sts
08.   8    left outer
09.   9    join test_table1 t partition by (manager)
10.  10      on (t.status = sts.st)
11.  11   group by manager
12.  12          , st
13.  13   order by manager
14.  14          , st
15.  15  /
16.  
17.    MANAGER ST         NVL(SUM(SALES),0)
18. ---------- ---------- -----------------
19.         14 done                     800
20.         14 in process               400
21.         15 done                       0
22.         15 in process               200
23.         16 done                     200
24.         16 in process                 0
25.  
26. 6 rows selected.

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值