PostgreSQL执行含ROLLUP的GROUP BY语句时报函数不存在错误求助

核心原因：你使用的数据库（大概率为PostgreSQL、低版本MySQL等）不支持将ROLLUP当作独立函数拆分调用，ROLLUP是GROUP BY子句的修饰符而非普通聚合函数，你当前将多个rollup(xxx)用逗号分隔的写法会被数据库识别为调用不存在的自定义函数，因此触发报错。

解决方案 #

1. 修正GROUP BY语法（SQL标准写法，适配PostgreSQL 9.5+、MySQL 8.0+、Oracle等主流数据库） #

将所有需要做上卷统计的维度统一放到同一个ROLLUP参数内，不要拆分为多个独立的rollup()调用。如果你原本的逻辑是按「年→类型→规模分类→地点类型→地点」的层级做上卷，修正后的GROUP BY写法如下：

group by rollup(tmp.year, tmp.kind, tmp.sizecategory, tmp.placetype, tmp.place)

如果你的需求是(placetype,place)作为固定组合维度，和前面三个独立维度分别做上卷，需要多组独立ROLLUP结果，可通过UNION ALL拼接多个单ROLLUP的查询实现，符合你只能使用ROLLUP的要求：

-- 按年上卷
select coalesce(tmp.year, max(tmp.time_root)) as time_key,
coalesce(tmp.kind, max(tmp.kind_root)) as kind_key,
coalesce(tmp.sizecategory, max(tmp.sizecategory_root)) as sizecategory_key,
coalesce(tmp.placetype, tmp.place, max(tmp.takeplace_root)) as takeplace_key,
sum(tmp.pc1) as pc1,
sum(tmp.pc2) as pc2,
sum(tmp.sumt) as sumt
from outside.something_something as tmp group by rollup(tmp.year)
union all
-- 按类型上卷
select coalesce(tmp.year, max(tmp.time_root)) as time_key,
coalesce(tmp.kind, max(tmp.kind_root)) as kind_key,
coalesce(tmp.sizecategory, max(tmp.sizecategory_root)) as sizecategory_key,
coalesce(tmp.placetype, tmp.place, max(tmp.takeplace_root)) as takeplace_key,
sum(tmp.pc1) as pc1,
sum(tmp.pc2) as pc2,
sum(tmp.sumt) as sumt
from outside.something_something as tmp group by rollup(tmp.kind)
union all
-- 按规模分类上卷
select coalesce(tmp.year, max(tmp.time_root)) as time_key,
coalesce(tmp.kind, max(tmp.kind_root)) as kind_key,
coalesce(tmp.sizecategory, max(tmp.sizecategory_root)) as sizecategory_key,
coalesce(tmp.placetype, tmp.place, max(tmp.takeplace_root)) as takeplace_key,
sum(tmp.pc1) as pc1,
sum(tmp.pc2) as pc2,
sum(tmp.sumt) as sumt
from outside.something_something as tmp group by rollup(tmp.sizecategory)
union all
-- 按地点组合上卷
select coalesce(tmp.year, max(tmp.time_root)) as time_key,
coalesce(tmp.kind, max(tmp.kind_root)) as kind_key,
coalesce(tmp.sizecategory, max(tmp.sizecategory_root)) as sizecategory_key,
coalesce(tmp.placetype, tmp.place, max(tmp.takeplace_root)) as takeplace_key,
sum(tmp.pc1) as pc1,
sum(tmp.pc2) as pc2,
sum(tmp.sumt) as sumt
from outside.something_something as tmp group by rollup(tmp.placetype, tmp.place)

2. 低版本MySQL适配 #

如果你使用的是MySQL 5.7及更早版本，不支持标准ROLLUP()语法，需使用MySQL专属的WITH ROLLUP写法：

select coalesce(tmp.year, max(tmp.time_root)) as time_key,
coalesce(tmp.kind, max(tmp.kind_root)) as kind_key,
coalesce(tmp.sizecategory, max(tmp.sizecategory_root)) as sizecategory_key,
coalesce(tmp.placetype, tmp.place, max(tmp.takeplace_root)) as takeplace_key,
sum(tmp.pc1) as pc1,
sum(tmp.pc2) as pc2,
sum(tmp.sumt) as sumt
from outside.something_something as tmp
group by tmp.year, tmp.kind, tmp.sizecategory, tmp.placetype, tmp.place
with rollup

注意事项 #

你当前SELECT列表中使用coalesce替换ROLLUP生成的NULL值的逻辑，建议配合GROUPING()函数使用，避免误替换原始数据中本身存在的NULL值，示例写法：

select case when grouping(tmp.year) = 1 then max(tmp.time_root) else tmp.year end as time_key,
...

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