T-SQL Enhancement in SQL Server 2005[下篇]

第一部分中,我们讨论了APPLYCTE这两个T-SQL Enhancement。APPLY实现了Table和TVF的Join,CTE通过创建“临时的View”的方式使问题化繁为简。现在我们接着来讨论另外两个重要的T-SQL Enhancement Items:PIVOTRanking。 

三、 PIVOT Operator

PIVOT的中文意思是“在枢轴上转动”,比如对于一个2维坐标,将横坐标变成纵坐标,将纵坐标变成横坐标。反映在一个Relational Table上的意思就是:变成为列,变列为行。相信大家在进行报表设计的时候都遇到过类似于这样的需求:统计2002年内某个销售人员第一季度每个月处理的订单数。在AdventureWorks Sample Databse中,Sales Order存储于SaleOrderHeader这张表中,它的结果如下:

我们一般情况下通过下面的SQL实现我们提出的统计功能:

SELECT SalesPersonID,
SUM(CASE DATEPART(MM,OrderDate)WHEN 1 THEN 1 ELSE 0 END) AS JAN,
SUM(CASE DATEPART(MM,OrderDate)WHEN 2 THEN 1 ELSE 0 END) AS FEB,
SUM(CASE DATEPART(MM,OrderDate)WHEN 3 THEN 1 ELSE 0 END) AS MAR,
SUM(CASE DATEPART(MM,OrderDate)WHEN 4 THEN 1 ELSE 0 END) AS APR
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) = 2002
GROUP BY SalesPersonID

于是我们得到了这样的统计数据:

通过数据在原表的结构和我们最终获得的结果进行比较,我们发现就像是“旋转”了90度,原来的OrderDate是存储在每行的基于Order的一个属性(行),现在我们要把Order Date按照不同月份统计,这样行变成了列。

像这样的需求,我们都可以可以通过PIVOT这个操作符来实现,下面就是基于PIVOT的SQL:

SELECT SalesPersonID, [1] AS JAN,[2] AS FEB, [3] AS MAR, [4] AS APR
FROM 
(
    SELECT SalesPersonID, DATEPART(MM,OrderDate) AS MON
    FROM Sales.SalesOrderHeader
    WHERE DATEPART(yyyy,OrderDate) = 2002
) S

PIVOT 
(
    COUNT(MON) FOR MON IN ([1],[2],[3],[4])
)
AS P

在上面的例子中,同过下面的SELECT语句筛选出来的是为经过PIVOT的数据。

SELECT SalesPersonID, DATEPART(MM,OrderDate) AS MON
    FROM Sales.SalesOrderHeader
    WHERE DATEPART(yyyy,OrderDate) = 2002

