SQL查询插入测试数据时如何从固定选项列表获取随机值

你可以通过数组随机取值的方式快速实现需求，修改后的完整SQL如下：

INSERT into onboarding_tasks (business_name, created_at, status)
SELECT
    'Business name ' || id AS business_name,
    (NOW() + (random() * (NOW() + '90 days' - NOW())) + '30 days') AS created_at,
    (ARRAY['NEW', 'IN_PROGRESS', 'COMPLETED'])[floor(random() * 3 + 1)] AS status
FROM generate_series(1,25) as g(id);

逻辑说明 #

• 先将三个可选状态值组装成一个文本数组ARRAY['NEW', 'IN_PROGRESS', 'COMPLETED']
• random()会生成0到1之间的随机浮点数，乘以3之后得到0到3之间的数值，用floor()取整后得到0、1、2三个整数，加1之后就得到1、2、3三个符合PostgreSQL数组下标规则的索引值
• 数组会根据随机生成的下标取出对应的状态值，实现等概率随机取值的效果

如果你需要自定义不同状态的出现概率，可以用CASE WHEN的写法灵活调整权重：

INSERT into onboarding_tasks (business_name, created_at, status)
SELECT
    'Business name ' || id AS business_name,
    (NOW() + (random() * (NOW() + '90 days' - NOW())) + '30 days') AS created_at,
    CASE 
        WHEN random() < 0.4 THEN 'NEW' -- 40%概率出现NEW
        WHEN random() < 0.7 THEN 'IN_PROGRESS' -- 30%概率出现IN_PROGRESS
        ELSE 'COMPLETED' -- 剩余30%概率出现COMPLETED
    END AS status
FROM generate_series(1,25) as g(id);

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