天道酬勤,学无止境

在固定间隔内产生明显的随机时间(Distinct random time generation in the fixed interval)

问题

我试图产生8:00 AM至8:00 PM从数据集合中选择每行之间的随机时间,但是,我总是得到相同的随机值的每一行-我想这是针对不同每行。

表架构和数据:

╔══════╦════════════════╗
║  ID  ║  CREATED_DATE  ║
╠══════╬════════════════╣
║ ID/1 ║   26/04/2014   ║
║ ID/2 ║   26/04/2014   ║
║ ID/3 ║   26/04/2014   ║
║ ID/4 ║   26/04/2014   ║
║ ID/5 ║   26/04/2014   ║
╚══════╩════════════════╝

urrent SQL语句:

SELECT [ID]
     , MyFunction.dbo.AddWorkDays(14, [CREATED_DATE]) AS [New Date]
     , CONVERT(VARCHAR, DATEADD(MILLISECOND, CAST(43200000 * RAND() AS INT), CONVERT(TIME, '08:00')), 114) AS [New Time]
FROM [RandomTable]

当前结果( [New Time]列中的每一行相同的时间):

╔══════╦════════════════╦════════════════╗
║  ID  ║    New Date    ║    New Time    ║
╠══════╬════════════════╬════════════════╣
║ ID/1 ║   10/05/2014   ║    09:41:43    ║
║ ID/2 ║   10/05/2014   ║    09:41:43    ║
║ ID/3 ║   10/05/2014   ║    09:41:43    ║
║ ID/4 ║   10/05/2014   ║    09:41:43    ║
║ ID/5 ║   10/05/2014   ║    09:41:43    ║
╚══════╩════════════════╩════════════════╝

所需的结果( [New Time]列中每行的时间不同):

╔══════╦════════════════╦════════════════╗
║  ID  ║    New Date    ║    New Time    ║
╠══════╬════════════════╬════════════════╣
║ ID/1 ║   10/05/2014   ║    09:41:43    ║
║ ID/2 ║   10/05/2014   ║    15:05:23    ║
║ ID/3 ║   10/05/2014   ║    10:01:05    ║
║ ID/4 ║   10/05/2014   ║    19:32:45    ║
║ ID/5 ║   10/05/2014   ║    08:43:15    ║
╚══════╩════════════════╩════════════════╝

有想法该怎么解决这个吗? 以上只是示例数据-我的真实表有大约2800条记录(不知道这是否会与任何人的建议有所不同)。

回答1

原始问题的解释:

问题指出:

  • 产生一个介于8:00 AM和8:00 PM之间的随机时间(即12小时窗口)
  • 每行应该不同(即在所有行中都是唯一的)
  • 实际表有大约2800条记录

现在考虑以下几点:

  • 样本数据仅显示一个日期
  • 24小时内有86,400秒,因此12小时内有43,200秒

在以下方面存在一些歧义:

  • 鉴于不能保证真正的随机值对于每一行都是不同的,因此在“每一行不同”的上下文中,什么是随机的。 实际上,理论上每一行的真正随机数可以相同。 那么,强调“随机”还是“不同”呢? 还是我们真的是在谈论不同但没有顺序排列的东西(在没有随机的情况下给出随机性的外观)?
  • 如果行数超过2800怎么办? 如果有一百万行怎么办?
  • 如果行数超过43,200,则如何处理“每行不同”(因为不可能在所有行中都具有唯一性)?
  • 日期会有所不同吗? 如果是这样,我们是否真的在谈论“每个日期的每一行都不同”?
  • 如果“每个日期的每一行都不同”:
    • 每个日期的时间可以遵循相同的非顺序模式吗? 还是每个日期的模式都需要不同?
    • 在任何特定日期,行数是否会超过43200? 如果是这样,则每组43,200行的时间只能是唯一的。

鉴于以上信息,有几种方法可以解释请求:

  1. 强调“随机”:日期和行数无关紧要。 使用其他答案中所示的三种方法之一,生成真正的随机时间,该时间很可能但不能保证是唯一的:
    • @notulysses: RAND(CAST(NEWID() AS VARBINARY)) * 43200
    • @史蒂夫·福特: ABS(CHECKSUM(NewId()) % 43201)
    • @Vladimir Baranov: CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) as int)
  2. 强调“每行不同”,总是<= 43,200行:如果数从不超过可用秒数,则很容易保证所有行的唯一时间,无论日期相同或不同,并且看起来是随机排序。
  3. 强调“每行不同”可能是> 43,200行:如果数可以超过可用秒数,则不可能保证所有行的唯一性,但是仍然可以保证所有行的唯一性特定日期的行数,前提是没有特定日期的行数超过43,200。

