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.
2 union all
03.
3 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.
9277
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
