DENSE_RANK() tanpa duplikasi

Coba ini menggunakan fungsi jendela:

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;

Menghasilkan:

Ia melakukan pembacaan tabel tunggal dan membentuk kelompok nilai col2 yang sama berturut-turut dalam urutan col1 yang meningkat dan kemudian menemukan peringkat padat di atasnya.

• x: Tetapkan nilai 0 jika kolom2 baris sebelumnya sama dengan kolom2 baris ini (dalam urutan naik col1) jika tidak 1
• whatyouwant: buat grup dengan nilai yang sama col2 dalam urutan peningkatan col1 dengan melakukan penjumlahan tambahan dari nilai x yang dihasilkan pada langkah terakhir dan itulah keluaran Anda.

Saya akan melakukannya dengan cte rekursif seperti ini:

declare @Dept table (col1 integer, col2 integer)

insert into @Dept values(1, 1),(2, 1),(3, 2),(4, 2),(5, 1),(6, 2),(7, 2),(8, 3)

;with a as (
select col1, col2, 
ROW_NUMBER() over (order by col1) as rn
from @Dept),
s as 
(select col1, col2, rn, 1 as dr from a where rn=1
union all
select a.col1, a.col2, a.rn, case when a.col2=s.col2 then s.dr else s.dr+1 end as dr 
from a inner join s on a.rn=s.rn+1)
col1, col2, dr from s

result:

col1        col2        dr
----------- ----------- -----------
1           1           1
2           1           1
3           2           2
4           2           2
5           1           3
6           2           4
7           2           4
8           3           5

ROW_NUMBER hanya diperlukan jika nilai col1 Anda tidak berurutan. Jika ya, Anda dapat langsung menggunakan cte rekursif

Berikut adalah salah satu cara menggunakan fungsi agregat jendela SUM OVER(Order by)

SELECT col1,Col2,
       Sum(CASE WHEN a.prev_val = a.col2 THEN 0 ELSE 1 END) OVER(ORDER BY col1) AS whatiwant 
FROM   (SELECT col1,
               col2,
               Lag(col2, 1)OVER(ORDER BY col1) AS prev_val
        FROM   Yourtable) a
ORDER  BY col1; 

Cara kerjanya:

Fungsi jendela LAG digunakan untuk mencari col2 sebelumnya untuk setiap baris yang diurutkan berdasarkan col1

SUM OVER(Order by) akan menambah jumlahnya hanya jika col2 sebelumnya tidak sama dengan col2 saat ini

Saya pikir ini mungkin terjadi dalam SQL murni menggunakan beberapa trik celah dan pulau, tetapi jalur yang paling sedikit hambatannya mungkin adalah menggunakan variabel sesi yang dikombinasikan dengan LAG() untuk melacak kapan peringkat padat yang dihitung Anda mengubah nilai. Dalam kueri di bawah ini, saya menggunakan @a untuk melacak perubahan peringkat padat, dan ketika berubah, variabel ini bertambah 1.

DECLARE @a int
SET @a = 1
SELECT t.col1,
       t.col2,
       t.denserank,
       @a = CASE WHEN LAG(t.denserank, 1, 1) OVER (ORDER BY t.col1) = t.denserank
                 THEN @a
                 ELSE @a+1 END AS [whatiwant]
FROM
(
    SELECT col1, col2, DENSE_RANK() OVER (ORDER BY COL2) AS [denserank]
    FROM [table1]
) t
ORDER BY t.col1