一个group里查找topN行的sql

问题

     在很多统计需求里，我们会有这样的问题：把一些记录某个字段分组，然后找到每个分组最小的某个列，并且返回包含这个最小列的这一行。

     一个示例表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)