分析函数举例

示例表：

CREATE TABLE TMP_NOTES( 
NOTE_ID NUMBER PRIMARY KEY, 
GROUP_ID NUMBER, 
NOTE VARCHAR2(4000) NOT NULL, 
CREATE_DATE DATE NOT NULL, 
CREATED_BY VARCHAR2(30) NOT NULL);

 

INSERT INTO TMP_NOTES
VALUES
  (1,
   1,
   'Customer refunded for missing item',
   TO_DATE('11/23/2004', 'MM/DD/YYYY'),
   'CUSTOMER_SERVICE');
INSERT INTO TMP_NOTES
VALUES
  (2,
   2,
   'Customer returning entire shipment',
   TO_DATE('1/5/2005', 'MM/DD/YYYY'),
   'CUSTOMER_SERVICE');
INSERT INTO TMP_NOTES
VALUES
  (3,
   2,
   'Shipment has not arrived',
   TO_DATE('2/5/2005', 'MM/DD/YYYY'),
   'WAREHOUSE');

如要查询某个组中最后输入的一个note，如果不使用分析函数，可以使用以下语句：

SELECT *
  FROM TMP_NOTES tn
 WHERE (tn.GROUP_ID, tn.CREATE_DATE) IN
       (SELECT tn2.GROUP_ID, MAX(tn2.CREATE_DATE)
          FROM TMP_NOTES tn2
         GROUP BY tn2.GROUP_ID)

但是如果一天输入了两条note，那么该问题将无法解决（因为两条数据都会返回）。二、必须扫描TMP_NOTES表两次。而使用分析函数可以完全消除问题1并提高问题而的性能。

SELECT L1.*
  FROM (SELECT tn.*,
               ROW_NUMBER() OVER(PARTITION BY tn.GROUP_ID ORDER BY tn.CREATE_DATE DESC) RN
          FROM TMP_NOTES tn) L1
 WHERE RN = 1

       “ROW_NUMBER() OVER(PARTITION BY tn.GROUP_ID ORDER BY tn.CRE ATE_DATE DESC”告诉Oracle将TMP_NOTES以组分成GROUP_ID并且对于每个组，以CREATE_DATE降序显示。

 

使用分析函数需要理解的问题

       首先分析函数在结果集上工作，而不是全部数据。

       第二个关键的问题是partition by和group by非常像，但是全部行都将保留在结果集中。

       第三，分析函数在各个分区上独立的工作。