因此,我的回答基于以下想法:

  • 即使OP的行数从未超过2800,大多数遇到相似的随机性需求的其他人也有可能使用更大的数据集(即,很容易有100万行,对于任何数目日期:1、5000等)
  • 在所有5行中都使用相同的日期来简化样本数据,或者在这种特殊情况下即使所有行中的日期都相同,在大多数其他情况下也不太可能发生
  • 唯一性要胜于随机性
  • 如果每个日期的秒的“看似随机”排序都有规律,则日期之间的序列开始应至少有一个变化的偏移量(当日期按顺序排序时),以显示随机性在任何小的日期分组之间。

回答:

如果情况需要唯一的时间,则任何生成真正随机值的方法都无法保证。 我真的很喜欢@Vladimir Baranov使用CRYPT_GEN_RANDOM ,但是要生成一组唯一的值几乎是不可能的:

DECLARE @Table TABLE (Col1 BIGINT NOT NULL UNIQUE);

INSERT INTO @Table (Col1)
    SELECT CONVERT(BIGINT, CRYPT_GEN_RANDOM(4))
    FROM [master].sys.objects so
    CROSS JOIN [master].sys.objects so2
    CROSS JOIN [master].sys.objects so3;
    -- 753,571 rows

将随机值增加到8个字节似乎确实有效:

DECLARE @Table TABLE (Col1 BIGINT NOT NULL UNIQUE);

INSERT INTO @Table (Col1)
    SELECT CONVERT(BIGINT, CRYPT_GEN_RANDOM(8))
    FROM [master].sys.objects so
    CROSS JOIN [master].sys.objects so2
    CROSS JOIN [master].sys.objects so3;
    -- 753,571 rows

当然,如果我们要减少到第二个,那么只有86,400个。 缩小范围似乎有所帮助,因为以下情况有时会起作用:

DECLARE @Table TABLE (Col1 BIGINT NOT NULL UNIQUE);

INSERT INTO @Table (Col1)
    SELECT TOP (86400) CONVERT(BIGINT, CRYPT_GEN_RANDOM(4))
    FROM [master].sys.objects so
    CROSS JOIN [master].sys.objects so2
    CROSS JOIN [master].sys.objects so3;

但是,如果每天都需要唯一性,那么事情就会变得有些棘手(这似乎是此类项目的合理要求,而不是每天都有唯一性)。 但是,随机数生成器不会知道在每一天都会重置。

如果仅看起来是随机的是可以接受的,那么我们可以保证每个日期的唯一性而无需:

  • 循环/游标构造
  • 将已使用的值保存在表中
  • 使用RAND()NEWID()CRYPT_GEN_RANDOM()

以下解决方案使用了我在此答案中了解到的模乘乘法逆(MMI)的概念:在SQL Server中生成看似随机的唯一数字ID。 当然,这个问题并没有一个严格定义的值范围,就像我们这里每天只有86,400个值一样。 因此,我使用范围为86400(作为“模数”)并在在线计算器中尝试了一些“ coprime”值(例如“ Integer”)来获取其MMI:

  • 13(MMI = 39877)
  • 37(MMI = 51373)
  • 59(MMI = 39539)

我在CTE中使用ROW_NUMBER()CREATED_DATE进行了分区(即分组),作为每天第二天分配值的一种方式。

但是,尽管依次生成的秒数0、1、2等的值将随机出现,但在不同的日期中,特定的秒将映射到相同的值。 因此,第二个CTE(名为“ WhichSecond”)通过将日期转换为INT(将日期转换为1900-01-01的顺序偏移量)来移动每个日期的起点,然后乘以101。

DECLARE @Data TABLE
(
  ID INT NOT NULL IDENTITY(1, 1),
  CREATED_DATE DATE NOT NULL
);

INSERT INTO @Data (CREATED_DATE) VALUES ('2014-10-05');
INSERT INTO @Data (CREATED_DATE) VALUES ('2014-10-05');
INSERT INTO @Data (CREATED_DATE) VALUES ('2014-10-05');
INSERT INTO @Data (CREATED_DATE) VALUES ('2014-10-05');
INSERT INTO @Data (CREATED_DATE) VALUES ('2014-10-05');
INSERT INTO @Data (CREATED_DATE) VALUES ('2015-03-15');
INSERT INTO @Data (CREATED_DATE) VALUES ('2016-10-22');
INSERT INTO @Data (CREATED_DATE) VALUES ('2015-03-15');

