Генерировать различное случайное время в данном интервале

Ответ 1

Интерпретация исходного вопроса:

В вопросе говорится:

• Генерировать случайное время между 8:00 и 20:00 (т.е. 12-часовое окно)
• Он должен отличаться для каждой строки (т.е. уникальной для всех строк)
• В реальной таблице содержится около 2800 записей

Теперь коэффициент в следующих пунктах:

• Пример данных показывает только одну дату
• Есть 86 400 секунд в течение 24 часов, следовательно, 43 200 секунд через 12 часов

Существует некоторая двусмысленность в следующих областях:

• Что именно является случайным в контексте "по-разному для каждой строки", учитывая, что по-настоящему случайные значения не могут быть гарантированы для разных строк. Фактически, действительно случайные числа теоретически могут быть одинаковыми для каждой строки. Так что акцент делается на "случайных" или "разных"? Или мы действительно говорим о разных, но не последовательно упорядоченных (чтобы представить случайность без фактического случайности)?
• Что делать, если есть более 2800 строк? Что делать, если есть 1 миллион строк?
• Если может быть более 43 200 строк, как обрабатывать "разные для каждой строки" (поскольку невозможно иметь уникальные по всем строкам)?
• Будет ли когда-нибудь изменяться дата? Если да, действительно ли мы говорим о "разных для каждой строки в день"?
• Если для каждой строки на дату "разные":
  • Могут ли времена для каждой даты следовать одному и тому же непоследовательному шаблону? Или шаблон должен отличаться для каждой даты?
  • Будет ли когда-либо более 43 200 строк для какой-либо конкретной даты? Если это так, время может быть уникальным только для каждого набора из 43 200 строк.

Учитывая приведенную выше информацию, есть несколько способов интерпретировать запрос:

• Акцент на "случайный": Даты и количество строк не имеют значения. Создавайте поистине случайные времена, которые весьма вероятны, но не гарантируются, чтобы быть уникальными, используя один из трех методов, показанных в других ответах:
  • @notulysses: RAND(CAST(NEWID() AS VARBINARY)) * 43200
  • @Steve Ford: ABS(CHECKSUM(NewId()) % 43201)
  • @Владимир Баранов: CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) as int)
• Акцент на "разные для каждой строки" , всегда <= 43,200 строк: Если количество строк никогда не превышает количество доступных секунд, легко гарантировать уникальное время для всех строк, независимо от того же или разных дат и, как представляется, упорядочены произвольно.
• Акцент на "разные для каждой строки" , может быть > 43,200 строк:. Если количество строк может превышать количество доступных секунд, то невозможно гарантировать уникальность во всех строках, но можно было бы гарантировать уникальность между строками любой конкретной даты, при условии, что никакая конкретная дата не имеет > 43.200 строк.

Следовательно, я основывал свой ответ на мысли, что:

• Даже если количество строк для OP никогда не превышает 2800, более вероятно, что большинство других, столкнувшихся с аналогичной потребностью в случайности, будет иметь больший набор данных для работы (т.е. может быть легко 1 миллион строк, для любого количества дат: 1, 5000 и т.д.).
• Либо выборочные данные слишком упрощены при использовании одной и той же даты для всех 5 строк, либо даже если дата одинакова для всех строк в этом конкретном случае, в большинстве других случаев, которые менее вероятно, произойдет
• Уникальность должна быть предпочтительной по сравнению с Randomness
• Если есть шаблон для "кажущегося случайного" упорядочения секунд для каждой даты, должно быть, по крайней мере, переменное смещение к началу последовательности по датам (когда даты упорядочиваются последовательно), чтобы дать появление случайности между любой небольшой группировкой дат.

Ответ:

Если ситуация требует уникальных времен, это не может быть гарантировано каким-либо методом генерации действительно случайных значений. Мне очень нравится использование CRYPT_GEN_RANDOM byVladimir Baranov, но почти невозможно получить уникальный набор генерируемых значений:

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;

Тем не менее, все становится немного сложнее, если в каждый день требуется уникальность (что кажется разумным требованием этого типа проекта, а не уникальным в течение всех дней). Но генератор случайных чисел не будет знать до reset в каждый новый день.

Если приемлемо просто иметь случайность, то мы можем гарантировать уникальность за каждую дату без:

• конструкции цикла/курсора
• сохранение уже используемых значений в таблице
• с помощью RAND(), NEWID() или CRYPT_GEN_RANDOM()

В следующем решении используется понятие Модульные мультипликативные образы (MMI), о которых я узнал в этом ответе: генерирует, по-видимому, случайный уникальный числовой идентификатор в SQL Server. Конечно, у этого вопроса не было четко определенного диапазона значений, как у нас здесь, и только 86 400 из них в день. Итак, я использовал диапазон 86400 (как "Modulo" ) и попробовал несколько "взаимных" значений (как "Integer" ) в онлайн-калькулятор получить их MMI:

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

