主页 / dba / 问题 / 205468
Accepted
user129291
Asked: 2018-05-02 09:59:11 +0800 CST

创建 SQL 随机出生日期

  • 772

有没有人有任何 SQL 代码来自动生成随机出生日期，其中出生日期小于今天？请添加出生日期范围参数，例如：从 18 到 70 岁。

是否有任何内联 SQL 或函数来执行此操作？

我们试图混淆我们的专栏[Date of Birth]。

谢谢，

sql-server data-masking

4 个回答

  1. Best Answer

    这会将随机天数添加到 1900 年 1 月 1 日：

    SELECT DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');

    根据 Microsoft Docs，CRYPT_GEN_RANDOM“返回由 Crypto API (CAPI) 生成的加密随机数。输出是指定字节数的十六进制数。”

    因此返回toCRYPT_GEN_RANDOM(2)范围内的一个两字节数字，当转换为有符号整数并“添加” to 时，将导致 to 范围内的日期。0x00000xFFFF1900-01-011900-01-012079-06-06

    对于名为 的表dbo.MyTable，具有名为 的列[Date of Birth]，这会将所有列值更新为随机生成的日期：

    UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');

    可以颠倒逻辑，使您拥有从 0 天到大约 59 岁的不同年龄的人：

    UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY, (1 - CONVERT(int, CRYPT_GEN_RANDOM(2)) / 3), GETDATE());

    以下示例将随机选择出生日期，导致年龄在 10 到 20 岁之间：

    DECLARE @MinAge int;
DECLARE @MaxAge int;

SET @MinAge = 10;
SET @MaxAge = 20;
UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY
    , (1 - (CONVERT(int, CRYPT_GEN_RANDOM(2)) % ((@MaxAge - @MinAge) * 365)))
    , CONVERT(date, DATEADD(YEAR, 1 - @MinAge, GETDATE()))
    );
    • 23

  2. 方法一

    select DATEADD(DAY, -(ABS(CHECKSUM(NEWID()) % 36500 )), getdate());

    示例输出：

    1980-11-10 02:19:37.643
1940-08-25 02:20:06.217
1967-10-10 02:20:15.030
2013-03-20 02:20:24.933
1951-11-19 02:20:38.973

    总而言之，以下代码生成一个介于 0 和 36500 之间的随机数。（36500 天大致等于 100 年；您可以使用36525使其正好为100 年。）

    ABS(CHECKSUM(NEWID()) % 36500 )

    通过将当前日期减去随机生成的数字（随机天数），您将能够获得 0 到 100 岁之间的人的随机日期。

    演示：http ://sqlfiddle.com/#!18/9eecb/15528/0

      

    方法二

    DECLARE @start DATE = '1980-01-01'
DECLARE @end DATE = '1980-01-05'

SELECT DATEADD(DAY,ABS(CHECKSUM(NEWID())) % DATEDIFF(DAY,@start,@end) ,@start)

    示例输出：

    1980-01-01
