ITPub博客

首页 > 数据库 > SQL Server > SQL Server 2005的评价函数

SQL Server 2005的评价函数

原创 SQL Server 作者:riyeye 时间:2007-09-19 13:21:44 0 删除 编辑
  对于本文中的示例,我们将使用一个含有产品、销售人员(雇员)、顾客和订单信息的数据库。我们使用五个表来建模:Customers,Employees,Products,Orders和OrderItems。其中,Customers,Employees和Products表分别包含每一个顾客,雇员和产品信息的行记录数据。每当一个顾客进行购买活动,一条新记录被添加到Orders表中,其中的信息指示该顾客实现了购买、该雇员进行的这一销售活动及订单的日期。其中,OrderItems映射订单中的每一件产品,产品的数量和价格总值(假定较大的购买量可以打折)。下图展示了这些表(及字段)以及它们之间的关系。

探讨SQLServer2005的评价函数

如图所展示的,这个OrderItems在Orders和Products表之间建立一个对多对的连接。

  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

  这个语句的执行结果如下表所示:

ProductIDNamePricePriceRank
8 Desk 495.0000 1
10Executive Chair295.00002
9 Chair125.0000 3
5 Mouse 14.95004
6 Mousepad9.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.99001
Darren 1/2/2006620.00002
Bob12/19/2005265.8500 3
Tito12/22/200514.95004
Bruce1/5/200614.95005
Tito12/18/200512.44006
Bruce1/4/2006 9.99007
Lee 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) 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

  这个语句将返回如下表所示的结果:

NameDateOrderedTotalOrderAmountBestCustomer
Bob12/1/200512649.99001
Bob12/19/2005265.85002
Tito12/22/200514.95001
Tito12/18/200512.44002
Darren 1/2/2006620.00001
Bruce1/5/200614.95001
Bruce1/4/20069.99002
Lee 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子句和要求的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

  这个语句将返回如下表所示的结果:

NameDateOrderedTotalOrderAmountBestCustomer
Bob12/1/200512649.99001
Darren1/2/2006620.00002
Bob12/19/2005 265.85003
Tito12/22/200514.95004
Bruce1/5/200614.95005
Tito 12/18/200512.44006
Bruce1/4/20069.99007
Lee 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 Server 2005新引入的最后一个与T-SQL函数相关的评价函数是NTILE(int)。NTILE象其它的评价函数一样操作,但是它能够把结果分成组,每组中相应相同评价结果的记录。你可以使用NTILE函数把结果分解成两组、三组或四组等,如下例所示:

SELECT ProductID,Name,Price,NTILE(4) OVER (ORDER BY Price DESC) as QuartileFROM Produts

  这个语句将返回如下表所示的结果:

ProductIDNamePrice Quartile
8 Desk 495.00001
10Executive Chair 295.00001
9 Chair 125.0000 2
5 Mouse 14.9500 2
6 Mousepad 9.99003
11 Scissors 8.5000 3
4 Stapler7.95004
3 Binder 1.9500 4

  在随本文下载的演示程序中,我提供了一个例子,它使用了一个具有NTILE函数的视图来允许用户有选择地查看各种分组的最大订单量。

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16163/viewspace-970096/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 日期验证的BUG
请登录后发表评论 登录
全部评论
  • 博文量
    17
  • 访问量
    167315