postgreUse fixed list作为SQL查询中的随机值

Question

我有一个表，我想在其中生成测试数据:

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,
/* here I can have values NEW, IN_PROGRESS, COMPLETED */ AS status,    
FROM generate_series(1,25) as g(id);

你知道我如何实现一个逻辑到SQL查询从一个固定的选项生成随机值?

Answer 1

您可以将CASE与RANDOM()功能结合使用来实现此功能。

例如:

SELECT
'Business name ' || id AS business_name,
(NOW() + (random() * (NOW() + '90 days' - NOW())) + '30 days') AS created_at,
case when random() < 0.3333 then 'NEW'
when random() < 0.5 then 'IN_PROGESS'
else 'COMPLETED'
end AS status
FROM generate_series(1,10) as g(id);

结果:

business_name     created_at                     status     
----------------- ------------------------------ ---------- 
Business name 1   2021-12-03 07:37:08.519843+00  NEW        
Business name 2   2022-01-22 19:14:10.085483+00  COMPLETED  
Business name 3   2021-11-23 16:47:39.207064+00  COMPLETED  
Business name 4   2022-01-26 13:08:33.643398+00  NEW        
Business name 5   2021-12-05 03:26:44.730359+00  COMPLETED  
Business name 6   2022-02-07 07:15:05.470689+00  COMPLETED  
Business name 7   2022-01-13 05:00:15.326175+00  COMPLETED  
Business name 8   2022-02-08 02:43:43.123768+00  IN_PROGESS 
Business name 9   2021-11-17 06:06:40.14872+00   NEW        
Business name 10  2021-12-19 07:59:55.786655+00  NEW        

参见运行示例:db<>fiddle。