9重点第九章开窗函数与表旋转(转载)

9重点第九章开窗函数与表旋转

 

小结: 由于开窗函数的起点是基础查询的结果集合，并且基础查询的结果集合只有到 SELECT 阶段时才生成，因此，开窗函数仅能用于查询的 SELECT 和ORDER BY 子句。

1. 开窗函数包括：排名开窗函数(ROW_NUMBER(), RANK()等)，聚合开窗函数如(AVG,SUM等)。在 SQLSERVER 2012 中，还提供了4个偏移开窗函数： LAG, LEAD , FIRST_VALUE和LAST_VALUE 此外，2012 开始支持了 对聚合的排序和框架支持，简化了 运行聚合的 计算。

好处： 看下图两种写法是同样结果，而 开窗函数生成的执行计划效率比分组然后关联的高

2012 开始可以带框架排序

表旋转： PIVOT 

附上测试代码：

IF OBJECT_ID('dbo.Students','U') IS NOT NULL

DROP TABLE dbo.Students

GO

CREATE TABLE dbo.Students

(

    ClassID int,

    StudentName nvarchar(20),

    Achievement numeric(5,2)

)

INSERT INTO dbo.Students

VALUES(1,'Grace',99.00),

      (1,'Andrew',99.00),

      (1,'Janet',75.00),

      (1,'Margaret',89.00),

      (2,'Steven',86.00),

      (2,'Michael',72.00),

      (2,'Robert',91.00),

      (3,'Laura',75.00),

      (3,'Ann',94.00),

      (3,'Ina',80.00),

      (3,'Ken',92.00)

SELECT ClassID,

       StudentName,

       Achievement,

       AVG(Achievement)OVER(PARTITION BY ClassID 

       ORDER BY Achievement DESC

       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 这个框架子句是限制求平均分时候算上大于

                                    分区开始的意思                              到当前行

等于本行人的分数去统计，是一个动态统计。

       ) AS Avg_Class 

FROM dbo.Students

SELECT stu.*,temp.Avg_Achievement FROM dbo.Students stu join

(

SELECT   ClassID,AVG(Achievement) AS Avg_Achievement  FROM  dbo.Students Group by ClassID

) temp on stu.ClassID = temp.ClassID

总而言之，开窗函数使得多个分区聚合成为可能，即一条语句多个分区分组聚合

SQL SERVER 提供的 聚合函数 包括 AVG,CHECKSUM_AGG,COUNT,COUNT_BIG,GROUPING ,GROUPING_ID,MAX/MIN/SUM/STDEV/STDEVP/VAR/VARP除了 GROUPING 和 GROUPING_ID 其它都可以跟在OVER 子句后面用于窗口的聚合计算。

原文链接

本文由豆约翰博客备份专家远程一键发布