Postgres width_bucket:函数width_bucket(bigint，integer[])不存在

Question

我运行两个不同的查询。这个很好用

with t(n) as(
    values
           (1),
           (1),
           (1),
           (2),
           (10)
)
select
       width_bucket(n,array[1,3,6,15]) g,
       count(*)
from t
group by g
order by g

但是当我运行这个(真正的)查询时，它会报告桶上的错误。

with data as (
    select
           vendor_id,
           count(distinct pi.id) as cnt
    from payment_invoice_items as pii
    join payment_invoices as pi
        on pii.invoice_id = pi.id
        and pii.deleted_at isnull
    group by vendor_id
)
select
       width_bucket(data.cnt, array[1,10,20,30,40,100,200,400]) as grp,
       count(*)
from data
group by grp
order by grp 

data.cnt是整数数据的一列。为什么这个报告错误？

42883错误:函数width_bucket(bigint，integer[])不存在提示:没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。

谢谢

Answer 1

COUNT函数返回类型为BIGINT，而不是INTEGER。因此，该类型出现了错误：

42883错误:函数width_bucket(bigint，integer[])不存在提示:没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。

您需要将COUNT的结果显式地转换为INTEGER，以便在您的函数中使用它：

with data as (
    select
           vendor_id,
           count(distinct pi.id) as cnt
    from payment_invoice_items as pii
    join payment_invoices as pi
        on pii.invoice_id = pi.id
        and pii.deleted_at isnull
    group by vendor_id
)
select
       width_bucket(data.cnt::INTEGER, array[1,10,20,30,40,100,200,400]) as grp,
       count(*)
from data
group by grp
order by grp