探讨SQL Server 2005的评价函数

一、 简介　　在2005年11月份，微软发行了三种新产品系列：Visual Studio 2005，SQL Server 2005和.NET框架2.0(它包括ASP.NET 2.0)。SQL Server 2005是微软自从其上一个主要发行版本SQL Server 2000以来最新版本的数据库平台。在过去五年的发展中，SQL Server中加入了大量的新特征，所有这些新内容都被总结到微软网站的一篇文章《What's New in SQL Server 2005?》中。使用SQL Server 2005作为后端数据库构建基于web应用程序的开发者很可能会对这些新特征抱有浓厚的兴趣，这些新特征包括新的T-SQL改进，更好的Visual Studio集成，与CLR/.NET框架的集成，以及SQL Server 2005 Management Studio应用程序（它是SQL Server 2000的企业管理器的一个更为"平滑"的版本）。　　与以前的SQL Server 2000相比， 2005中的T-SQL改进使得编写某些类型的查询极为容易。在SQL Server 2005中，T-SQL语法更为精练、可读和易于理解。　　在本文中，我们将专门探讨SQL Server 2005的评价函数，它们大大简化了对查询结果进行评价的过程。　　二、 数据模型和评价结果基础　　在我们分析如何使用普通查询模式之前，让我们首先创建一个能够运行这些查询的数据模型。在本文中，我使用SQL Server 2005 Express版本来实现我的演示，并且包括了一个数据库和一个ASP.NET 2.0网站（请参考本文相应的完整源码。就象Visual Studio一样，SQL Server发行中也一同加杂了其它一些不同的版本。其中，Express版本是一个针对业余爱好者、学生等群体的免费版本。如果你下载和安装Visual Web Developer（Visual Studio针对web开发者的Express版本），那么你可以选择一同安装SQL Server 2005 Express版本）。　　对于本文中的示例，我们将使用一个含有产品、销售人员（雇员）、顾客和订单信息的数据库。我们使用五个表来建模：Customers，Employees，Products，Orders和OrderItems。其中，Customers，Employees和Products表分别包含每一个顾客，雇员和产品信息的行记录数据。每当一个顾客进行购买活动，一条新记录被添加到Orders表中，其中的信息指示该顾客实现了购买、该雇员进行的这一销售活动及订单的日期。其中，OrderItems映射订单中的每一件产品，产品的数量和价格总值(假定较大的购买量可以打折)。下图展示了这些表（及字段）以及它们之间的关系。 　如图所展示的，这个OrderItems在Orders和Products表之间建立一个对多对的连接。　　当构建报告或分析数据时，用户或管理员经常希望看到以某种方式对数据的评价信息。例如，你的老板可能想要一个报告来显示卖路最好的前十项，或在第三个季度销售部中实现最大收入的前三名销售人员。更复杂的情况可能是仅返回第3到第5个评价排名的销售人员。在SQL Server 2000中，返回最高排名项的查询可以通过使用TOP或ROWCOUNT关键字来实现。为了检索一个特定评价子集，你需要使用一种"派生表"（或者是一种基于视图的手段）。　　SQL Server 2005中引入了四个新的评价函数：ROW_NUMBER，RANK，DENSE_RANK和NTILE。尽管这些与SQL Server 2000所提供的函数相比是一个明显的进步，但是这些函数的使用仍然存在一些限制（要求使用派生表或视图来实现功能更为强大的应用程序）。下面让我们分析一下每一个函数。 　　三、 使用ROW_NUMBER函数计算行数　　这个ROW_NUMBER函数把一个序数值赋给每一个返回的记录，该序数值依赖于一个特定的与这个函数一起使用的ORDER BY语句。函数ROW_NUMBER的语法是：ROW_NUMBER() OVER([partition] ORDER BY子句)。例如，下列查询将返回从最贵的到最便宜的产品，对每一种产品按价格进行评价： SELECT ProductID，Name，Price， ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRankFROM Products　　这个语句的执行结果如下表所示： ProductID Name Price PriceRank 8 Desk 495.0000 1 10 Executive Chair 295.0000 2 9 Chair 125.0000 3 5 Mouse 14.9500 4 6 Mousepad 9.9900 5 11 Scissors 8.5000 6 4 Stapler 7.9500 7 3 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 BestCustomerFROM vwTotalAmountBilledPerOrder 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 BestCustomer Bob 12/1/2005 12649.9900 1 Darren 1/2/2006 620.0000 2 Bob 12/19/2005 265.8500 3 Tito 12/22/2005 14.9500 4 Bruce 1/5/2006 14.9500 5 Tito 12/18/2005 12.4400 6 Bruce 1/4/2006 9.9900 7 Lee Ann 1/3/2006 8.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) AS BestCustomerFROM vwTotalAmountBilledPerOrder 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 BestCustomer Bob 12/1/2005 12649.9900 1 Bob 12/19/2005 265.8500 2 Tito 12/22/2005 14.9500 1 Tito 12/18/2005 12.4400 2 Darren 1/2/2006 620.0000 1 Bruce 1/5/2006 14.9500 1 Bruce 1/4/2006 9.9900 2 Lee Ann 1/3/2006 8.5000 1 ...　　注意，尽管这些结果非常不错；但是，你却不能在WHERE语句中使用ROW_NUMBER()函数(或任何其它的评价函数)。也就是说，你可能想要说，"把按价格评价第5到第8名的产品列出"。为此，你需要使用一个派生的表或视图。例如，你可以把上面的查询放到一个视图vwPriceRankedProducts中，然后使用如下查询返回第5到第8个排名的产品： SELECT ProductID，Name，Price，PriceRankFROM vwPriceRankedProductsWHERE PriceRank BETWEEN 5 AND 8

