Snowflake中如何结合GROUP BY与OBJECT_AGG实现不同层级聚合

报错原因 #

你的SQL执行报错的核心原因有两点：

• GROUP BY 按city聚合后，cuisine字段既没有出现在GROUP BY子句中，也没有被包裹在聚合函数内，不符合SQL分组聚合的语法规则
• GROUP BY 的执行优先级高于窗口函数，分组后结果集已经不存在cuisine字段，后续窗口函数自然无法调用该字段触发报错。

实现逻辑 #

你需要的效果需要完成两层聚合：

1. 第一层先按城市 + 菜系分组，计算每个城市下对应菜系的最高米其林星级
2. 第二层再按城市单独分组，调用OBJECT_AGG将同城市的菜系和对应最高星级拼接为映射对象

可运行SQL代码 #

写法1：CTE实现（可读性更高） #

WITH cuisine_max_star AS (
    -- 第一层聚合：计算每个城市各菜系的最高星级
    SELECT 
        city,
        cuisine,
        MAX(michelin_stars) AS max_star
    FROM top_restaurants
    GROUP BY city, cuisine
)
-- 第二层聚合：按城市分组生成映射对象
SELECT 
    city,
    OBJECT_AGG(cuisine, max_star::variant) AS 菜系最高星级映射对象
FROM cuisine_max_star
GROUP BY city;

写法2：嵌套子查询实现 #

SELECT 
    city,
    OBJECT_AGG(cuisine, max_star::variant) AS 菜系最高星级映射对象
FROM (
    SELECT 
        city,
        cuisine,
        MAX(michelin_stars) AS max_star
    FROM top_restaurants
    GROUP BY city, cuisine
) t
GROUP BY city;

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