使用DENSE_RANK子查询时单RequestId产品排名异常问题排查

你遇到的核心问题是窗口函数的计算时机和过滤条件的位置导致的逻辑偏差，以下是具体分析和解决方法：

为什么直接查询的写法不成立？ #

你给出的第一个直接查询语句：

SELECT [Product], [StatusId], [ProductId],
       DENSE_RANK() OVER (PARTITION BY [Product] ORDER BY [RequestId] DESC) ranked_order 
FROM [Size] where ranked_order = 1

在标准SQL（比如SQL Server）中是语法错误的。因为SQL的执行顺序是：FROM → WHERE → 窗口函数计算 → SELECT，WHERE子句执行时，ranked_order这个窗口函数的结果还没生成，所以无法直接引用。你说结果均为1，大概率是测试环境的特殊情况或者笔误。

子查询出现异常的原因 #

当你在子查询中添加WHERE ProductId IN (1,2,3)这类过滤条件时，窗口函数DENSE_RANK()是基于过滤后的数据集计算的，而非整个表的数据。举个例子：

• 假设某Product在全表中有RequestId 10和20两条记录，其中RequestId 20对应的ProductId不在(1,2,3)范围内，会被过滤掉；
• 子查询中该Product的数据集只剩RequestId 10的记录，此时DENSE_RANK()应该为1，但如果你的测试数据中该Product在过滤后还有其他RequestId的记录（比如RequestId 15），那么按倒序排列后，15排1，10排2，就会出现排名为2的情况。

另外，产品缺失是因为：如果某Product的最大RequestId对应的记录被过滤条件排除，那么子查询计算出的ranked_order=1的记录不存在，最终结果就会丢失该Product。

正确的解决方案 #

要高效获取每个Product对应的最后一个RequestId的所有记录，推荐用先取最大RequestId再关联的方式，逻辑更清晰且性能更优：

方案1：先过滤再取最后请求（适合仅关注过滤后的数据） #

SELECT s.[Product], s.[StatusId], s.[ProductId], s.[RequestId]
FROM [Size] s
INNER JOIN (
    -- 先过滤ProductId，再计算每个Product的最大RequestId
    SELECT [Product], MAX([RequestId]) AS MaxRequestId
    FROM [Size]
    WHERE ProductId IN (1,2,3) -- 这里放你的过滤条件
    GROUP BY [Product]
) m ON s.[Product] = m.[Product] AND s.[RequestId] = m.MaxRequestId

方案2：先取全表最后请求再过滤（适合保留全表最后请求后再筛选） #

SELECT s.[Product], s.[StatusId], s.[ProductId], s.[RequestId]
FROM [Size] s
INNER JOIN (
    -- 先获取全表每个Product的最大RequestId
    SELECT [Product], MAX([RequestId]) AS MaxRequestId
    FROM [Size]
    GROUP BY [Product]
) m ON s.[Product] = m.[Product] AND s.[RequestId] = m.MaxRequestId
WHERE s.ProductId IN (1,2,3) -- 这里放你的过滤条件

如果一定要用窗口函数 #

如果坚持用DENSE_RANK()，需要确保窗口函数是基于全表计算，再应用过滤条件：

SELECT [Product], [StatusId], [ProductId], [RequestId], ranked_order
FROM (
    SELECT [Product], [StatusId], [ProductId], [RequestId],
           DENSE_RANK() OVER (PARTITION BY [Product] ORDER BY [RequestId] DESC) ranked_order 
    FROM [Size]
) pz 
WHERE ranked_order = 1 
  AND ProductId IN (1,2,3) -- 过滤条件放在外层，而非子查询内

这样窗口函数是基于全表数据计算排名，再筛选排名为1的记录，最后过滤ProductId，就不会出现排名异常或产品缺失的问题。

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