oracle分析函数row_number

  近来在项目中看ETL过程的设计文档，其中有一个实现要求：更新管道层表T_XZHDM_STAGE中所有险种代码重复的数据，设置这些数据ERROR_CODE = '0001', ERROR_DESC = 'Dupilcate Data'。

    按照自己以前的理解，可以对所有结果按照险种代码排序，然后一次循环查找即可得解。哪知看到同事的实现代码后，方知一句话即可搞定，oracle提供的分析函数(Analytic Functions)解决此类问题再方便不过了。

    解决方案：

    --更新险种代码重复的数据
    UPDATE T_XZHDM_STAGE SET ERROR_CODE = '0001', ERROR_DESC = 'Dupilcate Data'
    WHERE ROWID IN (

         SELECT rowid FROM

             (SELECT rowid, xzhdm, c_branch_code, row_number() OVER(PARTITION BY xzhdm ORDER BY c_branch_code DESC ) num

              FROM T_XZHDM_STAGE 

              WHERE num>1)

         )

    在这里，row_number()作为分析函数使用，在《Oracle9i SQL Reference》的Analytic Functions一章（P220）中提到“Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.”，也就是说，row_number()基于PARTITION BY所给出的条件进行分类，返回记录的行数。与Aggregate Functions（如：SUM(), AVG(), COUNT()）不同的是，在每个分组内，row_number()也回返回所有记录的行数（按照各自的排名）。

 

    与row_number()相类似的函数，Oracle还提供了Rank()，Dense_Rank()，下面举例具体分析三者的区别。

    假设有一个雇员表，现在要对每个部门内的雇员薪水进行排列。

    SELECT empnum, dept, salary, rank() OVER(PARTITION BY dept ORDER BY salary DESC NULLS LAST) as rank,

                  dense_rank() OVER(PARTITION BY dept ORDER BY salary DESC NULLS LAST) as denserank,

                  row_number() OVER(PARTITION BY dept ORDER BY salary DESC NULLS LAST) as rownumber

    FROM EMP_TABLE;

 

    执行结果：

    EMPNUM  DEPT SALARY  RANK  DENSERANK   ROWNUMBER
    ------          ----        ------      ----  -     --------         ---------
    6                 1        78000        1                 1           1
    2                 1        75000        2                 2           2
    7                 1       75000       2                 2           3
    11               1       53000       4                 3           4
    5                 1        52000        5                 4           5
    1                 1        50000        6                 5           6
    --------------------------------------------------
    9                 2        51000        1                 1           1
    4                 2            -             2                 2           2 

 

   关注一下它们的返回值，可以发现：

   1.Rank()采用的是奥林匹克式排序（Olympic Rank），两个相等的值有相等的名次，接下来的行（薪水为53000的雇员）排名第4，排名中间有间隔。

   2.Densc_Rank()也采用奥林匹克式排序，相等的值有相等的名次，与Rank()所不同的是，它的排名记录之中没有间隔（薪水为53000的雇员排名第3）。

   3.Row_number()对于重复的值则给出不确定的排序（Nondeterministic Rank），相等的值有不同的名次，因此，row_number对于有重复值的数据分析有独特作用。如在项目中，解决有重复数据就采用Row_number()分析函数来处理。

   Oracle提供了大量的分析函数，用于解决数据仓库应用，如传统的聚合函数(Aggregate Function)如AVG, COUNT, MAX, MIN, SUM等都有分析函数的用法，具体使用则可以查看《Oracle9i SQL Reference》和《Oracle9i Data Warehousing User Guide》,其中在后一本书中还有大量的例子，分析很透彻。