복붙노트

[SQL] 고정 간격 고유 랜덤 시간 세대

SQL

고정 간격 고유 랜덤 시간 세대

나는 그러나, 나는 항상 각 행에 대해 동일한 임의의 값을 얻을 8:00 AM 및 데이터 세트에서 선택되는 각 행 8:00시 사이에 임의의 시간을 생성하기 위해 노력하고있어

해결법

  1. ==============================

    1.질문 상태 :

    질문 상태 :

    다음과 같은 점에서 지금 요인 :

    다음과 같은 분야에서 약간의 모호함이있다 :

    위의 정보를 감안할 때, 요청을 해석 할 수있는 몇 가지 방법이 있습니다 :

    따라서, 나는 생각이 내 대답을 기반으로 :

    상황이 고유의 시간을 필요로하는 경우, 그 진정 랜덤 값을 생성 어떤 방법으로 보장 할 수 없습니다. 정말 @Vladimir하기 Baranov에 의해 CRYPT_GEN_RANDOM의 사용과 같은 I,하지만 생성 된 값의 고유 한을 얻을 거의 불가능하다 :

    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;

    그러나, 상황이 조금 까다을받을 경우 (이러한 유형의 프로젝트, 모든 일에 걸쳐 독특한 반대의 합리적인 요구 사항처럼 보인다) 매일마다 고유성이 필요합니다. 그러나 난수 생성기는 각각의 새로운 일에 다시 알고 않을 것입니다.

    그것은 단지 무작위 인의 모습을 받아 들일 경우, 우리는 각 날짜별로없이 유일성 보장 할 수 있습니다 :

    SQL Server의 겉보기에 임의의 고유 한 숫자 ID를 생성합니다 다음 솔루션은 내가이 답변에 대해 알게 모듈러 곱셈 역원 (MMI)의 개념을 사용합니다. 우리가 하루에 그 중 86,400 여기가 같은 물론, 그 질문은 값의 밀접하게 정의 된 범위를 가지고 있지 않았다. 그래서, 난 ( "모듈로"로) 86400의 범위를 사용하고 자신의 MMIS를 얻기 위해 온라인 계산기에서 ( "정수"로) 몇 "서로 소"값을 시도 :

    I 하루 값 각 초를 할당하는 수단으로 CREATED_DATE 의해 구획 된 CTE에 ROW_NUMBER () (즉, 그룹화)을 사용한다.

    그러나, 숫자가 초 0, 1, 2에 대해 생성하는 동안, ... 등 순차적 특히 두번째 동일한 값으로 매핑하는 것을 다른 일에 걸쳐 무작위로 나타난다. 그래서, ( "WhichSecond"인) 제 CTE 101을 곱 후 (1900-01-01 오프셋 순차적으로 변환 날짜)는 INT에 날짜를 변환하여 각 날짜에 대한 시작 지점을 이동시킨다.

    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 시부 터 오후 8시 사이에 시간을 생성 할 경우, 우리는 몇 가지 사소한 조정을 할 필요가 :

    결과는 단 한 줄 (다른 진단 때문에)에 변경 될 것입니다 :

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

    덜 예측 가능한 방식으로 매일 변화의 또 다른 수단은 "WhichSecond"CTE에 CREATED_DATE의 INT의 형태로 전달하여 RAND ()의 사용을하는 것입니다. 이는 전달 (X)의 동일한 값에 대해 동일한 값 y를 반환 RAND (x)는 이후 각 날짜마다 오프셋 안정한 줄 것이지만 전달 (X)의 다른 값에 대해 서로 다른 값 y를 반환 의미한다. :

    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. ==============================

    2.문제의 영업 이익은 랜드 ()를 사용하여 쿼리 당 한번의 평가로 인해 동안했다.

    문제의 영업 이익은 랜드 ()를 사용하여 쿼리 당 한번의 평가로 인해 동안했다.

    문서에서 :

    () 열 번씩 평가 랜드되도록 제거한다 최적화 설명이 동작 억제된다 접근법 :

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

    SQLFiddle

  3. ==============================

    3.당신은 선택적으로 사용할 수 있습니다 :

    당신은 선택적으로 사용할 수 있습니다 :

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

    ABS는 (CHECKSUM (NEWID ()) % 43201)는 여기에 0 43200 참조 토론 사이의 임의의 숫자를 생성합니다.

    SQL 바이올린

    MS SQL 서버 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. ==============================

    4.몇 가지 방법이 있습니다 :

    몇 가지 방법이 있습니다 :

    나는 그것이 SQL Server 2008 및 이상를위한 아주 좋은 해결책이라고 생각 때문에, 구체적으로 마지막 방법을 설명합니다. 한 번만이라고 RAND, 반대로 CRYPT_GEN_RANDOM은 결과 집합의 각 행에 대해 호출됩니다.

    게다가, CRYPT_GEN_RANDOM는 RAND보다 훨씬 더 무작위 값을 제공해야합니다. 더 나은 유통 및 암호화 강도의 측면이다. 예:

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

    이 VARBINARY로 4 난수 바이트를 생성합니다. 명시 적으로 먼저 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

    여기에 (내가 @ 스티브 포드에 의해 다른 답변에서 쿼리를 사용) 복사 - 붙여 넣기 시도하기 쉬운 독립형 예입니다 :

    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 세트 또는 일의 모든 8만6천4백초 또는 2800 행, 일부 값 / 행 집합이 있습니다. 우리는이 값 / 행을 셔플하고 싶다. 우리는 무작위 순서로 이러한 행을 다시 정렬 할 수 있습니다.

    우리는 단순히 임의 번호로 주문해야 행의 지정된 세트를 섞으려면 (이 임의의 숫자는 여기 충돌의 합리적인 금액을 가질 수있다). 임의의 번호는 어떤 방법에 의해 생성 될 수있다. 이 같은:

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

    또는 문자

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

    에 따라 어디서 그것을 어떻게 사용됩니다.

  5. ==============================

    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. ==============================

    6.여기 당신에게 시간이 생성되는 방식을 좀 더 제어 할 다른 옵션입니다. 당신은 임의의 시간 사이의 간격을 지정할 수 있습니다. 또한 RAND 함수를 사용하지 않습니다.

    여기 당신에게 시간이 생성되는 방식을 좀 더 제어 할 다른 옵션입니다. 당신은 임의의 시간 사이의 간격을 지정할 수 있습니다. 또한 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 절을 제거 할 경우 (d.Id> 0 WHERE), 동시에 값이 모든 행에 대해 당신이 시작한 즉, 동일한 문제가 반환된다

  7. ==============================

    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

    이게 도움이 되길 바란다. 이것이 내가 그것을 해결 간단하게하는 방법이다 - 누군가가 더 나은 대답을 경우 내가 사과 그래서 위의 응답을 많이 읽지 못했어요.

    감사

    8. from https://stackoverflow.com/questions/23314054/distinct-random-time-generation-in-the-fixed-interval by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 왜 그렇게 느린 SQL 애저에 대한 쿼리를 실행?  (0) 2020.07.01
[SQL] 어떻게 SQL 테이블을 '빼기'합니까?  (0) 2020.07.01
[SQL] INSERT INTO TABLE은 쉼표에서 VARCHAR 목록을 분리  (0) 2020.07.01
[SQL] 때 당신은 테이블 반환 함수를 사용해야합니까? [닫은]  (0) 2020.07.01
[SQL] VARCHAR2 (n 개의 BYTE | CHAR) 기본 -> CHAR 또는 BYTE  (0) 2020.07.01
,

티스토리툴바