PostgreSQL中添加含GROUP BY和COUNT的生成列求助

你的需求是为每个门店显示其苹果的总数量，但PostgreSQL的标准**生成列（Generated Column）**无法直接实现这个功能——因为生成列的表达式只能依赖当前行的数据，而你需要的是基于整个门店分组的聚合计算（涉及其他行的数据）。下面提供两种适合新手的可行方案：

方案一：创建视图（推荐，简单易维护） #

视图会实时计算每个门店的苹果数量，不需要手动维护数据，适合大多数场景：

-- 假设你的表名为store_fruits，替换成实际表名
CREATE VIEW store_fruits_with_apple_count AS
SELECT 
  "门店编号",
  "水果",
  -- 按门店分组，统计该门店的苹果数量
  COUNT(CASE WHEN "水果" = '苹果' THEN 1 END) OVER (PARTITION BY "门店编号") AS "苹果数量"
FROM store_fruits;

使用时直接查询这个视图即可：

SELECT * FROM store_fruits_with_apple_count;

返回的结果就是你期望的格式。

方案二：添加字段并通过触发器维护（适合需要在表中存储数据的场景） #

如果必须把这个字段存在原表中，可以通过触发器自动维护数值：

1. 先添加一个空字段：

ALTER TABLE store_fruits ADD COLUMN "苹果数量" INTEGER DEFAULT 0;

2. 创建用于计算苹果数量的函数：

CREATE OR REPLACE FUNCTION update_apple_count()
RETURNS TRIGGER AS $$
BEGIN
  -- 更新当前门店所有行的苹果数量
  UPDATE store_fruits
  SET "苹果数量" = (
    SELECT COUNT(*) 
    FROM store_fruits sf 
    WHERE sf."门店编号" = NEW."门店编号" AND sf."水果" = '苹果'
  )
  WHERE "门店编号" = NEW."门店编号";
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建触发器，在数据插入、更新、删除时自动执行函数：

CREATE TRIGGER trigger_update_apple_count
AFTER INSERT OR UPDATE OR DELETE ON store_fruits
FOR EACH ROW
EXECUTE FUNCTION update_apple_count();

4. 初始化现有数据的苹果数量：

UPDATE store_fruits sf
SET "苹果数量" = (
  SELECT COUNT(*) 
  FROM store_fruits sf2 
  WHERE sf2."门店编号" = sf."门店编号" AND sf2."水果" = '苹果'
);

两种方案对比 #

• 视图方案：无需维护，数据实时准确，但每次查询都会重新计算，适合数据量不大或查询频率不高的场景。
• 触发器方案：数据存储在表中，查询更快，但需要维护触发器，数据变更时会有额外的性能开销。

注意：因为你的字段名是中文，在PostgreSQL中必须用双引号包裹，否则会被自动转为小写，导致字段找不到。

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