转载：rank() over(partition)的使用

最新推荐文章于 2024-02-02 16:19:59 发布

luofuIT

最新推荐文章于 2024-02-02 16:19:59 发布

阅读量640

点赞数

分类专栏： sql

sql 专栏收录该内容

11 篇文章

订阅专栏

本文介绍如何利用SQL中的RANK()函数结合分区查询来高效地生成业务排名报表，并探讨了不同数据规模下查询效率的变化及分区表的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转自：http://www.2cto.com/database/201202/119377.html

有的时候会遇到这样的问题，我们需要查询一张表，而且要按照业务排序，比如我需要如下的结果：

地区日期费用产品编号用户编号

290 201202 258 1 s1
290 201202 200 1 s5
290 201202 100 1 s100
290 201202 90 2 s7
290 201202 88 2 s9
290 201202 10 2 s12。

领导让我出一张报表，需要看到每一个业务的收费前三名是那些客户。这个时候用rank() over(partition)是一个很不错的选择。

我的测试表就像上面例子中的表一样，不过数据稍微多一点点。给大家一个截图：

可以看到我每一个项目都有5条记录，我只取前三，那么SQL如下：

SELECT A.AREA_ID, A.ACCT_MONTH, A.FEE, A.ITEM_ID, A.USER_ID
  FROM (SELECT T.AREA_ID,
               T.ACCT_MONTH,
               T.FEE,
               T.ITEM_ID,
               T.USER_ID,
               RANK() OVER(PARTITION BY T.ITEM_ID ORDER BY T.FEE DESC) RK
          FROM TEST T) A
 WHERE RK < 4;

如果只取一条记录（即取排序第一位的记录）则 where RK=1;

如果只取一条记录（取排序第二位的记录）则 where RK=2；以此类推

该语句执行的结果就是上述的情况了。

一不做二不休，我顺便查看一下该语句的执行计划好了。

首先写一下我的建表语句：

CREATE TABLE TEST
(
  area_id NUMBER,
  acct_month NUMBER,
  fee NUMBER,
  item_id NUMBER
)
PARTITION BY LIST(area_id)
(
  PARTITION part_290 VALUES('290'),
  PARTITION part_910 VALUES('910'),
  PARTITION part_911 VALUES('911'),
  partition part_912 values('912'),
  partition part_913 values('913'),
  partition part_914 values('914'),
  partition part_915 values('915'),
  partition part_916 values('916'),
  partition part_917 values('917'),
  partition part_919 values('919'),
  partition part_default values(default)
)

我按照地域进行了分区，其实也可以按照时间进行分区。explain一下plan：

可以看到，只有10条记录的表，COST却高达4，不得不说采取这个办法会极大地降低查询的效率。但是业务上需要的话，rank() over确实是一个很好使的玩意儿。

加两条数据进去，顺便测测分区表是否真的可以在没有索引的情况下提高一点点效率。加了两条数据，area_id是911，陕西省宝鸡市的区号：0911。语句也稍微变动一下：

SELECT A.AREA_ID, A.ACCT_MONTH, A.FEE, A.ITEM_ID, A.USER_ID
  FROM (SELECT T.AREA_ID,
               T.ACCT_MONTH,
               T.FEE,
               T.ITEM_ID,
               T.USER_ID,
               RANK() OVER(PARTITION BY T.ITEM_ID ORDER BY T.FEE DESC) RK
          FROM TEST T
          WHERE t.area_id = 290) A
 WHERE RK < 4;

explain一下plan：

发现虽然现在是12行数据，但是因为我只查询西安市（290），所以在rows里仍旧只是10行，没有新添加的宝鸡市的2行。由此可见，在面对非常海量的数据存储时，按照一定的条件建立分区，是十分有必要的。不过按照时间建立分区可能会稍微麻烦点，因为时间在不停的推进，多少年之后，你现在建立的分区就已经不可能再用了，就要添加新的分区进去，这也是一个挺讨厌的事情。