PostgreSQL中如何使用STRING_AGG实现发票项按价格汇总统计

嘿，我来帮你搞定这个发票项统计的问题！先把你的场景和需求理清楚，再给你高效的解决方案。

问题场景回顾 #

你有一个发票项表，表结构如下：

CREATE TABLE invoice_items (
  Id serial,
  serie character,
  invoice integer,
  price numeric
);

同一发票下存在重复价格的记录，比如示例数据：

1,'A',10,25.00
2,'A',10,25.00
3,'A',10,25.00
4,'A',10,15.00
5,'A',10,15.00
6,'A',10,15.00
7,'A',10,5.00
8,'A',10,5.00
9,'A',10,5.00
10,'A',10,5.00

你的需求是按serie+invoice组合汇总，统计每个价格对应的记录数量，输出格式要求类似：

之前用子查询实现但耗时，现在想改用STRING_AGG来优化性能。

高效解决方案：GROUP BY + STRING_AGG组合 #

核心思路是分两步走：先做基础统计，再用字符串聚合拼接结果，这样比多层子查询更高效，因为只需要扫描数据表两次（甚至数据库优化后可能一次）。

最终SQL代码 #

SELECT
  serie,
  invoice,
  STRING_AGG(CONCAT(price, ' ', price_count), ', ') AS breakdown
FROM (
  -- 第一步：统计每个发票下各价格的出现次数
  SELECT
    serie,
    invoice,
    price,
    COUNT(*) AS price_count
  FROM invoice_items
  GROUP BY serie, invoice, price
) AS price_stat
GROUP BY serie, invoice
ORDER BY serie, invoice;

代码解释 #

1. 内层子查询price_stat：这一步是最关键的基础统计，通过GROUP BY serie, invoice, price把相同发票、相同价格的记录分组，用COUNT(*)算出每个价格的出现次数。这一步的扫描是高效的，要是你给serie+invoice+price建了索引，速度还能再提一档。
2. 外层聚合：用STRING_AGG函数把内层得到的price和price_count拼接成"价格 数量"的格式，并用, 作为分隔符把所有组合连起来，最后再按serie和invoice分组，得到每个发票的最终汇总结果。

可选优化：处理价格精度 #

如果你的price字段存在小数位数不一致的情况，可以用ROUND(price, 2)来强制保留两位小数，确保输出格式统一：

SELECT
  serie,
  invoice,
  STRING_AGG(CONCAT(ROUND(price, 2), ' ', price_count), ', ') AS breakdown
FROM (
  SELECT
    serie,
    invoice,
    price,
    COUNT(*) AS price_count
  FROM invoice_items
  GROUP BY serie, invoice, price
) AS price_stat
GROUP BY serie, invoice
ORDER BY serie, invoice;

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