使用Oracle 10g引入的Partition Outer Joins進行統計報表的實現

非常有用的開發新特性，直接上程式碼：

SELECT * FROM employee_expense;
    EMP_ID       YEAR      MONTH EXPENSE_CLAIM APPROVED_AMT PAID_DATE
---------- ---------- ---------- ------------- ------------ ---------
      7369       2002          2       3072.43      3072.43 03-MAR-02
      7369       2002          4            30           30 01-JUN-02
      7369       2002          5        235.03        35.03 01-JUN-02
      7369       2002          9       5095.98      5095.08 31-OCT-02
      7369       2002         12       1001.01      1001.01 01-FEB-03
      7782       2002          1        111.09       111.09 01-FEB-02
      7782       2002          3          9.85         9.85 01-APR-02
      7782       2002          7       3987.32      3987.32 01-AUG-02
      7782       2002          9          1200         1200 01-OCT-02

SELECT *  FROM months WHERE year = 2002; YEAR      MONTH

---------- ----------

      2002          1

      2002          2

      2002          3

      2002          4

      2002          5

      2002          6

      2002          7

      2002          8

      2002          9

      2002         10

      2002         11

      2002         12

SELECT ee.emp_id, m.year, m.month, NVL(ee.expense_claim,0) FROM (SELECT * FROM months WHERE year = 2002) m  LEFT OUTER JOIN employee_expense ee  PARTITION BY (ee.emp_id)  ON m.year = ee.year AND m.month = ee.month ORDER BY ee.emp_id, m.month; EMP_ID       YEAR      MONTH NVL(EE.EXPENSE_CLAIM,0)

---------- ---------- ---------- -----------------------

      7369       2002          1                       0

      7369       2002          2                 3072.43

      7369       2002          3                       0

      7369       2002          4                      30

      7369       2002          5                  235.03

      7369       2002          6                       0

      7369       2002          7                       0

      7369       2002          8                       0

      7369       2002          9                 5095.98

      7369       2002         10                       0

      7369       2002         11                       0

      7369       2002         12                 1001.01

      7782       2002          1                  111.09

      7782       2002          2                       0

      7782       2002          3                    9.85

      7782       2002          4                       0

      7782       2002          5                       0

      7782       2002          6                       0

      7782       2002          7                 3987.32

      7782       2002          8                       0

      7782       2002          9                    1200

      7782       2002         10                       0

      7782       2002         11                       0

      7782       2002         12                       0

Notice the PARTITION BY clause in this query. That clause is new in Oracle Database 10g, and in this example it causes the database engine to conceptually perform the following steps:

1. Divide the rows from employee_expense into groups based on their emp_id values, one group per value.

2. Outer join each group to the months table as a separate operation.

The key here is that rather than one outer join, you are getting the equivalent of many outer joins, but with a much simpler syntax, and from one query. The preceding query is logically equivalent to the following UNION ALL query:

SELECT NVL(ee.emp_id, 7369), m.year, m.month, NVL(ee.expense_claim,0)

FROM (SELECT * FROM months WHERE year = 2002) m

     LEFT OUTER JOIN (SELECT *

                      FROM employee_expense

                      WHERE emp_id = 7369) ee

     ON m.year = ee.year AND m.month = ee.month

ORDER BY m.month

UNION ALL

SELECT NVL(ee.emp_id, 7782), m.year, m.month, NVL(ee.expense_claim,0)

FROM (SELECT * FROM months WHERE year = 2002) m

     LEFT OUTER JOIN (SELECT *

                      FROM employee_expense

                      WHERE emp_id = 7782) ee

     ON m.year = ee.year AND m.month = ee.month

ORDER BY m.month;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26521853/viewspace-1255289/，如需轉載，請註明出處，否則將追究法律責任。