生成直方图

核心提示：直方图是一种统计报告，它可以显示在某个最小值和最大值之间的值的等级或范围内值出现的频率，生成直方图，让我们以学生的考试成绩结果为例来进行说明，假定有 30 个学生参加考试，这两个解决方案都表现得很好，第二个解决方案在持续时间和 I/O 方面都略胜一筹，最低分为 51，最高分为 100

直方图是一种统计报告，它可以显示在某个最小值和最大值之间的值的等级或范围内值出现的频率。让我们以学生的考试成绩结果为例来进行说明。假定有 30 个学生参加考试，最低分为 51，最高分为 100。您希望在最低分和最高分之间生成若干个等级，并计算每个等级内的结果数目，从而了解整个班级的分数分布情况。如果您希望生成五个范围大小相似的连续等级，则可得到如下的等级和范围：50 到 60、60 到 70、70 到 80、80 到 90、90 到 100（下限不包括在内，但包括上限）。该直方图包括各个等级以及每个等级内的结果数目。

同样，利用直方图，您可以分析网络中在服务器上设置的性能计数器（例如，CPU 利用率、内存）的值的采样。例如，假设您每隔五分钟就记录某一网络服务器的 CPU 利用率，某天测得的值在 21% 到 100% 之间变动。您可以生成有四个等级的直方图 — 20 到 40、40 到 60、60 到 80、80 到 100 — 从而得出属于每个等级的样本数。如果您的服务器在那一天超负荷地工作，那么大多数样本会属于第四个等级。

生成性能计数器直方图

下面是一个涉及直方图的问题；您可以看看自己是否能在读到我的解决方案之前就解决它。一个已调度的 sql server(WINDOWS平台上强大的数据库平台) 代理作业将在一个名为 Samples 的表中定期记录某网络服务器的性能计数器的采样。运行 sqlmag03/html/GeneratingHistogramsListing_01.txt" target="_blank">清单 1 显示的代码，创建 Samples 表并向其中填充数据。采样 (dt) 后，该表的每一行都包含度量 ID (measid)、度量值 (value) 和表示表中其他列的筛选列（100 字节）。（例如，该表通常包括一个 serverid 列，这样您可以记录多个服务器的度量样本。考虑到这个问题，在此示例中，让该表只包含一个服务器的数据。）

假设 measid 1 是 CPU 利用率百分比，measid 2 是以兆字节为单位的内存使用量。您的用户需要直方图来帮助他们分析在一段时期内某个度量的性能数据。用户提供了以下参数：等级数 (@numsteps)、度量 ID (@measid) 和日期范围（@fromdt — 包括起始日期，@todt — 不包括终止日期）。您的任务是生成这些给定参数的直方图。请注意，您不必在结果中包括样本数为 0 的等级。例如，假设用户提供了以下参数：

DECLARE @numsteps int, @measidint, @fromdt datetime, @todtdatetimeSELECT @numsteps=5, @measid=1,@fromdt='20030101', @todt='20030102'

在 Samples 表中，在给定期间内，measid 1 的最小度量值是 26，最大度量值是 50。在 @numsteps 参数中请求的等级数为 5。首先，您需要计算五个等级内各范围的下限和上限。因为范围的下限不包括在内，所以经过计算可得到以下范围：25 到 30、30 到 35、35 到 40、40 到 45、45 到 50。sqlmag03/html/GeneratingHistogramsFigure_01.asp" target="_blank">图 1 显示了给定参数的等级和范围。

您需要编写能告诉您每个等级有多少度量的代码 — 在本例中，您的代码应产生与等级 1 相匹配的一个度量 (26)、与等级 2 相匹配的两个度量（33、35）以及与等级 5 相匹配的两个度量（47、50）。 sqlmag03/html/GeneratingHistogramsFigure_02.asp" target="_blank">图 2 显示了利用这组特定参数产生的输出。以下是我针对此问题提供的一些解决方案。

解决方案 1：使用等级表

第一个解决方案涉及到编写一个生成派生表 Steps 的查询，该表包含等级号和每个等级的值的范围。生成这样一个表后，该解决方案就显得简单了。您需要完成的操作是：根据属于等级范围内的 value 列将派生表 Steps 联接到 Samples 表、根据等级号对结果进行分组、计算每组内的行数。这个解决方案中比较困难的地方就是编写生成派生表的查询。要生成等级号，可以使用一个名为 Nums 的辅助表，在该表中填充有一个范围为 1 到 <max_number_of_possible_steps> 的整数序列。运行 sqlmag03/html/GeneratingHistogramsListing_02.txt" target="_blank">清单 2 中的脚本可创建 Nums 辅助表，并向其中填充 1000 个整数。

要计算每个等级范围的下限和上限，需要交叉连接两个查询的结果：一个是针对 Nums 的查询，以返回等级号，另一个是针对 Samples 的查询，以返回最小度量值和最大度量值。产生的查询可能如 sqlmag03/html/GeneratingHistogramsListing_03.txt" target="_blank">清单3 所示。运行此查询之后，您会在sqlmag03/html/GeneratingHistogramsFigure_03.asp" target="_blank">图 3 显示的结果中注意到，该查询对每个等级号返回相同的常规最小度量值和最大度量值。这只是该解决方案中的中间结果；稍后您会将常规最小值和最大值与等级号一起使用，以便计算等级的最小值和最大值。

现在，您需要用返回的表达式替换 SELECT 列表中的星号，此外，还需要替换等级号以及等级范围的下限（不包括）和上限（包括）。等级号很简单，是 n。

