如何在Snowflake中先按分组列取最大值，再按另一列分组算平均值

核心查询语句 #

WITH max_amount_per_app AS (
    SELECT 
        FEATURE,
        APPLICATION_ID,
        MAX(AMOUNT) AS MAX_AMOUNT
    FROM your_table_name
    GROUP BY FEATURE, APPLICATION_ID
)
SELECT 
    FEATURE,
    AVG(MAX_AMOUNT) AS AVG_AMOUNT
FROM max_amount_per_app
GROUP BY FEATURE
ORDER BY FEATURE;

语句说明 #

1. 提取每个应用ID的最高金额
   用CTE max_amount_per_app 按 FEATURE 和 APPLICATION_ID 分组，通过MAX(AMOUNT)取出每个组合下的最高金额，确保同一APPLICATION_ID的多条记录只保留代表最终总计的最高值。

2. 计算各Feature的平均值
   基于第一步的结果，再按FEATURE分组，用AVG(MAX_AMOUNT)计算每个Feature对应的平均金额。

测试验证 #

用你提供的测试数据可以直接验证效果，将查询改成带测试数据的CTE即可运行：

WITH test_data AS (
    SELECT * FROM VALUES
        (1, 'A', 234, 0),
        (2, 'A', 234, 40),
        (3, 'A', 235, 50),
        (4, 'A', 236, 60),
        (5, 'B', 237, 70),
        (6, 'B', 237, 90),
        (7, 'B', 238, 80),
        (8, 'B', 238, 100)
    AS t(ID, FEATURE, APPLICATION_ID, AMOUNT)
),
max_amount_per_app AS (
    SELECT 
        FEATURE,
        APPLICATION_ID,
        MAX(AMOUNT) AS MAX_AMOUNT
    FROM test_data
    GROUP BY FEATURE, APPLICATION_ID
)
SELECT 
    FEATURE,
    AVG(MAX_AMOUNT) AS AVG_AMOUNT
FROM max_amount_per_app
GROUP BY FEATURE
ORDER BY FEATURE;

运行后得到的结果与你期望的一致：

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