Group Number for similar values

Question

我正在尝试获得SQL Server中所有类似值的组号。

这是我的结果集:

Values              SortOrder
------------------------------
test note 1         1
test note 1         2
test note 3         3
test 10             4
test 10             5
test 11             6

我正在尝试获得以下结果集:

Values              SortOrder        Group
------------------------------------------
test note 1         1                 1
test note 1         2                 1
test note 3         3                 2
test 10             4                 3
test 10             5                 3
test 11             6                 4

我尝试了各种技术，如Dense_Rank, Rank, NTile等，但每次组号都是一个运行的数字系列。

Select
Dense_Rank() Over (Order By SortOrder) Rank,
SortOrder,
Values from table where values is not null

任何帮助都将是非常感激的。

Answer 1

这看起来像是一个缺口岛问题—假设相同的值可能出现在不同组的连续行中。

我们可以通过每次值改变时增加一个窗口和来解决这个问题，如下所示:

select t.*, 
sum(case when val = lag_val then 0 else 1 end) over(order by sortOrder) as grp
from (
select t.*, lag(val) over(order by sortOrder) lag_val
from mytable t
) t

grp33

Dense Rank可以用于该作业，但是您需要根据需要排序的列进行排序，只需稍微修改一下:

Select Dense_Rank() Over (Order By [Values]) Rank, SortOrder, [Values]
from table 
where [values] is not null;

当然，这将数据排序为字符串，为了仅基于数字内容获得排名，您需要根据对进行排序。

我不太了解你的数据，但是根据提供的样本，你可以假设最后一个空格后面最右边的字符总是数值，在这种情况下，以下将给出你想要的排名:

select Dense_Rank() Over (Order By Try_Convert(int,Right([values], CharIndex(' ', Reverse([values]))))) Rank,
SortOrder, [Values]
from table
order by sortorder;

查看工作示例

为了按Values分组，但也按最低的SortOrder排序，我建议采用两步方法。首先使用带窗口的Min()函数计算每组的最低SortOrder，然后将其输入Dense_Rank()，得到最终的Group值。

Select [Values], SortOrder, Dense_Rank() Over(Order By GroupOrder) AS [Group]
from (
select [Values], SortOrder, Min(SortOrder) Over(partition By [Values]) GroupOrder
from @table
) A
order by sortorder;

查看此数据库<>提琴