探讨SQL Server 2005的评价函数

踏雪寻梅

　　一、 简介　　在2005年11月份，微软发行了三种新产品系列：Visual Studio2005，SQL Server 2005和.NET框架2.0(它包括ASP.NET 2.0)。SQL Server2005是微软自从其上一个主要发行版本SQL Server 2000以来最新版本的数据库平台。在过去五年的发展中，SQLServer中加入了大量的新特征，所有这些新内容都被总结到微软网站的一篇文章《What's New in SQL Server2005?》中。使用SQL Server2005作为后端数据库构建基于web应用程序的开发者很可能会对这些新特征抱有浓厚的兴趣，这些新特征包括新的T-SQL改进，更好的VisualStudio集成，与CLR/.NET框架的集成，以及SQL Server 2005 Management Studio应用程序（它是SQLServer 2000的企业管理器的一个更为"平滑"的版本）。
　　与以前的SQL Server 2000相比， 2005中的T-SQL改进使得编写某些类型的查询极为容易。在SQL Server 2005中，T-SQL语法更为精练、可读和易于理解。
　　在本文中，我们将专门探讨SQL Server 2005的评价函数，它们大大简化了对查询结果进行评价的过程。
　　二、 数据模型和评价结果基础
　　在我们分析如何使用普通查询模式之前，让我们首先创建一个能够运行这些查询的数据模型。在本文中，我使用SQL Server 2005Express版本来实现我的演示，并且包括了一个数据库和一个ASP.NET 2.0网站（请参考本文相应的完整源码。就象VisualStudio一样，SQLServer发行中也一同加杂了其它一些不同的版本。其中，Express版本是一个针对业余爱好者、学生等群体的免费版本。如果你下载和安装Visual Web Developer（Visual Studio针对web开发者的Express版本），那么你可以选择一同安装SQLServer 2005 Express版本）。
　　对于本文中的示例，我们将使用一个含有产品、销售人员（雇员）、顾客和订单信息的数据库。我们使用五个表来建模：Customers，Employees，Products，Orders和OrderItems。其中，Customers，Employees和Products表分别包含每一个顾客，雇员和产品信息的行记录数据。每当一个顾客进行购买活动，一条新记录被添加到Orders表中，其中的信息指示该顾客实现了购买、该雇员进行的这一销售活动及订单的日期。其中，OrderItems映射订单中的每一件产品，产品的数量和价格总值(假定较大的购买量可以打折)。下图展示了这些表（及字段）以及它们之间的关系。