以下代码是构建计算下限的表达式的基础：

mn + <step_size>*(n-1) - 1

其中，mn 是常规最小度量值，n 是等级号，step_size 是每个等级所覆盖范围的大小。用于计算 step_size 的表达式如下所示：

(mx-mn+1)/@numsteps

通过将这两个表达式结合在一起，您可以得到

mn + (mx-mn+1)/@numsteps*(n-1) - 1

如果您得到的值范围总是能用等级数来整除，那么，使用上面这个表达式就可以了，但在实际应用中，情况却并不总是如此。下面的表达式使用数值除法和舍入法，适用于不能用等级数整除的值范围：

mn + CAST(round(1.0*(mx-mn+1)/@numsteps*(n-1), 0) AS int) - 1

您可以通过将除法运算的第一个操作数与数值 1.0（读法是一点零）相乘来进行分数除法（而非整数除法），这意味着 sql server(WINDOWS平台上强大的数据库平台) 不会截断小数部分。然后，您需要对结果进行舍入，以便得到整个范围的上限和下限。之后，sql server(WINDOWS平台上强大的数据库平台) 将经过舍入的结果转换为整数，以便去掉小数点后没有意义的零。您可以用相似的方式来计算上限：

mn + CAST(round(1.0*(mx-mn+1)/@numsteps*n, 0)AS int) - 1

上述表达式与前面的表达式之间的唯一区别是它将等级大小与等级号 (n) 相乘，而不是与 n-1 相乘。要测试计算派生的 Steps 表的完整查询，请运行 sqlmag03/html/GeneratingHistogramsListing_04.txt" target="_blank">清单 4 中的代码；请注意，得到的结果与图 1 相同。

请随便试用此解决方案的代码中的参数，以便测试一下更改等级数对结果会有什么影响，等等。请记住，返回等级表的查询只是该解决方案的一部分。现在，您可以在下面的伪代码中看到缺少的、被表示为 <query that calculates steps> 的查询部分：

SELECT step, count(*) AS cntFROM Samples JOIN (<query that calculatessteps>) AS StepsON value > f AND value <= tWHERE measid = @measidAND dt >= @fromdt AND dt < @todtGROUP BY step

用清单 4 显示的查询来替换缺少的部分。 sqlmag03/html/GeneratingHistogramsListing_05.txt" target="_blank">清单 5 显示了完整的解决方案，它将生成您所需的、如图 2 所示的直方图。对于请求的计数器和时段，在分为五个等级的所有度量中，有一个度量 (26) 属于第一个等级的范围，有两个度量（33、35）属于第二个等级，有两个度量（47、50）属于第五个等级。

如果您像我一样喜欢使用模块化的开发方法来简化代码及其维护，则可以编写一个用户定义函数 (UDF)，该函数将等级数、度量 ID 和日期范围作为参数，并返回表 Steps。运行 sqlmag03/html/GeneratingHistogramsListing_06.txt" target="_blank">清单 6 显示的代码来创建 fn_steps 函数。

要测试该函数，请运行下面的代码，并验证您获得的结果是否如图 1 所示：

SELECT * FROM fn_steps(1, 5, '20030101', '20030102')

现在，您可以使用 fn_steps 表，而不是派生表 Steps，如 sqlmag03/html/GeneratingHistogramsListing_07.txt" target="_blank">清单 7 中的代码所示。您将得到如图 2 所示的直方图。

解决方案 2：实时计算等级号

这个难题还有另一个解决方案，该方案不生成等级表，而是实时计算等级号。利用此解决方案时，您无需使用等级号辅助表。请注意 sqlmag03/html/GeneratingHistogramsListing_08.txt" target="_blank">清单 8 （第 18 页）的标注 A 中的 FROM 子句。您将交叉连接两个派生表 — 一个表名为 S，包含来自 Samples 的、与提供的参数相匹配的行，另一个表名为 R，包含最小度量值和整个范围的大小。下面的表达式（在 SELECT 列表中进行编写）用于计算等级号：

floor((value-mn) / (1.0*range/@numsteps)) + 1AS step

此表达式使用的逻辑与您在前面的解决方案中用于计算上限和下限的逻辑很相似。表达式 (value-mn) 可计算出值在范围内的位置，而 (1.0*range/@numsteps) 可计算出等级大小。用第二个操作数除以第一个操作数，在结果的基础上再加上 1，就可以得出等级号。既然您已经向每行附加了一个等级号（匹配来自 Samples 表的条件），那么您就可以在名为 RS 的派生表中使用此查询，从而能够按计算出的等级号对结果进行分组。清单 8 显示了完整的解决方案，在该解决方案中，外部查询按等级号对结果进行分组，并计算每个等级的行数。

关于性能

比较一下这两个解决方案 — 一个使用辅助表和模块化方法，另一个实时计算等级号 — 可以看出第二个解决方案的代码较短且更为简洁。要发现哪个解决方案更好，可以通过使用 sqlmag03/html/GeneratingHistogramsListing_09.txt" target="_blank">清单 9 显示的脚本将 1 百万行填入 Samples 表进行测试。这些行包含范围为 1 到 10 的度量 ID，每个计数器每分钟记录一个样本（一天记录 1440 个样本），日期范围从 2003 年 1 月 1 日到 2003 年 3 月 11 日。我使用范围从 1 天到 1 个月的时段比较了清单 5 和清单 8 所显示的两个解决方案。在我的测试中，这两个解决方案都表现得很好，第二个解决方案在持续时间和 I/O 方面都略胜一筹，因此我倾向于使用第二个。