如何用SQL按分组取浏览量TOP3产品及查询异常排查

原有问题原因 #

• 分组仅返回1条记录：你使用了全局TOP 3语法，是从整个查询结果集取前3行，而非每个（国家、产品类型、价格组）分组各取前3。同时ORDER BY后跟的是每个分组内的行号，所有分组内行号为1的记录会排在最前列，TOP 3仅能取出全局行号前3的记录，自然每个分组最多返回1条。
• Rank字段序号不连续：原有DENSE_RANK() OVER(ORDER BY Latitude,Longitude)是在过滤后的结果集上计算的，部分经纬度对应的产品因未进入TOP3被筛除，序号自然会出现跳号。
• Rank2序号异常：原有Rank2是（国家、产品类型）全量产品下的全局排名，不是价格组内的排名，过滤后仅保留TOP3产品，它们的全局排名本来就不一定是1-3，就会出现5、4、1这类不符合预期的结果。

另外注意原有CTE存在笔误：SELECT子句中写的是p.Product_Type，但GROUP BY子句中是p.ProductType，字段名不统一会直接报错，需要先修正。 #

修正后完整SQL #

WITH Weights AS
(
    SELECT 
        s.ProductID,
        p.Country,
        p.ProductType,
        ROUND(p.Latitude, 3) AS Latitude,
        ROUND(p.Longitude, 3) AS Longitude,
        CASE 
            WHEN p.Price > 0 AND p.Price < 500 THEN 1
            WHEN p.Price >= 500  THEN 2 
        END AS PriceGroup,
        COUNT(s.ProductID) AS Weight
    FROM 
        Views s
    JOIN 
        Products p ON s.ProductID = p.ProductID
    WHERE 
        s.ViewDate >= CAST(GETDATE()-7 AS date) 
        AND p.Latitude IS NOT NULL 
        AND p.Longitude IS NOT NULL 
    GROUP BY 
        s.ProductID, p.Country, p.ProductType,
        CASE WHEN p.Price > 0 AND p.Price < 500 THEN 1
             WHEN p.Price >= 500 THEN 2 END,
        ROUND(p.Latitude, 3),
        ROUND(p.Longitude, 3)
),
RankedProducts AS (
    SELECT
        *,
        -- 每个（国家、产品类型、价格组）内的浏览量排名，用于筛选TOP3
        ROW_NUMBER() OVER (PARTITION BY Country, ProductType, PriceGroup ORDER BY Weight DESC) AS GroupRank,
        -- 每个（国家、产品类型）全局浏览量排名，用于展示对比排名
        DENSE_RANK() OVER (PARTITION BY Country, ProductType ORDER BY Weight DESC) AS GlobalTypeRank,
        -- 全量数据先计算经纬度连续序号，避免过滤后跳号
        DENSE_RANK() OVER (ORDER BY Latitude, Longitude) AS LocationRank
    FROM Weights
)
SELECT
    ProductID,
    Country,
    ProductType,
    PriceGroup,
    Latitude,
    Longitude,
    Weight,
    LocationRank AS Rank,
    GlobalTypeRank AS Rank2,
    -- 直接生成你需要的对比文案，可直接用于信息窗展示
    CONCAT('No.', GlobalTypeRank, ' of ', ProductType, ' in ', Country) AS Comparison
FROM RankedProducts
WHERE GroupRank <= 3
ORDER BY LocationRank, GroupRank;

额外优化说明 #

如果需要处理并列浏览量的场景，把筛选TOP3的ROW_NUMBER()换成DENSE_RANK()即可，就能返回所有并列TOP3的产品。

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