Я использую ROW_NUMBER() в CTE, секционированном (т.е. сгруппированном) на CREATED_DATE в качестве средства присвоения каждой секунде дня значения.

Но, в то время как значения, сгенерированные за секунды 0, 1, 2,... и т.д., последовательно будут отображаться случайными, в разные дни, когда конкретная секунда будет отображать одно значение. Таким образом, второй CTE (называемый "WhatSecond" ) сдвигает начальную точку для каждой даты, преобразуя дату в 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 и 20:00, нам нужно сделать несколько незначительных корректировок:

• Измените диапазон (как "Modulo" ) с 86400 до половины: 43200
• Пересчитать MMI (можно использовать одни и те же "взаимные значения" как "Целое число" ): 39539 (как и раньше)
• Добавьте 28800 ко второму параметру DATEADD в качестве смещения за 8 часов

Результат будет изменен только на одну строку (так как остальные являются диагностическими):

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

Другим средством переключения каждого дня менее прогнозируемым способом было бы использовать RAND(), передав в форме INT CREATED_DATE в CTE "WhatSecond". Это дало бы стабильное смещение за каждую дату, так как RAND(x) вернет то же значение y для того же значения x, которое прошло, но вернет другое значение y для другого значения x пройденного Значение:

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

Во второй раз, когда был вызван RAND(2), он по-прежнему возвращал то же значение y2, что он возвращался при первом вызове.

Следовательно, CTE "WhichSecond" может быть:

(
  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 Database Engine присваивает начальное значение случайным образом. Для заданного начального значения возвращаемый результат всегда один и тот же.

Подход, описанный ниже, удаляет оптимизацию и подавляет это поведение, поэтому rand() оценивается один раз в строке:

dateadd( second
       , rand(cast(newid() as varbinary)) * 43200
       , cast('08:00:00' as time) )

• newid() генерирует уникальное значение типа uniqueidentifier;
• значение преобразуется с помощью cast для использования в качестве семени в rand([seed]) для генерации псевдослучайного значения float от 0 до 1, и поскольку семя всегда уникально, возвращаемое значение является уникальным.

SQLFiddle

Ответ 3

В качестве альтернативы вы можете использовать:

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

ABS(CHECKSUM(NewId()) % 43201) генерирует случайное число между 0 и 43200. См. Обсуждение здесь.

SQL Fiddle

Настройка схемы 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. Один из лучших способов сделать его более или менее равномерно распределенным - через CHECKSUM: RAND(CHECKSUM(NEWID())). Хорошая вещь об этом методе заключается в том, что функция NEWID доступна с SQL Server 2000.
• Вместо 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 из них все разные. CHECKSUM of MD5 приводит к 122 столкновениям. RAND этого CHECKSUM приводит к 246 столкновениям. При тестировании с номерами строк от 1 до 100 000 CHECKSUM было 1 столкновение, RAND имел 3 столкновения.
• Другая возможность - просто реализовать свою собственную пользовательскую функцию в T-SQL, которая генерирует случайное число, используя ваш предпочтительный алгоритм. В этом случае вы полностью контролируете все. Обычно псевдослучайные генераторы должны сохранять свое внутреннее состояние между вызовами, поэтому вы можете получить выделенную таблицу, в которой хранятся эти данные.
• Вы можете написать свою пользовательскую функцию с помощью CLR. В этом случае вы можете реализовать собственный генератор или использовать функции, встроенные в .NET, например Random class, или RNGCryptoServiceProvider класс.
• Наконец, поскольку SQL Server 2008 имеет встроенную функцию CRYPT_GEN_RANDOM.

Я подробно опишу последний метод, потому что считаю, что это очень хорошее решение для SQL Server 2008 и выше. CRYPT_GEN_RANDOM вызывается для каждой строки результирующего набора, в отличие от RAND, который вызывается только один раз.

CRYPT_GEN_RANDOM (Transact-SQL)

Возвращает криптографическое случайное число, сгенерированное Crypto API (АЛО). Вывод представляет собой шестнадцатеричное число указанного числа байтов.

Кроме того, CRYPT_GEN_RANDOM должен обеспечивать гораздо лучшие случайные значения, чем RAND. Лучше с точки зрения распространения и криптосилы. Пример:

(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(). Я думаю, что во многих случаях не имеет значения, есть ли несколько встречных случайных чисел. Во многих случаях это было бы правильное поведение.

Итак, я понимаю, что, когда существует необходимость в последовательности уникальных случайных чисел, это в некотором смысле эквивалентно следующей задаче. У нас есть набор некоторых значений/строк, например набор уникальных идентификаторов или всего 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:00 и 7:55 pm (примерно)

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([email protected] * @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

Надеюсь, это поможет. Я не прочитал много ответов выше, поэтому извиняюсь, если у кого-то есть лучший ответ - это просто, как я его решил.

Спасибо