SQL错误“Analytic function cannot be an argument of another analytic function”含义问询

Analytic function cannot be an argument of another analytic function 这个错误的核心意思是：SQL不允许将一个窗口（分析）函数直接作为另一个窗口函数的参数。你尝试把AVG(quantity) OVER (PARTITION BY order_id)这个窗口函数的结果，直接传入MAX() OVER()作为计算对象，这种嵌套调用不符合SQL语法规则。

你的需求是找出「订单内商品最大数量 > 所有订单平均商品数量的最大值」的订单，以下是两种可行的实现方式：

方式一：基于GROUP BY的分步计算 #

先统计每个订单的最大商品数、平均商品数，再单独算出所有订单平均商品数的最大值，最后关联筛选：

WITH order_stats AS (
    SELECT
        order_id,
        MAX(quantity) AS max_qty,
        AVG(quantity) AS avg_qty
    FROM ordersdetails
    GROUP BY order_id
),
global_avg_max AS (
    SELECT MAX(avg_qty) AS max_avg_qty
    FROM order_stats
)
SELECT os.order_id
FROM order_stats os
CROSS JOIN global_avg_max gam
WHERE os.max_qty > gam.max_avg_qty
ORDER BY os.order_id;

方式二：分层窗口函数计算 #

通过两层子查询拆分窗口函数的计算逻辑，避免直接嵌套：

SELECT DISTINCT order_id
FROM (
    SELECT
        order_id,
        MAX(quantity) OVER (PARTITION BY order_id) AS max_qty,
        avg_qty,
        MAX(avg_qty) OVER () AS max_avg_qty
    FROM (
        -- 先计算每个订单的平均商品数，生成独立字段
        SELECT
            order_id,
            quantity,
            AVG(quantity) OVER (PARTITION BY order_id) AS avg_qty
        FROM ordersdetails
    ) AS order_avg
) AS order_stats
WHERE max_qty > max_avg_qty
ORDER BY order_id;

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