sql开窗函数

用的Oracle数据库进行测试

一、数据准备

DROP TABLE T_TEST;
CREATE TABLE T_TEST (
  id NUMBER(10) VISIBLE NOT NULL ,
  姓名 VARCHAR2(50 BYTE) VISIBLE ,
  性别 VARCHAR2(50 BYTE) VISIBLE ,
  班级 VARCHAR2(50 BYTE) VISIBLE ,
  成绩 NUMBER(5,2) VISIBLE 
);

INSERT INTO T_TEST VALUES ('1', '张三', '男', '1班', '100');
INSERT INTO T_TEST VALUES ('2', '李四', '男', '1班', '89');
INSERT INTO T_TEST VALUES ('3', '王五', '男', '1班', '70');
INSERT INTO T_TEST VALUES ('4', '浩宇', '男', '2班', '99');
INSERT INTO T_TEST VALUES ('5', '祥康', '男', '2班', '66');
INSERT INTO T_TEST VALUES ('6', '刘丽', '女', '2班', '88');
INSERT INTO T_TEST VALUES ('7', '张三', '男', '3班', '59');
INSERT INTO T_TEST VALUES ('8', '张三', '男', '4班', '78');

二、开窗函数是什么？

开窗函数是对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

三、开窗函数的语法

OVER( PARTITION BY 列名1 ORDER BY 列名2 )

括号中的两个关键词PARTITION BY 和ORDER BY 可以只出现一个

四、开窗函数的类型？

聚合开窗	SUM(expr)	返回分组中的和
	AVG(expr)	返回分组中的平均值
	MIN(expr)	返回分组中的最小值
	MAX(expr)	返回分组中的最大值
	COUNT(expr)	返回分组中的行数
排序开窗	ROW_NUMBER()	分组排序，然后进行编号：1,2,3,4,5
	RANK()	分组排序，然后进行编号,对相同值处理，跳号：1,1,3,4,5
	DENSE_RANK()	分组排序，然后进行编号,对相同值处理，不跳号：1,1,2,3,4
偏移开窗	LAG(expr,n)	返回当前行的前n行的expr的值
	LEAD(expr,n)	返回当前行的后n行的expr的值
	FIRST_VALUE(expr)	返回第一个expr的值
	LAST_VALUE(expr)	返回最后一个expr的值

五、开窗函数的演示

1、聚合开窗

① ：SUM(expr)

语法：SUM(成绩) OVER( PARTITION BY 姓名 ORDER BY 班级 ASC )

描述：根据【姓名】分组，【班级】排序，然后对每个分组后的【成绩】进行求和操作。

SELECT 姓名,性别,班级,成绩, SUM(成绩) OVER(PARTITION BY 姓名 ORDER BY 班级 ASC) SUM_OVER FROM T_TEST

②：AVG(expr)

语法：AVG(成绩) OVER( PARTITION BY 姓名 ORDER BY 班级 ASC )

描述：根据【姓名】分组，【班级】排序，然后对每个分组后的【成绩】进行求平均值操作。

SELECT 姓名,性别,班级,成绩, AVG(成绩) OVER(PARTITION BY 姓名 ORDER BY 班级 ASC) AVG_OVER FROM T_TEST

③：MIN(expr)

语法：MIN(成绩) OVER( PARTITION BY 姓名 ORDER BY 班级 ASC )

描述：根据【姓名】分组，【班级】排序，然后对每个分组后的【成绩】进行求最小值的操作。

SELECT 姓名,性别,班级,成绩, MIN(成绩) OVER(PARTITION BY 姓名 ORDER BY 班级 ASC) MIN_OVER FROM T_TEST

④：MAX(expr)

语法：MAX(成绩) OVER( PARTITION BY 姓名 ORDER BY 班级 ASC )

描述：根据【姓名】分组，【班级】排序，然后对每个分组后的【成绩】进行求最最大值的操作。

SELECT 姓名,性别,班级,成绩, MAX(成绩) OVER(PARTITION BY 姓名 ORDER BY 班级 DESC) MAX_OVER FROM T_TEST

⑤：COUNT

语法：COUNT(成绩) OVER( PARTITION BY 姓名 ORDER BY 班级 ASC )

描述：根据【姓名】分组，【班级】排序，然后对每个分组后的【成绩】进行行数求和的操作。

SELECT 姓名,性别,班级,成绩, COUNT(成绩) OVER(PARTITION BY 姓名 ORDER BY 班级 DESC) COUNT_OVER FROM T_TEST

2、排序开窗

①：ROW_NUMBER()

语法：ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 成绩 DESC)

描述：根据【姓名】分组，【成绩】排序，然后进行数据行编号【1,2,3,4,5】。

SELECT 姓名,性别,班级,成绩, ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 成绩 DESC) ROW_NUMBER_OVER FROM T_TEST

②：RANK

语法：RANK() OVER(PARTITION BY 姓名 ORDER BY 成绩 DESC)

描述：根据【姓名】分组，【成绩】排序，然后进行数据行编号，遇到相同值，编号一样，下一个跳号【1,2,3,3,5】。

INSERT INTO T_TEST VALUES ('9', '金华', '男', '1班', '70');
INSERT INTO T_TEST VALUES ('10', '金华', '男', '1班', '60');

SELECT 姓名,性别,班级,成绩, RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) RANK_OVER FROM T_TEST

③：DENSE_RANK

语法：DENSE_RANK() OVER(PARTITION BY 姓名 ORDER BY 成绩 DESC)

描述：根据【姓名】分组，【成绩】排序，然后进行数据行编号，遇到相同值，编号一样，下一个不跳号【1,2,3,3,4】。

SELECT 姓名,性别,班级,成绩, DENSE_RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) DENSE_RANK_OVER FROM T_TEST

3、偏移开窗

①：LAG(expr,n)

语法：LAG(成绩,1) OVER(PARTITION BY 班级 ORDER BY 成绩 DESC)

描述：根据【班级】分组，【成绩】排序，然后返回当前行的前1行的成绩的值进行数据

SELECT 姓名,性别,班级,成绩, LAG(成绩,1) OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) LAG_OVER FROM T_TEST

②：LEAD(expr,n)

语法：LEAD(成绩,1) OVER(PARTITION BY 班级 ORDER BY 成绩 DESC)

描述：根据【班级】分组，【成绩】排序，然后返回当前行的后1行的成绩的值

SELECT 姓名,性别,班级,成绩, LEAD(成绩,1) OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) LEAD_OVER FROM T_TEST

③：FIRST_VALUE(expr)

语法：FIRST_VALUE(成绩) OVER(PARTITION BY 班级 ORDER BY 成绩 DESC)

描述：根据【班级】分组，【成绩】排序，然后返回当前组的第一个成绩的值

SELECT 姓名,性别,班级,成绩, FIRST_VALUE(成绩) OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) FIRST_VALUE_OVER FROM T_TEST

④：LAST_VALUE

语法：LAST_VALUE(成绩) OVER(PARTITION BY 班级 ORDER BY 成绩 DESC)

描述：根据【班级】分组，【成绩】排序，然后返回当前组的最后一个成绩的值

SELECT 姓名,性别,班级,成绩, LAST_VALUE(成绩) OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) LAST_VALUE_OVER FROM T_TEST