mysql查询的一些函数_MySQL窗口函数能够实现某种复杂的查询

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