先使用一个简单的例子引入问题,这个示例说明了我为什么要使用窗口函数,以及它给写sql和程序逻辑实现上带来了哪些便捷性。
例如有这样一张表window_function:select * from window_function;idcodeVALUE
1ST000120.1
2ST000121.5
3ST000218.9
4ST000123.1
5ST000214.7
6ST000122.1
7ST000217.8
8ST000216.4
9ST000219.9
上表中只有两种编号ST0001和ST0002,现在我们查询该表并获得两条记录,一种编号一条,形式如下:codeavg_valuethird_valuecount
ST000121.704
ST000217.545
说明:
code:window_function表中的code编号;
avg_value:window_function表中value的按code分组平均值;
third_value:window_function表中按code分组后再按value正向排序后的第3个value值。
count:window_function表中分组统计个数。
如果没有这个third_value,也就是第三个值,我们直接用group by 就可以获得:SELECT `code`, avg(`value`) as avg_value, count(1) as count FROM `window_function` group by code;
但是现在要在加上一列,就是每个分组中的第3个值也要列出来,怎么实现?
当然如果是在正常的写程序过程中,可以先根据code分组查询出一个List,然后再循环这个List,再在数据库中查询第3个值,再赋值到List中:select `value` from `window_function` where `code` = 'ST0001' order by value asc limit 2,1;
注:limit记录从0开始。
可是要是这样做就对不起我的“懒惰”,平生最大的爱好:“懒(lazy)”。我想一条sql搞定,直接上sql语句:select t.*, nth_value(`value`, 3) over w as third_value from `window_function` t window w as (partition by `code` order by `value` asc);
这条sql是运用window窗口函数在每行上都加上一个third_value值;window as (partition by `code` order by `value` asc)
这条sql 定义了一个窗口,按code分区,按value排序;nth_value(`value`, 3) over w as third_value;
这个sql就是找出窗口中第三个value值,并作为third_value字段。
结果如下:
id code value third_value
1ST000120.1
2ST000121.5
6ST000122.122.1
4ST000123.122.1
5ST000214.7
8ST000216.4
7ST000217.817.8
3ST000218.917.8
9ST000219.917.8
third_value为null很正常,因为还没有到3条记录,所以没有,比如第1、2条记录是没有的。
那么在这个sql的基础上做一次group by就可以了,如下:select v.code, avg(v.value) as avg_value, max(v.third_value) as third_value, count(1) as count from (select t.*, nth_value(`value`, 3) over w as third_value from `window_function` t window w as (partition by `code` order by `value` asc)) v group by code;
得到的结果如下:codeavg_valuethird_valuecount
ST000121.7022.14
ST000217.5417.85
这样就一条sql搞定了吧。
下面列出一些窗口函数:
我们这里只用了NTH_VALUE()获取窗口是第几个值。序号函数:row_number() / rank() / dense_rank()
分布函数:percent_rank() / cume_dist()
前后函数:lag() / lead()
头尾函数:first_val() / last_val()
其他函数:nth_value() / nfile()
用法就是:
函数名([expr]) over子句