Metabase中{{breakdown}}过滤器触发SQL GROUP BY子句报错如何解决

报错原因 #

你遇到的PostgreSQL报错：

column "analytics_moduleattemptanalyticsview.created" must appear in the GROUP BY clause or be used in an aggregate function Position: 27

核心原因是Metabase在参数化处理重复出现的{{breakdown}}变量时，数据库无法判定SELECT子句和GROUP BY、ORDER BY子句中的date_trunc({{breakdown}}, ...)表达式完全等价，因此要求原始created字段必须加入GROUP BY逻辑。你直接替换{{breakdown}}为'year'时所有位置的表达式都是固定值，不存在解析歧义，因此可以正常运行。

修复步骤 #

1. 简化SQL逻辑，所有GROUP BY、ORDER BY直接复用SELECT中定义的字段别名，避免重复写带{{breakdown}}的date_trunc表达式，从根源消除解析歧义
2. 确认{{breakdown}}过滤器配置：选择「文本」类型的可选值过滤器，预设三个选项为year、quarter、month即可，不需要给选项值加额外引号

修正后完整SQL #

SELECT 
    date_trunc({{breakdown}}, CAST("public"."analytics_moduleattemptanalyticsview"."created" AS timestamp)) AS "created", 
    "public"."analytics_moduleattemptanalyticsview"."is_complete" AS "is_complete", 
    count(*) AS "count"
FROM "public"."analytics_moduleattemptanalyticsview"
LEFT JOIN "public"."auth_user" "User" ON "public"."analytics_moduleattemptanalyticsview"."user_id" = "User"."id" 
LEFT JOIN "public"."companies_company" "Companies Company" ON ((floor(("public"."analytics_moduleattemptanalyticsview"."user_company_id" - 0.0) / 2000) * 2000) + 0.0) = "Companies Company"."id"
WHERE "User"."is_staff" = FALSE
[[and {{company_id}} and {{company_type}} and {{platform}}]]
GROUP BY "created", "is_complete"
ORDER BY "created" ASC, "is_complete" ASC

修改后直接切换{{breakdown}}的三个可选值，即可正常生成年、季度、月维度的统计报表，不会再触发GROUP BY相关报错。

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