PostgreSQL如何基于已有表去重列值创建新表

PostgreSQL 遵循严格的 SQL 标准，GROUP BY 子句必须覆盖 SELECT 列表中所有未被聚合函数包裹的字段，不会像关闭了ONLY_FULL_GROUP_BY模式的MySQL那样，隐式返回分组内随机行的非分组字段值。原语句只按customer_code分组，却直接查询另外三个非分组字段，逻辑上存在歧义（如果同一个客户对应多条不同市场信息的交易记录，数据库无法确定要返回哪条记录的值），因此会直接报错。

从需求描述看，业务逻辑上一个customer_code应该唯一对应一组market_code/market_name/zone值，这种场景有两种成熟写法：

方案1：DISTINCT 去重（最简洁，适合无脏数据场景） #

直接对需要的四个字段做去重，最后按customer_code升序排序即可，和预期逻辑完全匹配：

CREATE TABLE customers AS
SELECT DISTINCT customer_code, market_code, market_name, zone
FROM transactions
ORDER BY customer_code ASC;

方案2：GROUP BY + 明确聚合规则（适合存在脏数据可能的场景） #

如果担心表中存在同一个客户对应多组不同市场信息的脏数据，可以通过聚合函数明确取值规则，避免结果不确定。比如按交易时间取最新的关联信息：

CREATE TABLE customers AS
SELECT
  customer_code,
  (array_agg(market_code ORDER BY transaction_date DESC))[1] AS market_code,
  (array_agg(market_name ORDER BY transaction_date DESC))[1] AS market_name,
  (array_agg(zone ORDER BY transaction_date DESC))[1] AS zone
FROM transactions
GROUP BY customer_code
ORDER BY customer_code ASC;

说明：把语句里的transaction_date替换成你表中实际的交易时间字段即可；如果不需要按时间优先级取值，也可以去掉array_agg内部的ORDER BY子句，直接返回分组内任意一个匹配值。

建表前可以先执行以下语句，检查是否存在一个客户对应多组市场信息的脏数据，提前处理异常值：

SELECT customer_code, COUNT(DISTINCT market_code) AS diff_market_count
FROM transactions
GROUP BY customer_code
HAVING COUNT(DISTINCT market_code) > 1;

如果查询返回空结果，说明所有客户的市场关联信息都是唯一的，直接用方案1即可。

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