在业务数据处理中，我们经常会遇到需要按特定维度分组，再找出每组中异常偏大的最大值的需求，比如按地区统计订单金额时找出远超当地平均水平的异常订单，或者按班级统计考试成绩时找出远超班级平均分的异常高分。这类需求可以通过SQL的聚合函数、GROUP BY分组语句以及HAVING筛选条件组合实现。
基础分组统计最大值的方法
首先我们需要掌握最基础的分组统计最大值逻辑，使用GROUP BY语句按指定字段分组，再配合MAX()聚合函数获取每组的最大值，这是后续筛选异常值的基础。
假设我们有一张订单表order_info，包含以下字段：
order_id：订单IDregion：地区名称order_amount：订单金额
如果我们需要统计每个地区的最高订单金额，基础SQL语句如下：
-- 按地区分组统计最高订单金额
SELECT
region,
MAX(order_amount) AS max_order_amount
FROM
order_info
GROUP BY
region;
异常最大值的判定规则
要筛选异常最大值，首先需要明确异常值的判定标准，常见的判定规则有两种：
规则1：超过分组平均值的N倍
比如设定当最大值超过该组平均值的2倍时，判定为异常最大值，这种方法适用于数据分布相对均匀的场景。
规则2：超过固定阈值
比如设定订单金额超过10000就判定为异常，这种方法适用于业务有明确阈值标准的场景。
以下我们以第一种规则为例，演示如何筛选异常最大值。
结合聚合与筛选的完整实现
要同时获取分组的平均值和最大值，并且筛选出最大值超过平均值2倍的组，我们需要先通过子查询或者CTE计算出每个地区的平均订单金额和最高订单金额，再进行条件筛选。
方法1：使用子查询实现
首先通过子查询按地区分组，同时计算出每个地区的平均订单金额和最高订单金额，然后在外部查询中添加筛选条件。
-- 子查询计算分组平均值和最大值，外部查询筛选异常值
SELECT
region,
avg_order_amount,
max_order_amount
FROM
(
-- 内层查询：按地区分组，计算平均订单金额和最高订单金额
SELECT
region,
AVG(order_amount) AS avg_order_amount,
MAX(order_amount) AS max_order_amount
FROM
order_info
GROUP BY
region
) AS temp
-- 筛选条件：最大值超过平均值的2倍
WHERE
max_order_amount > avg_order_amount * 2;
方法2：使用CTE（公用表表达式）实现
CTE的写法逻辑更清晰，适合复杂的统计逻辑，实现效果和子查询一致：
-- 定义CTE计算分组统计值
WITH region_stats AS (
SELECT
region,
AVG(order_amount) AS avg_order_amount,
MAX(order_amount) AS max_order_amount
FROM
order_info
GROUP BY
region
)
-- 从CTE中筛选异常最大值
SELECT
region,
avg_order_amount,
max_order_amount
FROM
region_stats
WHERE
max_order_amount > avg_order_amount * 2;
获取异常最大值对应的完整记录
上面的方法只能获取到分组的统计值，如果我们需要获取异常最大值对应的完整订单记录，可以结合窗口函数实现。使用ROW_NUMBER()窗口函数按地区分区，按订单金额降序排序，找出每个地区的金额最高的记录，再结合分组统计的平均值进行筛选。
-- 使用窗口函数获取每个地区最高订单的完整记录，再筛选异常值
WITH order_rank AS (
-- 给每个地区的订单按金额降序排名，同时计算地区平均金额
SELECT
order_id,
region,
order_amount,
AVG(order_amount) OVER (PARTITION BY region) AS region_avg_amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY order_amount DESC) AS rn
FROM
order_info
)
SELECT
order_id,
region,
order_amount,
region_avg_amount
FROM
order_rank
-- 取每个地区排名第一的记录，且金额超过地区平均值的2倍
WHERE
rn = 1
AND order_amount > region_avg_amount * 2;
不同判定规则的适配
如果业务使用的是固定阈值判定异常，只需要把筛选条件修改为对应阈值即可，比如订单金额超过10000判定为异常：
-- 固定阈值筛选异常最大值
WITH region_stats AS (
SELECT
region,
AVG(order_amount) AS avg_order_amount,
MAX(order_amount) AS max_order_amount
FROM
order_info
GROUP BY
region
)
SELECT
region,
avg_order_amount,
max_order_amount
FROM
region_stats
-- 最大值超过10000判定为异常
WHERE
max_order_amount > 10000;
注意事项
- 使用
AVG()函数时，如果分组内只有一条记录，平均值等于该记录值，此时最大值不会超过平均值的2倍，这类分组会被自动过滤，符合预期。 - 如果数据中存在NULL值，
MAX()和AVG()函数会自动忽略NULL值，不需要额外处理，除非业务需要保留NULL值的统计逻辑。 - 窗口函数的方法适用于需要获取完整记录的场景，子查询和CTE的方法适用于只需要统计值的场景，可根据实际需求选择。