虛擬數字儲存表——SQLServer2012可高用

就著發表於2021-09-07
SQLServer

視窗函式之虛擬數字輔助表

數字輔助表是一個整數序列，可以用它來完成多種不同的查詢任務。數字表有很多工，如生成日期和時間值序列，及分裂值列表。通常，建議在資料庫中儲存這樣一個永久表，並填充儘可能多的數字，然後需要的時候查詢它，然而，在某些環境中，我們沒有機會建立和填充新的表，以及需要的查詢邏輯。

下面函式摘自 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

測試函式生成效果

  1. 獲取範圍在11-50 列
select n from dbo.GetNums(11,50)
  1. 效能測試 獲取0-1000000內列
select n from dbo.GetNums(0,1000000) --執行時間在10S左右
  1. 生成日期序列
--設定引數 起始時間 和結束時間
--請自行查閱 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 函式中時間得到不同時間間隔時間序列，比如，年，月，周，天，時，分，秒

生成樣本資料

使用虛擬數字輔助表 實現生成樣本資料，以供測試使用

新建兩張資料表用來模擬 儲存銀行的賬戶資訊、和交易流水

  1. 建立表表結構
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筆交易

  1. 新增賬戶資訊

--新增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)

使用者新增效果

  1. 新增消費流水記錄
--為每個賬號新增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

相關文章