sql server 2012 over()窗口的功能扩充，连接聚合示例

 --sql server 2012 over()窗口的功能扩充，连接聚合示例
 USE [AdventureWorks2012]
 GO
 -- examples in sql server 2012 bible
 ;
 WITH yearlycountryregionsales AS (
          SELECT [GROUP] AS ctryreg,YEAR(soh.OrderDate) AS oryr,SUM(totaldue) 
                 totaldueytd
          FROM   Sales.SalesOrderHeader AS soh
                 INNER JOIN sales.SalesTerritory AS st
                      ON  st.TerritoryID = soh.TerritoryID
          GROUP BY
                 st.[Group],
                 YEAR(soh.OrderDate)
      )
 
 SELECT ctryreg,oryr,totaldueytd  AS currenttotals,SUM(totaldueytd) OVER(
            PARTITION BY ctryreg ORDER BY oryr ROWS BETWEEN 1 preceding AND 
            CURRENT row
        ) -totaldueytd            AS ptotal,SUM(totaldueytd) OVER(
            PARTITION BY ctryreg ORDER BY oryr ROWS BETWEEN CURRENT row AND 1 
            following
        ) -totaldueytd            AS ftotal
 FROM   yearlycountryregionsales
 GO;
 WITH yearlycountryregionsales AS (
          SELECT [GROUP] AS ctryreg,YEAR(soh.OrderDate) AS oryr,SUM(totaldue) 
                 totaldueytd
          FROM   Sales.SalesOrderHeader AS soh
                 INNER JOIN sales.SalesTerritory AS st
                      ON  st.TerritoryID = soh.TerritoryID
          GROUP BY
                 st.[Group],
                 YEAR(soh.OrderDate)
      )
 
 SELECT ctryreg,oryr,totaldueytd  AS currenttotals,SUM(totaldueytd) OVER(
            PARTITION BY ctryreg ORDER BY oryr ROWS BETWEEN 1 preceding AND 
            CURRENT row
        ) -totaldueytd            AS ptotal,SUM(totaldueytd) OVER(
            PARTITION BY ctryreg ORDER BY oryr ROWS BETWEEN CURRENT row AND 
            unbounded following
        ) -totaldueytd            AS ftotal
 FROM   yearlycountryregionsales
 
 
 --my test
 USE [master]
 GO
 IF OBJECT_ID('tt') IS NOT NULL
 DROP TABLE tt
 GO
 --create table 名字，季度，数据，月份
 CREATE TABLE  tt (NAME NVARCHAR(10),qu nvarchar(2),sal NUMERIC(10,2) DEFAULT 1,m NVARCHAR(5))
 GO
 --insert test data
 INSERT INTO tt SELECT 'a','1q',rand()*100.0,'01m'
 INSERT INTO tt SELECT 'a','1q',rand()*100.0,'02m'
 INSERT INTO tt SELECT 'a','1q',rand()*100.0,'03m'
 INSERT INTO tt SELECT 'a','2q',rand()*100.0,'04m'
 INSERT INTO tt SELECT 'a','2q',rand()*100.0,'05m'
 INSERT INTO tt SELECT 'a','2q',rand()*100.0,'06m'
 INSERT INTO tt SELECT 'a','3q',rand()*100.0,'07m'
 INSERT INTO tt SELECT 'a','3q',rand()*100.0,'08m'
 INSERT INTO tt SELECT 'a','3q',rand()*100.0,'09m'
 INSERT INTO tt SELECT 'a','4q',rand()*100.0,'10m'
 INSERT INTO tt SELECT 'a','4q',rand()*100.0,'11m'
 INSERT INTO tt SELECT 'a','4q',rand()*100.0,'12m'
 
 
 INSERT INTO tt SELECT 'b','3q',rand()*100.0,'07m'
 INSERT INTO tt SELECT 'b','3q',rand()*100.0,'08m'
 INSERT INTO tt SELECT 'b','3q',rand()*100.0,'09m'
 INSERT INTO tt SELECT 'b','4q',rand()*100.0,'10m'
 INSERT INTO tt SELECT 'c','4q',rand()*100.0,'11m'
 INSERT INTO tt SELECT 'c','4q',rand()*100.0,'12m'
 GO
 --连续聚合
 SELECT NAME,qu,m ,SUM(sal) OVER (partition by qu ORDER BY m ROWS unbounded preceding)AS a ,
 CASE SUBSTRING(m,1,2)%3 WHEN 0 THEN qu+' total' ELSE '' END AS x --simple quater total
 FROM tt
WHERE NAME='a'--search by name 

SELECT NAME,qu,m ,SUM(sal) OVER (partition by qu ORDER BY m ROWS unbounded preceding)AS a ,
CASE SUBSTRING(m,1,2)%3 WHEN 0 THEN qu+' total' ELSE '' END AS x--simple quater total
FROM tt
WHERE NAME='b'  ----search by name 


SELECT NAME,qu,m ,SUM(sal) OVER (partition by qu ORDER BY m ROWS unbounded preceding)AS a ,
CASE SUBSTRING(m,1,2)%3 WHEN 0 THEN qu+' total' ELSE '' END AS x--simple quater total
FROM tt
WHERE NAME='c'  --search by name 

   --连续聚合全部
SELECT NAME,qu,m,SUM(sal) OVER(
        PARTITION BY NAME, qu,  m --group elements
		ORDER BY m ROWS unbounded preceding
    ) AS a
FROM   tt
 
SELECT NAME,qu,m,SUM(sal) OVER(PARTITION BY NAME, qu ORDER BY m ROWS unbounded preceding) AS 
    a
FROM   tt
 
SELECT NAME,qu,m,SUM(sal) OVER(PARTITION BY NAME ORDER BY m ROWS unbounded preceding) AS 
    a
FROM   tt