PostgreSQL按客户分组SUM求和计算图片总大小方法

核心问题出在pg_size_pretty()函数返回的是带存储单位的格式化文本，本身不支持直接做数值求和。正确的实现逻辑是先基于原始字节数值完成聚合求和，最后再做可读化格式转换，从根源上避免文本类型无法计算的问题。

可直接运行的聚合查询代码 #

SELECT 
  c.name AS customer, 
  i.customer_id,
  COUNT(i.id) AS matched_image_count, -- 可选：统计每个客户符合筛选条件的图片总数
  pg_size_pretty(SUM((i.manifest ->> 'size')::numeric)) AS total_image_size
FROM   public.images i
JOIN   public.customers c ON c.id = i.customer_id
WHERE  i.raw_upload_complete = 'true' 
  AND captured_at > date_trunc('day', now()) - interval '2 months'
GROUP BY c.name, i.customer_id
ORDER  BY customer ASC

以上述示例数据为例，该查询的输出结果为：

关键注意事项 #

• 所有数值类计算（求和、平均值、最大值等）必须基于从JSON字段中取出的原始字节数值完成，不要提前调用pg_size_pretty()转成人类可读文本后再尝试反向解析计算，既容易因为动态单位（KB/MB/GB自动切换）出现换算错误，也会增加不必要的计算开销。
• 做客户维度聚合时，SELECT子句中仅保留客户维度的属性字段（客户名、客户ID）和聚合计算结果，原查询中的单图拍摄时间、单图名称、上传状态等明细级字段不能出现在聚合查询的SELECT中，否则无法实现每个客户对应一行的汇总效果。
• 如果后续需要在Google Data Studio等BI工具中做二次聚合，建议直接将原始字节值（(i.manifest ->> 'size')::numeric AS size_bytes）作为字段输出，不要提前做单位格式化，BI工具中可以直接配置字节到MB/GB的自动换算规则，不会再出现字段被识别为文本无法计算的问题。

不推荐的兜底方案：如果受场景限制必须处理已经生成的xxx MB格式文本，且能100%确认所有值的单位统一为MB，可以通过截取文本数字部分的方式求和：SUM(SPLIT_PART(image_size, ' ', 1)::numeric)。一旦字段中出现KB、GB等其他单位，该方法的计算结果会完全错误，禁止在生产环境使用。

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