SQL 返回不在表中的随机数

问题描述

我有一个表，其中包含我们从活动帐户的流数据源收集的 user_id.现在我正在寻找并填写有关没有做太多事情的 user_id 的信息.

I have a table with user_ids that we've gathered from a streaming datasource of active accounts. Now I'm looking to go through and fill in the information about the user_ids that don't do much of anything.

是否有一种 SQL(如果重要的话，postgres)方法可以让查询返回表中不存在的随机数?

Is there a SQL (postgres if it matters) way to have a query return random numbers not present in the table?

例如像这样:

SELECT RANDOM(count, lower_bound, upper_bound) as new_id 
WHERE new_id NOT IN (SELECT user_id FROM user_table) AS user_id_table

可能，还是最好用脚本包装器生成一堆随机数并将它们传递到数据库中以找出不存在的随机数?

Possible, or would it be best to generate a bunch of random numbers with a scripted wrapper and pass those into the DB to figure out non existant ones?

推荐答案

这是可能的.如果您希望 ID 为整数，请尝试:

It is posible. If you want the IDs to be integers, try:

SELECT trunc((random() * (upper_bound - lower_bound)) + lower_bound) AS new_id 
FROM generate_series(1,upper_bound) 
WHERE new_id NOT IN (
    SELECT user_id 
    FROM user_table)

这篇关于SQL 返回不在表中的随机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！