Question

I am trying to add a column in a SQL query to use as an alternative ID. The data has this format:

UserID | Value
---------------
1      | 23
2      | 10
1      | 45

我想创建另一列，这是另一个数字，但尊重ID的唯一性。像这样

MaskedID | Value
---------------
9      | 23
8      | 10
9      | 45

我曾尝试使用子查询创建一个带有随机数的表，但第一个ID的随机数不保持不变：

Select b.Masked, a.value
from table a
left join 
(select distinct(UserID), dbms_random.value(1,100000) as Masked) b on a.UserID=b.UserID

但这会导致：

MaskedID | Value
---------------
7      | 23
8      | 10
9      | 45

用户ID的#可能会随着时间的推移而变化，因此不应该预先定义。CTE是否会阻止随机数在最终表中再生？

Answer 1

尝试哈希函数。它将始终为相同的输入返回相同的值，但不能反向工程到原始值。最古老和最简单的是ORA_HASH ：

select ORA_HASH(USERID) Masked, a.value
from table a

这会产生一个数字，但如果有足够的值，可能会发生碰撞。更好的是较新的STANDARD_HASH函数，它不太可能产生冲突，但会产生字母数字（十六进制）输出：

select STANDARD_HASH(USERID) Masked, a.value
from table a

Answer 2

您可以为每个UserId中的第一个生成GUID ，然后使用分析函数为具有相同UserId的所有其他行使用相同的值：

SELECT MAX(maskedid) OVER (PARTITION BY userid) AS maskedid,
       value
FROM   (
  SELECT CASE ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ROWNUM)
         WHEN 1
         THEN SYS_GUID()
         END AS maskedid,
         userid,
         value
  FROM   table_name
);

其中，对于示例数据：

CREATE TABLE table_name (UserID, Value) AS
SELECT 1, 23 FROM DUAL UNION ALL
SELECT 2, 10 FROM DUAL UNION ALL
SELECT 1, 45 FROM DUAL;

可输出：

MASKEDID	VALUE
0x0BFDC951F97909BCE06502163E386F05	23
0x0BFDC951F97909BCE06502163E386F05	45
0x0BFDC951F97A09BCE06502163E386F05	10

您可以使用任何函数而不是SYS_GUID () ，例如DBMS_RANDOM.VALUE ：

SELECT MAX(maskedid) OVER (PARTITION BY userid) AS maskedid,
       value
FROM   (
  SELECT CASE ROW_NUMBER() OVER (PARTITION BY userid ORDER BY ROWNUM)
         WHEN 1
         THEN DBMS_RANDOM.VALUE(0, 1e6)
         END AS maskedid,
         userid,
         value
  FROM   table_name
);

MYMEMORY WARNING: YOU USED ALL AVAILABLE FREE TRANSLATIONS FOR TODAY. NEXT AVAILABLE IN 23 HOURS 58 MINUTES 31 SECONDS VISIT HTTPS://MYMEMORY.TRANSLATED.NET/DOC/USAGELIMITS.PHP TO TRANSLATE MORE

MASKEDID	VALUE
84046.38920350070167103410156941056149	23
84046.38920350070167103410156941056149	45
297835.63547525613908362909300172303338	10

MYMEMORY WARNING: YOU USED ALL AVAILABLE FREE TRANSLATIONS FOR TODAY. NEXT AVAILABLE IN 23 HOURS 58 MINUTES 29 SECONDS VISIT HTTPS://MYMEMORY.TRANSLATED.NET/DOC/USAGELIMITS.PHP TO TRANSLATE MORE

Answer 3

MYMEMORY WARNING: YOU USED ALL AVAILABLE FREE TRANSLATIONS FOR TODAY. NEXT AVAILABLE IN 23 HOURS 58 MINUTES 27 SECONDS VISIT HTTPS://MYMEMORY.TRANSLATED.NET/DOC/USAGELIMITS.PHP TO TRANSLATE MORE

Select 987654321 - a.USER_ID AS MaskedID, a.value
from table a;

MYMEMORY WARNING: YOU USED ALL AVAILABLE FREE TRANSLATIONS FOR TODAY. NEXT AVAILABLE IN 23 HOURS 58 MINUTES 26 SECONDS VISIT HTTPS://MYMEMORY.TRANSLATED.NET/DOC/USAGELIMITS.PHP TO TRANSLATE MORE

Answer 4

MYMEMORY WARNING: YOU USED ALL AVAILABLE FREE TRANSLATIONS FOR TODAY. NEXT AVAILABLE IN 23 HOURS 58 MINUTES 24 SECONDS VISIT HTTPS://MYMEMORY.TRANSLATED.NET/DOC/USAGELIMITS.PHP TO TRANSLATE MORE

select col1, col2, SYS_GUID() uniqueId from table1

MYMEMORY WARNING: YOU USED ALL AVAILABLE FREE TRANSLATIONS FOR TODAY. NEXT AVAILABLE IN 23 HOURS 58 MINUTES 23 SECONDS VISIT HTTPS://MYMEMORY.TRANSLATED.NET/DOC/USAGELIMITS.PHP TO TRANSLATE MORE

select col1, col2, ROWID uniqueId from table1

MYMEMORY WARNING: YOU USED ALL AVAILABLE FREE TRANSLATIONS FOR TODAY. NEXT AVAILABLE IN 23 HOURS 58 MINUTES 21 SECONDS VISIT HTTPS://MYMEMORY.TRANSLATED.NET/DOC/USAGELIMITS.PHP TO TRANSLATE MORE

select col1, col2, ROWNUM uniqueId from table1

MYMEMORY WARNING: YOU USED ALL AVAILABLE FREE TRANSLATIONS FOR TODAY. NEXT AVAILABLE IN 23 HOURS 58 MINUTES 20 SECONDS VISIT HTTPS://MYMEMORY.TRANSLATED.NET/DOC/USAGELIMITS.PHP TO TRANSLATE MORE

友情链接