Sql 语句小课堂7：在sqlserver对多行数据实施随机数

首先，sqlserver里提供了随机函数rand，但这东西是个大坑，每次单独使用没问题，但是在一个查询中出现多行结果时，这就是个坑了，因为rand的优先级比查询指令高！所以，结果就会如下图这样了

然后，老顾想自己写个随机函数，使用rand和newid来结合，产生一个float值，结果，函数没办法写，sqlserver报错了

消息 443，级别 16，状态 1，过程 rnd，行 13 [批起始行 15]
在函数内对带副作用的运算符 ‘newid’ 的使用无效。
消息 443，级别 16，状态 1，过程 rnd，行 25 [批起始行 15]
在函数内对带副作用的运算符 ‘rand’ 的使用无效。

原因还是一样的，sqlserver中，不能存在不确定的值，1就是1，2就是2，你给我个不确定函数得到的内容，sqlserver不给承认~~

但是没关系，不能直接用rand，但用cross结合newid和rand，一样来实现随机值

select number,rnd.*
from master..spt_values
cross apply(
	select rnd 
	from (
		select convert(varchar(50),newid()) guid
	) a
	cross apply (
		select rand(
			convert(
				bigint
				,convert(
					varbinary
					,cast(N'0x' + substring(guid,0,charindex('-',guid)) as char)
					,1
				)
			)%2147483647
		) rnd
	) b
) rnd
where type='p'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

思路也很简单，使用newid生成一个序列值，并取第一个减号前的内容转成10进制数字作为rand的种子，得到随机数

需要注意各个类型转换不能缺少，newid()得到的类型是uniqueidentifier，不能使用字符串处理函数，需要先转成字符串才能截取减号前的内容

另外就是，转成十进制数字时，有时会超出int的取值范围，所以，我在这里用取余来得到一个确定的int类型的值，毕竟rand函数不支持bigint作为参数

那么，既然可以实现了各行的随机值不相同了，那么，咱们就用一个模拟需求来看看效果如何

先描述一下需求：

对0到2047这2000多个数字进行随机分布，分布到连续的30天中，早8点至晚10点的时间段内

因为有两个分布内容，一个是日期段，一个是时间段，所以我们使用两个cross来取两个随机数

select number,日期.*,时间.*
from master..spt_values
cross apply(
	select rnd 
	from (
		select convert(varchar(50),newid()) guid
	) a
	cross apply (
		select rand(
			convert(
				bigint
				,convert(
					varbinary
					,cast(N'0x' + substring(guid,0,charindex('-',guid)) as char)
					,1
				)
			)%2147483647
		) rnd
	) b
) 日期
cross apply(
	select rnd 
	from (
		select convert(varchar(50),newid()) guid
	) a
	cross apply (
		select rand(
			convert(
				bigint
				,convert(
					varbinary
					,cast(N'0x' + substring(guid,0,charindex('-',guid)) as char)
					,1
				)
			)%2147483647
		) rnd
	) b
) 时间
where type='p'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

根据两个随机数，然后用dateadd来运算出日期和时间

select number,日期.*,时间.*,x.*
from master..spt_values
cross apply(
	select rnd 
	from (
		select convert(varchar(50),newid()) guid
	) a
	cross apply (
		select rand(
			convert(
				bigint
				,convert(
					varbinary
					,cast(N'0x' + substring(guid,0,charindex('-',guid)) as char)
					,1
				)
			)%2147483647
		) rnd
	) b
) 日期
cross apply(
	select rnd 
	from (
		select convert(varchar(50),newid()) guid
	) a
	cross apply (
		select rand(
			convert(
				bigint
				,convert(
					varbinary
					,cast(N'0x' + substring(guid,0,charindex('-',guid)) as char)
					,1
				)
			)%2147483647
		) rnd
	) b
) 时间
cross apply (
	select dateadd(second,时间.rnd*60*60*13,dateadd(hour,8,dateadd(d,floor(日期.rnd*31),'2022-7-29'))) d
) x
where type='p'
order by d

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

通过这个简单的示例，我们可以将任意多的数据作出随机分布出来，通过命令，也可以直接伪造一些点击、访问或注册数据出来了

由于上边这个写法太复杂了，所以做一个newid到rand之间的中间函数

Create FUNCTION [dbo].[GuidToInt] 
(
	@guid uniqueidentifier
)
RETURNS int
AS
BEGIN
	DECLARE @r int,@s varchar(50)

	select @s = convert(varchar(50),@guid)
	
	select @r = convert(
		bigint
		,convert(
			varbinary
			,cast(N'0x' + substring(@s,0,charindex('-',@s)) as char)
			,1
		)
	)%2147483647

	RETURN @r

END

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

然后，我们的指令就可以变形一下了

select number,日期.*,x.*
from master..spt_values
cross apply(
	select rand(master.dbo.GuidToInt(newid())) rnd1
		,rand(master.dbo.GuidToInt(newid())) rnd2
) 日期
cross apply (
	select dateadd(ms,rnd1*60*60*13000,dateadd(hour,8,dateadd(d,floor(rnd2*31),'2022-7-29'))) d
) x
where type='p'
order by d

1
2
3
4
5
6
7
8
9
10
11
12

这样就可以很方便的对多行数据进行随机数实施了。

---

写完之后，才发现，还有一个checksum 函数，用来计算校验和，可以用作中间函数，即

select rand(checksum(newid())) --即可得到适用的随机数了
1

现在号尴尬啊。。。。