PostgreSQL中如何为NBA对阵比赛数据生成唯一game_id

你可以根据自己对game_id的格式要求选择以下任意一种方案：

方案1：字符串类型自动生成列（无需手动维护，推荐） #

直接用比赛日期+两队名称字典序拼接的结果作为game_id，可读性高，完全不需要手动维护，插入数据时自动生成：

-- 直接添加生成列，自动计算game_id
ALTER TABLE basketball_data ADD COLUMN game_id TEXT GENERATED ALWAYS AS (
    dategame || '_' || LEAST(team, opponent) || '_' || GREATEST(team, opponent)
) STORED;

执行后查询测试数据，你会得到的game_id分别为2021-10-19_GSW_LAL、2021-10-19_GSW_LAL、2021-10-19_BKN_MIL、2021-10-19_BKN_MIL，完全满足同场比赛id相同的要求。

方案2：数字类型game_id（存量数据批量生成） #

如果你需要纯数字格式的game_id，可以用窗口函数批量给现有数据赋值：

-- 新增game_id字段
ALTER TABLE basketball_data ADD COLUMN game_id INT;

-- 批量更新生成game_id，同一场比赛id相同
WITH game_groups AS (
    SELECT 
        team,
        dategame,
        opponent,
        -- 按日期+两队排序结果分组生成连续id，起始值为1
        DENSE_RANK() OVER (ORDER BY dategame, LEAST(team, opponent), GREATEST(team, opponent)) AS generated_id
    FROM basketball_data
)
UPDATE basketball_data b
SET game_id = g.generated_id
FROM game_groups g
WHERE b.team = g.team 
  AND b.dategame = g.dategame 
  AND b.opponent = g.opponent;

方案3：新增数据自动生成数字game_id #

如果后续会持续写入新的比赛数据，可以配合序列+触发器实现自动生成同场相同的数字game_id：

-- 生成id的序列，起始值可自定义，这里用100000
CREATE SEQUENCE basketball_game_id_seq START WITH 100000;

-- 触发器函数
CREATE OR REPLACE FUNCTION set_basketball_game_id()
RETURNS TRIGGER AS $$
DECLARE
    existing_id INT;
BEGIN
    -- 先查找同场次的反向对阵记录，存在则复用已有id
    SELECT game_id INTO existing_id
    FROM basketball_data
    WHERE dategame = NEW.dategame
      AND team = NEW.opponent
      AND opponent = NEW.team
    LIMIT 1;

    IF existing_id IS NOT NULL THEN
        NEW.game_id := existing_id;
    ELSE
        -- 无匹配记录则生成新id
        NEW.game_id := nextval('basketball_game_id_seq');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到表，插入前自动执行
CREATE TRIGGER trg_set_game_id
BEFORE INSERT ON basketball_data
FOR EACH ROW EXECUTE FUNCTION set_basketball_game_id();

注意：如果存在并发写入同一场比赛两条记录的场景，建议将两条记录放在同一个事务中插入，避免重复生成id。

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