SQL按CustomerID分组时计算每年Field最大值的平均值求助

你当前的写法存在语法错误，原因是不支持在聚合函数中直接嵌套窗口函数，且按CustomerID分组的查询层级无法直接计算全局年度级别的Field最大值均值，以下是3种可直接运行的实现方案：

方案1：CTE分层写法（逻辑最清晰，适合新手） #

分层计算各中间结果，可读性最高，方便排查问题，兼容所有支持CTE的主流数据库（SQL Server、PostgreSQL、MySQL 8.0+等）：

WITH annual_field_max AS (
    -- 计算每一年的Field最大值
    SELECT [Year], MAX([Field]) AS year_max
    FROM market
    WHERE [Year] IN (2019,2020,2021)
    GROUP BY [Year]
),
avg_annual_max AS (
    -- 对所有年度最大值求平均值
    SELECT AVG(year_max) AS Field_avr
    FROM annual_field_max
),
customer_stats AS (
    -- 计算客户维度的业务指标
    SELECT
        [CustomerID],
        MAX([CustomerName]) AS CustomerName,
        SUM(CASE WHEN [Year] = 2021 THEN [SalesValue_USD] END) AS Sales_USD_2021,
        SUM([SalesValue_USD]) AS Sales_USD_total
    FROM market
    WHERE [Year] IN (2019,2020,2021)
    GROUP BY [CustomerID]
)
-- 关联得到最终结果
SELECT cs.*, aam.Field_avr
FROM customer_stats cs
CROSS JOIN avg_annual_max aam

方案2：子查询嵌套写法（最简洁） #

不需要分层定义CTE，直接在SELECT子句中嵌套子查询计算均值：

SELECT
    t0.[CustomerID],
    MAX(t0.[CustomerName]) AS CustomerName,
    SUM(CASE WHEN t0.[Year] = 2021 THEN t0.[SalesValue_USD] END) AS Sales_USD_2021,
    SUM(t0.[SalesValue_USD]) AS Sales_USD_total,
    (
        SELECT AVG(year_max) 
        FROM (
            SELECT MAX([Field]) AS year_max 
            FROM market 
            WHERE [Year] IN (2019,2020,2021) 
            GROUP BY [Year]
        ) t
    ) AS Field_avr
FROM market t0
WHERE [Year] IN (2019,2020,2021)
GROUP BY t0.[CustomerID]

方案3：窗口函数写法（执行效率最高） #

只扫描一次原表，性能最优：

SELECT
    [CustomerID],
    MAX([CustomerName]) AS CustomerName,
    SUM(CASE WHEN [Year] = 2021 THEN [SalesValue_USD] END) AS Sales_USD_2021,
    SUM([SalesValue_USD]) AS Sales_USD_total,
    AVG(DISTINCT year_max_field) AS Field_avr
FROM (
    SELECT 
        *,
        MAX([Field]) OVER (PARTITION BY [Year]) AS year_max_field
    FROM market
    WHERE [Year] IN (2019,2020,2021)
) t
GROUP BY [CustomerID]

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