如何在Oracle SQL查询中生成唯一且固定的替代MaskedID

你的问题核心是要让每个唯一的UserID对应一个固定的随机替代ID，之前的写法因为dbms_random.value()会逐行生成随机数，即使加了DISTINCT也无法保证同一UserID只生成一个值——Oracle会先给每一行（包括重复的UserID行）生成随机数，再去重，最终可能导致同一个UserID被分配不同的随机数。

用CTE可以解决这个问题 #

CTE（公共表表达式）会先预计算出所有唯一UserID对应的MaskedID映射关系，之后主查询直接关联这个映射表，不会重复生成随机数，自然能保证同一UserID对应相同的MaskedID。

下面提供几种可行的实现方案：

方案1：生成随机整数作为MaskedID

WITH user_mask AS (
    SELECT 
        UserID,
        -- 生成1到99999之间的随机整数
        FLOOR(dbms_random.value(1, 100000)) AS MaskedID
    FROM (
        -- 先提取所有唯一的UserID
        SELECT DISTINCT UserID 
        FROM your_table
    )
)
SELECT 
    um.MaskedID,
    t.Value
FROM your_table t
JOIN user_mask um ON t.UserID = um.UserID;

这个方案先从原表中提取所有不重复的UserID，再给每个UserID分配一个随机整数，最后关联回原表，确保同一UserID的所有行使用同一个MaskedID。

方案2：生成唯一连续的随机替代ID

如果需要确保MaskedID绝对唯一（无重复），可以用ROW_NUMBER()结合随机排序：

WITH user_mask AS (
    SELECT 
        UserID,
        -- 按随机顺序给UserID分配唯一连续的ID
        ROW_NUMBER() OVER(ORDER BY dbms_random.value()) AS MaskedID
    FROM (
        SELECT DISTINCT UserID 
        FROM your_table
    )
)
SELECT 
    um.MaskedID,
    t.Value
FROM your_table t
JOIN user_mask um ON t.UserID = um.UserID;

这种方法生成的MaskedID是连续的整数，且每个UserID对应唯一值，排序是随机的，满足你的需求。

方案3：用哈希函数生成固定替代ID（非随机但唯一）

如果不需要随机，只是需要固定的替代ID，也可以用哈希函数（比如ORA_HASH）：

SELECT 
    ORA_HASH(UserID, 99999) AS MaskedID,
    Value
FROM your_table;

这种方法不需要CTE，直接计算每个UserID的哈希值，同一UserID的哈希值固定，不同UserID大概率不同（极小概率冲突），适合不需要随机的场景。

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