sql - 按顺序排列的密集排名

Question

我有这样的作业表

EMPLID | RCD | COMPANY |   EFFDT       |  SALARY
---------------------------------------------------
100    | 0   | xyz     |   1/1/2000    |    1000
100    | 0   | xyz     |   1/15/2000   |    1100
100    | 0   | xyz     |   1/31/2000   |    1200
100    | 0   | ggg     |   2/15/2000   |    1500
100    | 1   | abc     |   3/1/2000    |    2000
100    | 1   | abc     |   4/1/2000    |    2100

我需要一个计数器，它应该在 RCD 或公司组合发生变化时增加，并且应该由 effdt 订购。

EMPLID | RCD | COMPANY |   EFFDT       |  SALARY     | COUNTER
-------|-----|---------|---------------|-------------|----------
100    | 0   | xyz     |   1/1/2000    |    1000     | 1
100    | 0   | xyz     |   1/15/2000   |    1100     | 1
100    | 0   | xyz     |   1/31/2000   |    1200     | 1
100    | 0   | ggg     |   2/15/2000   |    1500     | 2
100    | 1   | abc     |   3/1/2000    |    2000     | 3
100    | 1   | abc     |   4/1/2000    |    2100     | 3

我按照 EMPLID 、 RCD 、 COMPANY 的顺序尝试了 Dense_Rank 函数，它为我提供了 Counter 但它不是按 effdt 的顺序。

SELECT EMPLID,RCD,COMPANY,EFFDT,
    DENSE_RANK() over (order by EMPLID , RCD , COMPANY) AS COUNTER
FROM ASSIGNMENT ;

按 EFFDT 排序，给出增量计数器 1 ... 6

SELECT EMPLID,RCD,COMPANY,EFFDT,
  DENSE_RANK() over (order by EFFDT) AS COUNTER 
FROM ASSIGNMENT;

请帮助我找出我所缺少的。

Answer 1

尝试滞后

WITH flagged AS (  
    SELECT *, 
      CASE WHEN LAG(RCD) OVER(PARTITION BY EMPLID ORDER BY EFFDT) = RCD 
              AND LAG(COMPANY) OVER(PARTITION BY EMPLID ORDER BY EFFDT) = COMPANY THEN 0 ELSE 1 END strtFlag
    FROM tbl
    )

SELECT EMPLID, RCD, COMPANY, EFFDT, SALARY, SUM(strtFlag) OVER(PARTITION BY EMPLID ORDER BY EFFDT) COUNTER
FROM flagged

或者，使用 DENSE_RANK() 组

WITH grps AS (  
    SELECT *, 
      ROW_NUMBER() OVER(PARTITION BY EMPLID ORDER BY EFFDT) -
      ROW_NUMBER() OVER(PARTITION BY EMPLID, RCD, COMPANY ORDER BY EFFDT) grp
    FROM tbl
    )

SELECT EMPLID, RCD, COMPANY, EFFDT, SALARY
  , DENSE_RANK() OVER(PARTITION BY EMPLID ORDER BY grp) COUNTER
FROM grps

无论如何，看起来需要两个步骤才能获得密集编号。