RANK() OVER和ROW_NUMBER() OVER的学习笔记

     近来在工作上遇到一件事情。我有一张用户订单表，这个订单表有一个order_id，是唯一约束。同时有一张订单流程表，和订单表以ser_id关联，一个ser_id至少对应一条订单流程记录。现在我要将两个表汇总，成为一张表，以ser_id为唯一约束，其中一个字段来自流程表，这个字段是ser_id对应的几条工作流程记录中work_id最大的。

     大致上订单表示这样的：

     order_id    ser_id ......

     112333      100001

     122112      100001

     122882      100211

     ......

     而工作流程表是这样的：

     work_id     ser_id ......

     91188       100001

     91198       100001

     91108       100001

     91223       100221

     ......

     最开始我的想法是使用rank() over，于是我写了这样一段：

SELECT 
A.*,
B.I_NAME
FROM ORDER_T A,
(SELECT X.*,
 RANK() OVER(PARTITION BY ser_id ORDER BY X.ser_id) RK) B
WHERE A.SER_ID = B.SER_ID(+)
AND B.RK = 1;

      最开始的时候我以为这个是对的，但是后来发现这是个很白痴的SQL。为什么白痴下面慢慢讲。

     下面是举例说明，有两张表，i_test作为订单表，duibibiao作为工作项表，直接上图：

图1

图2

      我理想的查询结果是这样的：

  

图3

      因为我的目的是做一张中间表，这个中间表是我最后用来统计我们这个月接了多少单子的。按照我上面的SQL，执行出来的结果是这样的：

     

      当时我还有点懵懂，不知道为什么会出现这种情况，不仅仅是100001对应的项多了那么多，而且还有几条记录不见了。后来我才发现，B.RK=1这一条就很白痴，因为之前做的乘积中，做了连接，这样的话形成的临时表中就会有一列的RK有的有数有的没有数，这样的话会把需要的数据过滤掉。我做连接查询的目的就是保留所有的订单表记录，因为最后我是要count这个表，然后告诉老板这个月我们接收了多少订单的。  order by的那个地方也很白痴，因为会有很多ser_id是重复的，因此很多个RK都是等于1的，这样下来挑不出来单个的一条记录，你怎么排序都不行。

      于是我把SQL改成了这样：

      

SELECT A.SER_ID, B.I_NAME
  FROM I_TEST A,
       (SELECT *
          FROM (SELECT X.WORK_ID,
                       X.SER_ID,
                       X.I_NAME,
                       RANK() OVER(PARTITION BY X.SER_ID ORDER BY X.ROWID) RK
                  FROM DUIBIBIAO X) Y
         WHERE Y.RK = 1) B
 WHERE A.SER_ID = B.SER_ID(+)

      坑爹的正确结果终于出来了。这里为了解决上面的那个问题，我用到了伪列ROWID，这个可没有重复的，不管正序倒序，都能找到一条记录。

      后来我上网的时候发现了一个有意思的东西，ROW_NUMBER() OVER。这个的好处就是不会产生序列号相同的情况。SQL如下：

      

  SELECT A.SER_ID, B.I_NAME
    FROM I_TEST A,
         (SELECT *
            FROM (SELECT X.WORK_ID,
                         X.SER_ID,
                         X.I_NAME,
                         row_number() OVER(PARTITION BY X.SER_ID ORDER BY X.ser_id) RK
                    FROM DUIBIBIAO X) Y
           WHERE Y.RK = 1) B
 WHERE A.SER_ID = B.SER_ID(+)

       得到的结果也完全正确。现在我就想比比谁的效率更高点。因为RANK() OVER的速度实在是......

       祭出一个非常大的表，叫做test，有10000000+数据。这个表有两个字段，ser_id和area_id，语句如下：

SELECT A.*,
       ROW_NUMBER() OVER(PARTITION BY A.AREA_ID ORDER BY A.SER_ID DESC) RK
  FROM TEST A;

SELECT a.*,
 RANK() OVER(PARTITION BY a.area_id ORDER BY a.ser_id DESC) rk FROM TEST a;

       对比一下效率：

      没有看到任何区别，都是一样的慢。

      总结一下，不管是RANK还是ROW_NUMBER，都是非常好用的分析函数。我这里只是用到了很简单的一部分，并且学习到了两个函数的通用之处。但是不管怎么样，只要你的表够大，做一次分析是不可避免的会出现超级慢的情况，这种情况下除了拼机器的性能之外，如果逻辑正确，可以对要开窗分析的表进行一些限制，这样的话会降低很大的成本。比如说我上面的SQL，如果我在业务中只是需要西安的数据，那么我可以在其中添加where t.area_id = 290。我试验了一下，ROWS变成了5660K，BYTES变成了140M，TEMPSPC变成了195M，CPU COST变成了49918。

      欢迎大家批评指正。欢迎留言。