;WITH cte AS
(
  SELECT tmp.ID,
         CONVERT(DATETIME, tmp.CREATED_DATE) AS [CREATED_DATE],
         ROW_NUMBER() OVER (PARTITION BY tmp.CREATED_DATE ORDER BY (SELECT NULL))
                      AS [RowNum]
  FROM   @Data tmp
), WhichSecond AS
(
  SELECT cte.ID,
         cte.CREATED_DATE,
         ((CONVERT(INT, cte.[CREATED_DATE]) - 29219) * 101) + cte.[RowNum]
                      AS [ThisSecond]
  FROM   cte
)
SELECT parts.*,
       (parts.ThisSecond % 86400) AS [NormalizedSecond], -- wrap around to 0 when
                                                         -- value goes above 86,400
       ((parts.ThisSecond % 86400) * 39539) % 86400 AS [ActualSecond],
       DATEADD(
                 SECOND,
                 (((parts.ThisSecond % 86400) * 39539) % 86400),
                 parts.CREATED_DATE
              ) AS [DateWithUniqueTime]
FROM WhichSecond parts
ORDER BY parts.ID;

返回值:

ID  CREATED_DATE  ThisSecond  NormalizedSecond  ActualSecond  DateWithUniqueTime
1   2014-10-05    1282297     72697             11483         2014-10-05 03:11:23.000
2   2014-10-05    1282298     72698             51022         2014-10-05 14:10:22.000
3   2014-10-05    1282299     72699              4161         2014-10-05 01:09:21.000
4   2014-10-05    1282300     72700             43700         2014-10-05 12:08:20.000
5   2014-10-05    1282301     72701             83239         2014-10-05 23:07:19.000
6   2015-03-15    1298558      2558             52762         2015-03-15 14:39:22.000
7   2016-10-22    1357845     61845             83055         2016-10-22 23:04:15.000
8   2015-03-15    1298559      2559              5901         2015-03-15 01:38:21.000

如果我们只想生成从8:00 AM到8:00 PM的时间,我们只需要进行一些小的调整:

  1. 将范围(作为“模数”)从86400更改为一半:43200
  2. 重新计算MMI(可以使用与“ Integer”相同的“ coprime”值):39539(与以前相同)
  3. 28800添加到DATEADD的第二个参数中,作为8小时的偏移量

结果将只是更改为一行(因为其他行都是诊断性的):

-- second parameter of the DATEADD() call
28800 + (((parts.ThisSecond % 43200) * 39539) % 43200)

每天CREATED_DATE另一种难以预测的方式是通过在“ WhichSecond” CTE中传递INT形式的CREATED_DATE来利用RAND() 。 这将提供一个稳定的,因为每个每个日期偏移RAND(x)将返回相同的值y为相同的值x的通过,但将返回不同的值y为不同的值x传入含义:

RAND(1)= y1
RAND(2)= y2
RAND(3)= y3
RAND(2)= y2

第二次调用RAND(2)时,它仍返回与第一次调用时相同的y2值。

因此,“ WhichSecond” CTE可能是:

(
  SELECT cte.ID,
         cte.CREATED_DATE,
         (RAND(CONVERT(INT, cte.[CREATED_DATE])) * {some number}) + cte.[RowNum]
                      AS [ThisSecond]
  FROM   cte
)
回答2

OP仅使用rand()遇到的问题是由于它对每个查询进行一次评估。

从文档中:

如果未指定种子,则SQL Server数据库引擎将随机分配一个种子值。 对于指定的种子值,返回的结果始终相同。

下面介绍的方法消除了优化并抑制了此行为,因此rand()每行评估一次

dateadd( second
       , rand(cast(newid() as varbinary)) * 43200
       , cast('08:00:00' as time) )
  • newid()生成类型uniqueidentifier的唯一值;
  • 该值被转换与cast被用作种子兰特([种子])函数生成的伪随机float通过10值,并且作为种子总是唯一的返回值是唯一的太。

SQLFiddle

回答3

您也可以使用:

SELECT DATEADD(s, ABS(CHECKSUM(NewId()) % 43201), CAST('08:00:00' AS Time))

ABS(CHECKSUM(NewId()) % 43201)生成一个介于043200之间的随机数。 请参阅此处的讨论。

SQL小提琴

MS SQL Server 2008架构设置

查询1

SELECT DATEADD(s, ABS(CHECKSUM(NewId()) % 43201), CAST('08:00:00' AS Time)) AS [RandomTime]
FROM 
( VALUES (1), (2), (3), (4), (5)
) Y(A)
CROSS JOIN
( VALUES (1), (2), (3), (4), (5)
) Z(A)

结果

