SQL Server : How to de-duplicate on two columns/conditions?

Question

我有一个数据库表，其中包含重复的地址行。对于重复的地址行，我想保留一列中具有最高值的一行，并且在仍然存在重复的情况下，只需选择一个(没关系，没有办法知道哪一个更好，因为这些是任意字符串值，最大/最小应该足够了)。例如，假设我们有下表，其中包含两个地址，其中一个地址重复四次:

ADDR1       CITY    STATE   APN     CONFIDENCE
1 Main St.  Dover   IL      apn-1   7
1 Main St.  Dover   IL      apn-2   8
1 Main St.  Dover   IL      apn-3   5
1 Main St.  Dover   IL      apn-4   8
7 Ajax Rd.  Dover   IL      apn-8   9

我需要在地址列上使用分组依据，将重复行过滤为仅那些具有最高置信度分数的行，然后从该“子选择”中选择未分组列的最大值或最小值(本例中为 APN)以获得以下所需结果:

ADDR1       CITY    STATE   APN     CONFIDENCE
1 Main St.  Dover   IL      apn-4   8
7 Ajax Rd.  Dover   IL      apn-8   9

我想出了一个有效的 SQL 查询:

select sub.ADDR1, sub.CITY, sub.STATE, MAX(APN), sub.CONFIDENCE
from example_db.example_table as table1
left join (
    select ADDR1,
           CITY,
           STATE,
           MAX(CONFIDENCE) as CONFIDENCE
    from example_db.example_table
    group by ADDR1,
             CITY,
             STATE
) as sub
on (table1.ADDR1 = sub.ADDR1)
and (table1.CITY = sub.CITY)
and (table1.STATE = sub.STATE)
group by sub.ADDR1, sub.CITY, sub.STATE, sub.CONFIDENCE

上面是一个简化的示例，用于演示我的用例，但是我将运行此 SQL 代码的数据很大(约 130M 行)，并且 SQL Server 实例很小 - 如果出现以下情况，我不想使其过载我可以优化上述内容或使用另一种更有效的方法。

Answer 1

您可以使用窗口函数:

select *
from (
    select t.*, 
        row_number() over(partition by addr1, city, state order by confidence desc) rn
    from mytable t
) t
where rn = 1