PostgreSQL数据生成优化：非空round、年份提取及随机姓名生成

1. 修复cup_matches表的round字段空值与year字段格式问题 #

问题根源 #

• round字段偶尔出现null：random()*5会生成0到5之间的浮点数，PostgreSQL数组索引从1开始，当结果小于1时（比如0.2），数组索引无效，返回null。
• year字段需要纯整数年份：原语句生成的是date类型，需提取年份转为整数格式。

修改后的代码 #

with populate_cup_matches as (
  insert into cup_matches 
  select generate_series(1,624680,1) as mid,
         -- 生成1-5的整数索引，确保从数组取有效值
         ('{final,semi-final,quarter-final,16,32}'::text[])[floor(random()*5)+1] as round,
         -- 提取年份并转为整数
         extract(year from ('epoch'::date+'1 year'::interval*floor(random()*53)))::int as year,
         floor(random()*1000000)::int as num_ratings,
         random()*10 as rating
  returning *
)

核心修改说明 #

• round字段：floor(random()*5)+1确保生成1到5的整数，刚好匹配数组的5个元素索引，彻底避免空值。
• year字段：用extract(year from 日期值)提取年份，再转为整数类型，得到纯年份数字。

2. 修复played_in表的姓名随机分布与真实姓名生成问题 #

问题根源 #

• 原代码仅在mid<=22218时生成带salah的姓名，无法实现全表随机分布。
• 用UUID拼接的字符串不是真实合法姓名，不符合需求。

修改后的代码 #

-- 定义包含真实姓名的列表，混入带salah的姓名
with name_list as (
  select unnest(array[
    'Mohamed Salah', 'Salah Ahmed', 'Salah Ali', 'Ahmed Salah', 'Ali Salah',
    'Lionel Messi', 'Cristiano Ronaldo', 'Neymar Jr', 'Kylian Mbappe', 'Kevin De Bruyne',
    'Robert Lewandowski', 'Erling Haaland', 'Karim Benzema', 'Luka Modric', 'Sadio Mane'
  ]) as full_name
)
insert into public.played_in
select 
  mid,
  -- 随机从姓名列表中抽取，实现salah相关姓名全表随机分布
  (select full_name from name_list order by random() limit 1) as name,
  year,
  -- 生成1-11的整数位置（符合足球位置常规取值，可按需调整）
  floor(random()*11)+1 as position
from populate_cup_matches 
limit 135960
returning *;

核心修改说明 #

• 随机分布salah：通过定义包含多个带salah的真实全名，和其他普通球星姓名一起，用order by random() limit 1随机选取，实现salah相关姓名在全表随机出现。
• 真实合法姓名：直接使用真实的全名格式，替换UUID拼接的字符串，你可以根据需求自由扩展name_list里的姓名数量和内容。
• position字段优化：原代码生成0-11的浮点数，改为floor(random()*11)+1得到1-11的整数，更符合真实比赛位置的取值（若不需要可改回原逻辑）。

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