sql-server – 为什么数字表“无价”？

发布时间：2020-12-12 06:16:14 所属栏目：MsSql教程来源：网络整理

导读：我们的居民 database expert告诉我们 numbers tables are invaluable.我不太明白为什么.这是一个数字表： USE ModelGOCREATE TABLE Numbers( Number INT NOT NULL,CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number) WITH FILLFACTOR = 100)INSERT INTO N

我们的居民 database expert告诉我们 numbers tables are invaluable.我不太明白为什么.这是一个数字表：

USE Model
GO

CREATE TABLE Numbers
(
    Number INT NOT NULL,CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)

INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM 
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) a (Number),(
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) b (Number)
GO

根据博客文章,给出的理由是

Numbers tables are truly invaluable. I use them all of the time for string manipulation,simulating window functions,populating test tables with lots of data,eliminating cursor logic,and many other tasks that would be incredibly difficult without them.

但我完全不明白这些用途是什么 – 你能提供一些引人注目的具体例子,说明“数字表”在SQL Server中为你节省了大量工作 – 以及为什么我们应该拥有它们？

解决方法

当你需要预测“缺失数据”时,我已经看到了很多用途.例如.你有一个时间序列(例如访问日志),你想显示过去30天每天的点击次数(想想分析仪表板).如果您按天从…组中选择计数(…),您将获得每天的计数,但结果只会在您实际拥有至少一次访问权限的每一天中有一行.另一方面,如果你首先从你的数字表中选择一个天数表(选择dateadd(day,-number,today)作为数字的日期)然后你离开加入计数(或外部应用,无论你喜欢什么)然后在没有访问权限的日子里,你会得到一个0的计数结果.这只是一个例子.当然,有人可能会争辩说,仪表板的表示层可以处理丢失的日期而只是显示0,但是某些工具(例如SSRS)根本无法处理这个问题.

我见过的其他例子使用了类似的时间序列技巧(日期/时间/ – 数字)来进行各种窗口计算.通常,每当使用命令式语言时,您将使用具有众所周知的迭代次数的for循环,SQL的声明性和集合性可以使用基于数字表的技巧.

顺便说一句,我觉得有必要提出一个事实,即使用数字表感觉就像命令式程序执行一样,不要陷入假设必要的谬误.让我举个例子：

int x;
for (int i=0;i<1000000;++i)
  x = i;
printf("%d",x);

这个程序将输出999999,这是非常有保证的.

让我们使用数字表在SQL中尝试相同的操作.首先创建一个包含1M个数字的表：

create table numbers (number int not null primary key);
go

declare @i int = 0,@j int = 0;

set nocount on;
begin transaction
while @i < 1000
begin
    set @j = 0;
    while @j < 1000
    begin
        insert into numbers (number) 
            values (@j*1000+@i);
        set @j += 1;
    end
    commit;
    raiserror (N'Inserted %d*1000',@i)
    begin transaction;
    set @i += 1;
end
commit
go

现在让我们做’for循环’：

declare @x int;
select @x = number 
from numbers with(nolock);
select @x as [@x];

结果是：

@x
-----------
88698

如果你现在有一个WTF时刻(在所有数字都是聚集的主键之后！),诀窍被称为allocation order scan并且我没有偶然插入@ j * 1000 @i …你也可以冒险猜测说结果是因为parallelism而且有时可能是正确的答案.

有很多trolls under this bridge,我在On SQL Server boolean operator short-circuit和T-SQL functions do no imply a certain order of execution提到了一些

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!