Chinaunix首页 | 论坛 | 博客
  • 博客访问: 90344303
  • 博文数量: 19283
  • 博客积分: 9968
  • 博客等级: 上将
  • 技术积分: 196062
  • 用 户 组: 普通用户
  • 注册时间: 2007-02-07 14:28
文章分类

全部博文(19283)

文章存档

2011年(1)

2009年(125)

2008年(19094)

2007年(63)

分类:

2008-05-21 14:57:30

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等构造,理论上不复杂,大家都可以试试。

取自""
阅读(1484) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~