PostgreSQL利用用户定制的聚集函数选取每个分组的若干行数据

From PgsqlWiki

Jump to: navigation, search

需求

有个很常见的需求：比如，有个表，里面有主键，然后有几个字段，然后要求我们根据某个字段的每个不同的值，取出表中的若干行出来。

这种需求在采样和分类输出类的应用中非常常见。

 分析

既然是分类输出，那么我们肯定想到了 group by，现在问题来了，group by 之后，在SELECT的输出列表里，只能出现非 group by 字段的聚集，而不能出现这些字段的具体值，PostgreSQL 这样做是符合集合数学的定义的，但是却让我们用起来有些不爽。据我所知 MySQL 很多时候就是直接做了这样的输出，但是多少是违反标准和数学理论。

那么PostgreSQL里头解决方法是什么呢？

这里基本的解决方法是，创建自己的用户定制的聚集函数，由这个聚集函数生成一个主键的集合，然后通过这个主键集合，用子查询检索出所有需要的数据行。

这里我们需要注意的是：聚集函数必须返回标量结果，所以我们需要把主键集合放在一个标量里，这里比较通用的做法是利用数组来存储这个集合。然后利用PostgreSQL的不同的数组的过程，来实现近乎 0 编码的通用定制。

举例

举例，我有一个柜员表，表结构如下，这个表可以通过 pgbench -i 获得：

laser=# \d tellers
资料表 "public.tellers"
栏位 | 型别 | 修饰词
----------+---------------+----------
tid | integer | not null
bid | integer |
tbalance | integer |
filler | character(84) |

这里的 bid 表示连锁店铺的ID，tid表示柜员的ID，我现在需要从每个连锁店中找出随便 5 个柜员来，这个事情应该怎么做？

我不太清楚mysql怎么做（也许很简单），不过在PG里稍微需要用一些模块，这个模块就是 intarray 模块，我的做法是：

• 创建一个聚集函数：

CREATE AGGREGATE int_array_accum ( BASETYPE=_int4,
SFUNC=intarray_push_array, stype=_int4);

这个聚集函数的目的很简单，就是把两个整数数组拼接起来，返回之。具体的文档，可以参考：

和 intarray 的文档：

以及 intagg 模块的文档：

其中用到的拼接函数 intarray_push_array 其实是 intarray 的两个数组相加（+号）的处理函数。可以在intarray的初始化SQL文件 _int.sql 里面找到（在4E的RPM包里是/usr/local/pgsql/share/contrib/_int.sql），

• 执行下面的查询：

select * from tellers where
( select int_array_accum(a.array) from
(select subarray(int_array_aggregate(tid),0,5) as array from tellers
group by bid) a)
@@ tid::text::query_int;

就会得到期望的输出。这个查询有点意思，我们分解看看：

最内层的：

  select subarray(int_array_aggregate(tid),0,5) as array from tellers group by bid;

我们其实是对每个 bid （连锁店ID），然后调用 int_array_aggregate 聚集函数（见intagg）的文档，把每个 bid 输出的 tid 组合成一个整数数组，int_array_aggregate是intagg模块提供的一个聚集函数，因为group by之后，在SELECT的输出列表里只能出现group by的id和聚集函数，所以这里必须聚集一下。之后用 intarray的subarray取输出的头5个元素。

然后，将上面这个查询的输出，再次用聚集函数拼成一个更大的数组：

  select int_array_accum(a.array) from
  (select subarray(int_array_aggregate(tid),0,5) as array from tellers
  group by bid) a;

因为聚集函数不能嵌套，所以我们用一个子查询骗骗它。然后就得到一个N长的整数数组，里头的所有 ID就是我们需要取出来的ID，于是，最后：

  select * from tellers where
  ( select int_array_accum(a.array) from
  (select subarray(int_array_aggregate(tid),0,5) as array from tellers
  group by bid) a)
  @@ tid::text::query_int;

把所有在这个数组里头的ID行的对应数据都找出来，我们这里用了intarray 的 @@操作符和query_int类型，不过这个其实也可以用ANY和IN等构造，理论上不复杂，大家都可以试试。

取自""