Oracle 在多个字段上对列进行排名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11400392/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle ranking columns on multiple fields
提问by Kevin Bojarski
I am having some issues with ranking some columns in Oracle. I have two columns I need to rank--a group id and a date.
我在对 Oracle 中的某些列进行排名时遇到了一些问题。我有两列需要排名——一个组 ID 和一个日期。
I want to group the table two ways:
我想以两种方式对表格进行分组：
- Rank the records in each GROUP_ID by DATETIME (RANK_1)
- Rank the GROUP_IDs by their DATETIME, GROUP_ID (RANK_2)
- 按 DATETIME (RANK_1) 对每个 GROUP_ID 中的记录进行排名
- 按 GROUP_ID 的 DATETIME、GROUP_ID 对 GROUP_ID 进行排名 (RANK_2)
It should look like this:
它应该是这样的：
GROUP_ID | DATE | RANK_1 | RANK_2
----------|------------|-----------|----------
2 | 1/1/2012 | 1 | 1
2 | 1/2/2012 | 2 | 1
2 | 1/4/2012 | 3 | 1
3 | 1/1/2012 | 1 | 2
1 | 1/3/2012 | 1 | 3
I have been able to do the former, but have been unable to figure out the latter.
我已经能够做到前者，但一直无法弄清楚后者。
SELECT group_id,
datetime,
ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY datetime) AS rn,
DENSE_RANK() OVER (ORDER BY group_id) AS rn2
FROM table_1
ORDER BY group_id;
This incorrectly orders the RANK_2 field:
这错误地对 RANK_2 字段进行了排序：
GROUP_ID | DATE | RANK_1 | RANK_2
----------|------------|-----------|----------
1 | 1/3/2012 | 1 | 1
2 | 1/1/2012 | 1 | 2
2 | 1/2/2012 | 2 | 2
2 | 1/4/2012 | 3 | 2
3 | 1/1/2012 | 1 | 3
采纳答案by Gordon Linoff
Assuming you don't have an actual id column in the table, it appears that you want to do the second rank by the earliest date in each group. This will require a nested subquery:
假设您在表中没有实际的 id 列，您似乎希望按每个组中的最早日期进行第二排名。这将需要一个嵌套的子查询：
select group_id, datetime, rn,
dense_rank() over (order by EarliestDate, group_id) as rn2
from (SELECT group_id, datetime,
ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY datetime) AS rn,
min(datetime) OVER (partition by group_id) as EarliestDate
FROM table_1
) t
ORDER BY group_id;
