MSSQL 的OVER()函数

1. OVER子句用于为行为定义一个窗口(即给纪录多添加特定一列)，以便进行特定的运算。
可以简单地认为是运算将要操作的一个行的集合。
聚合函数和排序函数都是可以支持OVER子句的运算类型。
由于OVER子句为这些函数提供了一个行的窗口，所以这些函数也称之为开窗函数。

如果over()里面没有子句, over子句为该次查询返回的所有行.  
over子句提供partition by进行分区. 
其实就是把可操作的数据进行分组, 并匹配出符合分组参数的行集合.

2. 排序函数, over子句 与 partition by, order by连用可以生成特殊排序

eg:
--建表
CREATE TABLE [dbo].[test](
[id] [int] NULL,
[col06] [int] NULL,
) ON [PRIMARY]
GO

--测试数据
insert into test (id, col06) values (1, 1);
insert into test (id, col06) values (2, 1);
insert into test (id, col06) values (3, 1);
insert into test (id, col06) values (4, 6);
insert into test (id, col06) values (5, 6);
insert into test (id, col06) values (6, 6);
insert into test (id, col06) values (7, 9);
insert into test (id, col06) values (8, 9);
insert into test (id, col06) values (9, 9);
insert into test (id, col06) values (10, 12);
insert into test (id, col06) values (11, 12);
insert into test (id, col06) values (12, 12);

select * from test

-- 聚合函数 COUNT(), AVG() 等
-- 排序函数 row_number() 等

SELECT id, col06, 
  COUNT(id) OVER() AS countid,                        --总纪录数
  MIN(id) OVER() AS minid,                            --最小ID号
  MAX(id) OVER() AS maxid,                            --最大ID号
  AVG(id) OVER() AS avgid,                            --ID平均
  SUM(id) OVER() AS sumid,                            --ID之和
  AVG(id) OVER(PARTITION BY col06) AS col06avgid,     --按col06分区, 求ID平均
  SUM(id) OVER(PARTITION BY col06) AS col06totalid    --按col06分区, 求ID和
FROM test
order by id;

--SQL2000:
SELECT id, col06,
  (SELECT COUNT(id) from test) AS countid,
  (SELECT MIN(id) from test) AS minid,
  (SELECT SUM(id) from test) AS maxid,
  (SELECT MAX(id) from test) AS sumid,
  (SELECT AVG(id) from test) AS avgid,
  (SELECT AVG(id) from test where id=T.id) as col06avgid,
  (SELECT SUM(id) from test where id=T.id) AS col06totalid
FROM test AS T
ORDER BY id;


SELECT id, col06,
  row_number() over(order by id desc) AS num,                                     --与排序函数一起使用, 根据ID降序排序
  row_number() over(partition by col06 order by id desc) AS rnum    --与排序函数一起使用, 按col06分组,然后根据ID降序排序
FROM test
ORDER BY id;