开窗函数的记录

开窗函数的简介

    开窗函数的语法为：over(partition by 列名1 order by 列名2 )，括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。
   开窗函数与聚合函数计算方式一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值。

数据准备：

>CREATE TABLE BOOKINFO (
id DECIMAL ( 10 ) NOT NULL PRIMARY KEY,
booknm VARCHAR ( 20 ),
booktype CHAR ( 10 ),
bookprice DECIMAL ( 10, 2 ) 
)

INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (1, ‘本名１’, 'IIS ', 99.00); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (2, ‘本名２’, 'IIS ', 56.00); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (3, ‘本名３’, 'IIS ', 33.00); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (4, ‘本名４’, 'IIS ', 56.12); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (5, ‘本名５’, 'IIS ', 99.00); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (6, ‘本名６’, 'IIS ', 11.00); GO

排名开窗函数

ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数。
PARTITION BY用于将结果集进行分组，开窗函数应用于每一组。
ORDER BY 指定排名开窗函数的顺序，在排名开窗函数中必须使用ORDER BY语句。

1. ROW_NUMBER排序函数
   例如：按照书类型进行价格排序

   SELECT
   booknm,
   booktype,
   bookprice,
   row_number () OVER ( PARTITION BY booktype ORDER BY bookprice DESC ) AS num1
   FROM
   BOOKINFO
   
   即使是相同的价格也会分开排序1，2

2. DENSE_RANK排序函数

   SELECT
   booknm,
   booktype,
   bookprice,
   dense_rank () OVER ( PARTITION BY booktype ORDER BY bookprice DESC ) AS num1
   FROM
   BOOKINFO
   
   相同的价格会放在同一号位，不会跳号。

3. RANK排序函数

   SELECT
   booknm,
   booktype,
   bookprice,
   rank() OVER ( PARTITION BY booktype ORDER BY bookprice DESC ) AS num1
   FROM
   BOOKINFO
   
   相同的价格在同一号位，会跳号。
   4.NTILE排序函数

   SELECT
   booknm,
   booktype,
   bookprice,
   NTILE(2) OVER ( PARTITION BY booktype ORDER BY bookprice DESC ) AS num1
   FROM
   BOOKINFO
   
   将查询的结果分发到指定数量的组中。对于IIS1类型来说，总共有5条数据，分发到两组中，5/2的结果是2余数是1，会将余数在前面的组里分发一遍。所以结果显示是11122。

聚合开窗函数

很多聚合函数都可以用作窗口函数的运算，如SUM、AVG、MAX、MIN、COUNT。
聚合开窗函数只能使用PARTITION BY子句，ORDER BY不能与聚合开窗函数一同使用。
1.sum聚合函数

SELECT
booknm,
booktype,
bookprice,
sum(bookprice) OVER ( PARTITION BY booktype ) AS num1
FROM
BOOKINFO

其他聚合函数不一一举例。