Grouped Table Outer Joins

Page 208

average rows that are already in the table. Single-set aggregate operations are also possible on insert and delete operations as seen in this example:

 Declare      a   number; begin      delete from employee where sal > 1000 returning avg(sal) into a;      dbms_output.put_line(a); end;

Normally, data is stored in a fairly dense format. However, in certain cases, this is not the case and data may be sparse in its nature. An example might be in an inventory report, we might not have inventoried a specific product over the period (perhaps there was none of that product in stock), but we might still want to reflect a 0 inventory for that product rather than a NULL.

A grouped table outer join (or partitioned outer join) allows you to improve the performance of SQL statements (and create easier-to-read SQL statements) that involve time-based calculations. The grouped table outer join allows you to fill in missing (or sparse) data in your joins. Here is an example of using a partitioned outer join:

 SELECT emp_name, dname, t.sales_date, NVL(amount,0) dense_sales FROM  (SELECT SUBSTR(e.ename,1,15) emp_name, d.dname,   s.sales_date sales_date, SUM(Amount) amount   FROM Sales s, dept d, emp e   WHERE s.empno = e.empno AND e.deptno=d.deptno   AND s.sales_date > sysdate - 365   AND d.deptno=30   GROUP BY SUBSTR(e.ename,1,15), d.dname, s.sales_date ) v PARTITION BY (v.emp_name) RIGHT OUTER JOIN  (SELECT DISTINCT sales_date   FROM sales   WHERE sales_date > sysdate - 365 ) t ON (v.sales_date = t.sales_date) ORDER BY t.sales_date;

And here is an example of the output from this query:

 EMP_NAME   DNAME             SALES_DATE  DENSE_SALES ---------- -------------- -------------- ----------- ALLEN      SALES             02/01/2004          100 WARD                         02/01/2004            0 ALLEN                        02/02/2004            0 WARD      SALES              02/02/2004          200