如何Dense_Rank()重复的值集 [英] How to Dense_Rank() Sets of Values that Repeat

问题描述

我有一个表格T，如下所示:

I have a table T like the following:

EMPLID  CODE    DT
101     PPP     01-JAN-15
101     PPP     02-JAN-15
101     PPP     03-JAN-15
101     OOO     04-JAN-15
101     OOO     05-JAN-15
101     PPP     06-JAN-15
101     PPP     07-JAN-15
101     PPP     08-JAN-15
101     PPP     09-JAN-15

我想要的是如下结果:

EMPLID  CODE    RNK   DT
101     PPP     1     01-JAN-15
101     PPP     1     02-JAN-15
101     PPP     1     03-JAN-15
101     OOO     2     04-JAN-15
101     OOO     2     05-JAN-15
101     PPP     3     06-JAN-15
101     PPP     3     07-JAN-15
101     PPP     3     08-JAN-15
101     PPP     3     09-JAN-15

但是我似乎只得到如下结果:

but I only seem to be getting results like the following:

SELECT EMPLID, CODE, DENSE_RANK() OVER(ORDER BY CODE) AS RNK, DT 
FROM T;


EMPLID  CODE    RNK   DT
101     OOO     1     05-JAN-15
101     OOO     1     04-JAN-15
101     PPP     2     03-JAN-15
101     PPP     2     08-JAN-15
101     PPP     2     09-JAN-15
101     PPP     2     06-JAN-15
101     PPP     2     07-JAN-15
101     PPP     2     02-JAN-15
101     PPP     2     01-JAN-15

由于PPP值在OOO值之后重复但在以后的日期重复，所以我不确定如何对其进行分组/排序，以便获得RNK字段以考虑第二组PPP值作为第三组.

Since the PPP values repeat after the OOO values but at later dates, I'm not sure how to group/order it so that I get the RNK field to consider the second set of PPP values as a third group.

以下是该表的代码，可以使此操作更简单:

Here is the code for the table to make this a little easier:

WITH
T AS(
SELECT '101' AS EMPLID, 'PPP' AS CODE, '01-JAN-15' AS DT FROM DUAL
UNION ALL
SELECT '101', 'PPP', '02-JAN-15' FROM DUAL
UNION ALL
SELECT '101', 'PPP', '03-JAN-15' FROM DUAL
UNION ALL
SELECT '101', 'OOO', '04-JAN-15' FROM DUAL
UNION ALL
SELECT '101', 'OOO', '05-JAN-15' FROM DUAL
UNION ALL
SELECT '101', 'PPP', '06-JAN-15' FROM DUAL
UNION ALL
SELECT '101', 'PPP', '07-JAN-15' FROM DUAL
UNION ALL
SELECT '101', 'PPP', '08-JAN-15' FROM DUAL
UNION ALL
SELECT '101', 'PPP', '09-JAN-15' FROM DUAL
)
SELECT * 
FROM T;

推荐答案

我认为您需要两次通过:首先找到代码更改的所有行，然后在此基础上建立一个组.

I think you need two passes: First find all rows where the code changes, then build a group on this.

select emplid, code, sum(change) over (order by dt) as rnk, dt 
from
(
  select 
    emplid,
    code,
    case when lag(code) over (order by dt) = code then 0 else 1 end as change,
    dt
  from t
  order by dt
);

这篇关于如何Dense_Rank()重复的值集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！