如何将聚合函数计算的current_rating设为数据表的永久列？

步骤1：确保表中存在current_rating列 #

如果你的greydb表还没有current_rating列，先执行以下语句添加（可根据实际需求调整数据类型，比如用numeric存储小数，int存储整数）：

ALTER TABLE greydb ADD COLUMN current_rating numeric;

步骤2：一次性更新所有行的current_rating值 #

使用UPDATE...FROM语法，将你的查询结果与原表关联，批量更新列值。注意替换语句中的id为你的表的主键或唯一标识字段（比如greyhound_id等，确保能唯一匹配每一行）：

UPDATE greydb g
SET current_rating = sub.current_rating
FROM (
    SELECT 
        t.id, -- 替换为你的表的唯一标识字段
        r.current_rating
    FROM (
        SELECT 
            greydb.*,  
            array_agg(race_rating) OVER (
                PARTITION BY greyhound
                ORDER BY date
                ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
            ) AS rating_5
        FROM greydb
    ) t
    CROSS JOIN LATERAL (
        SELECT avg(r) AS current_rating
        FROM (
            SELECT u.*
            FROM unnest(t.rating_5) WITH ORDINALITY u(r, n)
            WHERE r IS NOT NULL
            ORDER BY r DESC
            LIMIT 3
        ) r
    ) r
) sub
WHERE g.id = sub.id; -- 同样替换为对应的唯一标识字段

步骤3：自动更新（可选） #

如果后续race_rating、date或greyhound字段有变化，想要current_rating自动同步更新，可以创建触发器：

1. 创建计算current_rating的函数

CREATE OR REPLACE FUNCTION update_current_rating()
RETURNS TRIGGER AS $$
BEGIN
    SELECT avg(r) INTO NEW.current_rating
    FROM (
        SELECT u.r
        FROM (
            SELECT race_rating
            FROM greydb
            WHERE greyhound = NEW.greyhound
            ORDER BY date
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ) t
        UNNEST(ARRAY_AGG(t.race_rating)) WITH ORDINALITY u(r, n)
        WHERE u.r IS NOT NULL
        ORDER BY u.r DESC
        LIMIT 3
    ) r;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器绑定函数

CREATE TRIGGER trigger_update_current_rating
BEFORE INSERT OR UPDATE OF race_rating, date, greyhound
ON greydb
FOR EACH ROW
EXECUTE FUNCTION update_current_rating();

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