先使用一个简单的例子引入问题,这个示例说明了我为什么要使用窗口函数,以及它给写sql和程序逻辑实现上带来了哪些便捷性。
例如有这样一张表window_function:
select * from window_function;
id | code | VALUE |
1 | ST0001 | 20.1 |
2 | ST0001 | 21.5 |
3 | ST0002 | 18.9 |
4 | ST0001 | 23.1 |
5 | ST0002 | 14.7 |
6 | ST0001 | 22.1 |
7 | ST0002 | 17.8 |
8 | ST0002 | 16.4 |
9 | ST0002 | 19.9 |
上表中只有两种编号ST0001和ST0002,现在我们查询该表并获得两条记录,一种编号一条,形式如下:
code | avg_value | third_value | count |
ST0001 | 21.70 | 4 | |
ST0002 | 17.54 | 5 |
说明:
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
1 ST0001 20.1
2 ST0001 21.5
6 ST0001 22.1 22.1
4 ST0001 23.1 22.1
5 ST0002 14.7
8 ST0002 16.4
7 ST0002 17.8 17.8
3 ST0002 18.9 17.8
9 ST0002 19.9 17.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;
得到的结果如下:
code | avg_value | third_value | count |
ST0001 | 21.70 | 22.1 | 4 |
ST0002 | 17.54 | 17.8 | 5 |
这样就一条sql搞定了吧。
下面列出一些窗口函数:
我们这里只用了NTH_VALUE()获取窗口是第几个值。
序号函数:row_number() / rank() / dense_rank()
分布函数:percent_rank() / cume_dist()
前后函数:lag() / lead()
头尾函数:first_val() / last_val()
其他函数:nth_value() / nfile()
用法就是:
函数名([expr]) over子句