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

先使用一个简单的例子引入问题，这个示例说明了我为什么要使用窗口函数，以及它给写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

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;

得到的结果如下：

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子句