开窗函数 over 和分组函数和分析函数

首先说明mysql 8.0 以前不支持，oracle  hive 支持

 

先说说分组， sql 里面 使用 group by 分组，分组以后会吧分组的 值相等的几行放到一行。行数变少。

 

　　我们先假设 user 表 有用户的名字，和所属部门

　　例子：select name，deptno from user group by deptno 

 

　　本意按照部门分组，显示出名字，有的数据库不支持这种语法，因为按照部分以后同一部门组内的name可能有多个。mysql 可以支持，它显示的第一个，这个可能和我们要显示的结果有区别。我们可能想要的第2 个。

 

在说说聚合函数，分组以后做一些统计的就是聚合函数

 

　　例如：select sum(1)，deptno from user group by deptno 

 

 上面聚合函数分组以后求了部门的人数

 

 

我们提出一个问题要求取分组中的第一条 不支持开窗函数的数据库实现非常麻烦，有兴趣的可以百度一下。

　　

　　百度的写法很麻烦：但是我试了一下排序以后在查询就可以

 

　　例如：select * from (  select * from test order by test.`status` DESC) t1 group by t1.deptno

 

　　　　结果：

　　　　

 

 

 　　

　　原数据：

　　

 

 

 

支持开窗函数的数据库写法：select *from ( select rank() over( partition by xxx order by yyy ) num from t1 ) t2 where t2=1 就可以实现

 

　　over 是开窗函数 里面 有 partition by 和 order by ， partition by 是指那些数据要放到一个区（和分组类似）， order by 指的分区内部排序方式。

　　　　备注： partition by 如果没有 就都算作一个区

　　　　备注2：order by 如果缺失name算作 主键排序（或者说默认排序）

　　

　　over 前面 一般会有点别 比如 聚合函数（类似 sum 之类的），分析函数（上面的 rank）

　　

　　over 后面其实还有点东西  ROWS BETWEEN  表示 前面 函数统计的区间（默认是当前组的第一个到当前组的当前个，也就是不写等于 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   ）

 

　　常见分析行数

　　　　rank():  跳跃连续排序  类似  1,2,2,4,4,4

　　　　dense_rank() : 连续排序 类似 1,2,2,2,3,3,3,

　　　　row_num(): 生成编号 1,2,3,4,5,6

　　　　

　　ROWS BETWEEN  后面 分两个值  ROWS BETWEEN xxx AND yyy

 

　　　　大概可以这么写

　　　　　　unbounded preceding 起始行
　　　　　　unbounded following 结尾行
　　　　　　current row 当前行
　　　　　　1 preceding 前1行   数字可变
　　　　　　1 following 后1行

　　

 

总结：普通分组会让函数减少，over 开窗函数在分组的基础上不会减少数据，也可以分组统计。

 

 

 

附带oracle在线测试地址登录以后可以自己去试试开窗行数：https://livesql.oracle.com/apex/