Oracle SQL-DENSE_RANK [英] Oracle SQL - DENSE_RANK
问题描述
我有一个客户数据表，其选定列如下所示:
I have a Client data table, selected columns of which are shown below:
Row_ID Client_ID Status_ID From_date To_date
1 123456 4 20/12/2007 18:02 20/12/2007 18:07
2 789087 4 20/12/2007 18:02 20/12/2007 18:07
3 789087 4 20/12/2007 18:07 20/12/2007 18:50
4 789087 4 20/12/2007 18:50 21/12/2007 10:38
5 123456 4 20/12/2007 18:07 20/12/2007 18:50
6 123456 4 20/12/2007 18:50 21/12/2007 10:38
7 123456 4 21/12/2007 10:38 21/12/2007 16:39
8 789087 4 21/12/2007 10:38 21/12/2007 17:54
9 789087 4 21/12/2007 17:54 21/12/2007 18:32
10 789087 4 21/12/2007 18:32 22/12/2007 06:48
11 123456 5 21/12/2007 16:39
12 789087 5 22/12/2007 06:48 22/12/2007 10:53
13 789087 4 22/12/2007 10:53 22/12/2007 11:51
14 789087 5 22/12/2007 11:51
在按Client_ID然后按From_date将数据按升序排列之后，我的目标是在每次将状态与上一行进行比较时，该客户端的状态发生变化时，都添加一个计算得出的Rank_ID.我想要的Rank_ID所需的值如下所示:
After putting the data into ascending order by Client_ID and then by From_date, my objective is to add a calculated Rank_ID every time there is a change in the status for that client when comparing the status to the previous line. The desired values I want for the Rank_ID are shown below:
Row_ID Client_ID Status_ID From_date To_date Rank_ID
1 123456 4 20/12/2007 18:02 20/12/2007 18:07 1
5 123456 4 20/12/2007 18:07 20/12/2007 18:50 1
6 123456 4 20/12/2007 18:50 21/12/2007 10:38 1
7 123456 4 21/12/2007 10:38 21/12/2007 16:39 1
11 123456 5 21/12/2007 16:39 2
2 789087 4 20/12/2007 18:02 20/12/2007 18:07 3
3 789087 4 20/12/2007 18:07 20/12/2007 18:50 3
4 789087 4 20/12/2007 18:50 21/12/2007 10:38 3
8 789087 4 21/12/2007 10:38 21/12/2007 17:54 3
9 789087 4 21/12/2007 17:54 21/12/2007 18:32 3
10 789087 4 21/12/2007 18:32 22/12/2007 06:48 3
12 789087 5 22/12/2007 06:48 22/12/2007 10:53 4
13 789087 4 22/12/2007 10:53 22/12/2007 11:51 5
14 789087 5 22/12/2007 11:51 6
我正在尝试将DENSE_RANK用作分析函数，下面是我的错误" SQL代码
I am trying to use DENSE_RANK as an analytical function, my "incorrect" SQL code being below
SELECT t1.*, DENSE_RANK () OVER (ORDER BY t1.client_id, t1.status_id) rank_id
FROM (SELECT c.client_ID, c.status_id, c.from_date, c.to_date
FROM client c
ORDER BY c.client_id, c.from_date) t1
ORDER BY t1.client_id, t1.from_date
但是，我遇到的问题是它给出了编写的SQL代码，它按如下方式计算Rank_ID:
However, the problem I am encountering is that it given the SQL code as written it calculates the Rank_ID as follows:
Row_ID Client_ID Status_ID From_date To_date Rank_ID
1 123456 4 20/12/2007 18:02 20/12/2007 18:07 1
5 123456 4 20/12/2007 18:07 20/12/2007 18:50 1
6 123456 4 20/12/2007 18:50 21/12/2007 10:38 1
7 123456 4 21/12/2007 10:38 21/12/2007 16:39 1
11 123456 5 21/12/2007 16:39 2
2 789087 4 20/12/2007 18:02 20/12/2007 18:07 3
3 789087 4 20/12/2007 18:07 20/12/2007 18:50 3
4 789087 4 20/12/2007 18:50 21/12/2007 10:38 3
8 789087 4 21/12/2007 10:38 21/12/2007 17:54 3
9 789087 4 21/12/2007 17:54 21/12/2007 18:32 3
10 789087 4 21/12/2007 18:32 22/12/2007 06:48 3
12 789087 5 22/12/2007 06:48 22/12/2007 10:53 4
13 789087 4 22/12/2007 10:53 22/12/2007 11:51 3
14 789087 5 22/12/2007 11:51 4
对于记录13，返回的Rank_ID为3(而与该客户先前记录的状态相比，我希望该5为该客户的状态变化)，而对于记录14，返回的Rank_ID为4，而我想要6，因为与上一行相比，这再次是该客户的状态更改.
For record 13, the returned Rank_ID is 3 (whereas I want 5 at it is a change in status for that client when compared to the status on the previous record for that client) and for record 14 the returned Rank_ID is 4, whereas I want 6 as it is again a change in status for that client when compared to the previous line.
我想问题是我的SQL按Client_ID然后按Status_ID对数据进行排序，因此我可以看到为什么它产生它给出的答案.问题是无论我对DENSE_RANK行进行了什么更改，我都无法获得想要的答案.
I guess the problem is that my SQL orders the data by Client_ID and then by Status_ID, and so I can see why it produces the answers it gives. The problem is no matter what changes I make to the DENSE_RANK line, I can't obtain the answer I want.
任何帮助将不胜感激.
推荐答案
据我了解，这是您需要的:
As I understood, this is what you need:
select client_ID, status_id, from_date, to_date,
sum(start_of_group) over (order by client_ID, from_date) + 1 rank
from (SELECT c.client_ID, c.status_id, c.from_date, c.to_date,
case when lag(c.client_ID, 1, c.client_ID) over (order by c.client_ID, c.from_date) = c.client_ID
and lag(c.status_id, 1, c.status_id) over (order by c.client_ID, c.from_date) = c.status_id
then 0 else 1 end start_of_group
FROM client c)
order by client_ID, from_date
这篇关于Oracle SQL-DENSE_RANK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
