Datalemur SQL查询报错：round(double precision, integer)函数不存在求排查

报错详情 #

报错信息：function round(double precision, integer) does not exist (LINE: 4)

提交的原始代码 #

SELECT
    EXTRACT(MONTH FROM submit_date) AS mth,
    product_id                      AS product,
    round((AVG(stars)),
          2)                        AS avg_stars
FROM
    reviews
GROUP BY
    EXTRACT(MONTH FROM submit_date),
    product_id
ORDER BY
    mth,
    product;

错误原因 #

Datalemur基于PostgreSQL环境，PostgreSQL的ROUND函数没有提供round(double precision, integer)的重载版本。而AVG(stars)计算后返回的是double precision类型，直接传入ROUND就会触发这个报错。

修复方案 #

需要将AVG(stars)的结果显式转换为numeric类型，再使用ROUND函数。修复后的代码如下：

SELECT
    EXTRACT(MONTH FROM submit_date) AS mth,
    product_id                      AS product,
    round(AVG(stars)::numeric, 2)   AS avg_stars
FROM
    reviews
GROUP BY
    EXTRACT(MONTH FROM submit_date),
    product_id
ORDER BY
    mth,
    product;

也可以用CAST(AVG(stars) AS numeric)替代::numeric，两种写法效果完全一致。

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