http://images.itdb.cn/News/2006/08/30/8C8C3D123E4F7BD.jpg　　　如图所展示的，这个OrderItems在Orders和Products表之间建立一个对多对的连接。
　　当构建报告或分析数据时，用户或管理员经常希望看到以某种方式对数据的评价信息。例如，你的老板可能想要一个报告来显示卖路最好的前十项，或在第三个季度销售部中实现最大收入的前三名销售人员。更复杂的情况可能是仅返回第3到第5个评价排名的销售人员。在SQL Server2000中，返回最高排名项的查询可以通过使用TOP或ROWCOUNT关键字来实现。为了检索一个特定评价子集，你需要使用一种"派生表"（或者是一种基于视图的手段）。
　　SQL Server2005中引入了四个新的评价函数：ROW_NUMBER，RANK，DENSE_RANK和NTILE。尽管这些与SQL Server2000所提供的函数相比是一个明显的进步，但是这些函数的使用仍然存在一些限制（要求使用派生表或视图来实现功能更为强大的应用程序）。下面让我们分析一下每一个函数。
　　三、 使用ROW_NUMBER函数计算行数
　　这个ROW_NUMBER函数把一个序数值赋给每一个返回的记录，该序数值依赖于一个特定的与这个函数一起使用的ORDERBY语句。函数ROW_NUMBER的语法是：ROW_NUMBER() OVER([partition] ORDERBY子句)。例如，下列查询将返回从最贵的到最便宜的产品，对每一种产品按价格进行评价：
SELECT ProductID，Name，Price， ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRankFROM Products　　这个语句的执行结果如下表所示：
ProductIDNamePricePriceRank8 Desk 495.0000 110Executive Chair295.000029 Chair125.0000 35 Mouse 14.950046 Mousepad9.9900 511 Scissors 8.5000 64 Stapler 7.9500 73 Binder 1.9500 8...　　默认情况下，这个ROW_NUMBER函数把一个增量值（逐次加1）赋给结果集中的每一个记录。借助于可选的partition参数，无论何时分区（partitioning）列值发生变化，你都可以让ROW_NUMBER函数重新计算行数。为了说明这个问题，我使用如下查询语法创建了一个视图vwTotalAmountBilledPerOrder，它将返回每一个OrderID和该订购的总订单数：
SELECT OrderID，SUM(AmountBilled) AS TotalOrderAmountFROM OrderItemsGROUP BY OrderID　　这条语句将返回OrderItems表中每一个唯一的订单，还有相应于该订单的AmountBilled值的和。借助于这个视图，我们可以使用ROW_NUMBER方法来按最大花钱数来评价这些订单，如下所示：
SELECT c.Name，o.DateOrdered，tab.TotalOrderAmount， ROW_NUMBER() OVER(ORDER BY TotalOrderAmount DESC) AS BestCustomerFROMvwTotalAmountBilledPerOrder AS tab INNER JOIN Orders AS o ON o.OrderID= tab.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID　　这个语句将返回如下表所示的结果：
Name DateOrdered TotalOrderAmount BestCustomerBob 12/1/2005 12649.99001Darren 1/2/2006620.00002Bob12/19/2005265.8500 3Tito12/22/200514.95004Bruce1/5/200614.95005Tito12/18/200512.44006Bruce1/4/2006 9.99007Lee Ann1/3/20068.5000 8...　　注意，某些顾客多次出现在这个列表中（如Bob，Tito和Bruce）。也许有时，我们不是想观看以销售量排序的所有订单，而更想看到每一个顾客的最高订单量。为此，我们可以通过使用ROW_NUMBER函数中的PARTITION BY子句达到这一目的，如下所示：
SELECT c.Name， o.DateOrdered， tab.TotalOrderAmount， ROW_NUMBER()OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) ASBestCustomerFROM vwTotalAmountBilledPerOrder AS tab INNER JOIN OrdersAS o ON o.OrderID = tab.OrderID INNER JOIN Customers AS c ONc.CustomerID = o.CustomerID　　这个语句将返回如下表所示的结果：
NameDateOrderedTotalOrderAmountBestCustomerBob12/1/200512649.99001Bob12/19/2005265.85002Tito12/22/200514.95001Tito12/18/200512.44002Darren 1/2/2006620.00001Bruce1/5/200614.95001Bruce1/4/20069.99002Lee Ann1/3/20068.50001...　　注意，尽管这些结果非常不错；但是，你却不能在WHERE语句中使用ROW_NUMBER()函数(或任何其它的评价函数)。也就是说，你可能想要说，"把按价格评价第5到第8名的产品列出"。为此，你需要使用一个派生的表或视图。例如，你可以把上面的查询放到一个视图vwPriceRankedProducts中，然后使用如下查询返回第5到第8个排名的产品：
　　SELECT ProductID，Name，Price，PriceRankFROM vwPriceRankedProductsWHERE PriceRank BETWEEN 5 AND 8
　　四、 使用RANK和DENSE_RANK处理同级问题　　基于可选的partition子句和要求的orderby子句，ROW_NUMBER函数默认地递增（加1）每一个返回结果的值。然而，有时你可能想以不同方式处理相同级别，而不是把相同的值赋给相同的级别。例如，前面显示的总订单列表中，Tito在2005年12月22日实现的订单数与Bruce在2006年1月5日实现的订单数相同；然而，ROW_NUMBER函数却把这两行评价为#4和#5，而不是都评价为#4。
　　RANK和DENSE_RANK函数都能够使用相同的评价计数级。例如，使用下列查询：
SELECT c.Name，o.DateOrdered，tab.TotalOrderAmount， RANK() OVER(ORDER BY TotalOrderAmount DESC) AS BestCustomerFROMvwTotalAmountBilledPerOrder AS tab INNER JOIN Orders AS o ON o.OrderID= tab.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID　　这个语句将返回如下表所示的结果：
NameDateOrderedTotalOrderAmountBestCustomerBob12/1/200512649.99001Darren1/2/2006620.00002Bob12/19/2005 265.85003Tito12/22/200514.95004Bruce1/5/200614.95005Tito 12/18/200512.44006Bruce1/4/20069.99007Lee Ann1/3/20068.50008...　　注意，具有相同数量的两个订单是怎样都被评价为#4的。RANK和DENSE_RANK之间的不同之处在于，在平级的结果后他们是如何重新开始计数的。RANK绕过尽可能多的平级的结果。在我们上面的示例中，因为有两个结果绑定在#4上，因此跟随其后的#5被跳过而评价等级以#6继续。另一方面，DENSE_RANK从下一个整数继续。如果我们在上面的查询中使用函数名DENSE_RANK代替RANK，那么Tito在2005年12月18日相应于单价$12.44的订单评价将是#5。
　　类似于ROW_NUMBER函数，RANK和DENSE_RANK函数都能使用可选的PARTITION BY语句。
　　五、 使用NTILE函数对评价结果进行分组
　　SQL Server2005新引入的最后一个与T-SQL函数相关的评价函数是NTILE(int)。NTILE象其它的评价函数一样操作，但是它能够把结果分成组，每组中相应相同评价结果的记录。你可以使用NTILE函数把结果分解成两组、三组或四组等，如下例所示：
SELECT ProductID，Name，Price，NTILE(4) OVER (ORDER BY Price DESC) as QuartileFROM Produts　　这个语句将返回如下表所示的结果：
ProductIDNamePrice Quartile8 Desk 495.0000110Executive Chair 295.000019 Chair 125.0000 25 Mouse 14.9500 26 Mousepad 9.9900311 Scissors 8.5000 34 Stapler7.950043 Binder 1.9500 4　　在随本文下载的演示程序中，我提供了一个例子，它使用了一个具有NTILE函数的视图来允许用户有选择地查看各种分组的最大订单量。
　　六、 结论
　　在本文中，我们探讨了SQL Server2005中的四个新函数的用法。这四个新函数分别是：ROW_NUMBER，RANK，DENSE_RANK和NTILE。与SQL Server2000使用的旧技术相比，它们使得返回评价结果更为容易。然而，这些函数仅仅是SQL Server2005中所提供的新的T-SQL特征中的极少的一部分。