我查看了一些数据库供应商,到目前为止,只有Oracle似乎在两种情况下都支持rank((函数;分析和聚合。
这是他们文档的链接:https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm
我看过mySQL,Snowflake,e.t.c,但它们似乎都只支持分析等级((。有没有人在聚合上下文中使用排名与任何其他RDBMS一起使用,或者它通常是一种解决方法?
谢谢!
据我所知,目前(2018 年 4 月(只有两个数据库聚合实现了此功能:
甲骨文 --> 演示 --> http://sqlfiddle.com/#!4/74fa70/2
SELECT rank( 2 ) within group (order by x) as rank_x FROM qwe;
SELECT rank( 2 ) within group (order by y) as rank_y FROM qwe;
SELECT rank( 2, 7 ) within group (order by x,y) as rank_x_y FROM qwe;
| RANK_X |
|--------|
| 5 |
| RANK_Y |
|--------|
| 2 |
| RANK_X_Y |
|----------|
| 7 |
PostgreSQL -->演示 --> http://sqlfiddle.com/#!17/74fa7/5
SELECT rank( 2 ) within group (order by x) as rank_x FROM qwe;
SELECT rank( 2 ) within group (order by y) as rank_y FROM qwe;
SELECT rank( 2, 7 ) within group (order by x,y) as rank_x_y FROM qwe;
| RANK_X |
|--------|
| 5 |
| RANK_Y |
|--------|
| 2 |
| RANK_X_Y |
|----------|
| 7 |
分析版rank() over
,除了Oracle和PostgreSQL,也已经在SQL-Server,DB2,MySql中实现(将在即将推出的版本中(。
这两个演示基于以下示例数据:
CREATE TABLE qwe(
x int, y int
);
insert into qwe values(1,1);
insert into qwe values(1,2);
insert into qwe values(1,3);
insert into qwe values(1,4);
insert into qwe values(2,5);
insert into qwe values(2,6);
insert into qwe values(3,7);