SQL中如何剔除分组后销售数据的前3%高值？

你遇到的错误是因为窗口函数（如PERCENT_RANK()）仅允许出现在SELECT或ORDER BY子句中，不能直接用于HAVING。要实现剔除前3%高值分组的需求，需要先完成分组统计并计算排名，再在外层查询中过滤目标数据。

方法一：使用PERCENT_RANK()（适配降序排序的筛选逻辑） #

由于原查询按ColumnC降序排序，PERCENT_RANK()会将数值最大的分组标记为0，最小的标记为1。要剔除前3%的高值分组，需筛选rank_pct > 0.03（即保留排名在后97%的分组）：

SELECT "ColumnA", "ColumnC"
FROM (
    SELECT
      prod."ColumnA" AS "ColumnA",
      COUNT(items."ColumnB") AS "ColumnC",
      PERCENT_RANK() OVER (ORDER BY COUNT(items."ColumnB") DESC) AS "rank_pct"
    FROM "Table1" AS items
    LEFT JOIN "Table2" AS prod ON items."ColumnD" = prod."ColumnE"
    LEFT JOIN "Table3" AS sales ON items."ColumnF" = sales."ColumnG"
    WHERE sales."ColumnH" >= DATE_SUB(CURDATE(), INTERVAL 150 DAY)
      AND prod."ColumnA" NOT IN ('Value1', 'Value2', 'Value3')
    GROUP BY prod."ColumnA"
) AS ranked_data
WHERE "rank_pct" > 0.03
ORDER BY "ColumnC" DESC;

方法二：使用ROW_NUMBER()结合总行数（精准控制剔除数量） #

如果需要严格按实际行数剔除前3%分组，可先统计分组总数，再用行号筛选：

SELECT "ColumnA", "ColumnC"
FROM (
    SELECT
      prod."ColumnA" AS "ColumnA",
      COUNT(items."ColumnB") AS "ColumnC",
      ROW_NUMBER() OVER (ORDER BY COUNT(items."ColumnB") DESC) AS "row_num",
      (SELECT COUNT(*) FROM (
          SELECT 1
          FROM "Table1" AS items
          LEFT JOIN "Table2" AS prod ON items."ColumnD" = prod."ColumnE"
          LEFT JOIN "Table3" AS sales ON items."ColumnF" = sales."ColumnG"
          WHERE sales."ColumnH" >= DATE_SUB(CURDATE(), INTERVAL 150 DAY)
            AND prod."ColumnA" NOT IN ('Value1', 'Value2', 'Value3')
          GROUP BY prod."ColumnA"
      ) AS total_groups) AS "total_count"
    FROM "Table1" AS items
    LEFT JOIN "Table2" AS prod ON items."ColumnD" = prod."ColumnE"
    LEFT JOIN "Table3" AS sales ON items."ColumnF" = sales."ColumnG"
    WHERE sales."ColumnH" >= DATE_SUB(CURDATE(), INTERVAL 150 DAY)
      AND prod."ColumnA" NOT IN ('Value1', 'Value2', 'Value3')
    GROUP BY prod."ColumnA"
) AS ranked_data
WHERE "row_num" > CEIL("total_count" * 0.03)
ORDER BY "ColumnC" DESC;

方法三：使用CTE简化写法（适用于MySQL 8.0+、PostgreSQL等支持CTE的数据库） #

CTE能避免重复编写分组逻辑，让代码结构更清晰：

WITH grouped_data AS (
    SELECT
      prod."ColumnA" AS "ColumnA",
      COUNT(items."ColumnB") AS "ColumnC"
    FROM "Table1" AS items
    LEFT JOIN "Table2" AS prod ON items."ColumnD" = prod."ColumnE"
    LEFT JOIN "Table3" AS sales ON items."ColumnF" = sales."ColumnG"
    WHERE sales."ColumnH" >= DATE_SUB(CURDATE(), INTERVAL 150 DAY)
      AND prod."ColumnA" NOT IN ('Value1', 'Value2', 'Value3')
    GROUP BY prod."ColumnA"
),
ranked_data AS (
    SELECT
      *,
      PERCENT_RANK() OVER (ORDER BY "ColumnC" DESC) AS "rank_pct",
      ROW_NUMBER() OVER (ORDER BY "ColumnC" DESC) AS "row_num",
      (SELECT COUNT(*) FROM grouped_data) AS "total_count"
    FROM grouped_data
)
SELECT "ColumnA", "ColumnC"
FROM ranked_data
-- 二选一：使用PERCENT_RANK或ROW_NUMBER筛选
-- WHERE "rank_pct" > 0.03
WHERE "row_num" > CEIL("total_count" * 0.03)
ORDER BY "ColumnC" DESC;

补充优化说明 #

• 将原查询中重复的prod."ColumnA" != 'ValueX'条件改为NOT IN ('Value1', 'Value2', 'Value3')，代码更简洁且逻辑一致。
• 若分组总数较少（如不足34组），PERCENT_RANK()的0.03阈值可能无法筛选出数据，此时ROW_NUMBER()结合CEIL()的方式更可靠。

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