MySQL窗口函数能够实现某种复杂的查询

原创 创建时间:2020-05-14 访问量:0 赞:0 踩:0

先使用一个简单的例子引入问题,这个示例说明了我为什么要使用窗口函数,以及它给写sql和程序逻辑实现上带来了哪些便捷性。

例如有这样一张表window_function:

select * from window_function;

id
code
VALUE
1
ST0001
20.1
2
ST000121.5
3
ST000218.9
4ST000123.1
5ST000214.7
6
ST000122.1
7
ST000217.8
8
ST000216.4
9
ST000219.9

上表中只有两种编号ST0001和ST0002,现在我们查询该表并获得两条记录,一种编号一条,形式如下:

code
avg_valuethird_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_valuethird_value
count
ST0001
21.70
22.14
ST0002
17.54
17.85

这样就一条sql搞定了吧。

下面列出一些窗口函数:

我们这里只用了NTH_VALUE()获取窗口是第几个值。


  • 序号函数:row_number() / rank() / dense_rank()

  • 分布函数:percent_rank() / cume_dist()

  • 前后函数:lag() / lead()

  • 头尾函数:first_val() / last_val()

  • 其他函数:nth_value() / nfile()


用法就是:


    函数名([expr]) over子句

评论
 我想说:
==已经到底了==
关注: 粉丝: 积分:
工联信息网
如有问题请致邮箱:need@glxxw2018.com
不良信息反馈及建议请致邮箱:accusation@glxxw2018.com