SQL Server 2016中基于权重优先级与频次的分组取数优化问询

原始数据表 #

筛选规则 #

• 按Weight1、Weight2、Weight3的优先级依次排序，权重值越高的记录越优先保留
• 若同一GroupFrom下的记录在三个权重上均并列，则保留GroupTo出现频次更高的对应记录

预期输出 #

当前实现方案 #

采用分步临时表+多次dense_rank的方式，先筛选Weight1最高的记录，再从中筛选Weight2最高的，最后处理频次平局：

select * 
into #Staging1
from
(
select *, dense_rank() over (partition by GroupFrom order by Weight1 desc) as rank from #temp
) t
where rank = 1

select * 
into #Staging2
from
(
select *, dense_rank() over (partition by GroupFrom order by Weight2 desc) as rank2 from #Staging1
) t
where rank2 = 1

-- 后续再处理频次平局的逻辑

高效优化方案（SQL Server 2016适用） #

可以通过一次窗口函数计算完成所有筛选逻辑，避免多次临时表的IO开销，核心思路是：

1. 先计算每个GroupFrom下各GroupTo的出现频次
2. 将权重优先级（Weight1→Weight2→Weight3）和频次作为排序条件，一次性生成排名
3. 筛选排名为1的记录即可

具体代码：

WITH GroupToCounts AS (
    -- 计算每个GroupFrom下各GroupTo的出现次数
    SELECT 
        GroupFrom, 
        GroupTo, 
        COUNT(*) AS ToCount
    FROM #temp
    GROUP BY GroupFrom, GroupTo
)
SELECT t.ID, t.GroupFrom, t.GroupTo
FROM (
    SELECT 
        t.*,
        gc.ToCount,
        -- 按权重优先级+频次排序生成排名
        DENSE_RANK() OVER (
            PARTITION BY t.GroupFrom 
            ORDER BY t.Weight1 DESC, t.Weight2 DESC, t.Weight3 DESC, gc.ToCount DESC
        ) AS RankNum
    FROM #temp t
    JOIN GroupToCounts gc 
        ON t.GroupFrom = gc.GroupFrom AND t.GroupTo = gc.GroupTo
) t
WHERE t.RankNum = 1;

方案优势 #

• 仅需一次扫描原始表+一次分组统计，相比分步临时表减少了多次数据写入和读取的开销
• 逻辑集中，可读性更强，便于维护
• 完全适配SQL Server 2016版本的语法特性

内容的提问来源于stack exchange，提问作者woiya