近来在工作上遇到一件事情。我有一张用户订单表，这个订单表有一个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。
欢迎大家批评指正。欢迎留言。