|    RANDOMTIME    |
|------------------|
| 16:51:58.0000000 |
| 10:42:44.0000000 |
| 14:01:38.0000000 |
| 13:33:51.0000000 |
| 18:00:51.0000000 |
| 11:29:03.0000000 |
| 10:21:14.0000000 |
| 16:38:27.0000000 |
| 09:55:37.0000000 |
| 13:21:13.0000000 |
| 11:29:37.0000000 |
| 10:57:49.0000000 |
| 14:56:42.0000000 |
| 15:33:11.0000000 |
| 18:49:45.0000000 |
| 16:23:28.0000000 |
| 09:00:05.0000000 |
| 09:20:01.0000000 |
| 11:26:23.0000000 |
| 15:26:23.0000000 |
| 10:38:44.0000000 |
| 11:46:30.0000000 |
| 16:00:59.0000000 |
| 09:29:18.0000000 |
| 09:09:19.0000000 |
回答4

有几种方法:

  • 预先生成一个带有随机数的表,并在需要时使用它。 或从信誉良好的来源获取此数据。
  • 使用NEWID函数为RAND提供种子的各种组合。 应谨慎使用,因为不能保证有关NEWID值的分布。 使它或多或少均匀分布的最佳方法之一是通过CHECKSUMRAND(CHECKSUM(NEWID())) 。 关于此方法的好处是,从SQL Server 2000开始可以使用NEWID函数。
  • 而不是使用NEWID ,例如将某列的MD5用作RAND的种子: RAND(CHECKSUM(HASHBYTES('MD5', CAST(SomeID AS varbinary(4)))))或仅行号: RAND(CHECKSUM(HASHBYTES('MD5', CAST(ROW_NUMBER() OVER(ORDER BY ...) AS varbinary(4))))) 。 此方法至少从SQL Server 2005起可用。与NEWID方法的主要区别在于,您可以完全控制随机序列。 您无法控制NEWID返回什么,也无法再次从相同数字重新启动随机序列。 如果使用PARTITION BY提供相同的行号集合,则将获得相同的随机数集合。 当您需要多次使用相同的随机数序列时,这可能会很有用。 对于两个不同的种子,有可能获得相同的随机数。 我测试了行号从1到1,000,000。 它们的MD5都不同。 MD5 CHECKSUM导致122次碰撞。 此CHECKSUM RAND导致246次碰撞。 用1到100,000的行号进行测试时, CHECKSUM有1次碰撞, RAND有3次碰撞。
  • 另一种可能性是简单地在T-SQL中实现您自己的用户定义函数,该函数使用您喜欢的算法生成一个随机数。 在这种情况下,您可以完全控制所有内容。 通常,伪随机数生成器必须在调用之间存储其内部状态,因此最终可能会有一个专用表来存储此数据。
  • 您可以使用CLR编写用户定义的函数。 在这种情况下,您可以实现自己的生成器,也可以使用.NET内置的函数,例如Random类或RNGCryptoServiceProvider类。
  • 最后,从SQL Server 2008开始,内置了一个函数CRYPT_GEN_RANDOM。

我将详细介绍最后一种方法,因为我认为这对于SQL Server 2008及更高版本来说是一个非常好的解决方案。 与结果集RAND相对,仅对结果集的每一行调用CRYPT_GEN_RANDOM

CRYPT_GEN_RANDOM(Transact-SQL)

返回由加密API(CAPI)生成的加密随机数。 输出是指定字节数的十六进制数。

此外,与RAND相比, CRYPT_GEN_RANDOM应该提供更好的随机值。 在分配和加密强度方面更好。 例子:

(CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5)

这将生成4个随机字节作为varbinary 。 我们必须先将它们显式转换为int 。 然后将结果转换为0到1之间的浮点数。

因此,原始查询将如下所示:

SELECT ID AS [ID]
     , MyFunction.dbo.AddWorkDays(14, S.CREATED_DATE) AS [New Date]
     , CONVERT(VARCHAR, DATEADD(MILLISECOND, 
     CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) as int),
     CONVERT(TIME, '08:00')), 114) AS [New Time]
FROM RandomTable

这是一个易于复制和粘贴的独立示例(我使用了@Steve Ford的另一个答案中的查询):

SELECT DATEADD(millisecond, 
    CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) as int), 
    CAST('08:00:00' AS Time)) AS [RandomTime]
FROM 
    ( VALUES (1), (2), (3), (4), (5)
    ) Y(A)
    CROSS JOIN
    ( VALUES (1), (2), (3), (4), (5)
    ) Z(A)

结果如下:

