SQL Server 2017中计算非空Rank平均值并排名的实现问题

方法一：利用UNPIVOT+AVG函数（推荐） #

SQL Server的AVG()聚合函数会自动忽略NULL值，我们可以通过UNPIVOT将多列排名转为行数据，再按姓名分组计算平均，最后生成最终排名：

;WITH RankAvg AS (
    SELECT 
        Name,
        Rank1, Rank2, Rank3, Rank4,
        AVG(CAST(RankVal AS DECIMAL(5,2))) OVER (PARTITION BY Name) AS AvgRank
    FROM mytable
    UNPIVOT (
        RankVal FOR RankCols IN (Rank1, Rank2, Rank3, Rank4)
    ) AS unpvt
)
SELECT DISTINCT
    Name, Rank1, Rank2, Rank3, Rank4,
    ROUND(AvgRank, 2) AS AvgRank,
    ROW_NUMBER() OVER (ORDER BY AvgRank) AS FinalRank
FROM RankAvg;

方法二：手动计算非NULL列数与总和 #

如果不想用UNPIVOT，可以逐行统计非NULL的排名列数量，再计算总和除以该数量得到平均：

;WITH cte AS (
    SELECT 
        *,
        -- 统计当前行非NULL的排名列数量
        NonNullRankCount = IIF(Rank1 IS NOT NULL, 1, 0) + 
                           IIF(Rank2 IS NOT NULL, 1, 0) + 
                           IIF(Rank3 IS NOT NULL, 1, 0) + 
                           IIF(Rank4 IS NOT NULL, 1, 0),
        -- 计算当前行非NULL排名列的总和
        RankTotal = ISNULL(Rank1, 0) + ISNULL(Rank2, 0) + ISNULL(Rank3, 0) + ISNULL(Rank4, 0)
    FROM mytable
)
SELECT 
    Name, Rank1, Rank2, Rank3, Rank4,
    -- 保留两位小数计算平均排名
    ROUND(CAST(RankTotal AS DECIMAL(5,2)) / NonNullRankCount, 2) AS AvgRank,
    -- 按平均排名升序生成最终排名
    ROW_NUMBER() OVER (ORDER BY CAST(RankTotal AS DECIMAL(5,2)) / NonNullRankCount) AS FinalRank
FROM cte;

说明 #

• 原查询中使用全局变量@NumOfRankedBy的思路不可行，因为不同行的非NULL排名列数量可能不一致（比如某行Rank1有值，其他行没有），必须逐行计算有效列数。
• 两种方法都能得到你预期的结果，方法一更适合排名列较多的场景，代码扩展性更好；方法二逻辑更直观，适合列数较少的情况。

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