文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >Oracle DENSE_RANK

问Oracle DENSE_RANK
EN

Stack Overflow用户
提问于 2016-01-12 16:05:28
回答 4查看 152关注 0票数 4

我有一个客户端数据表，其中选定的列如下所示：

代码语言：javascript
复制
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值如下所示：

代码语言：javascript
复制
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代码在下面。

代码语言：javascript
复制
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计算如下：

代码语言：javascript
复制
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行做了什么更改，我都无法得到我想要的答案。

任何帮助都将不胜感激。

dense-rank
sql
oracle
EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2016-01-12 16:39:16

据我所知，这是你所需要的：

代码语言：javascript
复制
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

SQLFiddle

票数 1
EN

Stack Overflow用户

发布于 2016-01-12 16:41:39

问题是，您需要将您的排名划分为状态的更改，而不是状态的值。我在输出中保留了一些额外的列，以便您可以看到这些列是如何派生出来的：

代码语言：javascript
复制
WITH dat as (
 SELECT 1 row_id,     123456 client_id,      4 status,       to_date('20/12/2007 18:02','dd/mm/yyyy hh24:mi') frdate,    to_date('20/12/2007 18:07','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT   2 row_id,     789087  client_id,       4 status,        to_date('20/12/2007 18:02','dd/mm/yyyy hh24:mi') frdate,    to_date('20/12/2007 18:07','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT   3 row_id,     789087 client_id,        4  status,       to_date('20/12/2007 18:07','dd/mm/yyyy hh24:mi') frdate,    to_date('20/12/2007 18:50','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT   4 row_id,     789087 client_id,        4 status,        to_date('20/12/2007 18:50','dd/mm/yyyy hh24:mi') frdate,    to_date('21/12/2007 10:38','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT   5  row_id,    123456 client_id,        4 status,        to_date('20/12/2007 18:07','dd/mm/yyyy hh24:mi') frdate,    to_date('20/12/2007 18:50','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT   6 row_id,     123456 client_id,        4 status,        to_date('20/12/2007 18:50','dd/mm/yyyy hh24:mi') frdate,    to_date('21/12/2007 10:38','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT   7  row_id,    123456 client_id,        4 status,        to_date('21/12/2007 10:38','dd/mm/yyyy hh24:mi') frdate,    to_date('21/12/2007 16:39','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT   8 row_id,     789087  client_id,       4  status,       to_date('21/12/2007 10:38','dd/mm/yyyy hh24:mi') frdate,    to_date('21/12/2007 17:54','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT   9 row_id,     789087  client_id,       4 status,        to_date('21/12/2007 17:54','dd/mm/yyyy hh24:mi') frdate,    to_date('21/12/2007 18:32','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT  10 row_id,     789087 client_id,        4 status,        to_date('21/12/2007 18:32','dd/mm/yyyy hh24:mi') frdate,    to_date('22/12/2007 06:48','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT  11 row_id,     123456  client_id,       5 status,        to_date('21/12/2007 16:39','dd/mm/yyyy hh24:mi') frdate,    null from dual union all
 SELECT  12 row_id,     789087 client_id,        5 status,        to_date('22/12/2007 06:48','dd/mm/yyyy hh24:mi') frdate,    to_date('22/12/2007 10:53','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT  13 row_id,     789087 client_id,        4  status,       to_date('22/12/2007 10:53','dd/mm/yyyy hh24:mi') frdate,    to_date('22/12/2007 11:51','dd/mm/yyyy hh24:mi') todate from dual union all
 SELECT  14 row_id,     789087 client_id,        5 status,        to_date('22/12/2007 11:51','dd/mm/yyyy hh24:mi') frdate,    null from dual)
SELECT t1.*, DENSE_RANK () OVER (ORDER BY t1.client_id, t1.chg_status) rank_id
FROM (select client_id, status, prev_status, sum(case when nvl(prev_status,-1) != status then 1 else 0 end) over (partition by client_id order by frdate) chg_status, frdate, todate
      from (
      SELECT c.client_ID
           , c.status
           , lag(status) over (partition by client_id order by frdate) as prev_status
           , c.frdate
           , c.todate
      FROM dat c
      ORDER BY c.client_id, c.frdate)) t1
ORDER BY t1.client_id, t1.frdate

返回：

代码语言：javascript
复制
CLIENT_ID, STATUS, PREV_STATUS, CHG_STATUS,  FRDATE,                 TODATE,              RANK_ID
123456,    4,      ,            1,           20/12/2007 6:02:00 PM, 20/12/2007 6:07:00 PM, 1
123456,    4,      4,            1,          20/12/2007 6:07:00 PM, 20/12/2007 6:50:00 PM, 1
123456,    4,      4,            1,          20/12/2007 6:50:00 PM, 21/12/2007 10:38:00 AM, 1
123456,    4,      4,            1,          21/12/2007 10:38:00 AM, 21/12/2007 4:39:00 PM, 1
123456,    5,      4,            2,          21/12/2007 4:39:00 PM,,                       2
789087,    4,      ,            1,           20/12/2007 6:02:00 PM,20/12/2007 6:07:00 PM, 3
789087,    4,      4,            1,          20/12/2007 6:07:00 PM,20/12/2007 6:50:00 PM, 3
789087,    4,      4,            1,          20/12/2007 6:50:00 PM, 21/12/2007 10:38:00 AM, 3
789087,    4,      4,            1,          21/12/2007 10:38:00 AM, 21/12/2007 5:54:00 PM, 3
789087,    4,      4,            1,          21/12/2007 5:54:00 PM, 21/12/2007 6:32:00 PM, 3
789087,    4,      4,            1,          21/12/2007 6:32:00 PM,22/12/2007 6:48:00 AM, 3
789087,    5,      4,            2,          22/12/2007 6:48:00 AM, 22/12/2007 10:53:00 AM, 4
789087,    4,      5,            3,          22/12/2007 10:53:00 AM, 22/12/2007 11:51:00 AM, 5
789087,    5,      4,            4,          22/12/2007 11:51:00 AM,,                      6
票数 0
EN

Stack Overflow用户

发布于 2016-01-12 16:57:24

简单地用1标记每个客户端的所有状态更改(下面是GRP列)。而不是使用解析和函数将这些数字相加：

代码语言：javascript
复制
with tab1 as (
select client_id,from_date, status,
 nvl(lag(status) over (partition by client_id  order by from_date),-1) status_lag,
 case when (nvl(lag(status) over (partition by client_id  order by from_date),-1) <> status) then 
 1 end grp
from tst
)
, tab2 as (
select client_id,from_date, status,status_lag, grp,
sum(grp) over (partition by client_id  order by from_date) as RANK
from tab1
)
select * from tab2;

按预期给予

代码语言：javascript
复制
 CLIENT_ID FROM_DATE               STATUS STATUS_LAG        GRP       RANK
---------- ------------------- ---------- ---------- ---------- ----------
      1001 01.10.2015 00:00:00          1         -1          1          1 
      1001 02.10.2015 00:00:00          1          1                     1 
      1001 03.10.2015 00:00:00          2          1          1          2 
      1001 04.10.2015 00:00:00          2          2                     2 
      1001 05.10.2015 00:00:00          3          2          1          3 
      1001 09.10.2015 00:00:00          1          3          1          4 
      1002 12.10.2015 00:00:00          1         -1          1          1 
      1002 13.10.2015 00:00:00          3          1          1          2 
      1002 15.10.2015 00:00:00          3          3                     2

我的圈套

代码语言：javascript
复制
 create table tst 
 (client_id number,
 from_date date,
 status number);

 insert into tst values (1001, to_date('01-10-15','dd-mm-rr'),1);
 insert into tst values (1001, to_date('02-10-15','dd-mm-rr'),1);
 insert into tst values (1001, to_date('03-10-15','dd-mm-rr'),2);
 insert into tst values (1001, to_date('04-10-15','dd-mm-rr'),2);
 insert into tst values (1001, to_date('05-10-15','dd-mm-rr'),3);
 insert into tst values (1001, to_date('09-10-15','dd-mm-rr'),1);
 insert into tst values (1002, to_date('12-10-15','dd-mm-rr'),1);
 insert into tst values (1002, to_date('13-10-15','dd-mm-rr'),3); 
 insert into tst values (1002, to_date('15-10-15','dd-mm-rr'),3);
 commit;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/34748397

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档