问题
在很多统计需求里,我们会有这样的问题:把一些记录某个字段分组,然后找到每个分组最小的某个列,并且返回包含这个最小列的这一行。
一个示例表test(select * from test):
gid age username
1 11 zhangsan
1 13 zhangsan
1 15 lisi
1 10 wangwu
2 20 liusanjie
2 23 wumingshi
1 10 hh
比如一个机构的人员,每个人有年龄(age),名字(username)还有一个所在group(gid)
我们经常有这样的问题:找到每个group年龄最小的成员。
直观的想法
对于这样的问题,最先想到的当然是聚集函数(aggregate function),你可能这样:
select gid,min(age) from test;
但是怎么得到年龄最小的员工的名字呢?你或许想这样写:
select gid,min(age),username from test;
可惜这样会得到一个语法错误,比如postgresql的提示:column "test.gid" must appear in the GROUP BY clause or be used in an aggregate function
为什么会这样呢?我们是根据gid来分组的,一个gid当然只有一个最小的age,但是可能会有多个不同的name,它当然没法返回给你,因为它不知道是哪个。
也许你会说,我要的就是age最小的那行对应的name,可惜min函数不提供此功能*。*
那你也许想这样:
select gid,min(age),min(username) from test;
非常遗憾,你会发现返回的名字和年龄会对不上,返回的年龄是最小的,名字也是最小的!名字还有大小?嗯,当你跟人合作写了一本书,然后被告知:排名不分先后,按姓氏笔划排列,你可能抱怨自己的姓氏了。
问题的推广
推广一些,我们可能想知道某个部门年龄最小的3个员工。
我们来回忆或者猜测一下sql是怎么实现聚集函数,或者这么说:如果让你来实现聚集函数,你会怎么实现?
聚集函数的可能实现方法(欢迎补充)
1. 排序
一种思路是根据group by的字段排序,然后对同一个group的记录根据min函数的字段排序:
select * from test order by gid,age ;
gid age username
1 10 "wangwu"
1 10 "hh"
1 11 "zhangsan"
1 13 "zhangsan"
1 15 "lisi"
2 20 "liusanjie"
2 23 "wumingshi"
然后遍历每个分组,找第一行就可以了,时间复杂度O(nlogn),主要就是排序的复杂度,空间复杂度是O(n),也就是WHERE命中的记录数。
2. Hash分组
代码如下:
Map<GroupKey,Object> map=new HashMap<GroupKey,Object>();
for each row which matches where condition
GroupKey key=row.getGroupKey();
Object val=row.getValue();
Object old=map.get(key);
if(old==null) map.put(key,val);
else map.put(key,min(old,val);
时间复杂度O(n),空间复杂度O(groupCount),可能更多,参考下面的PG自定义聚集函数部分。
根据上面的分析,数据库实现应该是第二种方式(我的猜测)。
对于找topN而不是top one:第一种方法没有任何变化,而第二种方案可能需要一个堆(Heap)这样的数据结构来高效的保存top N
解决方法
参考内容:
http://archives.postgresql.org/pgsql-performance/2003-11/msg00135.php
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
http://stackoverflow.com/questions/723054/extra-fields-with-sql-min-group-by
http://sql-ex.ru/help/select16.php
标准SQL的解决方法
Top one
思路一: 使用子查询
--SELECT * FROM test AS v
--WHERE v.age = (
-- SELECT min(age) FROM test
-- WHERE gid = v.gid
-- )
可以这样来阅读这个sql:对于test里的每一行,首先用一个子查询找到这个组最小的年龄,如果这行的年龄等于这个最小年龄,那么就是满足条件的记录。
有一点绕口?仔细想想为什么这样是正确的!
时间复杂度:如果没有索引,那么是O(n*n),如果gid有索引,那么会好一点,好的程度取决于group count,如果只有一个gid,那么就退化到O(n*n),如果每个人都在不同的group,那么会是最好的O(n)。
问题:如果一个组的最小年龄不止一行,那么所有的都会返回
思路二: 我们可以使用group by和min函数找到每个分组的最小的年龄值,然后使用自连接的技巧来找到对应的其它字段。
--select f.*
--from (
-- select gid, min(age) as minage
-- from test group by gid
--) as x inner join test as f on f.gid = x.gid and f.age = x.minage;
这样来阅读这个sql:找到每个组的最小年龄,把它放到一个临时表 x 里,然后使用内连接找到每一行其它的字段,连接的条件是gid相同而且age是最小的age
这个SQL返回和前面完全相同的结果,也会有多个最小值的问题。
时间复杂度同上,但是可能比前一个会快一些(我的猜测,具体还要看优化器),因为一次找到所以的最小值然后放到内存(临时表),但是空间要比上面多用一些。
Top N
首先解决Top2
--select *
--from test
--where age = (select min(age) from test as f where f.gid = test.gid)
-- or age = (select min(age) from test as f where f.gid = test.gid
-- and age > (select min(age) from test as f2 where f2.gid = test.gid))
我们这样阅读它:首先找到每个组年龄最小的成员,然后找到年龄第二小的。什么是年龄第二小呢?也就是除了年龄最小 where age > (select min(age) from test as f2 where f2.gid = test.gid))
外,年龄最小的
用这样的思路解决Top 3就很费劲了:首先用前面的方法找到第二小的年龄,然后在所有大于第二小的年龄里寻找最小的。
问题,如果有重复的年龄,那么可能不止Top 2或者Top 3
Top N的解决办法
什么叫最小的年龄?也就是没有其它的年龄比这个年龄小
什么叫第二小的年龄?就是比这个年龄小的只有1个
什么叫Top2的年龄?就是年龄比它们小的小于等于2个(如果多于2个,它怎么能排第二呢?)
什么叫第三小的年龄?就是比这个年龄小的只有2个
什么叫Top3的年龄?就是年龄比它们小的小于3个
--select *
--from test
--where (
-- select count(*) from test as f
-- where f.gid = test.gid and f.age < test.age
--) <= 2;
我们这样来阅读它:请找出这样的记录------和此记录同一个group的记录里年龄比我小的小于等于2个
非标准SQL的解决方法
思路一: 使用排序的方法
按照前面的解决聚集函数的方法,我们可以先排序,然后从每个组里挑选第一个。怎么从一个group里选择第一个呢?很多数据库(比如Oracle和SQL Server等)提供了First和Last这样的聚集函数。
--select t.gid,first(t.age),first(t.username)
--from (
-- select gid,age,username from test order by gid,age
-- ) as t
--group by t.gid
首先按gid和age排序,然后从每个group里选择第一行
Postgresql的实现 pg默认没有,但是它提供了create aggregate来让用户创建自定义的聚集函数,这里暂不细讲,可以参考
http://wiki.postgresql.org/wiki/First_(aggregate)
时间复杂度O(nlogn),空间复杂度O(n)
思路二:还是排序
mysql既没有提供first,也没有提供接口让用户创建聚集函数,那怎么办呢?还好mysql可以在sql里使用变量。
set @num := 0, @type := '';
select type, variety, price
from (
select type, variety, price,
@num := if(@type = type, @num + 1, 1) as row_number,
@type := type as dummy
from fruits
order by type, price
) as x where x.row_number <= 2;
没看明白?我们用Java来实现一下:
sort rows by gid,age
int lastGid=-1;
int lasAge=-1;
int rowInGroup=-1;
for(row:rows){
int gid=row.getGid();
int age=row.getAge();
if(gid!=lastGid){ //第一次出现这个gid
rowInGroup=1;
}else{
rowInGroup++;
}
if(rowInGroup<=2){ //这是我想要的!!
}
lastGid=gid;
lastAge=age;
}
对照起来应该很容易明白了。
另外出来First和Last,也可以使用其它一些数据库相关的函数,比如RANK,ROW_NUMBER等实现类似的思路。
当然First只能找到Top 1,所以要找Top N,可能需要实现First_N 这样的聚集函数,实现起来和First没有什么本质区别
思路三:还是排序(使用PG的特殊语法)
--SELECT DISTINCT ON (gid) gid, age,username
-- FROM test
-- ORDER BY gid, age;
Distinct on的细节参考文档
思路四:Hash的方法
这就只能自己实现聚集函数了。
下面讲讲在PG里的实现方法(我的PG也很弱,所以写出来的可能很丑陋,欢迎帮助改进)
Postgresql的聚集函数框架
不过在讲它的Create Aggregate之前,我们想想如果我们来设计这个框架,我们怎么做?
聚集函数可以分为两类:函数的解依赖于局部解和当前值;另一类解依赖于所有的值。
这样说起来很抽象,我们具体举两个例子。
1. 求最小值的min函数
min(3,4,5,6)=min(3,min(4,min(5,6)))。我们遍历所有的记录,然后记下当前的最小值就可以了。
max,sum与之类似,avg呢?这个得稍微思考一下: 我们需要记下前面的平均值和个数 avg(3,4,5,6)= (avg(3,4,5)*3 + 6)/4,当然这样做比先sum然后除以个数慢,但是它需要更少的临时空间。有些场合可能需要权衡时间和空间。
2. 求中位数
这个没有所有的数据是没办法求到的
因此我们的框架可能是这样(这就是PG的框架):对于满足Where条件的每一条记录,调用一个用户定义的状态转移函数,最后在group遍历完成后调用一个终态函数。
比如求min,状态转移函数可能是:
sfunc(oldMin,curr)
if(curr < oldMin) return curr;
else return oldMin;
当然可以不使用状态转移函数而先保留所以的值,然后一次扫描找出最小的,不过这样的缺点是需要空间保留所有的值(当然对于求中位数这样的问题必须这样做)
明白了这点我们来看PG的Create Aggregate命令:http://www.postgresql.org/docs/9.0/static/sql-createaggregate.html
CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
SFUNC = sfunc,
STYPE = state_data_type
[ , FINALFUNC = ffunc ]
[ , INITCOND = initial_condition ]
[ , SORTOP = sort_operator ]
)
or the old syntax
CREATE AGGREGATE name (
BASETYPE = base_type,
SFUNC = sfunc,
STYPE = state_data_type
[ , FINALFUNC = ffunc ]
[ , INITCOND = initial_condition ]
[ , SORTOP = sort_operator ]
)
最重要的就是SFUNC,STYPE和FINALFUNC,STYPE就是中间状态的类型。我们来看First的实现:
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement AS $$
SELECT $1;
$$ LANGUAGE SQL IMMUTABLE STRICT;
-- And then wrap an aggreagate around it
CREATE AGGREGATE public.first (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement
);
非常简单。首先是定义状态转移函数first_agg,它有两个参数,第一个参数是之前的最小值,每次pg都会传过来,第二个就是当前行的值。这个函数只是简单的返回第一个参数,这样最终得到的就是每个group的第一行。然后创建一个聚集函数first,指定它的状态转移函数。first没有终态函数,因为第一个值已经求出来了。
然后我们来看怎么创建聚集函数不用排序来解决top one的问题。
首先创建一个自定义类型:(pg里定义的sql函数或者pl/pgsql函数没办法接受record类型的参数,所以我使用了比较土的方法)
--CREATE TYPE grouptype AS (
-- age integer,
-- username text
--);
定义了一个自定义类型,然后是定义状态转移函数和聚集函数,非常简单
--CREATE OR REPLACE FUNCTION public.minrow_agg ( grouptype, grouptype )
--RETURNS grouptype AS '
-- BEGIN
-- IF $2.age < $1.age THEN
-- RETURN $2;
-- END IF;
-- RETURN $1;
-- END;
--' LANGUAGE 'plpgsql' IMMUTABLE STRICT;
--CREATE AGGREGATE public.minrow (
-- sfunc = public.minrow_agg,
-- basetype = grouptype,
-- stype = grouptype
--);
下面是用法,主要ROW函数会把几个字段拼成我们定义类型
--select gid, minrow(ROW(age,username)) from test group by gid
PG里其它的解法
感谢小万 luoyi的提醒,pg的window function可以提供类似排序的解法,而且比全排序应该快一些。
SELECT gid, age, username, (rank() OVER (PARTITION BY gid ORDER BY age)) as r FROM test;
这是可以的。
但是不能把rank()当成where的条件:
SELECT gid, age, username, (rank() OVER (PARTITION BY gid ORDER BY age)) as r FROM test where r=1;
必须这么写:
SELECT gid, age,username
FROM
(SELECT gid, age,username,
rank() OVER (PARTITION BY gid ORDER BY age) AS pos
FROM test
) AS ss
WHERE pos < 3;
其实这种方法和我前面说的排序,然后first的实现类似,不过会稍微好一些,不用对所有的排序,而是对每个group排序。
时间复杂度应该比排序好,但是应该不止O(n)