·SQL Server 2000企业版安装教程·visual studio 2005 简体中文团队开发·Java连接SQL Server 2000·用Java访问SQL Server 2000数据库·一次SQL Server 2000 安装奇遇·不用安装客户端连接sql server 2000·Java 实现连接sql server 2000（JDBC数·SQL Server 2000安装和基本框架·剖析SQL Server 2005查询通知之基础篇·SQL Server 2005 Beta 2 Transact-SQL 四、 使用RANK和DENSE_RANK处理同级问题　　基于可选的partition子句和要求的order by子句，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 BestCustomerFROM vwTotalAmountBilledPerOrder 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 BestCustomer Bob 12/1/2005 12649.9900 1 Darren 1/2/2006 620.0000 2 Bob 12/19/2005 265.8500 3 Tito 12/22/2005 14.9500 4 Bruce 1/5/2006 14.9500 5 Tito 12/18/2005 12.4400 6 Bruce 1/4/2006 9.9900 7 Lee Ann 1/3/2006 8.5000 8 ...　　注意，具有相同数量的两个订单是怎样都被评价为#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 Server 2005新引入的最后一个与T-SQL函数相关的评价函数是NTILE(int)。NTILE象其它的评价函数一样操作，但是它能够把结果分成组，每组中相应相同评价结果的记录。你可以使用NTILE函数把结果分解成两组、三组或四组等，如下例所示： SELECT ProductID，Name，Price，NTILE(4) OVER (ORDER BY Price DESC) as QuartileFROM Produts　　这个语句将返回如下表所示的结果： ProductID Name Price Quartile 8 Desk 495.0000 1 10 Executive Chair 295.0000 1 9 Chair 125.0000 2 5 Mouse 14.9500 2 6 Mousepad 9.9900 3 11 Scissors 8.5000 3 4 Stapler 7.9500 4 3 Binder 1.9500 4　　在随本文下载的演示程序中，我提供了一个例子，它使用了一个具有NTILE函数的视图来允许用户有选择地查看各种分组的最大订单量。　　六、 结论　　在本文中，我们探讨了SQL Server 2005中的四个新函数的用法。这四个新函数分别是：ROW_NUMBER，RANK，DENSE_RANK和NTILE。与SQL Server 2000使用的旧技术相比，它们使得返回评价结果更为容易。然而，这些函数仅仅是SQL Server 2005中所提供的新的T-SQL特征中的极少的一部分。