根据连续行的分区的唯一标识符

Question

我正在处理一个SQL Server查询，该查询需要分区，以便按日期排序的具有相同Type值的连续行具有相同的唯一标识符。

假设我有下表

declare @test table 
(
CustomerId  varchar(10),
Type INT,
date datetime
)
insert into @test values ('aaaa', 1,'2015-10-24 22:52:47')
insert into @test values ('bbbb', 1,'2015-10-23 22:56:47')
insert into @test values ('cccc', 2,'2015-10-22 21:52:47')
insert into @test values ('dddd', 2,'2015-10-20 22:12:47')
insert into @test values ('aaaa', 1,'2015-10-19 20:52:47')
insert into @test values ('dddd', 2,'2015-10-18 12:52:47')
insert into @test values ('aaaa', 3,'2015-10-18 12:52:47')

我希望我的输出列是这样的(数字不需要排序，我只需要每个组的唯一标识符(：编辑了原始帖子，因为我在我想要的输出上犯了一个错误

0
0
1
1
2
3
4

免责声明：customerId与我的分区并不真正相关，如果每行的customerId不同，则输出将是相同的

我当前的查询似乎做到了，但在某些情况下，它无法为具有不同类型值的行提供相同的ID。

SELECT row_number() over(order by date) - row_number() over (partition by  Type order by date)
FROM @TEST 

Answer 1

尝试以下操作：

declare @test table (
CustomerId  varchar(10),
Type INT,
date datetime
)
insert into @test
values
('aaaa', 1,'2015-10-24 22:52:47'),
('bbbb', 1,'2015-10-23 22:56:47'),
('cccc', 2,'2015-10-22 21:52:47'),
('dddd', 2,'2015-10-20 22:12:47'),
('aaaa', 1,'2015-10-19 20:52:47'),
('dddd', 2,'2015-10-18 12:52:47'),
('aaaa', 3,'2015-10-18 12:52:47')
;
with cte as (
select *, newtype = case when Type <> lag(Type) over(order by date desc) then 1 else 0 end
from @test
)
select *,
Result = sum(newtype) over(
order by date desc
rows between unbounded preceding and current row
)
from cte
order by date desc

结果：

日期新类型结果[/tr>2015-10-24 22:52:47.0001<1><2><1>34

	客户Id	类型
aaaaa	1	0
bbbbb	1	2015-10-23 22:56:47.000	0
cccc	2	2015-10-22 21:52:47.000	1
dddd>	2	2015-10-20 22:12:47.000	0	1
aaaaa	1	2015-10-19 20:52:47.000
dddd>	2	2015-10-18 12:52:47.000
aaaaa	3	2015-10-18 12:52:47.000	1