我有一个客户数据表,其选定列如下所示：
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所需的值如下所示：
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代码在下面
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：
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,因为与前一行相比,该客户的状态发生了变化.
我想问题是我的sql通过Client_ID然后通过Status_ID对数据进行排序,因此我可以看出为什么它会产生它给出的答案.问题是无论我对DENSE_RANK行做了什么改变,我都无法得到我想要的答案.
任何帮助,将不胜感激.
解决方法
据我所知,这就是你所需要的：
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.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,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
