窗口函数之虚拟数字辅助表
数字辅助表是一个整数序列,可以用它来完成多种不同的查询任务。数字表有很多任务,如生成日期和时间值序列,及分裂值列表。通常,建议在数据库中保存这样一个永久表,并填充尽可能多的数字,然后需要的时候查询它,然而,在某些环境中,我们没有机会创建和填充新的表,以及需要的查询逻辑。
下面函数摘自 T-SQL性能调优秘笈——基于SQLServer2012窗口函数
创建虚拟数字辅助函数
use master --根据实际需要修改要保存的库中
go
--判断是否已存在 创建函数名、如果存在则执行删除操作
if OBJECT_ID(‘dbo.GetNums‘,‘IF‘) is not null drop function dbo.GetNums
go
--创建函数 GetNums 并指定 两个参数及返回值类型 这里以table 的形式返回
create function dbo.GetNums(@low as bigint ,@high as bigint) returns table
as
--函数体
return
with
--新建L0 内容中添加2行
L0 as(select C from(values(1),(1)) as D(c)),
--新建L1 数据来源L0 并连接L0 得到4行数据,L1的数据量为L0的 二次方
L1 as(select 1 as C from L0 cross join L0 as B),
L2 as(select 1 as C from L1 cross join L1 as B),
L3 as(select 1 as C from L2 cross join L2 as B),
L4 as(select 1 as C from L3 cross join L3 as B),
--至此 L5的数据达到了 2^2^2^2^2 数据量达到 4,294,967,296 行数据
L5 as(select 1 as C from L4 cross join L4 as B),
--将L5数据排序 使用带 order by (select null) 的row_number() 生成实际的数字
Nums as (select row_number() over(order by (select null)) as rownum from L5)
--根据 参数@low,@high 限定实际的输出行数。
/** SQLServer 2012 后的写法,SQLServer 2012 添加了 offset /fetch 选项 **/
select @low+rownum -1 as n from Nums order by rownum
offset 0 rows fetch first @high - @low +1 rows only;
/** SQLServer 2012 以前**/
--select top(@high -@low +1) @low+rownum -1 as n from Nums order by rownums;
go
测试函数生成效果
- 获取范围在11-50 列
select n from dbo.GetNums(11,50)
- 性能测试 获取0-1000000内列
select n from dbo.GetNums(0,1000000) --执行时间在10S左右
- 生成日期序列
--设定参数 起始时间 和结束时间
--请自行查阅 SQLServer中 日期函数
declare
@start as date = ‘20210701‘,
@end as date =‘20210901‘
--得到 每天 日期序列
select dateadd(day,n,@start) as date from dbo.GetNums(0,datediff(day,@start,@end))
--得到没12小时 时间间隔的日期序列
select dateadd(HOUR,n*12,@start) as date from dbo.GetNums(0,datediff(HOUR,@start,@end)/12)
--通过修改 date 函数中时间得到不同时间间隔时间序列,比如,年,月,周,天,时,分,秒
生成样本数据
使用虚拟数字辅助表 实现生成样本数据,以供测试使用
新建两张数据表用来模拟 存储银行的账户信息、和交易流水
- 创建表表结构
if OBJECT_ID(‘dbo.transactions‘,‘U‘) is not null drop table transactions;
if OBJECT_ID(‘dbo.accounts‘,‘U‘) is not null drop table accounts;
create table dbo.accounts(
actid int not null, --用户id
actname varchar(50) not null, --用户名
constraint pk_accounts primary key(actid)
);
create table dbo.transactions(
actid int not null, --用户id
tranid int not null, --消费流水号
val money not null, --消费值
trandate datetime not null, --日期
constraint pk_transactions primary key(actid,tranid),
constraint fk_transactions_accounts
foreign key(actid)
references dbo.accounts(actid)
);
根据虚拟数字辅助函数,生成样本数据、并添加到数据表中
在accounts 中添加100个账户,并在transactions 表中为每个账户生成 20000笔交易
- 添加账户信息
--添加100个用户账户,并将名称 命名为 account+序列号
insert into dbo.accounts with(tablock)(actid,actname)
select n as actid,‘account‘+cast(n as varchar(10)) as actname
from dbo.GetNums(1,100)
- 添加消费流水记录
--为每个账号添加20000个交易记录,由于交易时间不是固定的这里使用随机数 rand()进行日期拼接
insert into dbo.transactions with(tablock)(actid,tranid,val,trandate)
select a.n as actid,t.n as tranid,
(abs(CHECKSUM(newid())%2)*2-1)*(abs(CHECKSUM(newid())%1300)) as val, --随机消费值
--由于消费记录不一定在固定的时间点,这里用的随机生成日期形式。
convert(datetime,
datediff(day,‘1900-01-01‘,‘2010-01-01‘) --计算 1900-01-01 到 开始日期
+ abs(CHECKSUM(newid()))%datediff(day,‘2010-01-01‘,‘2021-09-01‘) --开始时间到随机结束时间的随机数
+(abs(CHECKSUM(newid())%86400000)*0.00000001)) --生成时分秒时间 86400000为每天的固定毫秒数
as trandate
from dbo.GetNums(1,100) as a
cross join dbo.GetNums(1,20000) as t
这里的随机数尝试用 rand()方法 生成时间日期整张表相同,随之弃用,也尝试封装过值函数
newid() 关键字 显示报错 在函数内对带副作用的运算符 ‘newid‘ 的使用无效。
然后才想到这种办法
核实表数据,发现好多数据不合常理,比如:消费流水号 tranid 与 日期不符,
--修改数据让数据更合理 利用窗口函数排序进行修改参数
with g as(
select actid,tranid,val,trandate,
ROW_NUMBER() over(partition by actid order by trandate) as rownum
from transactions
)
update g set g.tranid=rownum
修改完成后 数据看起来还比较合理 有一个地方需要注意 消费流水号 前几行相加出现负数的情况,可以通过修改流水号,让数据看起来是某一短时间值,比如:在流水号 加一个固定的值,或者在添加数据时 修改 GetNums() 中的参数 例如:cross join dbo.GetNums(500000,520000) as t
这样就比较合理了
下篇文章介绍SQLServer 的几个窗口函数,数据基于 添加样本数据表transactions