1980-01-04
1980-01-03
1980-01-02

    使用该DATEDIFF函数，您可以获得两个日期之间的差异。在这种情况下 ( DATEDIFF(DAY,@start,@end)，将以天为单位获取开始日期和结束日期之间的差值。通过将此值添加到开始日期，您可以在开始日期和结束日期之间生成随机日期。

    但是，这不会将结束日期 ( 1980-01-05) 作为随机生成的日期返回。为此，您可以将差值加 1。

    SELECT DATEADD(DAY,ABS(CHECKSUM(NEWID())) % ( 1 + DATEDIFF(DAY,@start,@end)),@start)

    演示：http ://sqlfiddle.com/#!18/9eecb/15542/0

      

    方法三

    示例 1

    SELECT DATEADD(DAY, RAND() * ((-36500) - 1), GETDATE())

    样本输出：

    1956-02-25T23:44:17.62Z
2006-09-08T23:44:40.62Z
1987-06-13T23:44:53.717Z

    示例 2

    SELECT DATEADD(DAY, RAND() * ((-1) - 1), GETDATE())

    样本输出：

    2018-05-03 06:32:56.753
2018-05-02 06:32:56.753

    注意：如果去掉' - 1'，2018-05-02 06:32:56.753将不会生成。

    演示：http ://sqlfiddle.com/#!18/9eecb/15554/0

      

    方法四

    DECLARE @start DATE = '1980-01-01'
DECLARE @end DATE = '1980-01-05'

SELECT DATEADD(DAY, RAND() * DATEDIFF(DAY,@start,@end) ,@start)

    示例输出：

    1980-01-02
1980-01-01
1980-01-03
1980-01-04

    注意：这也不会将结束日期 ( 1980-01-05) 作为随机生成的日期返回。你知道你必须像这样添加1。

    DATEDIFF(DAY,@start,@end) +1

    演示：http ://sqlfiddle.com/#!18/9eecb/15538/0

      

    方法五

    DECLARE @from INT = 18 
DECLARE @to INT = 70 

DECLARE @tfrom DATE = DATEADD(YEAR, -(@from), GETDATE()) 
DECLARE @tto DATE = DATEADD(YEAR, -(@to), GETDATE()) 

DECLARE @diff INT = DATEDIFF(DAY, @tfrom, @tto)

SELECT DATEADD(DAY, RAND() * (-(@diff) - 1), @tto)

    示例输出：

    1967-11-03
1955-10-09
1967-06-03
1962-11-17
1970-07-04

    演示：http ://sqlfiddle.com/#!18/9eecb/15555/0

    • 9

  3. 一个保留现有出生日期分布的解决方案如下：通过连接数据库中其他三个不同的现有出生日期的年、月和日来创建一个新的出生日期。Generate three different random numbers i, j, k (that are less than the total number of records), pick year from row i, month from row j, day from row k, and concatenate them into a date.最好创建第二列，在迭代初始生日列时填充它，然后删除初始生日列。否则，如果我们在迭代时填充生日列，我们可能会冒着选择已经使用此策略更改的数据的风​​险，并且最终可能会在整个地方重复同一年。

    这种方法不太擅长数据屏蔽，因为如果你有一个 1902 年出生的用户，今年会出现（虽然月份和日期不同），可能会导致用户的唯一标识。然而，就我们所关心的数据分布而言，这个解决方案保持了年份以及月份和日期的分布：它为数据库中的任何年份提供了平等的选择机会，所以如果我们有两倍于 1990 年出生的人与 1970 年相比，这一比例在生成的生日集合中将保持不变。

    第二种方法：for row N, pick year from row N+1, month from row N+2, day from row N+3.这种方法在数据屏蔽方面更差，但在保持分布方面更好，因为我们所做的只是置换数据，从而保持准确的年、月、日，只在不同的行上重新排列。

    第三种方法：

    new_month = (current_month + next_record's_month) % 12
new_day = (current_day + next_record's_day) % 30 or %31 - nr of days of that month
new_year = (current_year + next_record's_year) / 2

    这种方法在混淆方面是迄今为止最好的：如果我们只有两个用户，一个出生在 1 月，另一个出生在 4 月，那么最终用户将在 5 月出生（01 + 04 = 05 月）。就保持数据的分布而言，加法模 12 在某种程度上类似于排列。至于年份，计算平均值会使分布曲线更加向中心拥挤 - 如果我们只有一个用户出生于 1900 年，那么生成的生日将是平均 1900 年，但仍然是早年.

    一般模式是：利用现有数据，而不是生成完全随机的值。

    我没有提供任何代码，因为我不熟悉 sql-sever 语法，但我认为这个想法值得一提。

    • 3

  4. 您也可以使用日期表并通过 newid() 对其进行排序。

    我过去曾使用这种技术来打乱大量数据。一个优点是您可以通过将表连接到自身来打乱任何字段rank() over (order by newid())

    注意：如果您的人员表比您的日期表大，在此示例中，循环插入日期表几次，直到它变大。

        --get your data into a safe space so you don't blow out the wrong table while you work
    drop table if exists person_space
    select top 5000 * into person_space from AbstractData

    select * from person_space

    --create your date table
    drop table if exists datetable
    create table datetable (day date)

    declare @date date
    set @date = '1950-01-01'

    while @date < '2025-01-01'
    begin
    insert into datetable(day)
    select @date
    set @date = dateadd(day,1, @date)
    end

    --select * from datetable

    --scramble the shit out of your tables using rank by newid()
    ;with ScramDates as (select rank() over (order by newid()) as randomRank, day from datetable
    where day <= getdate())
    ,peeps as (select rank() over (order by newid()) as randomRank, BirthDateTime, AccountNumber from person_space)

    ,finalcountdown as (select p.AccountNumber, p.BirthDateTime as old_dob, s.day as new_dob from ScramDates s
    inner join peeps p on s.randomRank = p.randomRank)

    select * from finalcountdown

    --update the date_of_birth

    --update p
    --set p.BirthDateTime = new_dob
    --from finalcountdown f
    --inner join person_space p on p.AccountNumber = f.AccountNumber

    --select * from person_space

    以下是如何将表随机连接到自身并更新列。此方法还将维护数据的分布：

        ;with ScramDates as (select rank() over (order by newid()) as randomRank, day from datetable
    where day <= getdate())
    ,peeps1 as (select rank() over (order by newid()) as randomRank, * from person_space)
    ,peeps2 as (select rank() over (order by newid()) as randomRank, * from person_space)

    --select p1.BirthDateTime, p2.BirthDateTime, p1.Name, p2.Name, * from peeps1 p1
    --inner join peeps2 p2 on p1.randomRank = p2.randomRank

    update p1
    set p1.BirthDateTime = p2.BirthDateTime
    from peeps1 p1
    inner join peeps2 p2 on p1.randomRank = p2.randomRank

    select * from person_space
    • 1

相关问题