SQL计算同类型排除当前行的平均值 筛选销量超2倍该值的车型

方案1：窗口函数实现（支持MySQL 8.0+、PostgreSQL、SQL Server等主流新版数据库） #

核心逻辑是先通过窗口函数聚合拿到同类型总销量、总车型数，再扣除当前行的数值计算排除自身后的平均销量，性能比子查询更优：

SELECT model, sales
FROM (
    SELECT
        model,
        sales,
        type,
        -- 计算同类型排除当前车型后的平均销量
        (SUM(sales) OVER(PARTITION BY type) - sales) / (COUNT(*) OVER(PARTITION BY type) - 1) AS type_avg_without_current
    FROM car
) AS temp
WHERE sales > 2 * type_avg_without_current;

注意：如果某类车型只有1款的场景，会出现除以0的异常，可按需加CASE WHEN COUNT(*) OVER(PARTITION BY type) = 1 THEN 0 ELSE ... END处理边界情况

方案2：关联子查询实现（兼容所有数据库版本，包括低版本MySQL） #

通过子查询直接匹配同类型非当前车型的平均销量，逻辑更直观：

SELECT c1.model, c1.sales
FROM car c1
WHERE c1.sales > 2 * (
    SELECT AVG(c2.sales)
    FROM car c2
    WHERE c2.type = c1.type 
    -- 排除当前车型，model为车型唯一标识时可用，否则替换为表主键
    AND c2.model != c1.model
);

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