DENSE_RANK（）不重复 [英] DENSE_RANK() without duplication

问题描述

这是我的数据：

| col1 | col2 | denserank | whatiwant |
|------|------|-----------|-----------|
| 1    | 1    | 1         | 1         |
| 2    | 1    | 1         | 1         |
| 3    | 2    | 2         | 2         |
| 4    | 2    | 2         | 2         |
| 5    | 1    | 1         | 3         |
| 6    | 2    | 2         | 4         |
| 7    | 2    | 2         | 4         |
| 8    | 3    | 3         | 5         |

这是我到目前为止的查询：

Here's the query I have so far:

SELECT col1, col2, DENSE_RANK() OVER (ORDER BY COL2) AS [denserank]
FROM [table1]
ORDER BY [col1] asc

我想要实现的是让我的密集列在每次出现时递增是col2值的更改（即使值本身已被重用）。我实际上无法按我一直在进行密集排名的列排序，因此无法正常工作）。有关示例，请参见 whatiwant 列。

What I'd like to achieve is for my denserank column to increment every time there is a change in the value of col2 (even if the value itself is reused). I can't actually order by the column I have denserank on, so that won't work). See the whatiwant column for an example.

有什么方法可以通过实现DENSE_RANK（）？还是有其他选择？

Is there any way to achieve this with DENSE_RANK()? Or is there an alternative?

推荐答案

使用窗口函数尝试以下操作：

Try this using window functions:

with t(col1  ,col2) as (
select 1 , 1 union all  
select 2 , 1 union all  
select 3 , 2 union all  
select 4 , 2 union all  
select 5 , 1 union all  
select 6 , 2 union all  
select 7 , 2 union all  
select 8 , 3
)
select t.col1,
    t.col2,
    sum(x) over (
        order by col1
        ) whatyouwant
from (
    select t.*,
        case 
            when col2 = lag(col2) over (
                    order by col1
                    )
                then 0
            else 1
            end x
    from t
    ) t
order by col1;

产品：

它执行单个表读取，并按col1的升序形成一组连续的相等col2值，然后在其上找到密集的等级。

It does a single table read and forms group of consecutive equal col2 values in increasing order of col1 and then finds dense rank on that.

• x ：如果上一行的col2与该行的col2相同，则分配值0 （以增加 col1 的顺序）否则1


• 您想要的：创建组相等的 col2 值，方法是通过递增值来增加 col1 x 在最后一步中生成，这就是您的输出。

• x: Assign value 0 if previous row's col2 is same as this row's col2 (in order of increasing col1) otherwise 1
• whatyouwant: create groups of equal values of col2 in order of increasing col1 by doing an incremental sum of the value x generated in the last step and that's your output.

这篇关于DENSE_RANK（）不重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！