问题

我尝试计算按两列（User_location和Map_id）分组的行数，但它没有给我想要看到的值。它只允许我按一列分组。

使用的代码：

SELECT ROW_NUMBER() OVER(PARTITION BY User_location, Map_id
    ORDER BY User_location, Map_id ASC) AS Row_Num, 
    Session_id, User_location, Map_id, loc_div 
FROM table

这是我得到的结果。

| Row_num | Session_id | User_location | Map_id | loc_div |
| :--------: | :--------: | :--------------: | :--------------: | :--------------: |
| 1 | XD001| Delta 1| A1| loc010|
| 2 | XD002| Delta 1| A1| loc020|
| 3 | XD003| Delta 1| A1| loc040|
| 1 | XD004| Delta 1| A2| loc050|
| 2 | XD005| Delta 1| A2| loc060|
| 1 | XD006| Delta 2| A1| loc070|
| 2 | XD009| Delta 2| A1| loc030|
| 1 | XD007| Delta 2| A2| loc080|
| 2 | XD008| Delta 2| A2| loc090|

尝试实现按user_location和map_id分组的分开的行号，如下所示：

| Row_num | Session_id | User_location | Map_id | loc_div |
| :--------: | :--------: | :--------------: | :--------------: | :--------------: |
| 1 | XD001| Delta 1| A1| loc010|
| 2 | XD002| Delta 1| A1| loc020|
| 3 | XD003| Delta 1| A1| loc040|
| 1 | XD004| Delta 1| A2| loc050|
| 2 | XD005| Delta 1| A2| loc060|
| 1 | XD006| Delta 2| A1| loc070|
| 2 | XD009| Delta 2| A1| loc030|
| 1 | XD007| Delta 2| A2| loc080|
| 2 | XD008| Delta 2| A2| loc090|

SELECT ROW_NUMBER() OVER(PARTITION BY User_location
    ORDER BY Map_id ASC) AS Row_Num, 
    Session_id, User_location, Map_id, loc_div 
FROM table

答案1

得分: 1

你可以将Session_id或loc_div中的任一项添加到行号的分区中。假设选择后者，你可以尝试：

SELECT ROW_NUMBER() OVER (PARTITION BY User_location, loc_div
                          ORDER BY Map_id) AS Row_Num, 
       Session_id, User_location, Map_id, loc_div 
FROM yourTable
ORDER BY User_location, Map_id, loc_div;

SELECT ROW_NUMBER() OVER (PARTITION BY User_location, loc_div
                          ORDER BY Map_id) AS Row_Num, 
       Session_id, User_location, Map_id, loc_div 
FROM yourTable
ORDER BY User_location, Map_id, loc_div;