SQL 如何在给定范围之间插入 1000 个随机日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9645348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert 1000 random dates between a given range?
提问by user1260815
I am new to sql server. I need to generate random dates selected from a given date range.
Like the date of employment of an employee should be anywhere between 2011-01-01and 2011-12-31. The generated dates should be inserted into a 1000 row table randomly.
我是 sql server 的新手。我需要生成从给定日期范围中选择的随机日期。就像雇员的受雇日期应该介于2011-01-01和之间2011-12-31。生成的日期应该随机插入到一个 1000 行的表中。
Can any one guide me with my query?
任何人都可以指导我进行查询吗？
回答by Mikael Eriksson
declare @FromDate date = '2011-01-01'
declare @ToDate date = '2011-12-31'
select dateadd(day,
rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)),
@FromDate)
回答by DxTx
You can simply use this query.
您可以简单地使用此查询。
DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01')
If you want to add dates from a different timeline, you can change 01-01-2011and 364. 364is equal to days you want to add. In this case, it's between 01-01-2011and 31-12-2011.
(31-12-2011is also included.)
如果您想从不同的时间轴添加日期，您可以更改01-01-2011和364。364等于您要添加的天数。在这种情况下，它介于01-01-2011和之间31-12-2011。
（31-12-2011也包括在内。）
For example, let say you want to add a random date between 2018-01-01and 2018-01-31, you can change the query like this.
例如，假设您想在2018-01-01和之间添加一个随机日期2018-01-31，您可以像这样更改查询。
DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 31), '2018-01-01')
To insert (one row/date), simply use this...
要插入（一行/日期），只需使用此...
DECLARE @rdate DATE = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 31), '2018-01-01')
INSERT INTO TableName ([DateColumn])
VALUES (@rdate);
Output
输出
+-----+------------+
| ID | DateColumn |
+-----+------------+
| 01 | 2018-01-21 |
+-----+------------+
Online Demo: SQLFiddle.com
在线演示：SQLFiddle.com
To insert 1000 rows at once...
一次插入 1000 行...
DECLARE @rdate DATE
DECLARE @startLoopID INT = 1
DECLARE @endLoopID INT = 1000 -- Rows you want to add
WHILE @startLoopID <= @endLoopID
BEGIN
SET @rdate = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01');
SET @startLoopID = @startLoopID + 1;
INSERT INTO TableName ([DateColumn])
VALUES (@rdate);
END
Output
输出
+--------+------------+
| ID | DateColumn |
+--------+------------+
| 10000 | 2010-04-07 |
| 10001 | 2010-07-29 |
| 10002 | 2010-11-18 |
| 10003 | 2010-05-27 |
| 10004 | 2010-01-31 |
| 10005 | 2010-08-26 |
| ? | ? |
| 20000 | 2010-06-26 |
+--------+------------+
Online Demo: SQLFiddle.com
在线演示：SQLFiddle.com
To update existing rows...
要更新现有行...
UPDATE TableName
SET [DateColumn] = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01')
WHERE condition;
Online Demo: SQLFiddle.com
在线演示：SQLFiddle.com
回答by dani herrera
I have write to you this simple function that returns a random date between date range:
我给你写了这个简单的函数，它返回日期范围之间的随机日期：
create function date_rand ( @fromDate date, @toDate date) returns date
as
begin
declare @days_between int
declare @days_rand int
set @days_between = datediff(day,@fromDate,@toDate)
set @days_rand = cast(RAND()*10000 as int) % @days_between
return dateadd( day, @days_rand, @fromDate )
end
to call the function:
调用函数：
select dbo.date_rand( '1/1/2001', '10/1/2001' )
you can combine function with a row generator:
您可以将函数与行生成器结合使用：
;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT dbo.date_rand( '1/1/2001', '10/1/2001' )
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 1000
EDITED
已编辑
To generate random numbers use:
要生成随机数，请使用：
RAND(CHECKSUM(NEWID()))
instead of RAND()
而不是 RAND()
EDITED II
编辑二
Function returns 'Invalid use of a side-effecting operator 'rand' within a function' error. THis is because we can not use non-deterministic functions like RAND() or NEWID().
函数返回“在函数内无效使用副作用运算符‘rand’”错误。这是因为我们不能使用像 RAND() 或 NEWID() 这样的非确定性函数。
A workaround is to create a view like:
一种解决方法是创建一个视图，如：
create view myRandomNumber as
select cast( RAND(CHECKSUM(NEWID()))*1000 as int) as new_rand
and then use it in function:
然后在函数中使用它：
...
select @days_rand = new_rand % @days_between from myRandomNumber
...
or simple don't use the function and write expresion on select. I have write a function only yo explain step by step the solucion.
或者简单的不使用函数并在选择上写表达式。我已经写了一个函数，只能一步一步解释解决方案。
declare @fromdate date
declare @todate date
set @fromdate = '1/1/2001'
set @todate = '10/1/2001'
;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT
dateadd( day,
cast( RAND(CHECKSUM(NEWID()))*1000 as int) %
datediff(day,@fromDate,@toDate),
@fromDate )
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 1000
You can test here this query.
您可以在此处测试此查询。
回答by Zohar Peled
Well, I know that this is an old question, but it was linked from a newer one so... Here are my 2 cents:
好吧，我知道这是一个老问题，但它与一个新问题相关联，所以......这是我的 2 美分：
- Database tables are unsorted by nature.
- There are only 365 possible dates in a given year, 366 if it's a leap year.
- Duplicated data is a sign of poor design.
- 数据库表本质上是未排序的。
- 给定年份中只有 365 个可能的日期，如果是闰年则为 366 个。
- 重复的数据是设计不佳的标志。
Based on these premises, I believe that there really is no actual need to store a 1000 random dates in a table, when it's possible to store only the relevant date and just select how many rows and in whatever order you need.
基于这些前提，我相信实际上没有必要在表中存储 1000 个随机日期，因为可以仅存储相关日期，并且只需选择您需要的行数和顺序。
First, store the data inside the table. you can use a Tally tableto create the relevant date range.
A Tally table is a table containing a sequence of numbers. for argument's sake, let's assume you already created your tally table of numbers between 0 to 1,000,000.
You can check this linkfor the best way to create one, personally I like this method:
首先，将数据存储在表中。您可以使用Tally 表来创建相关的日期范围。
Tally 表是一个包含数字序列的表。为了论证起见，我们假设您已经创建了 0 到 1,000,000 之间的数字计数表。
您可以查看此链接以获取创建一个的最佳方式，我个人喜欢这种方法：
-- create the tally table
SELECT TOP 100000 IDENTITY (int ,0, 1) as num
INTO Tally
FROM sys.sysobjects
CROSS JOIN sys.all_columns
Now that you have the Tally table, it's fairly simple to create a calendar:
现在您有了 Tally 表，创建日历就相当简单了：
DECLARE @FromDate datetime = GETDATE(),
@ToDate datetime = DATEADD(YEAR, 1, GETDATE()) -- a year from now in my example
;With CalendarCTE AS
(
SELECT DATEADD(DAY, num, @FromDate) As caneldarDate
FROM Tally
WHERE num < DATEDIFF(DAY, @FromDate, @ToDate)
)
Now that you have the calendar and the tally table, it's fairly simple to use them both to get any number of records in any order you want. A thousand randomly-ordered dates? no problem:
既然您有了日历和计数表，就可以很容易地使用它们以您想要的任何顺序获取任意数量的记录。一千个随机排序的日期？没问题：
SELECT TOP 1000 caneldarDate
FROM CalendarCTE c
CROSS JOIN Tally t
WHERE t.num < 1000
ORDER BY NEWID()
Full script, including creating and dropping the tally table took less then a second to execute:
完整脚本，包括创建和删除计数表的执行时间不到一秒钟：
-- create the tally table
SELECT TOP 100000 IDENTITY (int ,0, 1) as num
INTO Tally
FROM sys.sysobjects
CROSS JOIN sys.all_columns
-- crealte the calendar cte:
DECLARE @FromDate datetime = GETDATE(),
@ToDate datetime = DATEADD(YEAR, 1, GETDATE())
;With CalendarCTE AS
(
SELECT DATEADD(DAY, num, @FromDate) As caneldarDate
FROM Tally
WHERE num < DATEDIFF(DAY, @FromDate, @ToDate)
)
-- select a 1000 random dates
SELECT TOP 1000 caneldarDate
FROM CalendarCTE c
CROSS JOIN Tally t
WHERE t.num < 1000
ORDER BY NEWID()
-- cleanup
DROP TABLE Tally
