SQL的开窗函数

开窗函数也属于分析函数，与聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

格式：函数名（列）over（选项）

OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。

OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

PARTITION BY 子句：

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。

ORDER BY子句：

开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。

语法：ORDERBY字段名 RANGE|ROWSBETWEEN边界规则1AND边界规则2

注意：PARTITION BY子句和ORDER BY 可以共同使用，从而可以实现更加复杂的功能

---

新建临时表

CREATETABLET_Person (FNameVARCHAR(40),FCityVARCHAR(40),FAge INT,FSalaryINT);

---

插入数据：

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Tom','BeiJing',20,3000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Tim','ChengDu',21,4000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Jim','BeiJing',22,3500);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Lily','London',21,2000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('John','NewYork',22,1000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('YaoMing','BeiJing',20,3000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Swing','London',22,2000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Guo','NewYork',20,2800);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('YuQian','BeiJing',24,8000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Ketty','London',25,8500);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Kitty','ChengDu',25,3000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Merry','BeiJing',23,3500);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Smith','ChengDu',30,3000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Bill','BeiJing',25,2000);

INSERT INTO xiaoxiao.T_Person(FName,FCity,FAge,FSalary)

VALUES('Jerry','NewYork',24,3300);

---

（1）row_number() 函数语法：

语法：ROW_NUMBER() OVER(PARTITIONBYCOL1ORDERBY COL2)

功能：表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）row_number() 返回的主要是“行”的信息，并没有排名

例如：

SELECT FName, FSalary,FAge,

ROW_NUMBER() OVER(ORDER BY fsalary desc) id

FROM T_Person;

结果：

 

---

（2）rank()

语法：RANK ( ) OVER([query_partition_clause] order_by_clause )

功能：rank()是跳跃排序，有两个第二名时接下来就是第四名。

例如：

SELECT FNAME, FSALARY,FAGE,

RANK() OVER(ORDER BY FSALARY DESC) RANK_ID

FROM T_PERSON;

结果：

 

---

（3）dense_rank()语法

语法：dense_RANK ( ) OVER([query_partition_clause] order_by_clause )

功能：dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。

例如：

SELECT FName, FSalary,FAge,

DENSE_RANK() OVER(ORDER BY fsalary desc) DENSE_RANK_ID

FROM T_Person;

结果：

 

---

（4）分组排序累积求和

语法：SUM(列名2)OVER(PARTITION BY 列1 ORDER BY 列2)

注意：row可以换成range，是按照范围进行定位的

栗子：sum(列2 ) over (PARTITION BY 列1  order by 列2 range between unbounded preceding and current row) 

例如：

SELECT FNAME,

      FCITY,

      FAGE,

      FSALARY,

      SUM(FSALARY) OVER(PARTITION BY fage ORDER BY FSALARY) FSALARY_SUM

  FROM T_PERSON

结果：

 

释义：按照fage 分组，FSALARY列累积求和，ORDER BY FSALARY，假如FSALARY一样，需要同时累积求和，类似第1、2、3行。

---

（5）按列统计个数

语法：COUNT(*) OVER (ORDER BY 列 desc)

功能：按照列统计统计个数，并且降序排列，注意后面的个数是排序累积个数。

例如：

select distinct fcity,

COUNT(*) OVER (ORDER BY fcity desc)

from T_Person

结果：

 

释义：NewYork 统计个数为3个，London有3个，由于是统计个数累积求和，所以后面的个数是6，以此类推。

---

（6）max求到目前行的最大值

语法：MAX(列2)OVER(PARTITION BY 列1 ORDERBY 列2)

功能：按列1分组求列2的最大值，并且按照 列2排序

例如：

SELECT fname,fcity,FSalary,

MAX(FSalary) OVER(PARTITION BY fcity order by fsalary desc) 同城薪资最高

FROM T_Person;

结果：

 

---