如何执行与“row_number() over (partition by [Col] order by [Col])”相反的操作？

Question

我正在尝试将数据表中的重复条目合并，并赋予它们一个新的编号。

以下是一个示例数据集（可运行副本）。

declare @tmpTable table
    (ID Varchar(1), 
     First varchar(4), 
     Last varchar(5), 
     Phone varchar(13),
     NonKeyField varchar(4))

insert into @tmpTable select 'A', 'John', 'Smith', '[phone]', 'ASDF'
insert into @tmpTable select 'B', 'John', 'Smith', '[phone]', 'GHJK'
insert into @tmpTable select 'C', 'Jane', 'Smith', '[phone]', 'QWER'
insert into @tmpTable select 'D', 'John', 'Smith', '[phone]', 'RTYU'
insert into @tmpTable select 'E', 'Bill', 'Blake', '[phone]', 'BVNM'
insert into @tmpTable select 'F', 'Bill', 'Blake', '[phone]', '%^&*'
insert into @tmpTable select 'G', 'John', 'Smith', '[phone]', '!#RF'

select row_number() over (partition by First, Last, Phone order by ID) NewIDNum, *  
from @tmpTable order by ID

现在它给我结果

NewIDNum             ID   First Last  Phone         NonKeyField
-------------------- ---- ----- ----- ------------- -----------
1                    A    John  Smith [phone] ASDF
2                    B    John  Smith [phone] GHJK
1                    C    Jane  Smith [phone] QWER
3                    D    John  Smith [phone] RTYU
1                    E    Bill  Blake [phone] BVNM
2                    F    Bill  Blake [phone] %^&*
4                    G    John  Smith [phone] !#RF

然而这与我想要的相反，NewIDNum 每次找到一个新的键组合时都会重置其计数器。我希望所有相同的组合都有相同的 ID。因此，如果它按照我想要的方式运行，我将获得以下结果。

NewIDNum             ID   First Last  Phone         NonKeyField
-------------------- ---- ----- ----- ------------- -----------
1                    A    John  Smith [phone] ASDF
1                    B    John  Smith [phone] GHJK
2                    C    Jane  Smith [phone] QWER
1                    D    John  Smith [phone] RTYU
3                    E    Bill  Blake [phone] BVNM
3                    F    Bill  Blake [phone] %^&*
1                    G    John  Smith [phone] !#RF

如何获得我想要的结果？

---

我在原帖中没有包含这个要求：如果在ID列上进行排序，假设所有新行都将具有更高的ID“值”，则我需要NewIDNum在现有行的后续运行中生成相同的数字，如果添加了更多行。

因此，如果在以后的某个日期执行以下操作：

insert into @tmpTable select 'H', 'John', 'Smith', '[phone]', '4321'
insert into @tmpTable select 'I', 'Jake', 'Jons', '[phone]', '1234'
insert into @tmpTable select 'J', 'John', 'Smith', '[phone]', '2345'

运行正确的查询会再次给出结果。

NewIDNum             ID   First Last  Phone         NonKeyField
-------------------- ---- ----- ----- ------------- -----------
1                    A    John  Smith [phone] ASDF
1                    B    John  Smith [phone] GHJK
2                    C    Jane  Smith [phone] QWER
1                    D    John  Smith [phone] RTYU
3                    E    Bill  Blake [phone] BVNM
3                    F    Bill  Blake [phone] %^&*
1                    G    John  Smith [phone] !#RF
1                    H    John  Smith [phone] 4321
4                    I    Jake  Jons  [phone] 1234
1                    J    John  Smith [phone] 2345

- Scott Chamberlain

Answer 1

你可以使用 dense_rank()：

dense_rank() over (order by First, Last, Phone) as NewIDNum

针对您的评论，您可以按照相同的(First, Last, Phone)组合的行组上旧的Id列的最小值排序：

select  *
from    (
        select  dense_rank() over (order by min_id) as new_id
        ,       *
        from    (
                select  min(id) over (
                            partition by First, Last, Phone) as min_id
                ,       *
                from    @tmpTable 
                ) as sub1
        ) as sub3
order by
        new_id

- Andomar

---

我能让它按照旧ID排序吗？如果添加更多行（假设在ID列上进行order by，新行将具有更高的ID“值”），则需要新ID为现有行生成相同的数字。 - Scott Chamberlain

针对你的更新：添加“Jake Jons”测试导致他的DR为2，而John和Jane Smith的DR都改变了1。 - Scott Chamberlain

更新以基于每个 (First, Last, Phone) 组的最低 id 分配密集排名。 - Andomar

我使用 group by 找到了 自己的方法，但是在将您的查询和我的查询并排运行时，您的执行时间更快%。感谢您的帮助！ - Scott Chamberlain

sub3是必要的吗？为什么不将最终的order by放在sub3内部并删除最外层（我甚至不会在我的实际查询中使用order by（它将提供“插入到”），但我很好奇）。 - Scott Chamberlain

Sub3 防止重复定义 new_id。如果您不需要按顺序排列，可以肯定地省略它。就像您可以通过简单的测试看到的那样 :) - Andomar

Answer 2

在 @Andomar 原本的回答基础上进行改进 -- 这将适用于您更新后的需求（尽管这不太可能有良好的可扩展性）.

select
    DENSE_RANK() over (ORDER BY IdRank, First, Last, Phone) AS NewIDNum,
    ID,
    First,
    Last,
    Phone,
    NonKeyField
from
(
    select
        MIN(ID) OVER (PARTITION BY First, Last, Phone) as IdRank,
        *
    from
        @tmpTable
) as x
order by
    ID;

- etliens

Answer 3

感谢Andomar的回答，作为一个起点，我自己解决了这个问题

select sub1.rn, tt.*
from @tmpTable tt
inner join (
    select row_number() over (order by min(ID)) as rn, first, last, phone
    from @tmpTable
    group by first, last, phone
    ) as sub1 on tt.first = sub1.first and tt.last = sub1.last and tt.phone = sub1.phone

这将产生

rn                   ID   First Last  Phone         NonKeyField
-------------------- ---- ----- ----- ------------- -----------
1                    A    John  Smith [phone] ASDF
1                    B    John  Smith [phone] GHJK
1                    D    John  Smith [phone] RTYU
1                    G    John  Smith [phone] !#RF
1                    H    John  Smith [phone] 4321
1                    J    John  Smith [phone] 2345
2                    C    Jane  Smith [phone] QWER
3                    E    Bill  Blake [phone] BVNM
3                    F    Bill  Blake [phone] %^&*
4                    I    Jake  Jons  [phone] 1234

---

查看SQL执行计划，Adnomar的答案在处理更大数据集时比我的更快。（当它们并排运行并勾选“包括实际执行计划”时，53％的执行时间与47％的执行时间相比。）

- Scott Chamberlain

---

你正在查询表变量两次，这不如@Andomar的解决方案优化。 - etliens

那就解释了我执行时间变慢的原因。感谢您的见解。 - Scott Chamberlain

Answer 4

这应该可以工作

select dense_rank() over (order by First, Last, Phone) NewIDNum, *  
from @tmpTable order by ID

- iruvar

---

请看我的评论，针对Andomar的回答。 - Scott Chamberlain