RandomTime
10:58:24.7200000
19:40:06.7220000
11:04:29.0530000
08:57:31.6130000
15:03:14.9470000
09:15:34.9380000
13:46:43.1250000
11:27:00.8940000
14:42:23.6100000
15:07:56.2120000
11:39:09.8830000
08:16:44.3960000
14:23:38.4820000
17:28:31.7440000
16:29:31.4320000
09:09:15.0210000
12:31:09.8370000
11:23:09.8430000
15:35:45.5480000
17:42:49.3390000
08:07:05.4930000
18:17:16.2980000
11:49:08.2010000
10:20:21.7620000
15:56:58.6110000

添加

当我阅读原始问题时,我认为并没有必要确保所有生成的随机数都是唯一的。 我将问题中的“不同”一词解释为含糊不清,与使用简单的SELECT RAND()时在结果的每一行中看到相同的数字相反。 我认为,在许多情况下,是否有很少的冲突随机数并不重要。 在许多情况下,这实际上是正确的行为。

因此,我的理解是,当需要一系列唯一的随机数时,从某种意义上讲,它等效于以下任务。 我们有一些值/行的集合,例如,一组唯一的ID或一天中的所有86400秒或给定日期的2800行。 我们想改组这些值/行。 我们想以随机顺序重新排列这些行。

要改组给定的行集,我们只需要对随机数进行ORDER BY (这些随机数在这里可能有合理的冲突量)。 随机数可以通过任何方法生成。 像这样的东西:

ROW_NUMBER() OVER ([optional PARTITION BY ...] ORDER BY CRYPT_GEN_RANDOM(4)) 

或字面上

SELECT ...
FROM ...
ORDER BY CRYPT_GEN_RANDOM(4)

取决于在何处以及如何使用它。

回答5

测试一下:

 Declare @t table(ID int,CREATED_DATE datetime)
insert into @t values
 (1 ,  '04/26/2014'),
 (2 ,  '04/26/2014'),
 (3 ,  '04/26/2014'),
 (4 ,  '04/26/2014')

 ;WITH CTE AS
 (
   SELECT *,CONVERT(VARCHAR, DATEADD(SECOND, RAND(CAST(NEWID() AS VARBINARY)) * 43200, 
   CAST('08:00:00' AS TIME)),114) AS [New Time] FROM @t WHERE ID=1
   UNION ALL
   SELECT *,CONVERT(VARCHAR, DATEADD(SECOND, RAND(CAST(NEWID() AS VARBINARY)) * 43200, 
   CAST('08:00:00' AS TIME)), 114)  FROM @t WHERE ID>1 AND ID<=5
 )
 SELECT * FROM CTE
回答6

这是另一个选项,可让您更好地控制时间的生成方式。 您可以指定随机时间之间的间隔。 它还不使用RAND函数。

DECLARE @StartTime  VARCHAR(10) = '08:00',
        @EndTime    VARCHAR(10) = '20:00',
        @Interval   INT = 5 --(In Seconds)

WITH times AS(
    SELECT CONVERT(TIME, @StartTime) AS t
    UNION ALL
    SELECT DATEADD(SECOND, @Interval, t)
    FROM times
    WHERE t < @EndTime
)

SELECT *, 
(SELECT TOP 1 t FROM times WHERE d.Id > 0 ORDER BY NEWID())
FROM #data d
option (maxrecursion 0)

附带说明:
如果在上面的子查询中删除WHERE子句( WHERE d.Id > 0 ),则为所有行返回相同的时间值,即与您开始时遇到的相同问题

回答7

全部,

我以为我会分享我的问题的答案。 我不记得确切在哪里找到了细节-我认为这是通过sgeddes提供的链接之一进行的。

我使用以下查询来获取大约上午8点至晚上7:55之间的随机时间(大约)

SELECT convert(varchar,CONVERT(varchar, DATEADD(ms, dbo.MyRand(335 ,830) * 86400, 0), 114),114)

MyRand函数如下:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.myRand(@Min INT, @Max INT) RETURNS decimal(18,15) AS
BEGIN
DECLARE @BinaryFloat BINARY(8)
SELECT @BinaryFloat = CAST(Id AS BINARY) FROM vwGuid

DECLARE
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT,
@RandomNumber FLOAT

SELECT
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

WHILE @Part <= 8
BEGIN
SELECT
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =

WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

SELECT
@Mask = @Mask / 2
END
END

SET @RandomNumber = CASE @Exponent WHEN 0 THEN 0 ELSE CAST(@Exponent AS FLOAT) / 2047 END

RETURN CAST((@RandomNumber * (@Max - @Min)) + @Min AS DECIMAL(18,15))

END
GO
END

我希望这有帮助。 如果有人有更好的答案,我还没有阅读上面的许多回复,所以我很抱歉-这就是我解决问题的方式。

谢谢

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