1、窗口函数也称为OLAP函数,意思是对数据库数据进行实时分析处理,mysql目前不支持窗口函数
2、窗口函数大体分为两类
(1) 能够作为窗口函数的函数,sum avg count max min
(2) rank dense_rank row_number等专用窗口函数
3、窗口函数兼具分组和排序两种功能:
rank() over(partition by order by )其中partition by能够设定排序的对象范围,order by能够指定按照哪一列、何种顺序进行排序,partion by在横向上对表进行分组,order by决定了纵向排序的规则
通过partition by分组后的记录集合可以称为窗口,如果希望先将表中的数据分为多个部分(窗口),再使用窗口函数时,可以使用partition by选项
4、专用窗口函数,无需参数,因此通常括号中都是空白的:
(1)rank
计算排序时,如果存在相同位次的记录,则会跳过之后的位次,比如有3条记录排在第一位时,前4条记录的位次为1、1、1、4
(2)dense_rank
同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次,比如有3条记录排在第一位时,前4条记录的位次为1、1、1、2
(3)row_number
赋予唯一的连续位次
5、原则上窗口函数只能在select子句中使用,因为在dbms内部,窗口函数是对where子句或者group by子句处理后的结果进行操作,所以在select子句之外使用没有意义
6、所有聚合函数都可以用作窗口函数,其语法和专用窗口函数完全相同
7、以自身记录(当前记录)作为基准进行统计,就是将聚合函数当作窗口函数使用时的最大特征,比如
avg(price) over(order by id rows 2 preceding)
其中的rows(行)和preceding(之前)两个关键字将框架指定为截止到之前2行,也就是将作为汇总对象的记录限定为如下最靠近的3行:自身(当前记录)、之前1行记录、之前2行记录,也可以将preceding替换为follwing,则为向后
8、窗口函数oder by只是用来决定窗口函数按照什么样的顺序进行计算,不同于select中的order by,不会对结果的顺序产生影响
9、将聚合函数作为窗口函数使用时,会以当前记录为基准来决定汇总对象的记录
10、grouping运算符包含以下3种:
(1) rollup
rollup是卷起的意思,比如卷起百叶窗、窗帘卷等,形象说明了该操作能够得到像从小计到合计这样,从最小的聚合级开始,聚合单位逐渐扩大的结果
select type,sum(price) from product group by rollup(type)
从语法上来讲,就是将group by子句中的聚合键清单像rollup(列1,列2…)这样使用,就是一次计算出不同聚合键组合的结果,上例一次计算出两种组合的结果:
group by()
group by(type)
第一个表示没有聚合键,也就相当于没有聚合键,这时会得到全部数据的合计行记录,该合计行记录称为超级分组记录,这行记录的键值并不明确,因此会默认使用null作为聚合键
为了避免混淆,sql提供了一个用来判断超级分组记录的null的特定函数——grouping,该函数在其参数列的值为超级分组记录所产生的null时返回1,其他情况返回0,这样就能够分辨超级分组记录中的null和原始数据本身的null了
select case when grouping(type)=1 then ‘各类合计’ else type end
,sum(price)
from product
group by rollup(type)
在实际业务中如果需要获取包含合计或者小计的汇总结果时,就可以使用rollup和grouping函数来实现了
(2) cube
cube是立方体的意思,就是将group by子句中聚合键的所有可能的组合汇总到结果集中到一个结果中,因此组合中的个数就是2的n次方,n是聚合键的个数,rollup的组合个数是n+1
(3) grouping sets
用于从rollup或cube的结果中取出部分记录,从中取出个别条件对应的不固定的结果