ITPub博客

首页 > 数据库 > Oracle > 使用Oracle 10g引入的Partition Outer Joins进行统计报表的实现

使用Oracle 10g引入的Partition Outer Joins进行统计报表的实现

Oracle 作者:nathanzhn 时间:2014-08-21 14:30:24 0 删除 编辑
非常有用的开发新特性,直接上代码:

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: LIKE / NOT LIKE 改写
请登录后发表评论 登录
全部评论

注册时间:2014-01-23

  • 博文量
    49
  • 访问量
    263430