开窗函数的简介
开窗函数的语法为: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, ‘本名1’, 'IIS ', 99.00); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (2, ‘本名2’, 'IIS ', 56.00); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (3, ‘本名3’, 'IIS ', 33.00); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (4, ‘本名4’, 'IIS ', 56.12); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (5, ‘本名5’, 'IIS ', 99.00); GO
INSERT INTO [BOOKINFO]([id], [booknm], [booktype], [bookprice]) VALUES (6, ‘本名6’, 'IIS ', 11.00); GO
排名开窗函数
ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数。
PARTITION BY用于将结果集进行分组,开窗函数应用于每一组。
ORDER BY 指定排名开窗函数的顺序,在排名开窗函数中必须使用ORDER BY语句。
- ROW_NUMBER排序函数
例如:按照书类型进行价格排序SELECT
booknm,
booktype,
bookprice,
row_number () OVER ( PARTITION BY booktype ORDER BY bookprice DESC ) AS num1
FROM
BOOKINFO
即使是相同的价格也会分开排序1,2 - DENSE_RANK排序函数
SELECT
booknm,
booktype,
bookprice,
dense_rank () OVER ( PARTITION BY booktype ORDER BY bookprice DESC ) AS num1
FROM
BOOKINFO
相同的价格会放在同一号位,不会跳号。 - 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
其他聚合函数不一一举例。