如何在Rails中通过PostgreSQL转换重量字段后按买家ID分组求和

问题根源 #

直接用CAST(weight_lb AS INTEGER)触发报错，是因为weight_lb字段包含"lb"这类非数字字符，PostgreSQL无法直接将带非数字的字符串转成数值类型，必须先清理字符串中的非数字部分。

分步解决方案 #

1. 清理字符串提取数字 #

用PostgreSQL的regexp_replace函数移除所有非数字、非小数点的字符：
regexp_replace(weight_lb, '[^0-9.]', '', 'g')
比如把"12.3lb"转成"12.3"，"5lb"转成"5"。

2. 转换为整数 #

将清理后的字符串转成DECIMAL，再用FLOOR()取整数部分（只保留小数点前的数字），最后转成INTEGER类型：
FLOOR(CAST(regexp_replace(weight_lb, '[^0-9.]', '', 'g') AS DECIMAL))::INTEGER

3. Rails中实现分组求和并格式化结果 #

直接用ActiveRecord查询即可得到你要的哈希格式：

result = Order.select("buyer_id, SUM(FLOOR(CAST(regexp_replace(weight_lb, '[^0-9.]', '', 'g') AS DECIMAL))::INTEGER) AS total_weight")
              .group(:buyer_id)
              .pluck(:buyer_id, :total_weight)
              .to_h

执行后会返回类似{1: 65, 2: 190}的结果。

性能优化方案（可选） #

如果这个查询会频繁使用，建议添加生成列提前计算好整数重量，避免每次查询都做字符串处理：

-- 添加生成列
ALTER TABLE orders ADD COLUMN weight_int INTEGER GENERATED ALWAYS AS (
  FLOOR(CAST(regexp_replace(weight_lb, '[^0-9.]', '', 'g') AS DECIMAL))::INTEGER
) STORED;

-- 创建联合索引提升分组求和性能
CREATE INDEX idx_orders_buyer_weight_int ON orders(buyer_id, weight_int);

之后查询可以简化为：

result = Order.select("buyer_id, SUM(weight_int) AS total_weight")
              .group(:buyer_id)
              .pluck(:buyer_id, :total_weight)
              .to_h

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