值更改时重置行号，但分区中具有重复值 [英] Reset Row Number on value change, but with repeat values in partition
问题描述
我遇到了与此问题非常相似的问题 在字段更改时T-sql重置行号
I'm having trouble with something very similar to this question T-sql Reset Row number on Field Change
该问题的解决方案是完美的，效果很好.除非我尝试其他多个"custno"，否则它会崩溃.
The solution to this question is perfect, works fine. Except when I try with multiple other 'custno', it breaks down.
我的意思是
custno moddate who
--------------------------------------------------
581827 2012-11-08 08:38:00.000 EMSZC14
581827 2012-11-08 08:41:10.000 EMSZC14
581827 2012-11-08 08:53:46.000 EMSZC14
581827 2012-11-08 08:57:04.000 EMSZC14
581827 2012-11-08 08:58:35.000 EMSZC14
581827 2012-11-08 08:59:13.000 EMSZC14
581827 2012-11-08 09:00:06.000 EMSZC14
581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1
581827 2012-11-08 09:05:04.000 EMSZC49
581827 2012-11-08 09:06:32.000 EMSZC49
581827 2012-11-08 09:12:03.000 EMSZC49
581827 2012-11-08 09:12:38.000 EMSZC49
581827 2012-11-08 09:14:18.000 EMSZC49
581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1
-- my new rows for example of problem
581829 2012-11-08 09:12:03.000 EMSZC14 1
581829 2012-11-08 09:12:38.000 EMSZC49 1
581829 2012-11-08 09:14:18.000 EMSZC49
581829 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1
引入新的custno打破了这一解决方案，该解决方案对于一个custno来说是完美的.
The introduction of a new custno breaks this solution, which works perfectly for the one custno.
with C1 as
(
select
custno, moddate, who,
lag(who) over(order by moddate) as lag_who
from
chr
),
C2 as
(
select
custno, moddate, who,
sum(case when who = lag_who then 0 else 1 end)
over(order by moddate rows unbounded preceding) as change
from
C1
)
select
row_number() over(partition by change order by moddate) as RowID,
custno, moddate, who
from
C2
我敢肯定，处理多个custno只是稍作调整，但这已经超出了我的能力范围，我设法使其适用于我的数据，但这纯粹是通过替换列和表名来完成的.不幸的是，我没有足够详细的理解来解决我遇到的问题.
I'm sure it's only a little tweak to handle multiple custno's, but this is already way beyond my capabilities and I managed to make it work for my data but that was purely by replacing column and table names. Unfortunately don't have a detailed enough understanding to resolve the issue I have.
我的数据看起来像
custno start_date value
实际上完全一样.每当值"或谁"更改时，我都希望行/等级为1，无论该值/谁以前见过.这都是相对于custno而言的.我确实看到了一个值/谁也可以返回相同值的实例.同样，上述解决方案可以很好地处理重复"……但对于一个custno
effectively exactly the same. I want a Row/rank of 1 for every time the 'value' or 'who' changes, regardless if that value/who has been seen before. This is all relative to a custno. And I do see instances where a value/who can return back to the same value as well. Again, solution above handled that 'repetition' just fine... but for the one custno
我想我只是需要以某种方式在某处添加某种按custno分组的方式?只是不确定在哪里或如何
I'm thinking I just need to somehow add some sort of grouping by custno in somewhere? Just not sure where or how
谢谢！
推荐答案
这是一个空白问题，我们可以在此处使用行数差异法:
This is a gaps and islands problem, and we can use the difference in row numbers method here:
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY custno ORDER BY moddate) rn1,
ROW_NUMBER() OVER (PARTITION BY custno, who ORDER BY moddate) rn2
FROM chr
)
SELECT custno, moddate, who,
ROW_NUMBER() OVER (PARTITION BY custno, rn1 - rn2 ORDER BY moddate) rn
FROM cte
ORDER BY
custno,
moddate;
为解释此处使用的行号方法的差异，根据上面显示的数据，rn1只是每个客户从1开始的时间顺序序列. rn2序列另外由who划分.对于每个客户来说，在rn1和rn2之间的差异总是具有相同的值.正是由于这种不同，我们才在整个表中使用一个行号来生成您实际想要查看的序列.
For an explanation of the difference in row number method used here, rn1 is just a time-ordered sequence from 1 onwards, per customer, according to the data you have shown above. The rn2 sequence is partitioned additionally by who. It is the case the difference between rn1 and rn2 will always have the same value, for each customer. It is with this difference that we then take a row number over the entire table to generate the sequence you actually want to see.
这篇关于值更改时重置行号，但分区中具有重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