通过下面的PIVOT(COUNT(MON)是我们需要统计的数据,FOR MON IN ([1],[2],[3],[4]是统计的范围)就成了我们最终输出的结构了。

PIVOT 
(
    COUNT(MON) FOR MON IN ([1],[2],[3],[4])
)

如果你第一次见到PIVOT,可以不能一下明白它的实现,但是只要你是使用了一两次,相信就会很容易地掌握它。与PIVOT对应的还以一个操作符UNPIVOT,它完成PIVOT的逆操作,在这里就不介绍了,如果有兴趣的话,可以参考SQL Server Books Online。

四、 Ranking

排序与排名是我们最为常用的统计方式,比如对班级的学生根据成员进行排名,或者按照成绩高低把学生划分成若干梯队:比如最好成绩的10名学生属于第一梯队,后10名又划分为第二梯队,以此类推。Ranking设计的Key Words包括:ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()。我们现在就来介绍一下他们的用法和相互之间的差异。

1. 1. ROW_NUMBER()

看到ROW_NUMBER(),我想绝大多数人会像想到Oracle的ROWNUM。他们的作用相似,都是表示某条记录所处的Index。ROW_NUMBER()比Oracle的ROWNUM更加强大的是,它可以通过OVER语句指定一个进行排序的Column,比如:ROW_NUMBER() OVER (ORDER BY CustomerID)。

我们来看一个例子:对Sales.SalesOrderHeader按照CustomerID进行排序,并显示每条记录的Row Number。

SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader

下面是查询结果:

我们发现最终的结果按照CutomerID进行排序,RowNum从1开始以此递增,每条记录(不管是否具有相同的CustomerID)拥有不同的RowNum。

提到排序,我们就不得不提到Order BY,如果我们在后面加上ORDER BY,并指定不同的排序字段,会出现怎样的结果呢?

SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID

查询获得的结果是:

从上图中可以看到,最终的结果以ORDER BY中指定的SalesOrderID进行排序,但是ROW_NUMBER()体现的值却是基于CustmerID排序的。

由于ROW_NUMBER()体现是基于某个确定的字段进行排序后某个DataRow所处的位置,所以它不能直接使用到Aggregate的Column中。比如下面的SQL是不合法的:

SELECT CustomerID,COUNT(*) AS OrderCount,ROW_NUMBER() OVER (ORDER BY OrderCount)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID

要是想按照OrderCount,可以使用第一部分介绍的CTE:

WITH CTE_Order(CustomerID,OrderCount)
AS
(
SELECT CustomerID,COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)

SELECT CustomerID,OrderCount,ROW_NUMBER() OVER (ORDER BY OrderCount)
FROM CTE_Order

2. RANK()

RANK()的使用和ROW_NUMBER()类似。不过它与ROW_NUMBER()所不同的是:对于被指定为排序的字段,具有相同值得Row对应的返回值相同。比如:

SELECT SalesOrderID,CustomerID,RANK() OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader

下面是相应的查询结果:

对于RANK(),还有一点需要说明的是,它的回返值不是连续的, 比如第五条记录的Row_Num是5而不是2。如果想实现这样需求,就需要用下面一个Function:DENSE_RANK()。

3. 3. DENSE_RANK()

DENSE_RANK()实现了一个连续的Ranking。比如下面的SQL:

SELECT SalesOrderID,CustomerID,DENSE_RANK() OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader

就来产生如下的查询结果:

4. NTILE()

上面我们说到划分梯队的问题,这样的问题可以通过NTILE() Function来实现。比如我们现在按照CustomerID排序,把CustomerID为1和2的划分到3梯队中:

SELECT SalesOrderID,CustomerID,NTILE(3) OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
WHERE CustomerID <3

其查询结果为:

我们可以看到,一共12条记录,划分为3组,平均下来每组4条记录。

5. 5.PARTITION BY

上面提到的所有Ranking都是基于真个结果基的。而有的时候我们需要将真个结果集按照某个Column 进行分组,进行基于组的Ranking。这就需要PARTITION BY了。PARTITION BY置于OVER Clause中,和ORDER BY 平级。

比如下面的SQL将Order记录按照CustomerID进行分组,在每组中输出排名(安OrderDate排序):

SELECT SalesOrderID,CustomerID,RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RowNum
FROM Sales.SalesOrderHeader

相应的查询结果:

T-SQL Enhancement in SQL Server 2005: [原创]T-SQL Enhancement in SQL Server 2005 - Part I [原创]T-SQL Enhancement in SQL Server 2005 - Part II Reference: 《Programming Microsoft SQL Server 2005》 By Andrew J. Brust & Stephen Forte

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏熊二哥

SQL优化快速入门

最近遇到一个专门进行SQL技术优化的项目,对很多既有的老存储过程进行调优(现在已经不再新增任何存储过程),因此系统的对SQL语句编写进行一次科学的学习变得很有必...

2089
来自专栏杨建荣的学习笔记

通过分区键值发现性能问题(r2笔记84天)

在很多应用中如果数据量少有规模,都会有大量的分区表存在,使用比较多的是range partition. 一般的range partition都一时间为键值,或者...

2995
来自专栏c#开发者

获取数据字典

 表结构信息查询 SELECT      TableName=CASE WHEN C.column_id= THEN O.name ELSE N'' END,...

3835
来自专栏乐沙弥的世界

SQL server 2005 UNPIVOT运算符的使用

      UNPIVOT运算符相对于PIVOT运算符,它执行与PIVOT相反的操作,即将列转换到行。需要注意的是UNPIVOT运算符并不完全是PIVOT的逆向...

771
来自专栏GreenLeaves

SQL学习之HAVING过滤分组

1、SQL除了能用Group By分组数据之外,SQL还允许过滤分组,规定包括那些分组,排除那些分组。例如,你可能想要列出至少有两个订单的所有顾客。为此,必须基...

2515
来自专栏杨建荣的学习笔记

和Null有关的函数(r3笔记第48天)

关于null相关的函数在日常的工作中还有比较实用的,可能会碰到各种和Null校验相关的情况,大体有以下几种。 case when decode nvl nvl...

32212
来自专栏文渊之博

初识SQL Server2017 图数据库(一)

背景:   图数据库对于表现和遍历复杂的实体之间关系是很有效果的。而这些在传统的关系型数据库中尤其是对于报表而言很难实现。如果把传统关系型数据库比做火车的话,那...

3058
来自专栏GopherCoder

2016.05.27

1584
来自专栏数据和云

元宵佳节:看Oracle技术粉们用SQL画团圆

话团圆,画团圆,元宵佳节倍思亲,可是大家知道吗,万能的SQL可以帮助大家绘制团圆。 在ITPUB论坛里,一群SQL爱好者们会用SQL来描摹一切可能。请看如下这段...

3005
来自专栏乐沙弥的世界

SQL,PL/SQL 数据类型一览表

The following is a list of datatypes available in Oracle.

1183

扫码关注云+社区

领取腾讯云代金券